none
Syntax error creating a new ado.net DataTable row RRS feed

  • Question

  • Hei all,
    I got an access database with form language strings, where I want to add a new table row (which is a copy of the English table row) if the user select a language not in the database. Everything is working properly until the update statement, there I get the error "Syntax error in the INSERT-Statement".
    I have tried to set a BreakPoint on the line: 

    daNew.Update(dsNew, "NewLanguage")  'update the database

    and the dsNew DataSet shows the line inserted with all columns filled as it should - but still I get this Syntax error.
    The code is as follows:

    		'get all tabel names in the database
                    Dim tblrestrictions As String() = New String() {Nothing, Nothing, Nothing, "TABLE"}
                    Dim dt As DataTable = m_ConContacts.GetSchema("tables", tblrestrictions)
    
                    For i As Integer = 0 To dt.Rows.Count - 1
                        Dim strTabelName As String
                        strTabelName = dt.Rows(i)(2).ToString()
                        If Microsoft.VisualBasic.Strings.Left$(strTabelName, 3) = "frm" Then    'insert new language if the three first letters are = "frm"
                            Debug.Print(strTabelName)
                            'get a copy of the english language table
                            strSql = "SELECT * FROM "
                            strSql = strSql & strTabelName
                            strSql = strSql & " WHERE CStr([Language]) ="
                            strSql = strSql & Chr(39) & "English" & Chr(39)
    
                            Dim daEnglish As OleDbDataAdapter = New OleDbDataAdapter(strSql, m_ConContacts)
                            Dim dsEnglish As DataSet = New DataSet
                            daEnglish.Fill(dsEnglish, "EnglishLanguage")
    
                            For n As Integer = 0 To dsEnglish.Tables("EnglishLanguage").Rows.Count - 1
                                strSql = "SELECT * FROM " & strTabelName
                                Dim daNew As OleDbDataAdapter = New OleDbDataAdapter(strSql, m_ConContacts)
                                Dim dsNew As New DataSet
                                daNew.Fill(dsNew, "NewLanguage")
                                Dim tblNew As DataTable
                                Dim newRow As DataRow
                                tblNew = dsNew.Tables("NewLanguage")
                                newRow = tblNew.NewRow()
                                'read the english rov data and add the values to the new row data
                                For x As Integer = 1 To dsEnglish.Tables("EnglishLanguage").Columns.Count - 1
                                    Dim strColName As String = dsEnglish.Tables("EnglishLanguage").Columns.Item(x).ToString
                                    newRow(strColName) = dsEnglish.Tables("EnglishLanguage").Rows(n).Item(x).ToString 'copy the english text into the new columns
                                Next
                                newRow("Language") = List1.SelectedItem.ToString
                                tblNew.Rows.Add(newRow)
                                daNew.MissingSchemaAction = Data.MissingSchemaAction.AddWithKey
                                Dim myDataRowsCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(daNew)
                                daNew.Update(dsNew, "NewLanguage")  'update the database
                            Next
                        End If
                    Next

    The error shows as follows:

    Syntax error in the INSERT INTO-clause 
       at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
       at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
       ved System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
       at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
       at WindowsApplication1.frmCountry.cmdOK_Click(Object sender, EventArgs e) i C:\VB_Net Programmer\MyContacts\MyContacts_ADO.NET\MyContacts\frmCountry.vb:linje 338 - cmdOK_Click

    I have a suspicion about the field name = Language is the culprint, but can not find any alternative. This field is also the primary key as well.
    I would be more than happy if someone could take a look at this code and point out where I have gone wrong.

    Kind regards
    Jorgen



    levesen


    • Edited by levesen Wednesday, November 14, 2012 9:29 PM
    Wednesday, November 14, 2012 9:27 PM

