Problem with updating records using transaction

[victor64]

Hello,

I am trying to update a link table with values from multiple datagrids, the field types in the link table are numbers and the value selected from the datagrids are also numbers.

I am getting the following error:

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

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


Any ideas on how to fix this error?

Thanks in advance

Victor

Code:

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)

Dim Trans As OleDbTransaction

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

cmdCompany = New OleDbCommand(CompanySQL, objConnection)

cmdCompany.Parameters.Add("@Company_ID") *****ERROR LOCATION

cmdCompany.Parameters("@Company_ID").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

[ForumAccount]

try:

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

instead of:

cmdCompany.Parameters.Add("@Company_ID") *****ERROR LOCATION
cmdCompany.Parameters("@Company_ID").Value = C1TrueDBGrid1.Columns(0).Value

Also, what is the value of "C1TrueDBGrid1.Columns(0).Value" at that line?

[victor64]

The value is a number (ID column of the grid).

Will try your suggestion and let you know the results.

Thanks,

[victor64]

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)

[ForumAccount]

Please use code or vb.net tags when posting code. I see what you mean though. You are doing two insert statements, therefore 2 records. To have them in one record you simply change your insert statement. Your code should look like this:

        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(@Company_ID, @Receiver_ID)" 
        cmdCompany = New OleDbCommand(CompanySQL, objConnection) 
        cmdCompany.Parameters.AddWithValue("@Company_ID", C1TrueDBGrid1.Columns(0).Value) 
        cmdCompany.Parameters.AddWithValue("@Receiver_ID", C1TrueDBGrid1.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

[victor64]

It works great. THANK YOU.

My project includes 10 data grids to update the table, not all the grids are mendatory, my concern with the syntax is, what if no data is entered for a particular Grid, how would that afferct the code?, wouldn't I get an error message?

I will try it, but I think I may run into some problems.

Thanks,

Victor

[ForumAccount]

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.

[victor64]

Will Do. I was thinking of using "On Error Resume Next", but that may not work.

Thank you again for all your Help.

Have a Great Weekend.

Victor