Problem with updating records

[vcharles]

Hello,

I am trying to update a table based on row selected in a grid (C1TrueDBGrid1.Columns(1).Value), but I am getting the following error:

Update unable to find TableMapping['Table'] or Data Table 'Table'

Can you please help me locate the error in my code.

Code:

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\AOPT2002org.mdb;Persist Security Info=True;Jet OLEDBatabase Password=testaopupdate"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

Dim objDataAdapter As New OleDb.OleDbDataAdapter("select * from AOP5", objConnection) 'where country in(SELECT COUNTRY FROM AOP5 WHERE SN like " & "'" & result & "'" & ")order by country", objConnection)
'dataset object
Dim objDataSet As New DataSet
'fill dataset
objConnection.Open()
'************************************
objDataAdapter.Fill(objDataSet, "Chapter")
MsgBox(C1TrueDBGrid1.Columns(1).Value)
Try
objDataAdapter.Update(objDataSet)
objDataAdapter.SelectCommand.CommandText = ("INSERT INTO AOP5(NSN) VALUES('" & C1TrueDBGrid1.Columns(1).Value & "')")

objDataSet.Clear()
objDataAdapter.Update(objDataSet)
objDataAdapter.Fill(objDataSet)
Catch ex As Exception
MsgBox(ex.Message)
Exit Sub
End Try

Thanks,

Products used ACCESS 2003, VS 2008.

Victor

 

[jebenson]

Change this line:

objDataAdapter.Update(objDataSet)

to this:

objDataAdapter.Update(objDataSet[red], "Chapter"[/red])

When using a DataSet, you have to tell it which table you want to update, even if there is only 1 table.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[vcharles]

Hello,
I need to update a link Table with the data I select from multiple grids, the problem is only the last command statement takes effect, therefore I am only updating the link table from the last command statement, is there a way to update the link table with all the command statements?


Thanks,

Code:

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aop29.mdb"
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
Dim sql As String = "Select * from Link_TableA"
Dim daaop5 As New OleDb.OleDbDataAdapter(sql, objConnection)
Dim ds As New DataSet()
Dim dsA As New DataSet()
daaop5.Fill(ds)
daaop5.Update(ds)
C1TrueDBGrid1.AllowAddNew = True

daaop5.SelectCommand.CommandText = ("INSERT INTO Link_TableA(COMPANY_ID) VALUES('" & C1TrueDBGrid1.Columns(0).Value & "')")
daaop5.SelectCommand.CommandText = ("INSERT INTO Link_TableA(Receiver_ID) VALUES('" & C1TrueDBGrid2.Columns(0).Value & "')")

daaop5.Fill(ds)
daaop5.Update(ds)

 

[jebenson]

Try something like this:

Dim cmdCompany As OleDbCommand
Dim cmdReceiver as OleDbCommand

Dim CompanySQL As String
Dim ReceiverSQL As String

Dim objConnection As New OleDb.OleDbConnection(ConnectionString)

Dim Trans As OleDbTransaction

CompanySQL = "INSERT INTO Link_TableA(COMPANY_ID) VALUES(@COMPANY_ID)"

cmdCompany = New OleDbCommand(CompanySQL, objConnection)

cmdCompany.Parameters.Add("@COMPANY_ID")

cmdCompany.Parameters("@CompanyID").Value = C1TrueDBGrid1.Columns(0).Value



ReceiverSQL = "INSERT INTO Link_TableA(Receiver_ID) VALUES(@Receiver_ID)"

cmdReceiver = New OleDbCommand(ReceiverSQL, objConnection)

cmdReceiver.Parameters.Add("@Receiver_ID")

cmdReceiver.Parameters("@Receiver_ID").Value = C1TrueDBGrid2.Columns(0).Value


Try
Trans = objConnection.BeginTransaction

cmdCompany.Transaction = Trans

cmdCompany.ExecuteNonQuery()

cmdReceiver.Transaction = Trans

cmdReceiver.ExecuteNonQuery()

