How to update table from multiple grid selection

[victor64]

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)

[ForumAccount]

Hey Victor, is this post resolved as well? You can apply the code I gave you here: http://www.vbforums.com/showthread.php?t=567736

[victor64]

Hello,

I am still trying to figure out how to avoid the errors which will occur when some of the grids are not selected to update the datable.

"If the values of non-mandatory cells are System.DBNull.Value or Nothing then yes you will get errors. You will have to do checks for this."

Can you please help me figure out the syntax to resolve this problem?

Thanks,

Victor

[ForumAccount]

If Value IsNot Nothing AndAlso Value IsNot System.DBNull.Value Then 
     'code here will only execute if Value is both: Not Nothing and Not DBNull 
End If

System.DBNull is actually a class in .NET

[victor64]

Please take a look at the code that I'm using, if Receiver is not selected from the grid, I would get an error because the parameters are hard coded, how do I incorporate your code to fix this problem? I have many more grids to slect from, that are not mandatory.

Code:

Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\aop29.mdb"
Dim cmdCompany As OleDbCommand
Dim CompanySQL As String
Dim objConnection As New OleDb.OleDbConnection(ConnectionString)
objConnection.Open()
Dim Trans As OleDbTransaction
CompanySQL = "INSERT INTO Link_TableA(Company_ID, Receiver_ID, ) VALUES(@Cmpany_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)

Try
Trans = objConnection.BeginTransaction
cmdCompany.Transaction = Trans
cmdCompany.ExecuteNonQuery()
Trans.Commit()
Catch ex As Exception
If Not Trans Is Nothing Then
Trans.Rollback()
End If
MsgBox("Error saving data.")
MsgBox(ex.Message)
End Try

Thank you.

Victor