Good day...
I have a problem in Adding/Editing in Two Tables in Northwind database(Products and Categories). It generates an error: DYNAMIC SQL GENERATION IS NOT SUPPORTED AGAINST MULTIPLE BASE TABLES.
Please send me a running code.
Thanks a lot.

My code goes like this:

    Dim strConn As SqlConnection
    Dim daTrans As SqlDataAdapter
    Dim ds As New DataSet()
    Dim dr As DataRow
    Dim SQLCmdBuilder As SqlCommandBuilder

    strConn = New SqlConnection("data source=LQS2;initial catalog=Northwind;integrated                          security=SSPI;persist security info=False;workstation id=EDP25;packet size=4096")
    strConn.Open()

    daTrans = New SqlDataAdapter("Select A.CategoryID, A.CategoryName, A.Description,B.ProductID,               B.ProductName, B.CategoryID from Categories A, Products B where A.CategoryID =               B.CategoryID", strConn)

    ds = New DataSet()

    daTrans.Fill(ds, "Products")

    DataGrid1.DataSource = ds.Tables("Products")


'My code on adding a new row:
    dr = ds.Tables("Products").NewRow
    dr("ProductName") = "XXX"
    dr("CategoryID") = 1
    ds.Tables("Products").Rows.Add(dr)

    SQLCmdBuilder = New SqlCommandBuilder(daTrans)
    daTrans.Update(ds, "Products")
    dr.AcceptChanges()






____________________________
rtbuquia

Have you tried with

daTrans = New SqlDataAdapter("Select A.CategoryID, A.CategoryName, A.Description,B.ProductID,               B.ProductName, B.CategoryID from Categories A INNER JOIN Products B ON A.CategoryID =               B.CategoryID", strConn)


____________________________
If you find the answer helpful, please mark this topic as solved.

Yup. I tried it but it generates the same error. I just want to update only the Categories Table and used the Products Table as a lookup.

____________________________
rtbuquia

You seem to have a wrong SQl query in your Adapter

What are the names of your tables A and B or Category A and Category B. I think the last two would be invalid. You also need to perform and explicit join as Goran advised.
Thus changing your query should redult in

daTrans = New SqlDataAdapter("Select A.CategoryID, A.CategoryName, A.Description,B.ProductID,   B.ProductName, B.CategoryID from A inner join B on A.CategoryID = B.CategoryID where A.CategoryID = B.CategoryID", strConn)

I cannot figure out how you have named your tables but that query does not look correct to me.
Hope you have some luck

____________________________
BrainBench ADO.NET and ASP.NET Certified Developer

Dear rtbuquia ,

I can say you from my experience that u can not update more than 1 Base tables through  a view ;
when the view contains multiple tables .

do u agree ?.....

Similarly if a select statement is based on two tables then
update command can not be used  in VB.NET ..

Your problem can be solved for filling the data by creating  a single view but you wont be able to update or delete or insert any records through the newly created view ..

By




____________________________
Vinod Sutar
COCONUT Software Pvt Ltd