[AlexG788]

 GridView.doc (75.5K)
Number of downloads: 43
I have a database with two tables. Table1 has a 3 fields and field 2 has ID's in it (10,20,30...). When I import the table into the datagrid, it converts the ID's into actual values by doing a look up against Table2 (consisting of 2 fields... ID and Name). I have a procedure to convert the ID's into values, and then convert them back into ID's before they get saved back to the DB.

I built a simple app where I import the data into the grid, and when I double click on a cell, it bring the value of field2 for that specific row into a textbox... in that box i can change the name to something else (valid against the lookup table) and when I click a button it updates the cell in the datagrid... after this when I click save, it converts all the text in field2 of x rows in the datagrid into the ID and then saves to the actual database.

When I have just one row, this works just fine, but when I have multiple rows, if i change any data in just the first row, everything is ok, but if i change data in any of the subsequent rows and save, it will convert the values to ID's only in the 1st row, and insert the actual value into the database for the other rows (that were changed)... whats worse is that it updates the database not only with teh value instead of the ID, but it updates it with the original value (noth the value that i saved to the grid.)

I created 3 grids on my app... (see the attachment for a pic), the top left shows the changed value in the grid (the previous value in the highlighted cell was Test20), the bottom left grid shows the actual dataset after the value was inserted into the grid, and the top right box shows the database after I ran the "convertgridId" job... as you can see it updated the 1st row with the ID, but the 2nd with the value.

Here is my code... for the app

Public Class Form1

	Dim cn As New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Desktop\Databases\Contacts.mdb")
	Dim da As OleDbDataAdapter
	Dim ds As DataSet
	Dim ds2 As DataSet
	Dim da2 As OleDbDataAdapter
	Dim CurrentRow As Integer

	Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		da2 = New OleDbDataAdapter("SELECT * FROM [Lookup]", cn)
		ds2 = New DataSet
		da2.Fill(ds2, "LookUp")
		Call LoadGrid()
		DataGridView1.DataSource = ds.Tables("TestTable")
		Call ConvertGridID()
	End Sub

	Public Sub LoadGrid()
		ds = New DataSet
		da = New OleDbDataAdapter("SELECT * FROM [Main]", cn)
		ds = New DataSet
		da.Fill(ds, "TestTable")
	End Sub


	Public Sub ConvertGridID()
		Dim x As Integer = DataGridView1.RowCount
		Dim i As Integer
		Dim TestValID As String
		Dim TestValName As String
		For i = 1 To 2
			TestValID = DataGridView1.Item("Field2", i - 1).Value
			Call IDChange(TestValID, TestValName)
			DataGridView1.Item("Field2", i - 1).Value = TestValName
		Next
	End Sub

	Public Sub IDChange(ByVal TestValID, ByRef TestValName)
		Dim fRow() As DataRow
		fRow = ds2.Tables("Lookup").Select("ID = '" & TestValID & "'")
		TestValName = fRow(0)("LookUpVal")
	End Sub

	Public Sub ConvertGridName()
		Dim x As Integer = DataGridView1.RowCount
		Dim i As Integer
		Dim TestValID As String
		Dim TestValName As String
		For i = 1 To 2
			TestValName = DataGridView1.Item("Field2", i - 1).Value
			Call NameChange(TestValID, TestValName)
			DataGridView1.Item("Field2", i - 1).Value = TestValID
		Next
	End Sub

	Public Sub NameChange(ByRef TestValID, ByVal TestValName)
		Dim fRow() As DataRow
		fRow = ds2.Tables("Lookup").Select("LookUpVal = '" & TestValName & "'")
		TestValID = fRow(0)("ID")
	End Sub
	Private Sub DataGridView1_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
		CurrentRow = DataGridView1.CurrentCellAddress.Y
		TextBox1.Text = DataGridView1.Item("Field1", CurrentRow).Value
		TextBox2.Text = DataGridView1.Item("Field2", CurrentRow).Value
		TextBox3.Text = DataGridView1.Item("Field3", CurrentRow).Value

		TextBox4.Text = CurrentRow
		TextBox5.Text = DataGridView1.CurrentCellAddress.Y & "," & DataGridView1.CurrentCellAddress.X
	End Sub

	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

		DataGridView1.Item("Field1", CurrentRow).Value = TextBox1.Text
		DataGridView1.Item("Field2", CurrentRow).Value = TextBox2.Text
		DataGridView1.Item("Field3", CurrentRow).Value = TextBox3.Text

		DataGridView2.DataSource = Nothing
		DataGridView2.DataSource = ds.Tables("TestTable")

		Dim ds3 As DataSet
		ds3 = New DataSet
		Dim da3 As New OleDbDataAdapter("SELECT * FROM [Main]", cn)
		da3.Fill(ds3, "TestTable2")

		DataGridView3.DataSource = Nothing
		DataGridView3.DataSource = ds3.Tables("TestTable2")

	End Sub

	Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
		Call ConvertGridName()
		Dim cb As New OleDbCommandBuilder(da)
		da.Update(ds, "TestTable")
		ConvertGridID()
	End Sub
End Class

I also included the db in the attachments, so if you can try it yourself and see what happens....