By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

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

P: 43
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...


Expand|Select|Wrap|Line Numbers
  1.          ##############
  2.  
  3.       Page Load Sub
  4.  
  5.  
  6.       sqlselect = "Select ProductID,Name,Price from Products,Store Where Products.StoreID = Store.StoreID And StoreID = 7"
  7.  
  8.       connectionstring = dh.returnconnection # returns the connection string to the location of the database
  9.  
  10.  
  11.       sqlcon = new sqlconnection(connectionstring)
  12.  
  13.       da = new sqlDataAdapter(sqlselect,connectionstring)
  14.  
  15.       ds = new dataset()
  16.  
  17.       dr = ds.Tables(0).Rows(0)
  18.  
  19.       If NotPostBack Then
  20.  
  21.       tbPrice.Text = dr("Price")
  22.  
  23.       #etc...
  24.  
  25.       EndIf
  26.  
  27.       End Sub
  28.  
  29.       ##############
  30.  
  31.  
  32.       ##############
  33.  
  34.       Submit Button "Protected Sub "SubButton""
  35.  
  36.     dr.BeginEdit()
  37.  
  38.       dr("Price") = tbPrice.Text
  39.  
  40.       dr.EndEdit()
  41.  
  42.       Dim objCommandBuilder As New SqlCommandBuilder(da)
  43.  
  44.       da.Update(ds, 0)
  45.  
  46.       End of sub etc..
  47.       #############
  48.  


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!
Jan 15 '08 #1
Share this Question
Share on Google+
1 Reply


pureenhanoi
100+
P: 175
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...


Expand|Select|Wrap|Line Numbers
  1.          ##############
  2.  
  3.       Page Load Sub
  4.  
  5.  
  6.       sqlselect = "Select ProductID,Name,Price from Products,Store Where Products.StoreID = Store.StoreID And StoreID = 7"
  7.  
  8.       connectionstring = dh.returnconnection # returns the connection string to the location of the database
  9.  
  10.  
  11.       sqlcon = new sqlconnection(connectionstring)
  12.  
  13.       da = new sqlDataAdapter(sqlselect,connectionstring)
  14.  
  15.       ds = new dataset()
  16.  
  17.       dr = ds.Tables(0).Rows(0)
  18.  
  19.       If NotPostBack Then
  20.  
  21.       tbPrice.Text = dr("Price")
  22.  
  23.       #etc...
  24.  
  25.       EndIf
  26.  
  27.       End Sub
  28.  
  29.       ##############
  30.  
  31.  
  32.       ##############
  33.  
  34.       Submit Button "Protected Sub "SubButton""
  35.  
  36.     dr.BeginEdit()
  37.  
  38.       dr("Price") = tbPrice.Text
  39.  
  40.       dr.EndEdit()
  41.  
  42.       Dim objCommandBuilder As New SqlCommandBuilder(da)
  43.  
  44.       da.Update(ds, 0)
  45.  
  46.       End of sub etc..
  47.       #############
  48.  


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
Jan 15 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.