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

Sameeksha
Tue Aug 10 05:35:01 CDT 2004

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
>
>
>
>
>

Bram
Tue Aug 10 06:17:41 CDT 2004

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.
support@geticasoftware.ro

"DraguVaso" <pietercoucke@hotmail.com> wrote in message
news:eJ%23L8vrfEHA.3632@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
>
>
>
>

Marina
Tue Aug 10 08:24:28 CDT 2004

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" <pietercoucke@hotmail.com> wrote in message
news:eJ%23L8vrfEHA.3632@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
>
>
>
>

Uri
Sun Aug 15 06:31:37 CDT 2004

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
>
>
>
>