Datagrid and update command

[Mitsuki]

Hi I need your help with this please :brrr:
I am retreiving items from Access Database to a datagrid
and this works fine :tntworth: ... I have and edit options where I want the user to be able to edit the status only!
(MS ACCESS and ASP.NET)
I did the coding but it doesnt work ..

PHP: Select all

Sub DataGrid1_Update(Source As Object, E As DataGridCommandEventArgs)
 
             Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwr"& _
                "oot\HASEM_sys\HASEM_sys.mdb"
             Dim myConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)

            Dim Status As TextBox
            Status = Ctype(E.Item.Cells(7).Controls(0), TextBox)
            Dim ItemNo As TextBox
            ItemNo = Me.DataGrid1.DataKeys(E.Item.ItemIndex)
            Dim strUpdateStmt As String

            strUpdateStmt = "UPDATE tblItems SET Status = @Status WHERE ItemNo = @ItemNo"
            Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
            dbCommand.CommandText = strUpdateStmt
            dbCommand.Connection = myConnection
            Dim dbParam_status As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_status.ParameterName = "@Status"
            'Call the Text property of the Status field as it is a TextBox control.
            dbParam_status.Value = Status.Text
            dbParam_status.DbType = System.Data.DbType.String
            dbCommand.Parameters.Add(dbParam_status)
            Dim dbParam_itemNo As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
            dbParam_itemNo.ParameterName = "@ItemNo"
            dbParam_itemNo.Value = Me.DataGrid1.DataKeys(E.Item.ItemIndex)
            dbParam_itemNo.DbType = System.Data.DbType.Int32
            dbCommand.Parameters.Add(dbParam_itemNo)

            'Now execute the query against the database
            dbCommand.ExecuteNonQuery
            'You need to create a datasource the same way you did when you originally bound the control.
            connectionString  = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\HASEM_sys\HASEM_sys.mdb"
            Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString)
            Dim queryString = "SELECT ItemNo, ItemName, SenderName, SerialNo, ReceivedDt, Model, Quantity, Status From tblItems where Year(ReceivedDt)=Year(Now) And Month(ReceivedDt)=Month(Now) And Day(ReceivedDt)=Day(Now)"
            dbCommand = New System.Data.OleDb.OleDbCommand
            dbCommand.CommandText = queryString
            dbCommand.Connection = dbConnection
             Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
             dataAdapter.SelectCommand = dbCommand
             Dim dataSet As System.Data.DataSet = New System.Data.DataSet
             dataAdapter.Fill(dataSet)
            'Create a new view.
            Dim oView As New DataView(dataSet.Tables(0))
            'Set up the data grid and bind the data.
            DataGrid1.DataSource = oView
            'Now that your data is updated you need to repopulate the datagrid and in the process deselect the selected row.
            DataGrid1.EditItemIndex = -1
            DataGrid1.DataSource = dataSet
 
            Finally bind the source to the control and you should see you changes.
            DataGrid1.DataBind()
    End Sub 

ERROR:
Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
Source Error:

Line 63:
Line 64: Dim ItemNo As TextBox
Line 65: ItemNo = Me.DataGrid1.DataKeys(E.Item.ItemIndex)
Line 66:
Line 67: Dim strUpdateStmt As String

:chomp: Help please