Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Regular Coder
    Join Date
    Feb 2014
    Posts
    235
    Thanks
    46
    Thanked 0 Times in 0 Posts

    INSERT INTO error?

    Hello

    I am getting a 'syntax error in INSERT INTO' error in this script. I have compared the script to other examples, but can't see what is wrong. My script looks like this:

    Code:
    <%
    username = ""
    password = ""
    confirmPassword = ""
    fullname = ""
    strEmail = ""
    ErrorMessage = ""
    
    if request.form <> "" then
    username = Request.Form("username")
    password = Request.Form("password")
    confirmPassword = Request.Form("confirmPassword")
    fullname = Request.Form("fullname")
    strEmail = Request.Form("strEmail")
    
      if strEmail="" or password="" then
         ErrorMessage = "You must specify both email and password."
      end if
      if password <> confirmPassword then
        ErrorMessage = "Password and confirmation do not match."
      end if
    
      if ErrorMessage = "" then 
        set conn = Server.CreateObject("ADODB.Connection")
    
     conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    
        conn.Open("E:\myDatabase.mdb")
    
        set rs = Server.CreateObject("ADODB.recordset")
        rs.Open "Select * FROM userlist WHERE strEmail = '" & strEmail & "'", conn
        if rs.EOF = true then      
    
    sql="INSERT INTO userlist (username, password, fullname, strEmail) VALUES ('" & username & "','" & password & "', '" & fullname & "','strEmail')"
          conn.Execute(sql)
          Response.Redirect("Default.asp")
         else
          ErrorMessage = "Email address is already in use."
        end if
      end if
    end if
    
    if ErrorMessage <> "" then
      response.write("<p>" & ErrorMessage & "</p>") 
      response.write("<p>Please correct the errors and try again.</p>") 
    end if
    %>
    Where am I going wrong, please?

  • #2
    New Coder
    Join Date
    Jun 2005
    Location
    Blackpool. UK
    Posts
    98
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Show us what the query looks like when it is concatenated and we may be able to tell you.
    Chris

    Indifference will be the downfall of mankind, but who cares?

  • #3
    Regular Coder
    Join Date
    Feb 2014
    Posts
    235
    Thanks
    46
    Thanked 0 Times in 0 Posts
    Hello Chrishirst

    Thanks for your post.

    I am not too sure what you mean to be honest.

    I have tried the script in three different ways:

    Register1.asp:

    Code:
    sql="INSERT INTO userlist (username, password, strEmail) VALUES ('" & username & "','" & password & "', 'strEmail')"
          conn.Execute(sql)
    Register2.asp:

    Code:
    ' set varaible values from request.form
    
    my_username = Replace( Request.Form("username"), "'", "''" )
    my_password = Replace( Request.Form("password"), "'", "''" )
    my_strEmail = Replace( Request.Form("strEmail"), "'", "''" )
    
    'build insert statement
    
    sSQL="INSERT INTO userlist " &_
     "(username, password, strEmail)" &_
     " VALUES ('" & my_username & "', '" & my_password & "', '" & my_strEmail & "')"
    
    ' insert record
    
    connection.Execute sSQL
    Register3:

    Code:
    sql="INSERT INTO userlist (username, password, strEmail) VALUES ('username', 'password', 'strEmail')"
    Each alternative generates an INSERT INTO error, which makes me think it is not an INSERT INTO error at all.

    The data types in the mdb are all 'text', and the form is the same in each of the three files above:

    Code:
    Response.Redirect("Default.asp")
         else
          ErrorMessage = "Email address is already in use."
        end if
      end if
    end if
    
    if ErrorMessage <> "" then
      response.write("<p>" & ErrorMessage & "</p>") 
      response.write("<p>Please correct the errors and try again.</p>") 
    end if
    %>
    
    <h1>Register</h1>
    <form method="post" action="Register1.asp">
     <fieldset>
    <legend>Sign-up Form</legend>
    <ol>
    
    <li>
     <label>Username:</label>
    <input type="text" id="username" name="username" />
     </li>
    
    <li>
    <label>Password:</label>
    <input type="password" id="password" name="password" />
    </li>
    
    <li>
    <label>Confirm Password:</label>
    <input type="password" id="confirmPassword" name="confirmPassword" />
    </li>
    
    <li>
     <label>Email:</label>
    <input type="text" id="strEmail" name="strEmail" />
     </li>
    
    <li>
    <p><input type="submit" value="Register" /></p>
    </li>
    </ol>
    </fieldset>
    </form>
    I am not sure if this is meaningful?

    Thanks again for your time.

    Blue

  • #4
    New Coder
    Join Date
    Jun 2005
    Location
    Blackpool. UK
    Posts
    98
    Thanks
    0
    Thanked 4 Times in 4 Posts
    The ASP code is of no use, it is the SQL query AFTER it has be built into a string that will point to where the error is.


    ASP -> SQL debugging 101 - Send your queries to the browser so you can read them. instead of execute(SQL) use

    Response.Write(SQL)
    Exit
    Chris

    Indifference will be the downfall of mankind, but who cares?

  • #5
    Regular Coder
    Join Date
    Feb 2014
    Posts
    235
    Thanks
    46
    Thanked 0 Times in 0 Posts
    Hello Chris

    I have done this:

    'conn.Execute(sql)

    Response.Write(SQL)

    I don't get any error messages or any indication as to where the problem resides. I complete the form fields here ASP Register and I am taken to the 'Login' page. Nothing is inserted into the database.

  • #6
    Regular Coder
    Join Date
    Feb 2014
    Posts
    235
    Thanks
    46
    Thanked 0 Times in 0 Posts
    I have also inserted this in the Register1.asp file:

    Code:
    Response.Write "<hr/>DEBUG SQL:<br/>" & SQL & "<hr/>" & vbNewLine
    
    conn.Execute(sql)
    and got the following:

    DEBUG SQL:
    INSERT INTO userlist (username, password, strEmail) VALUES ('hello','hello', 'strEmail')
    --------------------------------------------------------------------------------

    Microsoft JET Database Engine error '80040e14'

    Syntax error in INSERT INTO statement.

    /schoolsReg/Register1.asp, line 51

  • #7
    New Coder
    Join Date
    Jun 2005
    Location
    Blackpool. UK
    Posts
    98
    Thanks
    0
    Thanked 4 Times in 4 Posts
    But what is the SQL query that is output to the browser and is visible? The exit() command stops the script so nothing else can overwrite the output from the response.write.
    Chris

    Indifference will be the downfall of mankind, but who cares?

  • #8
    Regular Coder
    Join Date
    Feb 2014
    Posts
    235
    Thanks
    46
    Thanked 0 Times in 0 Posts
    If I comment out my statement as follows:

    'conn.Execute(sql)

    and insert your:

    Response.Write(SQL)
    Exit

    I get this:

    Microsoft VBScript compilation error '800a040f'

    Invalid 'exit' statement

    /schoolsReg/Register1.asp, line 54
    Exit
    ----^

  • #9
    New Coder
    Join Date
    Jun 2005
    Location
    Blackpool. UK
    Posts
    98
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Sorry wrong languange.


    Response.End

    not exit
    Chris

    Indifference will be the downfall of mankind, but who cares?

  • #10
    Regular Coder
    Join Date
    Feb 2014
    Posts
    235
    Thanks
    46
    Thanked 0 Times in 0 Posts
    I get this on screen, Chris:

    INSERT INTO userlist (username, password, strEmail) VALUES ('Freddie','bill', 'strEmail')
    I typed in the form: Freddie (username), bill (password), and fKruger@hotmail.com for the email address. Only the email address is not appearing from what I can gather? Maybe it doesn't like strEmail. I have used that because it's a column in my MS Access database and it relates to another part of the site which has a form for a forgotten password.

    If you think that's the source of the problem, I suppose I could change it?

  • #11
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,901
    Thanks
    46
    Thanked 202 Times in 201 Posts
    Quote Originally Posted by Blue1 View Post
    INSERT INTO userlist (username, password, strEmail) VALUES ('Freddie','bill', 'strEmail')
    So what happens when you execute this query outside the asp page? If the fields in the DB are text it will not care about these values 'Freddie', 'bill', 'strEmail' ... Also do you realize that you are losing the email in that insert? instead of passing the value you seem to have passed the word "strEmail". The only thing I see that 'stands out' is maybe* those fields are trying to hit keywords (password or username might be reserved). What about putting [] around the fields? Note: You're also missing some stuff in your query which is why 'strEmail' is trying to be inserted instead of an actual email.

    Code:
    sql="INSERT INTO userlist ([username], [password], fullname, strEmail) VALUES ('" & username & "','" & password & "', '" & fullname & "','" & strEmail & "')"
          conn.Execute(sql)
          Response.Redirect("Default.asp")
         else
    But what happens when you run the query? (ie do not run it from asp page- what happens?)

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • Users who have thanked alykins for this post:

    Blue1 (06-02-2014)

  • #12
    Regular Coder
    Join Date
    Feb 2014
    Posts
    235
    Thanks
    46
    Thanked 0 Times in 0 Posts
    Hello Alykins

    Thanks for your reply.

    I have just run the script with the square brackets [ ] and it inserts the username, password, and email address into the database. So that's a mammoth step forward! Thank you.

    Yes, you are right: 'password' is a reserved word, though 'username' is not: List of Microsoft Jet 4.0 reserved words

    strEmail is the actual email and I can now see fKruger@hotmail.com inserted in the MDB.

    Strangely, after pressing 'submit', I can see in the address bar that I am still on Register1.asp and it's a blank page?

    I also have two other columns called fEmail and fPassword. These relate to another part of the site where a user has forgotten his password, and should be populated with exactly the same values as strEmail and password. How would I go about that?

    Thanks again for your help.

  • #13
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,901
    Thanks
    46
    Thanked 202 Times in 201 Posts
    Would need to see the code before this for the response.redirect not working...
    Code:
    Response.Redirect("Default.asp")
         else
          ErrorMessage = "Email address is already in use."
        end if
      end if
    end if
    
    if ErrorMessage <> "" then
      response.write("<p>" & ErrorMessage & "</p>") 
      response.write("<p>Please correct the errors and try again.</p>") 
    end if
    %>
    As for storing the values there are a lot of ways to do this. You could add a case statement and handle it there for the response redirect and then pass it as parameters to the webpage and then process the arguments on the receiving page.

    As for the keywords, yeah I would have bet $ that password was reserved- usually I try to avoid words that *could* be in use as key words :P

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #14
    Regular Coder
    Join Date
    Feb 2014
    Posts
    235
    Thanks
    46
    Thanked 0 Times in 0 Posts
    Hello Alykins

    It doesn't seem to be working now (it did before) - nothing is being inserted into the DB. I don't get any error messages and after I have clicked 'submit' I land on a page called Login.asp.

    The form is here: ASP Register

    This is the code before Response.Redirect:

    Code:
    <%
    username = ""
    password = ""
    confirmPassword = ""
    strEmail = ""
    ErrorMessage = ""
    
    if request.form <> "" then
    username = Request.Form("username")
    password = Request.Form("password")
    confirmPassword = Request.Form("confirmPassword")
    strEmail = Request.Form("strEmail")
    
      if strEmail="" or password="" then
         ErrorMessage = "You must specify both email and password."
      end if
      if password <> confirmPassword then
        ErrorMessage = "Password and confirmation do not match."
      end if
    
      if ErrorMessage = "" then 
        set conn = Server.CreateObject("ADODB.Connection")
    
     conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    
     conn.Open("E:\myDatabase.mdb")
    
        set rs = Server.CreateObject("ADODB.recordset")
        rs.Open "Select * FROM userlist WHERE strEmail = '" & strEmail & "'", conn
        if rs.EOF = true then
          
    sql="INSERT INTO userlist ([username], [password], strEmail) VALUES ('" & username & "','" & password & "', '" & strEmail & "')"
          
    conn.Execute(sql)
    
    'Response.Write(SQL)
    'Response.End
    
          Response.Redirect("success.asp")
    Not sure if that's much help.

  • #15
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,901
    Thanks
    46
    Thanked 202 Times in 201 Posts
    my guess is you are now hitting this
    Code:
    rs.Open "Select * FROM userlist WHERE strEmail = '" & strEmail & "'", conn
    and then getting a record or more, then you have if.EOF, well you never go to the next record so it should eval false, and never hit the redirect to success.asp. Have you tried breaking on that line and stepping through to see what happens? it also would never hit that insert if it never moves to the actual rs.EOF

    I don't think the in red is getting called now bc I think you have records in DB

    Code:
        if rs.EOF = true then
          
    sql="INSERT INTO userlist ([username], [password], strEmail) VALUES ('" & username & "','" & password & "', '" & strEmail & "')"
          
    conn.Execute(sql)
    
    'Response.Write(SQL)
    'Response.End
    
          Response.Redirect("success.asp")

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •