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

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!

 

Answer 1

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?

Answer 2

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?

Answer 3

User-821857111 posted

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

 

 

Answer 4

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!

 

Answer 5

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.

 

Answer 6

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

Answer 7

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?

 

 

Answer 8

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!

 

 

Answer 9

User-821857111 posted

Year - number/--> later changed to text

 

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

 

Answer 10

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!

 

Answer 11

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. 

 

Answer 12

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!

 

Answer 13

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/

 

Answer 14

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!

 

Answer 15

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.