locked
-Syntax error in INSERT INTO statemen- I searched in this forum but couldn't find a solution! Please take a look and help! RRS feed

  • Question

  • User-575310684 posted

    I apologize to mod of this forum if this post is in the wrong place!  Here is the issue:

    Error message:
    {"Syntax error in INSERT INTO statement."}

    Stack Trace:

    [OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
       System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +1341831
       System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +45
       System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +2323
       System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) +42
       System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) +176
       CRUD.AddTitleDetail() in C:\Documents and Settings\BRIAN NGUYENHUYNH\Desktop\LIBRARY\AddNew.aspx.vb:76
       CRUD.btnConfirm_Click(Object sender, EventArgs e) in C:\Documents and Settings\BRIAN NGUYENHUYNH\Desktop\LIBRARY\AddNew.aspx.vb:40
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
       System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565


    My code:
    myConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath("Library Database Final_Nov_18.mdb")
            myConn.Open()
            Dim DSLibrary As New DataSet
            Dim LibrarySQL As String = "Select * from Library"
            Dim daLibrary As New OleDb.OleDbDataAdapter(LibrarySQL, myConn)
            daLibrary.Fill(DSLibrary, "Library")
            Dim newRowLibrary As DataRow
            newRowLibrary = DSLibrary.Tables("Library").NewRow

            newRowLibrary.Item("Title") = txtTitle.Text.Trim

            Dim temp1 As String = GetAuthorID(txtLastname.Text.Trim, txtFirstname.Text.Trim)
            newRowLibrary.Item("AuthorID") = Convert.ToInt32(temp1)

            Dim temp2 As String = GetPublisherID(txtPublisher.Text.Trim)
            newRowLibrary.Item("PublisherID") = Convert.ToInt32(temp2)

            'newRowLibrary.Item("Year") = txtYear.Text.Trim

            newRowLibrary.Item("MediaTypeID") = txtMediaType.Text

            newRowLibrary.Item("SubjectID") = txtSubject.Text

            ' newRowLibrary.Item("NumberOfCopy") = txtNumberOfCopy.Text.Trim

            'newRowLibrary.Item("Description") = txtDescription.Text.Trim
            '  newRowLibrary.Item("DateUpdate") = Today()

            DSLibrary.Tables("Library").Rows.Add(newRowLibrary)
            Dim cb As New OleDb.OleDbCommandBuilder(daLibrary)
            daLibrary.Update(DSLibrary, "Library")
            MsgBox("New title has been added into the library", MsgBoxStyle.OkOnly, MsgBoxStyle.Information)



    ===================
    I have checked all the type/format of data when being inserted into the table in Access and they are same type (int or text). But i still cannot find the problem! Even I deleted all the relationship between the table like Author table, Publisher table with the main table Library so that data can be insert freely into Library table but it still remains this problem!

    It stops at line         daLibrary.Update(DSLibrary, "Library")

     

    Please advise!

     

    Thursday, December 11, 2008 9:08 PM

Answers

  • User-821857111 posted

    Year - number/--> later changed to text
     

    YEAR is a reserved word: http://support.microsoft.com/kb/321266

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 14, 2008 4:03 PM
  • User-821857111 posted

     

    But I left it blank, I didn't touch it in the code

    But the code generators won't have ignored it.  Even if there is no value, I suspect that the Auto-generated code still includes it in a command somewhere.  Try renaming and regenerating the datasets. 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 15, 2008 3:15 PM
  • User-821857111 posted

    I'm not certain that the way you are getting the new ID is very sound.  Have a look at this: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/791f1532-d161-4ba5-a6e4-17467aacc35e/

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 16, 2008 2:27 AM
  • User-821857111 posted

    I'm not sure about tableadapters.  I don't use them.  But generally, names are not guaranteed to be unique, so you may be getting a different row altogether.

    Since your next question is not related to the subject of the Access sub-forum, or the title of the thread you started, you should aks it in the Web Forms forum.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 16, 2008 3:53 PM

