Cannot update 1 of 2 tables - Dynamic SQL generation is not supported

[pieandpeas]

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!