469,336 Members | 5,552 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

implement a Join between 2 DataTables (Dynamic SQL generation is not supported against multiple base tables)

Hi,

For my VB.NET application I have the following situation:
2 tables on my SQL Server: tblAccounts and tblRules. For each Account there
are many Rules (so tblRules is linked to my tblAccounts by the Account). In
the tblAccounts thee is a field Company which occurs many times (there is
more than one Account for each Company:).

Whet I want to do on my Fom is this: I have a combobox with all my company's
in it. When I choce a Company in it, I want it to give me all the Rules for
all the Accounts that this Company has.

I tried it like this:
'TblRULES
strSql = "SELECT * FROM tblRules INNER JOIN tblAccounts ON
tblRules.Account = tblAccounts.Account"

Dim cmdSql As New SqlCommand(strSql, conSql)
dadRules = New SqlDataAdapter(cmdSql)

Dim cbSql As New SqlCommandBuilder(dadRules)
cbSql.GetUpdateCommand()

dtsRules = New DataSet
dadRules.Fill(dtsRules, "tblRules")

Unfortunately this gives me this error:
Dynamic SQL generation is not supported against multiple base tables. at
System.Data.Common.CommandBuilder.BuildInformation (DataTable schemaTable)

So I guess it's not good to put a join in a SqlCommand and try to do an
Update afterwards on it. The 'good' way should be to somewhere be able to
put tblRules and tblAccounts as two different tables in my DataSet, and
perform the Join n the DataSet or something like that when I need to do a
search on the Company.

does anybody knows how to do this? Or to get rid of that error? I just need
the best way to get this working!

Thanks a lot in advance! Any help will be really appreciated!

Pieter


Nov 21 '05 #1
4 22861
The problem is with the CommandBuilder object. It works only for simplest of
the select commands. Also it requires to execute the select command to
generate the metadata required for generating update, insert and delete
commands.
So instead of using the CommandBuilder to generate Update command, write the
update command directly as follows:
dadRules.UpdateCommand.CommandText = "update command text"

Hope this helps.
Sameeksha

"DraguVaso" wrote:
Hi,

For my VB.NET application I have the following situation:
2 tables on my SQL Server: tblAccounts and tblRules. For each Account there
are many Rules (so tblRules is linked to my tblAccounts by the Account). In
the tblAccounts thee is a field Company which occurs many times (there is
more than one Account for each Company:).

Whet I want to do on my Fom is this: I have a combobox with all my company's
in it. When I choce a Company in it, I want it to give me all the Rules for
all the Accounts that this Company has.

I tried it like this:
'TblRULES
strSql = "SELECT * FROM tblRules INNER JOIN tblAccounts ON
tblRules.Account = tblAccounts.Account"

Dim cmdSql As New SqlCommand(strSql, conSql)
dadRules = New SqlDataAdapter(cmdSql)

Dim cbSql As New SqlCommandBuilder(dadRules)
cbSql.GetUpdateCommand()

dtsRules = New DataSet
dadRules.Fill(dtsRules, "tblRules")

Unfortunately this gives me this error:
Dynamic SQL generation is not supported against multiple base tables. at
System.Data.Common.CommandBuilder.BuildInformation (DataTable schemaTable)

So I guess it's not good to put a join in a SqlCommand and try to do an
Update afterwards on it. The 'good' way should be to somewhere be able to
put tblRules and tblAccounts as two different tables in my DataSet, and
perform the Join n the DataSet or something like that when I need to do a
search on the Company.

does anybody knows how to do this? Or to get rid of that error? I just need
the best way to get this working!

Thanks a lot in advance! Any help will be really appreciated!

Pieter


Nov 21 '05 #2
Use the Design View for any complicated queries and use the Database Diagram
for relationships between tables of a database. You can explore through
these features using the Server Explorer in Visual Studio (View->Server
Explorer or, easier, CTRL + ALT + S).
Good luck,

Bram.
su*****@geticasoftware.ro

"DraguVaso" <pi**********@hotmail.com> wrote in message
news:eJ****************@TK2MSFTNGP09.phx.gbl...
Hi,

For my VB.NET application I have the following situation:
2 tables on my SQL Server: tblAccounts and tblRules. For each Account there are many Rules (so tblRules is linked to my tblAccounts by the Account). In the tblAccounts thee is a field Company which occurs many times (there is
more than one Account for each Company:).

Whet I want to do on my Fom is this: I have a combobox with all my company's in it. When I choce a Company in it, I want it to give me all the Rules for all the Accounts that this Company has.

I tried it like this:
'TblRULES
strSql = "SELECT * FROM tblRules INNER JOIN tblAccounts ON
tblRules.Account = tblAccounts.Account"

Dim cmdSql As New SqlCommand(strSql, conSql)
dadRules = New SqlDataAdapter(cmdSql)

