Question Dynamic SQL generation is not supported against multiple base tables.

[Neytiri]

Hi Everyone,

I have a database created using sql server express 2008. There I have two tables called tbl1 and tbl2,
which are related as tbl1.ID=tbl2.ID.

I have created a programme using VB 2010; there, a form has two textboxes.

I'm trying to save data which have entered to these two textboxes, to these two tables(tbl1,tbl2). But, I'm getting the above error when I'm trying to update the tables using following method!!;

mydataAdapter.update(dataset1,"Mydataset")
Error:- "Dynamic SQL generation is not supported against multiple base tables" :concern::concern:

Can you please help me to get-over from this problem? As I'm a newcomer to this forum please ignore if I've made any mistake.

Thank you sooooo much!!!!

 

[jmcilhinney]

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.

 

[Neytiri]

Dynamic SQL generation is not supported against multiple base tables

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.

Thank you so much for your quick response.It really encourages a new comer like me. Yes, you are correct, that I was trying to use the command builder.
Actually I don't know how to write this SQL code for the data adapter by myself as I've used the command builder so far. This is my first instance to deal with a relational database.

Can you please help me to write the sql code for this?

Tables: tbl1,tbl2 ; (related as tbl1.CityNumber=tbl2.CityNumber)

Columns to be updated:tbl1.CityName , tbl1.CityNumber, tbl2.StreetName

TextBoxes on the VB form: txtCityName , txtCityNumber, txtStreetName


Please let me thank you in advance.

 

[jmcilhinney]

Retrieving and Saving Data in Databases

Examples 4 and 5 in post #1 contrast manual SQL generation and automatic SQL generation to do the same thing. In your case, the second option is out so you must use the first. Because there are two tables involved, you must write two statements for each command, separated by a semicolon.

 

[Neytiri]

Thanks again for the reply.I was waiting for it. Ok, I'll go through it and let you know the progress.
Actually I'm really tired of searching a solution for my problem. So, a BIG 'thank you' is given for you!!!!:courage:

 

[Neytiri]

I have gone though the link and gave the update command into two statements.
By giving some pop-up message boxes after each statement of my entire code of the VB form, I found that the my cord is ok and I don't get get any error message.

But, now the problem is, the two tables have not get updated,after going to the database in MS.SQL Server Management Studio.

I think I'm very closer to have my required result. Can you please help me to get-over from this problem?
I have tried my best to track place,where I've gone wrong.
(I have been trying to find a method for last four days.Although I have gone to this web page earlier, I couldn't understand which modifications to be made. Thanks for telling me what to do.)

Tables: tbl1,tbl2 ; (related as tbl1.CityNumber=tbl2.CityNumber)

Columns to be updated:tbl1.CityName , tbl1.CityNumber, tbl2.StreetName

TextBoxes on the VB form: txtCityName , txtCityNumber, txtStreetName


The following is the entire code of my form:


Option Explicit On


Public Class MyForm

Private connection As New SqlClient.SqlConnection
Private sql As String
Private adapter As New SqlClient.SqlDataAdapter
Private table As New DataTable


Private Sub InitialiseDataAdapter()

Dim update As New SqlClient.SqlCommand("UPDATE tbl1 SET CityNumber = txtCityNumber, CityName = txtCityName;UPDATE tbl2 SET StreetName=txtStreetName", Me.connection)

update.Parameters.Add("txtCityNumber", SqlDbType.Int, 8, "CityNumber")
update.Parameters.Add("txtCityName", SqlDbType.NVarChar, 40, "CityName")
update.Parameters.Add("txtStreetName", SqlDbType.NVarChar, 40, "StreetName")

MsgBox("update.Parameters is ok", vbInformation, "Database Status 4")

Me.adapter.UpdateCommand = update

Me.adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

MsgBox("adapter.MissingSchemaAction is ok", vbInformation, "Database Status 5")

End Sub


Private Sub GetData()

Me.adapter.Fill(Me.table)
MsgBox("adapter.fill is ok", vbInformation, "Database Status 3")

End Sub

Private Sub SaveData()

Me.adapter.Update(Me.table)
MsgBox("saveData() is ok", vbInformation, "Database Status 6")

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

connection.ConnectionString = "Data Source=......."
connection.Open()
MsgBox("Database is now open", vbInformation, "Database Status1")

sql = "Select tbl1.CityNumber,tbl1.CityName,tbl2.StreetName FROM tbl1 INNER JOIN tbl2 ON tbl1.CityNumber=tbl2.CityNumber"

adapter = New SqlClient.SqlDataAdapter(sql, connection)
MsgBox("adapter(sql,connection) is ok", vbInformation, "Database Status2")

Call GetData()

Call InitialiseDataAdapter()

Call SaveData()

End Sub

End Class


Thanks again!!!!

 

[Neytiri]

If you could please help me to get-over from this problem. Your response is the only hope for me to succeed this task as I have tried all the ways I know.
So,please give me a helping-hand.thanks a lot!!!!!!!!!!!!!!!!

 

[Neytiri]

Oh God!! I'm like frustrated by trying soo harder. This is my 5th day of searching a solution for the error of my programme.
Please friends help me to get-rid-of the error mentioned above. Please.....
Thank you!

 

[Herman]

I wrote a COM SQL library a little while ago to be used from some VBScripts. It was pretty extensive, but I stripped it down to the bare minimum for you. This only contains a small synchronous read routine that you can use to execute pretty much any query. It returns a datatable with resulting rows if there are any. If there is no readable results the datatable will be empty. It should give you a pretty good idea how to use it...

Public Class SQLLib
    Public Event evSQLException(ByVal strMessage As String, ByVal errNum As Integer)

    Private ConnectionString As New System.Data.SqlClient.SqlConnectionStringBuilder

    Public Property SQLConnectionString As String
        Get
            Return ConnectionString.ToString
        End Get
        Set(ByVal value As String)
            Try
                ConnectionString = New System.Data.SqlClient.SqlConnectionStringBuilder(value)
            Catch SQLExc As SqlClient.SqlException
                RaiseEvent evSQLException(SQLExc.Message, SQLExc.Number)
            End Try
        End Set
    End Property

    ' ###############################################################################################
    ' SQL Synchronous Read - This function sends a SQL query to the selected database and returns the
    ' result in a datatable.
    ' Example: Dim Result As DataTable = SQLReadSync("SELECT * FROM customers")
    ' ###############################################################################################
    Public Function SQLReadSync(ByVal strQuery As String) As DataTable
        SQLReadSync = New DataTable
        Using SQLTmpConn As New System.Data.SqlClient.SqlConnection(SQLConnectionString)
            Try
                ConnectionString.AsynchronousProcessing = False
                SQLTmpConn.Open()
                Using SQLCommand As New System.Data.SqlClient.SqlCommand(strQuery, SQLTmpConn)
                    Using SQLRdr As System.Data.SqlClient.SqlDataReader = SQLCommand.ExecuteReader()
                        SQLReadSync.Load(SQLRdr)
                    End Using
                End Using
            Catch SQLExc As System.Data.SqlClient.SqlException
                RaiseEvent evSQLException(SQLExc.Message, SQLExc.Number)
            Finally
                SQLTmpConn.Close()
            End Try
        End Using
    End Function

    Public Sub New()
        MyBase.New()
    End Sub

End Class

 

[Neytiri]

Thank you very much for your response. I'll try it-out and let you know the progress of my programme.
Thanks a lot!!!!!!!