Trans.Commit

Catch ex As Exception
'an error occurred, so rollback the transaction
If Not Trans is Nothing Then
Trans.RollBack
End If

MsgBox("Error saving data.")
MsgBox(ex.Message)

End Try

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[vcharles]

Hello,

I'm getting the following error:

The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not String objects.

In line : cmdCompany.Parameters.Add("@COMPANY_ID")

Can you please help me fix that part of the code.

Thanks,

Victor

 

[vcharles]

Hello,

I got it to work partially with the code below, the problem is the data from each grid is saved in different records, is there a way to save the data from both grids in one record?. I don't don't know how to concatenate the code, perhaps that would be the solution.

Thanks,

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aop29.mdb"
Dim cmdCompany As OleDbCommand
Dim cmdReceiver As OleDbCommand

Dim CompanySQL As String
Dim ReceiverSQL As String

Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
objConnection.Open()
Dim Trans As OleDbTransaction

CompanySQL = "INSERT INTO Link_TableA(Company_ID) VALUES(@Company_ID)"

cmdCompany = New OleDbCommand(CompanySQL, objConnection)

cmdCompany.Parameters.AddWithValue("@Company_ID", C1TrueDBGrid1.Columns(0).Value)

ReceiverSQL = "INSERT INTO Link_TableA(Receiver_ID) VALUES(@Receiver_ID)"
cmdReceiver = New OleDbCommand(ReceiverSQL, objConnection)

cmdReceiver.Parameters.AddWithValue("@Receiver_ID", C1TrueDBGrid2.Columns(0).Value)

Try
Trans = objConnection.BeginTransaction

cmdCompany.Transaction = Trans

cmdCompany.ExecuteNonQuery()

cmdReceiver.Transaction = Trans

cmdReceiver.ExecuteNonQuery()

Trans.Commit()

Catch ex As Exception
'an error occurred, so rollback the transaction
If Not Trans Is Nothing Then
Trans.Rollback()
End If

MsgBox("Error saving data.")
MsgBox(ex.Message)

 

[jebenson]

CompanySQL = "INSERT INTO Link_TableA(Company_ID, Receiver_ID) VALUES(@Company_ID, @Receiver_ID)"

cmdCompany = New OleDbCommand(CompanySQL, objConnection)

cmdCompany.Parameters.AddWithValue("@Company_ID", C1TrueDBGrid1.Columns(0).Value)

cmdCompany.Parameters.AddWithValue("@Receiver_ID", C1TrueDBGrid2.Columns(0).Value)



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[vcharles]

Thank you for the code, the only problem is I'm selecting from multiple grids and some of them are not mendatory, is there a way to use the insert command for only the grids selected?

CompanySQL = "INSERT INTO Link_TableA(Company_ID, Receiver_ID) VALUES(@Company_ID, @Receiver_ID)"


For example the grid Receiver_Email_Address may not be selected, therefore if included in the insert command above, it would give me an error.

Thank you again for all your help.

Victor

 

[jebenson]

Well, assuming you have some way of determining if a particular grid is selected, you can do this:

CompanySQL = "INSERT INTO Link_TableA(Company_ID, Receiver_ID, Receiver_Email_Address) VALUES(@Company_ID, @Receiver_ID, @Receiver_Email_Address)"

cmdCompany.Parameters.AddWithValue("@Company_ID", C1TrueDBGrid1.Columns(0).Value)

cmdCompany.Parameters.AddWithValue("@Receiver_ID", C1TrueDBGrid2.Columns(0).Value)

If <Grid Is Selected> Then
cmdCompany.Parameters.AddWithValue("@Receiver_Email_Address", C1TrueDBGrid3.Columns(0).Value)
Else
cmdCompany.Parameters.AddWithValue("@Receiver_Email_Address", DBNull.Value)
End If

Note: In place of <Grid Is Selected> put your logic to determine if the email address grid is selected.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[vcharles]

I will work on it and get back to you next week.

Thanks,

Victor