Dim cbSql As New SqlCommandBuilder(dadRules)
cbSql.GetUpdateCommand()

dtsRules = New DataSet
dadRules.Fill(dtsRules, "tblRules")

Unfortunately this gives me this error:
Dynamic SQL generation is not supported against multiple base tables. at
System.Data.Common.CommandBuilder.BuildInformation (DataTable schemaTable)

So I guess it's not good to put a join in a SqlCommand and try to do an
Update afterwards on it. The 'good' way should be to somewhere be able to
put tblRules and tblAccounts as two different tables in my DataSet, and
perform the Join n the DataSet or something like that when I need to do a
search on the Company.

does anybody knows how to do this? Or to get rid of that error? I just need the best way to get this working!

Thanks a lot in advance! Any help will be really appreciated!

Pieter

Nov 21 '05 #3
You can only update 1 of the tables in the join. So pick one, change the SQL
query for the adapter to be a SELECT just from that table of the appropriate
columns, and then create the command builder. That way the command builder
will ignore any other columns, and the update will be able to update that
one table.

"DraguVaso" <pi**********@hotmail.com> wrote in message
news:eJ****************@TK2MSFTNGP09.phx.gbl...
Hi,

For my VB.NET application I have the following situation:
2 tables on my SQL Server: tblAccounts and tblRules. For each Account there are many Rules (so tblRules is linked to my tblAccounts by the Account). In the tblAccounts thee is a field Company which occurs many times (there is
more than one Account for each Company:).

Whet I want to do on my Fom is this: I have a combobox with all my company's in it. When I choce a Company in it, I want it to give me all the Rules for all the Accounts that this Company has.

I tried it like this:
'TblRULES
strSql = "SELECT * FROM tblRules INNER JOIN tblAccounts ON
tblRules.Account = tblAccounts.Account"

Dim cmdSql As New SqlCommand(strSql, conSql)
dadRules = New SqlDataAdapter(cmdSql)

Dim cbSql As New SqlCommandBuilder(dadRules)
cbSql.GetUpdateCommand()

dtsRules = New DataSet
dadRules.Fill(dtsRules, "tblRules")

Unfortunately this gives me this error:
Dynamic SQL generation is not supported against multiple base tables. at
System.Data.Common.CommandBuilder.BuildInformation (DataTable schemaTable)

So I guess it's not good to put a join in a SqlCommand and try to do an
Update afterwards on it. The 'good' way should be to somewhere be able to
put tblRules and tblAccounts as two different tables in my DataSet, and
perform the Join n the DataSet or something like that when I need to do a
search on the Company.

does anybody knows how to do this? Or to get rid of that error? I just need the best way to get this working!

Thanks a lot in advance! Any help will be really appreciated!

Pieter

Nov 21 '05 #4
I had a similar problem, which I solved by using 2 dataadapters: one
which SELECTs without a JOIN and is used for UPDATEing, and another with
the JOIN. They both work with the same dataset - one reads, the other writes
DraguVaso wrote:
Hi,

For my VB.NET application I have the following situation:
2 tables on my SQL Server: tblAccounts and tblRules. For each Account there
are many Rules (so tblRules is linked to my tblAccounts by the Account). In
the tblAccounts thee is a field Company which occurs many times (there is
more than one Account for each Company:).

Whet I want to do on my Fom is this: I have a combobox with all my company's
in it. When I choce a Company in it, I want it to give me all the Rules for
all the Accounts that this Company has.

I tried it like this:
'TblRULES
strSql = "SELECT * FROM tblRules INNER JOIN tblAccounts ON
tblRules.Account = tblAccounts.Account"

Dim cmdSql As New SqlCommand(strSql, conSql)
dadRules = New SqlDataAdapter(cmdSql)

Dim cbSql As New SqlCommandBuilder(dadRules)
cbSql.GetUpdateCommand()

dtsRules = New DataSet
dadRules.Fill(dtsRules, "tblRules")

Unfortunately this gives me this error:
Dynamic SQL generation is not supported against multiple base tables. at
System.Data.Common.CommandBuilder.BuildInformation (DataTable schemaTable)

So I guess it's not good to put a join in a SqlCommand and try to do an
Update afterwards on it. The 'good' way should be to somewhere be able to
put tblRules and tblAccounts as two different tables in my DataSet, and
perform the Join n the DataSet or something like that when I need to do a
search on the Company.

does anybody knows how to do this? Or to get rid of that error? I just need
the best way to get this working!

Thanks a lot in advance! Any help will be really appreciated!

Pieter

Nov 21 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Paul | last post: by
1 post views Thread by Felix_WafyTech | last post: by
4 posts views Thread by HLCruz via AccessMonster.com | last post: by
47 posts views Thread by Larry Smith | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.