Update Database w/DataAdapter and the DataSet
Hi All,
I keep getting the below error when I try and write (update) from a dataset back to my access database - I am certain that I am connected to this database - as I am also connected thru a datagrid - and am able to update my database thru the datagrid object. Although this is not what I want to do - I have textboxes that are bound to my dataset - the update_click is supposed to update the dataset and then the dataset update the access database - Can anyone please tell me what I am missing or failing to do.....
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll
Additional information: Update unable to find TableMapping['Table'] or DataTable 'Table'.
Here is my code:
Dim MyLat As Double
Dim MyLon As Double
Dim MySiteID As Integer
Me.txtLatitude.Text = MyLat
Me.txtLongitude.Text = MyLon
Me.txtSiteID.Text = MySiteID
Dim myConnectionString As String = ....
Dim myConnection As New SqlConnection(myConnectionString)
myConnection.Open()
If myConnection.State.ToString() = "Open" Then
Try
comm = New SqlClient.SqlCommand("SELECT Top 100 SiteID, AddressLine1, City, State, UserAttribute2, UserAttribute3 FROM CustomerSites", myConnection)
da = New SqlClient.SqlDataAdapter(comm)
cb = New SqlClient.SqlCommandBuilder(da)
da.InsertCommand = cb.GetInsertCommand()
'To use the command builder to build your Delete and Update commands for you
'you MUST have a primary key in your datatable and it must be part of your select query
da.DeleteCommand = cb.GetDeleteCommand()
da.UpdateCommand = cb.GetUpdateCommand()
ds = New DataSet("TestDataBase")
da.Fill(ds, "CustomerSites")
Catch sqlconn As SqlClient.SqlException
MessageBox.Show(sqlconn.Message)
End Try
End If
Try
da.UpdateCommand = New SqlCommand("UPDATE CustomerSites SET UserAttribute2 = MyLat, UserAttribute3 = MyLon WHERE SiteID = MySiteID", myConnection)
da.UpdateCommand.Parameters.Add("MyLat, MyLon", SqlDbType.Float, 15, "UserAttribute2, UserAttribute3")
Dim workParm As SqlParameter = da.UpdateCommand.Parameters.Add("MyLat, MyLon", SqlDbType.Float)
workParm.SourceColumn = "MySiteID"
workParm.SourceVersion = DataRowVersion.Original
'THIS IS WHERE MY CODE GETS HUNG UP
Dim cRow As DataRow = ds.Tables("CustomerSites").Rows(0)
cRow("UserAttribute2") = "MyLat"
da.Update(ds)
Catch sqlconn As SqlClient.SqlException
MessageBox.Show(sqlconn.Message)
End Try
