New programmer need help on ASP - SQL syntax error

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'

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.

 

which is the error line in your code?

 

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 & "')"

 

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

 

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.

 

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

 

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.

 

change this

 

lugwig,

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

regards,
Joe

 

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

 

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

 

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...

 

hey

i have solve your problem pls check attach file

pls move your database in db folder

 

Hi Shaliesh,

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

Regards,
Joe

 

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.

 

good post and reply

 

WHOOSH! right over my head. I don't know HOW i missed that one. good catch

 

Yes you are right i agree with you