All replies

  • User-1199946673 posted

    Syntax Error in INSERT INTO statement...

    I do not see any INSERT statement, so how could we possibly know what's wrong with it?

    Maybe this will help you?

    Thursday, December 11, 2008 9:52 PM
  • User-575310684 posted

    Thanks for the reply! 

     I don't use Insert statement but I insert the into the dataset and have the dataadapter and the command builder to do the work as you can see in a couple last line of code when I declare a 'cb' as oledb command builder. It works fine for the table Author and Publisher when I insert new author name and publisher name but it doesnt not work on the Library table! It just weird when the method works on this particular table but does not on another table! I cant find what seems to be the problem! Anyway other advise?

    Friday, December 12, 2008 3:07 AM
  • User-821857111 posted

    Hopefully, this will give you some pointers:  http://www.mikesdotnetting.com/Article.aspx?ArticleID=76

     

     

    Friday, December 12, 2008 5:10 AM
  • User-575310684 posted

     Thanks Mike,

    I read your articles. Pretty good! It gave me some guide to tracd the problem . Yet, problem still remains. As the code I posted above, now i just try add the Title of the media only; I clear all other line just leave one line: newRowLibrary.Item("Title") = txtTitle.Text.Trim and run it. It still the same error, which I dont know what kind of "mistake" this falls in in the four/five causes as in your article.! Please advise!

     

    Friday, December 12, 2008 6:57 PM
  • User-821857111 posted

    Basically, I pointed you to the article because it tells you that the actual SQL that is being executed contains the error.  Without knowing what SQL statement the TableAdapter is firing makes diagnosis very difficult. But start with checking that you are not using reserved words as field or table names first.

     

    Saturday, December 13, 2008 12:10 PM
  • User-575310684 posted

     Oh I see! Unless the word "Library" is a reserved word. I dont see any other field in that table is a reserve word! I'll check the Library word! Will get back to you! Thanks

    Saturday, December 13, 2008 5:32 PM
  • User-821857111 posted

    Library isn't in the list of reserved words for Jet: http://support.microsoft.com/kb/321266 or Access: http://support.microsoft.com/kb/286335.

    What's the generated SQL and what values are you trying to insert?

     

     

    Saturday, December 13, 2008 6:00 PM
  • User-575310684 posted

    The table library structure is:

    ID - auto number

    Title - String

    AuthorID - Number

    MediaTypeID- Number

    SubjectID - Number ( similar to mediatype)

    Year - number/--> later changed to text

    DateUpdate - Datetime/--> later changed to text and value taken from function Today() but then just leave it blank since i still dealing with this error, so I just leave it blank for now

    NumberOfCopy Number /--<now leave it blank!

    Comment - String

     

    I'm sorry but I dont know what the generated SQL is. As the code above, i first update in the dataset then use dataAdapter and oledb command builder to update the database. I did the insert new author or new publisher and the similar code worked fine for these two tables.

    On the form fLibrary the datasource of mediatype and subjecttype is taken from table Media Type which contain (MediatypeID and MediaTypeName) and table SubjectType (SubjectID, SubjectName). All the ID's are auto number. When they are foreign key (in table Library) thier types are nunber.

     Please advise!

     

     

    Sunday, December 14, 2008 3:43 PM
  • User-821857111 posted

    Year - number/--> later changed to text
     

    YEAR is a reserved word: http://support.microsoft.com/kb/321266

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 14, 2008 4:03 PM
  • User-575310684 posted

     Mike,

    But I left it blank, I didn't touch it in the code. Yes, later I will have to change it to something like "iyear" but I have tried just to use the field "title" only, i just add a new record with title only and it still runs to the error!

     

    Monday, December 15, 2008 1:06 PM
  • User-821857111 posted

     

    But I left it blank, I didn't touch it in the code

    But the code generators won't have ignored it.  Even if there is no value, I suspect that the Auto-generated code still includes it in a command somewhere.  Try renaming and regenerating the datasets. 

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, December 15, 2008 3:15 PM
  • User-575310684 posted

     You are right, Mike! I got it work! But there is something really weird. How come the code run fine and no error thrown but sometimes the record i have just entered is updated into the table, sometime doesn't???

    For example, in the main form, I let user enter author name then it "check" button to check if that author already in the table "Author" if not, insert new author into that table and then I wrote a function to load the AuthorID of that new author (using author lastname and firstname) and sometimes it loads the authorid sometime it gets "bugging" as following:

    There is no row at position 0

     

    Line 207:        DAAuhtor.Fill(tmpDSAuthor, "Author")
    Line 208: Dim tmpAuthorID As String
    Line 209: tmpAuthorID = tmpDSAuthor.Tables("Author").Rows(0).Item("AuthorID")
    Line 210:
    Line 211: Return tmpAuthorID
    Let talk about the "updating new author" process,  after I insert the new author into the table should I use some kind of function to kind of refresh the table? if so, how can i do it? because sometimes i can load the authorid sometimes can't!

     

    Monday, December 15, 2008 6:49 PM
  • User-821857111 posted

    I'm not certain that the way you are getting the new ID is very sound.  Have a look at this: http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/791f1532-d161-4ba5-a6e4-17467aacc35e/

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 16, 2008 2:27 AM
  • User-575310684 posted

     Mike,

    You have gone along with me to help me solving one problem after another. I truly appreciate it! Thank you. Yeah I know that that way of retrieving the ID is the worse one. Before doing that, I have researched another way but couldn't find one. The post you sent seems pretty good but I don't understand it, and I'll try to learn it (I'm a beginner). As a way more experienced than me in this field, what you think that could be a problem of this retrieving id of mine? I'm trying to think. I know it's not a good way, but why is it not?The only thing I think is maybe it takes longer? since keyword are longer string! But I will try to switch to the solution you suggest when i understand that!

    Oh by the way, i have other questions not relate to this topic if you can help me out again:

    I display the library information on a gridview (after user search with some keyword), and thank to this forum, i found a way to add in a checkboxes column. The next task is user will pick the record to print out. To attaint this there are alot of tasks. But for now, let just set asides the other tasks, I just want to ask you how can I pass a dataset (contains records chosen by user) to another page (that has just a gridview) and becomes datasource of that gridview?

    Thank you very much!

     

    Tuesday, December 16, 2008 3:05 PM
  • User-821857111 posted

    I'm not sure about tableadapters.  I don't use them.  But generally, names are not guaranteed to be unique, so you may be getting a different row altogether.

    Since your next question is not related to the subject of the Access sub-forum, or the title of the thread you started, you should aks it in the Web Forms forum.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 16, 2008 3:53 PM