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

[DraguVaso]

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]

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]

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

[Marina]

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

[Uri Dor]

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