DataGrid - Column Value Translation

[Ladyhawk]

I have a datagrid that I have bound to a datatable which was created from a SQL statement such as

SELECT Code, OtherData
FROM CodeTable, OtherDataTable
WHERE CodeTable.Id = OtherDataTable.Id

This all works fine until I wanted to make the grid editable and updatable. Because the data did not come from a single table, the data adapter can't update the data source from the data table.

So basically how do I use 1 table in the sql statement but translate the value of one column (the id) based on information in another table? I did have the dataadapter update code in the CurrentCellChanged event, but I guess another way I could do it is to leave the datagrid bound to the 2 table sql statement and interrogate the datagrid row information in the CurrentCellChanged event and write my own update statement.

Surely there must be an easier way.

Ladyhawk.
** ASP/VB/Java Programmer **

 

[Ladyhawk]

Can I change the UpdateCommand of the data adapter to only update the one table properly?

Ladyhawk.
** ASP/VB/Java Programmer **

 

[PankajBanga]

try something like this

Dim updateString As String 'Variable to hold the SQL statement.
Dim cmd As OleDbCommand 'Create an OleDbCommand object.
Dim updateResult As Integer 'Variable to hold result of ExecuteNonQuery method.

Try
'Initialize SQL string.
updateString = "UPDATE TableName " & _
"SET ColumName = " & yourValues & _
"WHERE ..."


'Initialize OleDbCommand object.
cmd = New OleDbCommand(updateString, Connection)

'Send the CommandText to the connection, execute the Command.
updateResult = cmd.ExecuteNonQuery

Catch Excep As System.Exception
MessageBox.Show(Excep.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

Email: pankajmsm@yahoo.com

 

[Ladyhawk]

Yeah.. I didn't really want to have to build up my own sql statement in code, but like most things M$ does.. looks good in the demo but when you want to do something useful with it you find all of the limitations and end up having to do it all manually anyway. Anyway.. what I ended up doing was using this code in the validated event of the grid.

Dim bm As BindingManagerBase = dgMyGrid.BindingContext(dgMyGrid.DataSource, dgMyGrid.DataMember)
If bm.Count = 0 Then
Cursor = Cursors.Default
Exit Sub
End If
Dim dr As DataRow = CType(bm.Current, DataRowView).Row

Dim iId As String = dr.Item("Id&quot
Dim sCode As String = dr.Item("Code&quot
Dim sName As String = dr.Item("Name&quot

Dim objDataGrabber As DataGrabber = New DataGrabber
objDataGrabber.ExecuteCommand _
("UPDATE Table SET Code = '" & sCode & "', " _
& "Name = '" & sName & "' " _
& "WHERE FreightId = " & iFreightId)

NOTE: DataGrabber is just my database stuff wrapped up. and yes I realise that it's not actually grabbing data at the moment.

Seems to work well, but just more stuff that needs maintaining.


Thanks PankajBanga for your help.



Ladyhawk.
** ASP/VB/Java Programmer **

 

[younguser]

I spend a couple days to solve the same problem. Here is partial code. Use currencymanager to get cell data. I wrote GetcustomerID to get ID from Customer Name.

Dim cm As CurrencyManager = CType(Me.BindingContext(grdSearch.DataSource, grdSearch.DataMember), CurrencyManager)
' Retrieve the default DataView of the DataGrid
Dim dv As DataView = CType(cm.List, DataView)
' Use Currency Manager and DataView to retrieve the Current Row
Dim dr As DataRow
dr = dv.Item(cm.Position).Row
strSQL = "UPDATE tblDisk SET"
strSQL &= " ,CustomerID = '" & GetCustomerID(dr(1).ToString) & "'"

 

[Ladyhawk]

Argh... my problem now is when do I call the update. I want to update the database when the cell data changes, but how can I create a cell lost focus event?

Ladyhawk.
** ASP/VB/Java Programmer **