Answers

  • Hi again you both,
    looks like I found the problem(s), it sure helps to write things down to clear ones mind.
    It turned out to be e twofold problem:

    Firstly one of the dataset's in the database contained the wordings "No" and "yes", that was rewritten to strNo and strYes.
    Secondly the  line: daNew.Update(dsNew, "NewLanguage") came a bit early in the code. Moved the line down.
    The finished and working code now reads:

    For i As Integer = 0 To dt.Rows.Count - 1
                        Dim strTabelName As String
                        strTabelName = dt.Rows(i)(2).ToString()
                        If Microsoft.VisualBasic.Strings.Left$(strTabelName, 3) = "frm" Then    'insert new language if the three first letters are = "frm"
                            Debug.Print(strTabelName)
                            'get a copy of the english language table
                            strSql = "SELECT * FROM "
                            strSql = strSql & strTabelName
                            strSql = strSql & " WHERE CStr(MyLanguage) ="
                            strSql = strSql & Chr(39) & "English" & Chr(39)
    
                            Dim daEnglish As OleDbDataAdapter = New OleDbDataAdapter(strSql, m_ConContacts)
                            Dim dsEnglish As DataSet = New DataSet
                            daEnglish.Fill(dsEnglish, "EnglishLanguage")
    
                            For n As Integer = 0 To dsEnglish.Tables("EnglishLanguage").Rows.Count - 1
                                strSql = "SELECT * FROM " & strTabelName
                                daNew = New OleDbDataAdapter(strSql, m_ConContacts)
                                daNew.Fill(dsNew, "NewLanguage")
                                Dim newRow As DataRow
                                newRow = dsNew.Tables("NewLanguage").NewRow
                                'read the english rov data and add the values to the new row data
                                For x As Integer = 1 To dsEnglish.Tables("EnglishLanguage").Columns.Count - 1
                                    Dim strColName As String = dsEnglish.Tables("EnglishLanguage").Columns.Item(x).ToString
                                    newRow(strColName) = dsEnglish.Tables("EnglishLanguage").Rows(n).Item(x).ToString 'copy the english text into the new columns
                                Next
                                newRow("MyLanguage") = List1.SelectedItem.ToString
                                dsNew.Tables("NewLanguage").Rows.Add(newRow)
                            Next
    
                            Dim myDataRowsCommandBuilder As New OleDbCommandBuilder(daNew)
                            daNew.Update(dsNew, "NewLanguage")  'update the database
    
                            dsNew.Dispose()
                            daNew.Dispose()
                        End If

    Once again I thank you very much for your assistance.
    Sincerely yours
    Jorgen


    levesen

    • Marked as answer by levesen Sunday, November 18, 2012 10:16 PM
    Sunday, November 18, 2012 10:16 PM

All replies

  • Hi,

    Please check if this article helps or not: http://support.microsoft.com/kb/892608?wa=wsignin1.0

    Sunday, November 18, 2012 3:37 PM
  • Hi Jorgen,

    If it's anything like the last problem I helped you out with, it could very well be a column-name problem as you suspect (if I recall, you had to enclose one of your column names in brackets to solve the issue in your other thread, http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/ae5b72a0-0cb4-43f5-8fc6-1f1a52ca56e4).

    Perhaps you need to do something similar to solve this problem too ...


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, November 18, 2012 4:43 PM
  • Hi again you both,
    looks like I found the problem(s), it sure helps to write things down to clear ones mind.
    It turned out to be e twofold problem:

    Firstly one of the dataset's in the database contained the wordings "No" and "yes", that was rewritten to strNo and strYes.
    Secondly the  line: daNew.Update(dsNew, "NewLanguage") came a bit early in the code. Moved the line down.
    The finished and working code now reads:

    For i As Integer = 0 To dt.Rows.Count - 1
                        Dim strTabelName As String
                        strTabelName = dt.Rows(i)(2).ToString()
                        If Microsoft.VisualBasic.Strings.Left$(strTabelName, 3) = "frm" Then    'insert new language if the three first letters are = "frm"
                            Debug.Print(strTabelName)
                            'get a copy of the english language table
                            strSql = "SELECT * FROM "
                            strSql = strSql & strTabelName
                            strSql = strSql & " WHERE CStr(MyLanguage) ="
                            strSql = strSql & Chr(39) & "English" & Chr(39)
    
                            Dim daEnglish As OleDbDataAdapter = New OleDbDataAdapter(strSql, m_ConContacts)
                            Dim dsEnglish As DataSet = New DataSet
                            daEnglish.Fill(dsEnglish, "EnglishLanguage")
    
                            For n As Integer = 0 To dsEnglish.Tables("EnglishLanguage").Rows.Count - 1
                                strSql = "SELECT * FROM " & strTabelName
                                daNew = New OleDbDataAdapter(strSql, m_ConContacts)
                                daNew.Fill(dsNew, "NewLanguage")
                                Dim newRow As DataRow
                                newRow = dsNew.Tables("NewLanguage").NewRow
                                'read the english rov data and add the values to the new row data
                                For x As Integer = 1 To dsEnglish.Tables("EnglishLanguage").Columns.Count - 1
                                    Dim strColName As String = dsEnglish.Tables("EnglishLanguage").Columns.Item(x).ToString
                                    newRow(strColName) = dsEnglish.Tables("EnglishLanguage").Rows(n).Item(x).ToString 'copy the english text into the new columns
                                Next
                                newRow("MyLanguage") = List1.SelectedItem.ToString
                                dsNew.Tables("NewLanguage").Rows.Add(newRow)
                            Next
    
                            Dim myDataRowsCommandBuilder As New OleDbCommandBuilder(daNew)
                            daNew.Update(dsNew, "NewLanguage")  'update the database
    
                            dsNew.Dispose()
                            daNew.Dispose()
                        End If

    Once again I thank you very much for your assistance.
    Sincerely yours
    Jorgen


    levesen

    • Marked as answer by levesen Sunday, November 18, 2012 10:16 PM
    Sunday, November 18, 2012 10:16 PM
  • You know, I didn't even notice that you had the .Update() in the wrong place!

    Glad that you got it figured out!!  =0)


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, November 19, 2012 1:25 AM