[RESOLVED] DataTable not updating Database with DataAdapter

[_cerberus_]

I am trying to update a database with info from a WinForm. I had no issues when using a “normal” SQL update command written by hand (parameters set to the text box values,) but I am trying to clean up and reduce my code and I thought I would bind the controls to a DataTable and use a DataAdapter's update command to achieve the same thing.

I have tried to get various combinations of setting parameters and update commands to work, but the Database is not getting updated from the new DataTable values. I have stepped through the code with each change and can see that the DataTable is getting the new textbox values, but those updates aren’t going to the Database.

Things I’ve tried: Letting the binding get the new values vs. setting the parameters manually. Using the command builder to build the update command, using the .UpdateCommand.ExecuteNonQuery(), command and of course a straight .Update(DataTable) command.

Below is the rough code that I am using (stripped out all the extras.) I am hoping someone can tell me what it is I am doing wrong/missing, or what correct path to take. Is there a "best practice" or a better way to do this?

  
Public Class frmDemog
	Dim dt_Test As New DataTable
	Dim da_Test As New SqlDataAdapter
	Dim SQLcmd As SqlCommand

	Private Sub frmDemog_Load(sender As Object, e As EventArgs) Handles MyBase.Load
		BuildSQL()
		FillInfo()
		BindControls()
	End Sub

	Private Sub BindControls()
		txtLName.DataBindings.Add("Text", dt_Test, "Last_Name")
		txtFName.DataBindings.Add("Text", dt_Demog, "First_Name")
	End Sub

	Private Sub ClearBindings()
		For Each c As Control In Me.Controls
			c.DataBindings.Clear()
		Next
	End Sub

	Public Sub Update_Me(RefreshSearch As Boolean, RefreshView As Boolean)
		Try
			With da_Test.UpdateCommand
				.Parameters("@LName").Value = txtLName.Text
				.Parameters("@FName").Value = txtFName.Text
			End With

			'** One version of updating that was tried
			'If Not SQLcmd.Connection.State = ConnectionState.Open Then
				'SQLcmd.Connection.Open()
			'End If
			'da_Test.UpdateCommand.ExecuteNonQuery()
			
			'** The update command that should work
			da_Test.Update(dt_Test)

		Catch ex As Exception
			MsgBox(ex.Message)
		End Try
	End Sub

	Public Sub FillInfo()
		dt_Test.Clear()
		da_Test.SelectCommand.Parameters("@ID").Value = frmMain.txtID.Text

		Try
			da_Test.Fill(dt_Test)
		Catch ex As Exception
			MsgBox(ex.Message)
		End Try
	End Sub


	Private Sub BuildSQL()
		'** Build Selection Query
		SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
			"SELECT ",
				"data_Table.[Last_Name], ",
				"data_Table.[First_Name], ",
				"data_Table1.[Last_Name] + ', ' + data_Table1.[First_Name] as [AKA], ",
			"FROM [DB].data_Table ",
				"LEFT JOIN [DB].data_Table as data_Table1 ",
				"ON data_Table.[ID] = data_Table1.[AKA_ID] ",
			"WHERE data_Table.ID_Demog=@ID"
			), Vars.sqlConnACL)

		SQLcmd.Parameters.Add("@ID", SqlDbType.Int)
		da_Test.SelectCommand = SQLcmd

		'** Build Update Query
		SQLcmd = New SqlCommand(String.Join(Environment.NewLine,
			"UPDATE [ACL].data_Table SET ",
				"[Last_Name]  = @LName",
				",[First_Name]  = @FName",
			"WHERE [ID_Demog] = @ID"
			), Vars.sqlConnACL)

		With SQLcmd.Parameters
			.Add("@LName", SqlDbType.NVarChar, 255, "Last_Name")
			.Add("@FName", SqlDbType.NVarChar, 255, "First_Name")
		End With
		da_Test.UpdateCommand = SQLcmd
	End Sub
End Class