Datagrid won't update database

L

lorena

Centurion
Joined
Oct 23, 2003
Messages
134
Location
Phoenix, Arizona
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
 
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.
 
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
 
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
 
Back
Top