Search then Edit Datagrid

[syno]

Hi Guys

I have a textbox and button which performs a simple text search on my database. My datagrid also has simple edit, update and cancel controls where I can update my database from my datagrid with values I insert into a corresponding textbox. However when I try to edit the record that is returned from my text search it is not the record returned that gets updated. Its the first record in the database. This is also true if the text search returns, for example three records. If I then go to edit the third record returned. It will update the third record in the database rather then the record that was actually found which may actually have been, for example the tenth record in the database.

How do I set the datagrid to edit the actual record returned from my text search?

Thank you

 

[DotNetGnat]

what is the datakey that you have used on your datagrid...

make sure that you use the database record id so that edit stuff occurs on the actual intended record..

-DNG

 

[syno]

Hi

I am doing a search by CustomerID so I guess my Datakey is CustomerID.

But I am having difficulty incorporating the Edit function to recognise the CustomerID field.

Sorry I am very new to this and any help would be really appreciated.

Thanks

 

[DotNetGnat]

on the datagrid definition on aspx page...use the DataKeyField="CustomerID"

try this link:

http://aspnet.4guysfromrolla.com/demos/dgExample19.aspx

-DNG

 

[jbenson001]

what code do you have in your update event of the datagrid?

 

[syno]

Hi

My code for the update event is as follows:

Public Sub DataGrid_Update(ByVal Source As Object, ByVal E As DataGridCommandEventArgs)
Dim objConnection As SqlConnection
Dim objCommand As SqlCommand

Dim txtCompanyName As TextBox = E.Item.Cells(2).Controls(0)
Dim txtContactName As TextBox = E.Item.Cells(3).Controls(0)
Dim txtContactTitle As TextBox = E.Item.Cells(4).Controls(0)
Dim txtAddress As TextBox = E.Item.Cells(5).Controls(0)
Dim txtPhone As TextBox = E.Item.Cells(6).Controls(0)
Dim strUpdateStmt As String

strUpdateStmt = " UPDATE [Customers] SET [CompanyName] = '" & txtCompanyName.Text & "' , [ContactName] = '" & txtContactName.Text & "', [ContactTitle] = '" & txtContactTitle.Text & "' , [Address] = '" & txtAddress.Text & "' , [Phone] = '" & txtPhone.Text & "' WHERE [CustomerID] = '" & E.Item.Cells(1).Text & "'"

objConnection = New SqlConnection("Data Source =syno\sqlexpress; Initial Catalog=Northwind; Integrated Security=SSPI;")
objCommand = New SqlCommand(strUpdateStmt, objConnection)

objConnection.Open()
objCommand.ExecuteNonQuery()
dgSearch.EditItemIndex = -1
BindData()
objConnection.Close
End Sub

 

[jbenson001]

I would trace through your code and stop on just before the command is exectued and check the values of all your varialbes and make sure the UPdate command is what you expect.

 

[syno]

It’s when I do a search on my datagrid, then edit the value in the datagrid to update it, that its not recognising the row I want to edit. I have tried adding a primary key field and setting the DataKeyField within my Datagrid control, i.e:


<aspataGrid ID="dgSearch" runat="server" AutoGenerateColumns="False" OnEditCommand="DataGrid_Edit" OnCancelCommand="DataGrid_Cancel" OnUpdateCommand="DataGrid_Update" DataKeyField="CustomerID">


I have then added the datakey to reference the Item Index as follows, when I initiate the edit event, but nothing happens, i.e


Public Sub DataGrid_Edit(ByVal Source As Object, ByVal E As DataGridCommandEventArgs)

Dim CustID As String = dgSearch.DataKeys(E.Item.ItemIndex)
BindData()

End Sub

Can somebody see what I am doing wrong?

Thank you

 

[jbenson001]

you need to set the index then bind the data.

   dgSearch.EditItemIndex = E.Item.ItemIndex
   DataBind()

 

[syno]

Thats fine, but when I use that it does not recognise to edit the primary feild column in my database, ie the edit event is not using the Datakey I have setup?

 

[syno]

Thats fine, but when I use that it does not recognise to edit the correct field in my database, ie the edit event is not using the Datakey I have setup?

 

[jbenson001]

Not sure what you mean.. that will edit the row you clicked on, not a specific column .. and colums that are editable will be able to be changed. You cannot change a key column. Guess I am a bit confused on exactly what your problem is.

 

[syno]

Thanks for your help.

I have a search facility that only returns a few columns of data based on the search criteria. When I go to edit one of the columns that is returned, it does not edit the column the search returned because my edit event does not recognise the column. Instead it searches for the row number and edits that.

I need to find a way of when I click the edit event it does not edit the column by row number, but by primary key field.

My search you see returns the columns representative of the value of the primary key field and not the row number.

I have this makes more sense.....

 

[jbenson001]

The row you click will become editable. It doesn't matter what the key(s) are. The dataadapter or datasource object will take care of the update by key, unless you are manually creating the Update staement yourself.

 

[syno]

Hi

Your right, I think I was getting a bit confused. However when I search my datagrid and it brings up one record say, in one row. If I then click edit, it edits the first record in the database rather then the record that was found.

So if I do a search for a record ID and the search returns one value and click on edit the datagrid will recognise the record ID found but will see that the record is on row 0, which is not correct. The record will be on a different row in the database.

The code I have is as follows:

Sub DataGrid_Edit(ByVal Source As Object, ByVal E As DataGridCommandEventArgs)
dgEdit.EditItemIndex = E.Item.ItemIndex
lblResults.Text = "Opted to edit DataGrid row " & E.Item.ItemIndex & _
", which has a CustID of " & dgEdit.DataKeys(E.Item.ItemIndex)
BindData()

Thanks

 

[jbenson001]

You will have to use DataKeys in the grid, or make sure you return the ID to your result set so that you Update the correct row. You cannot use the RowIndex of the grid, as you have seen, this ID will be the wrong one.