1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

New programmer need help on ASP - SQL syntax error

Discussion in 'C#' started by cgo991, Sep 21, 2006.

  1. #1
    My code is as following :

    sql="INSERT INTO RegistrationDate (BRANCH_name,BLK,Year,DateFrom,DateTo,TimeFrom,TimeTo) VALUES"
    sql=sql & "('" & strBranch & "',"
    sql=sql & "'" & Request.Form("BLK") & "',"
    sql=sql & "'" & strYear & "',"
    sql=sql & "'" & strDateFrom & "',"
    sql=sql & "'" & strDateTo & "',"
    sql=sql & "'" & strTimeFrom & "',"
    sql=sql & "'" & strTimeTo & "')"


    error msg is :
    Microsoft JET Database Engine error '80040e14'
    SEMrush
    Syntax error in INSERT INTO statement.

    Registration2.asp, line 263

    * I know that this is simple question but I am very new asp - trying to learn. Pls help if possible. Many Thks.

    E.g:
    I have added strBranch=Request.Form("Branch")

    strBranch, strYear and str etc... refer to variables.
     
    cgo991, Sep 21, 2006 IP
    SEMrush
  2. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #2
    which is the error line in your code?
     
    ludwig, Sep 21, 2006 IP
  3. cgo991

    cgo991 Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    The error code line that is indicating is :

    conn1.Execute(sql)

    I wonder whether it is becos of my syntax error in the Insert portion:

    sql="INSERT INTO RegistrationDate (BRANCH_name,BLK,Year,DateFrom,DateTo,TimeFrom,TimeTo) VALUES"
    sql=sql & "('" & strBranch & "',"
    sql=sql & "'" & Request.Form("BLK") & "',"
    sql=sql & "'" & strYear & "',"
    sql=sql & "'" & strDateFrom & "',"
    sql=sql & "'" & strDateTo & "',"
    sql=sql & "'" & strTimeFrom & "',"
    sql=sql & "'" & strTimeTo & "')"
     
    cgo991, Sep 21, 2006 IP
  4. shaileshk

    shaileshk Well-Known Member

    Messages:
    455
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    108
    #4
    sql="INSERT INTO RegistrationDate (BRANCH_name,BLK,Year,DateFrom,DateTo,TimeFrom,TimeTo) VALUES ('" & strBranch & "','" &equest.Form("BLK") "','" & strYear & "','" & strDateFrom & "','" & strDateTo & "','" & strTimeFrom & "','" & strTimeTo & "')"

    try abow string

    which database using ? and pls chack your database connection string if your using MS Access then check database path first and if your are using MS SQL then check IP ,databse name,username and password

    If have any error pls send full code of your page so we can get your problem
     
    shaileshk, Sep 21, 2006 IP
  5. cgo991

    cgo991 Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Hi,
    Thks for yr prompt reply but my problem is still not solved.

    I am trying to create a very simple submission form.

    My database is MS access. I don't think it is the connection problem. I have attached my code here. PLs help me see where is the problem. Many Thks.
     

    Attached Files:

    cgo991, Sep 21, 2006 IP
  6. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #6
    could you also please provide your DB structure, I mean which is an INTEGER and which is a TEXT filed

    and try this one also
     
    ludwig, Sep 21, 2006 IP
  7. cgo991

    cgo991 Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    BRANCH_name -Text
    BLK-Text
    DateFrom - Text
    DateTo - Text
    TimeFrom - Text
    TimeTo - Text
    Year -Text

    i just wanted a simple submit form thus even date field, i have indicate this as Text format.

    Thks.
     
    cgo991, Sep 21, 2006 IP
  8. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #8
    change this
     
    ludwig, Sep 21, 2006 IP
  9. cgo991

    cgo991 Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #9
    lugwig,

    I have tried but it is still not working. Thks.

    regards,
    Joe
     
    cgo991, Sep 21, 2006 IP
  10. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #10
    the code is correct, there should be some other problem.

    it says an invalid character is used but if all your fields are TEXT then your code is correct

    maybe the problem is in request("BLK") where there is an ' in the string
    replace all the requested data change " to ", ' to ',

    then you'll see maybe there is a " or ' in your requested strings
     
    ludwig, Sep 21, 2006 IP
  11. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #11
    agree with ludwig, this is likely your culprit. even if it isn't, it's never good practice to insert data gathered using the request method directly into your database. It leaves you completely vulnerable to sql injection.

    you should cleanse or parameterize any values received via the request method before they ever hit your database. there are dozens of methods for this, ranging from a simple replace function to a more complex parameter function, google "sql injection prevention asp" for more information.

    you might stick the line "response.write sql" just before your execute temporarily in an attempt to debug your sql, that way you can see the query that you're trying to execute which might give you more information as to what's going on.

    VG
     
    vectorgraphx, Sep 21, 2006 IP
  12. cgo991

    cgo991 Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #12
    Hi All,

    Correct me if I am wrong on the reply.

    1. Cleanse the data before insert to database - I have tried the following methods.
    - For all variables fields, i have added in the trim function
    strBlk=Trim(Request.Form("BLK"))
    strDateFromDD=Trim(Request.Form("DateFromDD"))
    - Added in the replace function
    strDateFromDD=Replace(strDateFromDD,"'","''")

    2. Do not directly add request.Form("BLK") in SQL statement to prevent SQL injection.

    sql="INSERT INTO RegistrationDate (BRANCH_name, BLK, Year, DateFrom, DateTo, TimeFrom, TimeTo) VALUES ('" & strBranch & "','" &strBlk& "','" & strYear & "','" & strDateFrom & "','" & strDateTo & "','" & strTimeFrom & "','" & strTimeTo & "')"

    3. Try to use Response.Write (sql)

    *** Try to follow the steps above but it still show error even before I have actually clicked on Submit. Why is that so?

    Added in the line but it is still the same error.

    Below is the error.. Sigh..
    INSERT INTO RegistrationDate (BRANCH_name, BLK, Year, DateFrom, DateTo, TimeFrom, TimeTo) VALUES ('ADC','501','2008','10/10/2008','10/10/2008','10:10AM','10:10AM')
    Microsoft JET Database Engine error '80040e14'

    Syntax error in INSERT INTO statement.

    /test2.asp, line 268

    * Is there other online tutorial that I can read on, should i continue to debug or is there another way I can do this part again.. sigh...
     
    cgo991, Sep 21, 2006 IP
  13. shaileshk

    shaileshk Well-Known Member

    Messages:
    455
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    108
    #13
    hey

    i have solve your problem pls check attach file

    pls move your database in db folder
     

    Attached Files:

    shaileshk, Sep 22, 2006 IP
  14. cgo991

    cgo991 Peon

    Messages:
    9
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #14
    Hi Shaliesh,

    Thks for yr help.
    I will try to analysis and understand yr code.

    Regards,
    Joe
     
    cgo991, Sep 24, 2006 IP
  15. rb3m

    rb3m Peon

    Messages:
    192
    Likes Received:
    11
    Best Answers:
    0
    Trophy Points:
    0
    #15
    It's one of those hair pullers. The first time I encountered I lost hours and hours before I found what it was, and then the solution was so simple, one of those "duh!" things. I still do it from time to time, until I remember to check.

    The problem is that "Year" is a reserved SQL word and you can't use it for a field name. Change it to something else, like fYear (f is for field) and your app will automagically work.
     
    rb3m, Sep 28, 2006 IP
  16. honeydesign

    honeydesign Peon

    Messages:
    62
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #16
    good post and reply
     
    honeydesign, Sep 29, 2006 IP
  17. vectorgraphx

    vectorgraphx Guest

    Messages:
    545
    Likes Received:
    16
    Best Answers:
    0
    Trophy Points:
    0
    #17
    WHOOSH! right over my head. I don't know HOW i missed that one. good catch
     
    vectorgraphx, Sep 29, 2006 IP
  18. shaileshk

    shaileshk Well-Known Member

    Messages:
    455
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    108
    #18
    Yes you are right i agree with you
     
    shaileshk, Sep 29, 2006 IP