Datagrid won't update database

[lorena]

I have a grid which should (in theory) allow the user to enter a new value for a field and write that value to the database. The record does not update when the user clicks "Update". I have the grid set so that the event is "dgRecs_UpdateRow" on the grid. I put a label on the form to show the values of the parameters being written and the parameter which should change, never does.

Here is my code:

Sub dgRecs_UpdateRow(ByVal sender As Object, ByVal e As DataGridCommandEventArgs)

Label2.Text = ""

If Not Page.IsValid Then Exit Sub

irecordID = dgRecs.DataKeys(e.Item.ItemIndex)

Dim Acc As TextBox = e.Item.Cells(12).Controls(0)

Dim objConn As OleDb.OleDbConnection

Dim objCommand As OleDb.OleDbCommand

objConn = New OleDb.OleDbConnection(strConnString)

objCommand = New OleDb.OleDbCommand(strSQL, objConn)

strSQL = "UPDATE [toolroom] " & _

" set [ACCEPT] = @accParam, " & _

" WHERE [REC_ID] = @recordIDParam "

Dim accParam As New OleDb.OleDbParameter("@accParam", OleDb.OleDbType.VarChar, 25)

accParam.Value = Acc.Text

objCommand.Parameters.Add(accParam)

'Record ID

Dim recordIDParam As New OleDb.OleDbParameter("@recordIDParam", OleDb.OleDbType.Single)

recordIDParam.Value = irecordID

objCommand.Parameters.Add(recordIDParam)

objConn.Open()

objCommand.ExecuteNonQuery()

Label2.Text = recordIDParam.Value & "," & accParam.Value

objConn.Close()

dgRecs.EditItemIndex = -1

ddlView.SelectedIndex = -1

BindData()

End Sub

I would REALLY appreciate some help with this. Thanks

[eramgarden]

I'd say run the update statement in the database with the values u have to make sure your sql is correct. This might give u a better idea.

[ombun2]

i've just skimmed through your code but in case this helps you here is an example using sql instead of ole. I know that this code works at least and will update the database.

 

Dim Key As String = DataGrid2.DataKeys(e.Item.ItemIndex).ToString()

 

Dim Command As String = "DELETE Assets WHERE AssetID = " & Key

 

Dim objComm As New SqlClient.SqlCommand()

 

SqlConnection1.Open()

objComm.Connection = SqlConnection1

objComm.CommandText = Command

objComm.ExecuteNonQuery()

SqlConnection1.Close()

 

yeah, boy was I ever surprised when I've put delete columns, select columns, page navigation, etc on a datagrid and nothing happened when I clicked them...then I realized that even this monster control won't write the code for me...especially when it comes to updating it's some really freaky stuff and this is why especially...

 

most often we load data from a database into a dataset with multiple tables...from that we create a dataview, and that is what we bind to the datagrid, so we have 3 layers between the edit button and the database, how does one associate what happens on the grid with the record that should be edited in the database. this is where a little unknown fact comes in: the datagrid.datakeyfield which when set to the primary key will create a mapping that will allow us to retrieve the primary key for the selected row by using something like

Dim Key As String = DataGrid2.DataKeys(e.Item.ItemIndex).ToString()

 

from then on it should be easy.

 

now there are different methods to update the grid...the one above is one where you do all the work and define the string as opposed to commandbuiler objects and adapter.update statements, but I can't see why anybody would not want to use this...it just gives one so much control over what happens...

 

i hope at least some of this helped

[lorena]

Thanks!

 

I finally wound up going back and rebuilding the grid from scratch. Whatever I had that was hosed up seems to be corrected.

Now I am trying to go back and make Template Columns with Edit Templates and validation. Wish me luck :)

Thanks for your help