none
Syntax Error in INSERT INTO Statement. PLEASE HELP I'M DESPERATE! I TRIED EVERYTHING! RRS feed

  • Question

  •  

    Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegister.Click
            If Page.IsValid Then

                Dim strName, strAddress, strCountry As String
                Dim strPhone, strEmail, strPwd As String
                Dim intUserID As Integer

                strName = txtName.Text
                strAddress = txtAddress.Text
                strCountry = lstCountry.SelectedValue
                strPhone = txtPhone.Text
                strEmail = txtEmail.Text
                strPwd = txtPwd1.Text

                Dim objDBMgmt As New DbMgmt
                objDBMgmt.Connect()

                Dim strSqlCmd As String
                strSqlCmd = "INSERT INTO User " & _
                "([Name], Address, Country, Phone, Email, Passwd) " & _
                "VALUES ('" & strName & "','" & strAddress & "','" & _
                strCountry & "','" & strPhone & "','" & _
                strEmail & "','" & strPwd & "')"
                objDBMgmt.ExecuteNonQuery(strSqlCmd)

                strSqlCmd = "SELECT MAX(UserID) From User"
                intUserID = objDBMgmt.ExecuteScalar(strSqlCmd)

                Session("UserID") = intUserID

                objDBMgmt.Close()

                lblMsg.Text = "Registration is sucessful!"
                lblMsg.ForeColor = Color.Red
            End If
        End Sub

     

    And heres the error message:

     

    Syntax error in INSERT INTO statement.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

    Source Error:


    Line 46:
    Line 47:         sqlCmd.CommandText = cmd
    Line 48:         sqlCmd.ExecuteNonQuery()
    Line 49:
    Line 50:     End Sub
     

    Source File: C:\IHP\DbMgmt.vb    Line: 48

    Stack Trace:


    [OleDbException (0x80040e14): Syntax error in INSERT INTO statement.]
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
       Project.DbMgmt.ExecuteNonQuery(String cmd) in C:\IHP\DbMgmt.vb:48
       Project.Register.btnRegister_Click(Object sender, EventArgs e) in C:\IHP\Register.aspx.vb:64
       System.Web.UI.WebControls.Button.OnClick(EventArgs e)
       System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
       System.Web.UI.Page.ProcessRequestMain()

     

     

    I've checked the syntax a million times, and checked if the words are restricted, and ive tried it with the [] incase somethings restricted and without. It all doesnt work!! Help!!! I'm about to go crazy!

    (Sorry I'm new to this)

    Tuesday, August 7, 2007 6:54 PM

All replies

  • Hi there,

    First thing I notice is that the error refers to something that ISN"T in the code sample that you provided.    It might simply be that you have two sub functions and you are looking at the wrong one.

     

     

    Tuesday, August 7, 2007 7:00 PM
  • Code Snippet

    Public Sub ExecuteNonQuery(ByVal cmd As String)

     

    sqlCmd.CommandText = cmd

    sqlCmd.ExecuteNonQuery()

     

    End Sub

     

     

    This is from my DbMgmt.vb file.. its the only other place where i can see the error refer-ing to.. I really dont know what to do.. Sad

     

     

    Tuesday, August 7, 2007 7:12 PM
  • Hi,

     

    Just a small tip on debugging SQL Statements in your code. Try these:

    1. MessageBox or Break on the statements after the values have been placed in the SQL statement. This is to see if the error is being caused by the values containing special characters (single quote) which will cause your SQL Statement to fail.
    2. Try placing all of your fieldnames and tablenames in square brackets. It might be that you are using a reserved word.
    3. Try outputing the generated SQL Statement in a textbox. Copy and execute it on SQL Management Studio and see where the error is.
    4. Also, it will be very useful if you have access to a SQL Profiler. You can see all of the scripts executing. Because the error might not be originating from your SQL Script but from a trigger or something.

    Hope this helps.

     

     

     

    cheers,

     

    Paul June A. Domag

    Tuesday, August 7, 2007 7:13 PM
  • I have tried (2),

    (1) how exactly do i do that?

    (3) what is sql management studio

     

    ** I'm sorry im really new to this, i wouldn't seek for help if i wasnt at my wit's end -_-

    Tuesday, August 7, 2007 7:20 PM
  • Ok, first thing we need to do here is verify the SQL, so you need to use the response object:

    Code Snippet

    Your code:

    Dim strName, strAddress, strCountry As String
                Dim strPhone, strEmail, strPwd As String
                Dim intUserID As Integer

                strName = txtName.Text
                strAddress = txtAddress.Text
                strCountry = lstCountry.SelectedValue
                strPhone = txtPhone.Text
                strEmail = txtEmail.Text
                strPwd = txtPwd1.Text

                Dim objDBMgmt As New DbMgmt
                objDBMgmt.Connect()

                Dim strSqlCmd As String
                strSqlCmd = "INSERT INTO User " & _
                "([Name], Address, Country, Phone, Email, Passwd) " & _
                "VALUES ('" & strName & "','" & strAddress & "','" & _
                strCountry & "','" & strPhone & "','" & _
                strEmail & "','" & strPwd & "')"

      Response.Write(strSqlCmd,true)

      'This should end it, if not...

      Exit Sub

      objDBMgmt.ExecuteNonQuery(strSqlCmd)

                strSqlCmd = "SELECT MAX(UserID) From User"
                intUserID = objDBMgmt.ExecuteScalar(strSqlCmd)

                Session("UserID") = intUserID

                objDBMgmt.Close()

                lblMsg.Text = "Registration is sucessful!"
                lblMsg.ForeColor = Color.Red

     

     

    Ok, add that line, and it will be like using a message box. use that against your data source and see if that is the problem

    Tuesday, August 7, 2007 7:21 PM
  • You can't use a message box in a WebApp, so ignore that, just use Response.Write of the response object.

    SQL Management is downloadable from MS, it a replacement of the SQL Enterprise Manager for MS SQL, when you use that it will help you trouble-shoot your sql statements.

    Also, since you are using OLEDB, I'm assuming that you are workign with Accss?  if so, then you can use the Access DB to verify your sql statement, by taking your information and pasteing it a query.

    Tuesday, August 7, 2007 7:25 PM
  • Build error - Overload resolution failed because no accessible 'Write' accepts this number of arguments - pointing to the line that i just added

     

    Tuesday, August 7, 2007 7:27 PM
  • Oh, get rid of the True part, my mistake.

    Tuesday, August 7, 2007 7:29 PM
  • sorry, but what do you mean by "use Response.Write of the response object." ?

     

    Yes i'm working with access, ok i will try it, thanks for your suggestions

    Tuesday, August 7, 2007 7:29 PM
  • You are in the code behind file of a web page right?  and you are capturing the click event?  The reason I say that is because the error is saying WebResponse. 

    Well a part of the web application is the Response object, which built into the page object.  So if you type Reponse.write(Some string here)

    it will write out on to the page your information, now for the exit sub, it is there so you will exit your code with out running the rest, because we really need to make sure you SQL is being parsed correctly. 

    Also, just as a side note, you will defenately not want to do that way in the future, you want to use Parameter objects, which is another discussion<g>.

     

     

    Tuesday, August 7, 2007 7:33 PM
  •  

    ok, ive added the Response.Write(strSqlCmd)

     

    but it doesn't really seem to do anything? or could it be i'm looking into the wrong place? where am i supposed to get this information from?

    Tuesday, August 7, 2007 7:36 PM
  • It should be on the page that rendered, you will probably see it at the bottom, do a ctrl-f in the web browser and look for insert that should show you the code.  If not, do a view source, it might be hidden.

     

    Tuesday, August 7, 2007 7:40 PM
  • ive done a view source as well, but still nothing

    Tuesday, August 7, 2007 7:44 PM
  • Add this line after Respone.write:

    Response.End()

     

    Tuesday, August 7, 2007 7:47 PM
  • still nothing Sad

     

    argh i'm really sorry, bugging you to end with these newbie questions and stuff..

    Tuesday, August 7, 2007 7:51 PM
  • Did you click on the registration button?

    Tuesday, August 7, 2007 7:54 PM
  • INSERT INTO [User] ([Name], [Address], [Country], [Phone], [Email], [Passwd]) VALUES ('sdfdsf', 'sdfsdf', 'Singapore', '234234', 'rseresr@dfsdfdsf.com', 'testing')

     

     

    That's the reply I got..

    Is there anything wrong with this?

    Tuesday, August 7, 2007 7:58 PM
  • There are two problems that you have to solve here.

    The first problem is that you need to learn how to debug ASP.NET pages.  There are lots and lots of resources on the web, books, etc. that will teach you how to do this.  If you don't know how to do this, it's going to be very hard for you to solve any problems that you encounter while developing.

    The second problem is that you should never form SQL by concatenating string values entered on the client.  There are two reasons for this.  Google "SQL injection" for the first.  The second is that even if the user isn't deliberately trying to exploit this, he can do it accidentally just by entering a field value that contains a single quote.

    You should be using parameterized SQL, and setting the parameters' values to the values in your UI controls.  For instance:

       SqlCommand cmd = new SqlCommand("INSERT INTO User (Name, Address, City...) VALUES (@Name, @Address, @City...", myConnection)
       cmd.Parameters.AddWithValue("Name", txtName.Text);
       cmd.Parameters.AddWithValue("Address", txtAddress.Text);
       cmd.Parameters.AddWithValue("City", txtCity.Text);

    ...and so on.
    Tuesday, August 7, 2007 8:00 PM
  • Ok, take that and using open the database with MS Access and do the following:

    Click on Queries (left hand side).

    Click on New (top)

    Make sure that Design view is selected above and click on OK,

    Click on Close,

    up at the top it will say SQL, click on that, and paste in the text you got above, then click on ! mark and run the query, that will give you the error, probably one of the values is of the wrong type or something.

     

    Tuesday, August 7, 2007 8:02 PM
  • thanks. ive done that, but there is no error.. the record is inserted into the database..
    Tuesday, August 7, 2007 8:10 PM
  • Ok, now we know that isn't the problem you SQL is good, (if we ignore the guy who just complained about you not using Parameter objects).. Now we know it is after that. 

    Get rid of my previous example and lets focus on the next piece:

    Code Snippet

    strSqlCmd = "SELECT MAX(UserID) From User"
                intUserID = objDBMgmt.ExecuteScalar(strSqlCmd)

                Session("UserID") = intUserID

                objDBMgmt.Close()

     

     

    Lets change the code to read the following way:

    Code Snippet

    strSqlCmd = "Select @@IDENTITY"
                intUserID = objDBMgmt.ExecuteScalar(strSqlCmd)

                Session("UserID") = intUserID

                objDBMgmt.Close()

     

     

    Re compile then click on your registration button and see if that works now.

    Tuesday, August 7, 2007 8:42 PM
  • To be honest I'd vote for using parameterised Sql too. If there's a character in one of the string values, for example the @, that's causing a problem then using parameters will solve the problem.

    Sean
    Wednesday, August 8, 2007 1:54 AM
  • Thanks for the replies guys.

     

    I've tried changing to

     

    strSqlCmd = "Select @@IDENTITY"

     

    I get an error "Operation must use an updatable query."

     

     

     

    As for the people who encouraged me to use parameters instead, its not that I do not want to use it, its that i dont know the proper syntaxes, and the other thing is, rms_michael has already kindly assisted me in testing that section of the code itself and it appears that it wasnt the problem.

    Wednesday, August 8, 2007 5:07 AM
  •  

    bump! anyone care to give any suggestions? any people who are real good at this care to take a look at my codes?

    im really new to this, so this is simple stuff, but its driving me crazy?

     

    can anyone experienced please take 5 mins to look through and offer a little help? thanks in advance for your time taken.

    Wednesday, August 8, 2007 8:07 AM
  • > Ok, now we know that isn't the problem you SQL is good, (if we ignore the guy
    > who just complained about you not using Parameter objects)..


    You're not just ignoring me.  You're ignoring the fact that the original problem he reported was a syntax error in the INSERT INTO statement.  You've moved on from that problem to the next.  But you haven't solved the first problem yet. 

    The original poster dumped a copy of the SQL, ran it against his database, and it didn't have a syntax error.  Well, he didn't change the code.  So why doesn't the SQL have a syntax error any more?  Because he changed something else.  Since the code is clearly vulnerable to bad input, perhaps what he changed was what he was inputting.

    In fact, the problem's still there in the original code.  Type in an address with an apostrophe in it and click the button.  You'll get a syntax error in the INSERT INTO statement.

    To the original poster:  If you're building software that's going to be used under conditions that you don't control, you have to handle input that you don't control.  If the software's going to be querying a database, you have to learn parameter queries and use them.  This is not an optional, nice-to-have feature.

    Someone could enter an address in your form like:

       xxx'; DROP TABLE Users;

    and then your user table would be gone.  You would then have the fun experience of explaining to whoever is actually paying for this project that you left their site vulnerable to a widely-known exploit, and now nobody can log in, because you didn't want to take the time to learn how to formulate parameter queries.
    Wednesday, August 8, 2007 8:54 AM
  •  

    Ok from your comments and a few other user's comments, and some simple looking around on the net, I realised what you're trying to tell me here - Even if this issue is fixed it doesnt fix the weakness of the system.

     

    I understand that with my codes, it is extremely easy to cause an error by key-ing in some certain values, as you mentioned such as an apostrophe. It is also vulnerable to "sql injection" as mentioned earlier by someone (i went to read up a little about this).

     

    However, the point is that what I'm doing actually for a pretty big school project. Thus, it would really screw me over if I dont do this properly.

     

    I would like to point out that I have just started learning this only very very recently. I also have a very limited time constraint. Therefore I cant "learn the proper way" at this point of time, nor can I learn more about debugging and stuff. I have tried to read up about these, but much of these is way beyond my level, and I cant really understand everything.

     

    ** Regarding the previous poster, thanks for your advice. One thing I would like to say is, I did not change what I was inputting. **

     

    Another thing that I would like to ask is, couldn't I just put in some validation controls with my textboxes and stuff to prevent users from accidentally typing in stuff like apostrophe?

     

    For now I do not have the time to learn to formulate parameter queries. After this project is done though, I will read up about it so that I can do it the proper way in the future. Right now I'm literally begging anyone out there who knows this stuff, spare me a couple mins to see if theres anything wrong with what I've posted.

    Wednesday, August 8, 2007 9:17 AM
  • I would suggest passing all the values as parameters and avoiding using concatenation. It is pretty easy to miss some single quote or other special character that will trigger that error.

     

    Wednesday, August 8, 2007 10:46 AM
    Moderator
  • Robert gave you most of the code in his earlier post, you just need to do the same as he did for all of your parameters. It's a lot easier to do it that way than to try to think of all the characters a user could enter than might mess things up. And there's a reasonable chance it could solve the problem you're having right now too.

    It's actually quicker and easier to add parameters than to concatenate the values onto the insert statement.

    Also bear in mind that whoever's testing this school project should be trying to catch you out and put in all kinds of weird and wonderful characters, not just apostrophes.

    Sean
    Wednesday, August 8, 2007 12:28 PM
  • I suspect that because you using a custom object to run the queries that you are passing to it, that it is the problem.  I would verify that the connection is going to the correct DB and that your object is set up to send the data correctly.  At the moment, I would say that even though you are not doing the sql string correctly, that it is doing the job at the moment, and not the cause for the problem at the moment.  Ignroe my code for Identity, and just use what you had previously.  Since that ran and gave an error, then code must not be the block where you identified the problem exists, unless you did use a differn't value as one of the previous posers suggested.

     

     

     

    Wednesday, August 8, 2007 2:52 PM
  • 1. How do i "verify that the connection is going to the correct DB and that your object is set up to send the data correctly" ?

     

    2. Maybe it might have something to do with the Access Database that I've linked it to? I have all the codes to connect it to the database, and its probably correct, I will double and triple check just incase

    - However, just a quick question about the database, i'm not familiar with databases..

     

    The database that I have made, does it only require me to have the tables I need inside? For example, the table "User" contains the attributes "UserID, Name, Address, Country, Phone, Email, Passwd". The entries are entirely blank. The data-type and length have already been set as well.


    Is there anything else I need to do with the database? I'm worried that it might be due to something I have left out in the database?

    Wednesday, August 8, 2007 3:41 PM
  • Post the code where you connect to the database and setup the adapter. if it is possiable, put the whole class object you have in there so we can see what you did.  remove anything like passwords or items that might be the problem.  I suspect that it is in this area your problem lies.

     

    Wednesday, August 8, 2007 3:45 PM
  • Hmm i highly doubt it as the codes to connect to database etc were provided to us. And i have checked through for errors. Anyway, its getting very late over here and my eyelids are already closing as I'm typing this.

     

    I will post the codes first thing in the morning. Another thing is, regarding Access Databases, to connect to and use them, all i need are the Tables with the attributes in them right? then i set a primary key and i'm done?

     

    Is there anything else I need to set? I'm afraid some of the implications might have been due to the database?

     

    Thanks once again. Thanks for your patience and your help so far.

    Wednesday, August 8, 2007 5:05 PM
  •  

    bump
    Thursday, August 9, 2007 6:03 AM