Help Urgently needed

[Mitsuki]

Hi ...

This is driving me crazy :(

I have an editable datagrid where the user updates the statuse .. This works fine ... Now when the status is updated the ItemNo + Item Name must be saved in another table therefore I did an insert code ...


But

Am kinda confused so please bare with me ...

This is what I did ... Is it correct?


Public Sub DataGrid1_Update(Source As Object, E As DataGridCommandEventArgs)

Dim objConnection As OleDBConnection
Dim objCommand As OleDBCommand
Dim objAdapter As OleDBDataAdapter
Dim objDataSet As DataSet
Dim strOleDBQuery As String

'Dim txtItemNo As TextBox = E.Item.Cells(1).Controls(0)
'Dim txtItemName As TextBox = E.Item.Cells(2).Controls(0)
'Dim txtSenderName As TextBox = E.Item.Cells(3).Controls(0)
'Dim txtSerial As TextBox = E.Item.Cells(4).Controls(0)
'Dim txtReceivedDt As TextBox = E.Item.Cells(5).Controls(0)
'Dim txtModel As TextBox = E.Item.Cells(6).Controls(0)
'Dim txtQuantity As TextBox = E.Item.Cells(7).Controls(0)
Dim txtStatus As TextBox = E.Item.Cells(8).Controls(0)


Dim strUpdateStmt As String

strUpdateStmt = "UPDATE [tblItems] SET " & _
"[Status] = '" & txtStatus.Text & "' " & _
"WHERE ItemNo = " & E.Item.Cells(1).Text & ""


objConnection = New OleDBConnection("Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\HASEM_sys\HASEM_sys.mdb")

objCommand = New OleDBCommand(strUpdateStmt, objConnection)
objConnection.Open()
objCommand.ExecuteNonQuery()


DataGrid1.EditItemIndex = -1
BindData()
objConnection.Close()

Dim queryString As String = "INSERT INTO [tblBF] ([ItemNo], [ItemName]) VALUES (@ItemNo, @ItemName)"

Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
Dim dbCommand.CommandText = queryString
Dim dbCommand.Connection = dbConnection

Dim dbParam_itemNo As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_itemNo.ParameterName = "@ItemNo"
dbParam_itemNo.Value = itemNo
dbParam_itemNo.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_itemNo)

Dim dbParam_ItemName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_ItemName.ParameterName = "@ItemName"
dbParam_ItemName.Value = ItemName
dbParam_ItemName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_ItemName)


Dim rowsAffected As Integer = 0
dbConnection.Open
rowsAffected = dbCommand.ExecuteNonQuery
dbConnection.Close


End Sub


In bold is the insert ...

All what I am trying to do here is once the user updates in the same time the ItemNo and ItemName will be inserted in the BF table ... Am I doing it right?




P.S - I really need your help plzzzzzzzzzzzzzzzzzzzzzzzzzzz :'(

[SWI03]

Dim rowsAffected As Integer = 0
dbConnection.Open
rowsAffected = dbCommand.ExecuteNonQuery
dbConnection.Close



this part isn't doing anything. you are setting the rowsaffected to zero and telling it the dbcommand.executenonquery equals zero.

think you would want an if/else statement in there.

If rowsAffected > 0 Then

dbCommand.ExecuteNonQuery()

Else

End If

But you shouldn't need that if you just execute the dbCommand.ExecuteNonQuery right after the update query. Plus I wouldnt bind back to the datagrid until you have done all your database updating and inserting. Think this could actually be the problem because you are resetting the index of the row to update before calling the insert and it might not know what id to insert into the database. You could also just use one connection if it is connecting to the same database.

Put this at the end to reset the index and bind back to the datagrid.
DataGrid1.EditItemIndex = -1
BindData()
objConnection.Close()



have you tried flagging this step when you are debugging it to see if it is actually doing anything or trying to?

[Mitsuki]

Thank you v.much