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