Hi, I'm having a problem with my code,
I am doing the following - retrieving a field from a table, which is linked to another table, i'm only updating one of the fields in one of the two tables...
-
##############
-
-
Page Load Sub
-
-
-
sqlselect = "Select ProductID,Name,Price from Products,Store Where Products.StoreID = Store.StoreID And StoreID = 7"
-
-
connectionstring = dh.returnconnection # returns the connection string to the location of the database
-
-
-
sqlcon = new sqlconnection(connectionstring)
-
-
da = new sqlDataAdapter(sqlselect,connectionstring)
-
-
ds = new dataset()
-
-
dr = ds.Tables(0).Rows(0)
-
-
If NotPostBack Then
-
-
tbPrice.Text = dr("Price")
-
-
#etc...
-
-
EndIf
-
-
End Sub
-
-
##############
-
-
-
##############
-
-
Submit Button "Protected Sub "SubButton""
-
-
dr.BeginEdit()
-
-
dr("Price") = tbPrice.Text
-
-
dr.EndEdit()
-
-
Dim objCommandBuilder As New SqlCommandBuilder(da)
-
-
da.Update(ds, 0)
-
-
End of sub etc..
-
#############
-
The code breaks at the line
"da.Update(ds, 0)", with the error -
invalidOperationException was unhandled by the user code
Dynamic SQL generation is not supported against multiple base tables.
This is because I'm doing a link between tables in my sql and the dataadapter is looking for a table to reference (well, i assume this is the problem),
however i've heard of different ways to remedy this e.g do a seperate dataadapter for updating or something.. but i really wouldnt know how to do this, so if someone could have a look at my code, and show me how i could go about this, or another way (being a newbie of .net i'm learning as i'm going along) i'd be really grateful!
