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...
Expand|Select|Wrap|Line Numbers
- ##############
- 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!