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

[clzanas]

Hi guys,

I am facing a Dynamic SQL generation is not supported against multiple base tables.problem when i try to insert data into another table on the same database.

The error is at

da.Update(ds, "abc")

i have two tables : Alarmss and record.

The record table will hold any data that is inserted into the database and then compare it with the predefined data on the Alarmss table.

the sql for the ms access database is this:

sql = "SELECT Alarmss.Alarms, record.history FROM Alarmss LEFT JOIN record ON Alarmss.Alarms = record.history"

where i need to join two tables together in order for them to compare.

I am using dim da as my oledataadapter and dim ds as my dataset.

here is my coding i've done so far:

Public Class Form1

    Dim dsNewRow As DataRow
    Dim inc As Integer
    Dim da As OleDb.OleDbDataAdapter
  
    Dim ds As New DataSet
   
    Dim sql As String
   
    Dim Email As String
    Dim DotCom As String


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =E:\My documents\TP\sem 6 MPSIP\more to project\mpsip things needed\work from here!!\table mapping\abc.mdb"
        da = New OleDb.OleDbDataAdapter(sql, con)
        con.Open()


        sql = "SELECT Alarmss.Alarms, record.history FROM Alarmss LEFT JOIN record ON Alarmss.Alarms = record.history"

        'select Artists.ArtistID, Artists.ArtistName, Albums.Name, Albums.PublishDate from()Artists inner join Albums on Artists.ArtistID = Albums.ArtistID where()Artists.ArtistID = 1
        'SELECT column_name(s) FROM(table_name1)INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

        da = New OleDb.OleDbDataAdapter(sql, con)

        da.Fill(ds, "abc")
     

        MsgBox("A Connection to the Database is now open")

        con.Close()

        MsgBox("The Connection to the Database is now Closed")

        ' to connect to Alarmss DATABASE






    End Sub

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

        Dim cb As New OleDb.OleDbCommandBuilder(da)



        'dadRules.UpdateCommand.CommandText = "update command text"


        dsNewRow = ds.Tables("abc").NewRow()



        'how to insert more than two column in one go

        'OKOK+CMGR REC READ+659111970109/07/06,15:54:46+3hi OK

        ' dsNewRow.Item(1) = TextBox1.Text.Substring(9, 9)

        ' dsNewRow.Item(2) = TextBox1.Text.Substring(21, 8)

        '  MsgBox(TextBox1.Text)

        dsNewRow.Item("history") = TextBox1.Text


        ds.Tables("abc").Rows.Add(dsNewRow)

        da.Update(ds, "abc")
     
        MsgBox("New Record added to the Database")





    End Sub
End Class

hope someone can help me. Thnks in advance.

regards,

Charles

 

[jmcilhinney]

If your SELECT statement includes a join then you can't use a CommandBuilder. It can only update one table and if there's two in your query then it doesn't know which to pick. You'll need to write your own UPDATE statement.

 

[clzanas]

If use a sql database?

Hi jmcilhinney,

thnks for the reply. If i change the database to a sql database, will i encounter the same problem?

And if i continue to use the same ms access database how do i write the select statement for the update statement and do i need to create a new dataset for every select statement?

thnks once again for helping me.

regards,

Charles

 

[jmcilhinney]

If you use reserved words as column names then you'll have the same issue every time you use a CommandBuilder, no matter what database you use.

[ame=http://www.vbforums.com/showthread.php?t=469872]Retrieving and Saving Data in Databases[/ame]