Dynamic SQL generation is not supported against multiple databases

[joecruz36]

I'm developing an application in Visual Studio 2013 using Visual Basic with an SQL Server database. When I perform a query it gives me the following error message Dynamic SQL generation is not supported against multiple databases

If I execute the query in the SQL Management Studio executes perfect but in the application throws the error.The curious thing is that after throwing the error the query executes perfectly. Please help me. Here is the code:

If rdbCustCelPnone.Checked = True Then
                    Try
                        SQL.RunQuery("SELECT * FROM SERVICE_ORDER, CUSTOMER WHERE SERVICE_ORDER.custID =  CUSTOMER.custID AND CUSTOMER.custCelPhone = '" & txtSearchSO.Text & "' ")
                        If SQL.SQLDS.Tables(0).Rows.Count <> 0 Then
                            dgvDisplaySO.DataSource = SQL.SQLDS.Tables(0)
                            SQL.SQLDA.UpdateCommand = New SqlClient.SqlCommandBuilder(SQL.SQLDA).GetUpdateCommand
                        Else
                            MsgBox("No record found with that search criteria. Please perform another search ! ! !")
                            'refreshClear()
                            Exit Sub
                        End If
                    Catch ex As Exception
                        'MsgBox(ex.Message) '<-----Research Exception ( Dynamic SQL generation is not supported against multiple databases )
                    End Try

[techgnome]

Try changing your SQL to a more universally accepted format with a proper join:
SELECT * FROM SERVICE_ORDER
inner join CUSTOMER on SERVICE_ORDER.custID = CUSTOMER.custID
WHERE CUSTOMER.custCelPhone = '" & txtSearchSO.Text & "' "


-tg

[joecruz36]

Try changing your SQL to a more universally accepted format with a proper join:
SELECT * FROM SERVICE_ORDER
inner join CUSTOMER on SERVICE_ORDER.custID = CUSTOMER.custID
WHERE CUSTOMER.custCelPhone = '" & txtSearchSO.Text & "' "



-tg

Thanks for the quick reply. It's still doing the same...

[techgnome]

I missed the fact that you were using the updateCommand based on the command builder. Here's the deal, well part of it.

1) don't use select * - there is never a reason for it... list out your fields. Only ever select just what you need. Select * is fine for diagnostic purposes when you're in SSMS, but by the time you transfer it to code, it shouldn't ever have that splat in there.
2) One of two things is happening - 1: you don't have a Primary Key defined so it doesn't know how to build up the update command (make sure your PKey of the table you want to update is part of the select, see item #1 above). 2: You have a field with the same name in both tables, so when it tries to build the update query it doesn't know which one you really want, so it can't build a proper Update. See item #1 above.
3) This is the option I prefer myself - build the update yourself. That way you can be sure the right fields get updated in the right table and there is no guess as to what the update command ends up looking like.

-tg

[TysonLPrice]

I didn't dig that deep but a cursory Google searched came up with:

SqlCommandBuilder cannot generate the UPDATE/INSERT statements required for the DataAdapter update command when the SELECT command contains JOINS between two or more tables.

That is from here:

http://stackoverflow.com/questions/2...se-tables-in-w

The error message is telling you that a command builder cannot automatically generate SQL code to save changes when the query contains more than one table. You will have to forgo the command builder and write the SQL code for the data adapter yourself.

That is from here:

http://www.vbdotnetforums.com/sql-se...se-tables.html

[techgnome]

Yeah, unfortunately the CommandBuilder, while handy, is pretty stupid and can't handle multiple tables. I've never used it since I do all my CRUDs through SProcs.

-tg