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!

[pureenhanoi]

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!

I think the way of keeping a DataSet in memory and update it each time you need is worst way. The DataSet (as i think, it like RecordSet in VB6) just used to store data in a short periods of time, and it should be release as soon as possible.
I've never use Update to Recordset (or DataSet like you), so i never get problem with linked table.
Just store data of each table on seperate varriables, and update it in seperate statement