can't figure out how to insert a row to my access table

  • Thread starter Thread starter Voyager
  • Start date Start date
V

Voyager

Hi,
I have an access table called contact with about 10 columns. I have
been trying to write code to insert a new row with no success. My eyes
are in pain!
The first row is an autonumber contactid. The following procedure
gives me an error at the line markes with <<--------- with the
message: Procedure call or argument is not valid. If you cannot solve
this problem, is there a sample procedure I can try to use... Thanks
for your help, I have been at this for a while now and VB NET help
files are a disaster.

SQL = "INSERT INTO Contact (FirstName, LastName) VALUES ('?', '?')"
Dim catDA As OleDbDataAdapter = New OleDbDataAdapter ("SELECT
FirstName, LastName FROM Contact", mDBConn)

catDA.InsertCommand = New OleDbCommand(SQL, mDBConn)
catDA.InsertCommand.CommandType = CommandType.Text
mDBConn.Open()

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS)

aDataRow = catDS.Tables(0).NewRow
aDataRow.Item("FirstName") = "???"
aDataRow.Item("LastName") = "???"
catDS.Tables(0).Rows.Add(aDataRow)

catDA.Update(catDS) <<---------
 
U¿ytkownik "Voyager said:
Hi,
I have an access table called contact with about 10 columns. I have
been trying to write code to insert a new row with no success. My eyes
are in pain!
The first row is an autonumber contactid. The following procedure
gives me an error at the line markes with <<--------- with the
message: Procedure call or argument is not valid. If you cannot solve
this problem, is there a sample procedure I can try to use... Thanks
for your help, I have been at this for a while now and VB NET help
files are a disaster.

SQL = "INSERT INTO Contact (FirstName, LastName) VALUES ('?', '?')"
Dim catDA As OleDbDataAdapter = New OleDbDataAdapter ("SELECT
FirstName, LastName FROM Contact", mDBConn)

catDA.InsertCommand = New OleDbCommand(SQL, mDBConn)
catDA.InsertCommand.CommandType = CommandType.Text
mDBConn.Open()

Dim catDS As DataSet = New DataSet
catDA.Fill(catDS)

aDataRow = catDS.Tables(0).NewRow
aDataRow.Item("FirstName") = "???"
aDataRow.Item("LastName") = "???"
catDS.Tables(0).Rows.Add(aDataRow)
Click to expand...

Working example:

Sub Main()
Dim sql As String
Dim mDbConn As New OleDb.OleDbConnection
mDbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=E:\MyDb.mdb;"

Dim catDA As OleDbDataAdapter = _
New OleDbDataAdapter("SELECT FirstName, LastName FROM Contact",
mDbConn)
'defininig insert command
Dim cmdIns As New OleDbCommand
sql = "INSERT INTO Contact (FirstName, LastName) " + _
"VALUES (?, ?)"
cmdIns = New OleDbCommand(sql, mDbConn)
cmdIns.CommandType = CommandType.Text
'defining parameters
cmdIns.Parameters.Add("FName", _
OleDbType.VarChar, 50, "FirstName")
cmdIns.Parameters.Add("LName", _
OleDbType.VarChar, 50, "LastName")
catDA.InsertCommand = cmdIns

'filling dataset
mDbConn.Open()
Dim catDS As DataSet = New DataSet
catDA.Fill(catDS)

'adding one row
Dim aDataRow As DataRow = catDS.Tables(0).NewRow
aDataRow.Item("FirstName") = "Bill"
aDataRow.Item("LastName") = "Cosby"
catDS.Tables(0).Rows.Add(aDataRow)

'updating database
Try
catDA.Update(catDS)
Catch ex As Exception
MsgBox(ex.Message)
End Try

mDbConn.Close()
End Sub

Regards,
Grzegorz
 
Back
Top