Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

INSERT INTO statement 2

Status
Not open for further replies.
Airpan

Airpan

Technical User
Jun 14, 2005
172
US
I am getting a syntax error on this SQL statement (I wasn't sure whether to post here or the SQL forum) which I have being executed in an ASP page. Can someone take a look and let me know if they see anything standing out? I am running it on IIS (XP OS) and it gives me a syntax error. I have pasted the code below:
Code: 
<html>
<head>
<title></title>
</head>
<%
	customer=Request.QueryString("customer") 
	
	Set Con = Server.CreateObject("ADODB.Connection")
	Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/logins.mdb")
%>	
<body>
<span class="style4">* Please use the Customer ID obtained from the FileMaker Database, as you will need this ID to grant them access to the web site!!!</span>
<form action="addcust.asp" method="post" name="Form">
Customer ID: <input name="custid" type="text" id="custid" size="6" maxlength="6">
<br>
Customer Name: <input name="custname" type="text" id="custname" size="25" maxlength="25">
<br>
Cust Username: <input name="custuser" type="text" id="custuser" size="25" maxlength="25">
<br>
Cust Password: <input name="custpass" type="text" id="custpass" size="10" maxlength="10">
<br>
Customer Email: <input name="custemail" type="text" id="custemail" size="35" maxlength="35"><br>
<input name="submit" type="submit" value="Submit">
<input name="clear" type="reset" value="Clear">
</form>
<%
frmcustid=Request.Form("custid")
frmcustname=Request.Form("custname")
frmcustuser=Request.Form("custuser")
frmcustpass=Request.Form("custpass")
frmcustemail=Request.Form("custemail")

Set Con = Server.CreateObject("ADODB.Connection")
	Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/logins.mdb")
SQL = "INSERT INTO cust_logins(CustID,CUSTUSER,CUSTPASS,CustName,email) VALUES ("& frmcustid &","& frmcustuser &","& frmcustpass &","& frmcustname &","& frmcustemail &")"
	   Set rec=Con.execute(sql)
%>
</body>
</html>

~Erin
 
Sort by date Sort by votes
What error are you getting?

Looks like you need to wrap some of the strings in single quotes.

Code: 
INSERT INTO cust_logins(CustID,CUSTUSER,CUSTPASS,CustName,email) VALUES ("& frmcustid &",'"& frmcustuser &"','"& frmcustpass &"','"& frmcustname &"','"& frmcustemail &"')


You can always response.write out the SQL string and paste it into Access and see it run with the variable values.
 
Upvote 0 Downvote
travisbrown,
I tried the single quotes and it still generates the following error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/aspdevelopment/WasteConn/addcust.asp, line 48
Thanks for reminding me about running the SQL in Access. I had forgotten, but I did run it in Access and it generates the same Syntax error in there. Any more ideas? I am literally banging my head on the keyboard. :)
 
Upvote 0 Downvote
actually, i just tried running it in Access again and it succeeded in Access in appending the row... now I am really confused. :|
 
Upvote 0 Downvote
Airpan, you know that you are setting your connection details twice?! Not that it will make a difference to this problem.
You said it ran fine when you done it in Access? The Syntax that travis has provided is correct and should work, are you positive you have added single quotes for columns that are not of type integer in your asp code?

Nick
 
Upvote 0 Downvote
Show us the generated SQL, what you are running in Access.

Is CustID an integer or string?
 
Upvote 0 Downvote
nickdel,
Yes, I did notice that I had it in there twice after I posted, and I removed one. The new code now reads:
Code: 
<form action="addcust.asp" method="post" name="Form">
Customer ID: <input name="custid" type="text" id="custid" size="6" maxlength="6">
<br>
Customer Name: <input name="custname" type="text" id="custname" size="25" maxlength="25">
<br>
Cust Username: <input name="custuser" type="text" id="custuser" size="25" maxlength="25">
<br>
Cust Password: <input name="custpass" type="text" id="custpass" size="10" maxlength="10">
<br>
Customer Email: <input name="custemail" type="text" id="custemail" size="35" maxlength="35"><br>
<input name="submit" type="submit" value="Submit">
<input name="clear" type="reset" value="Clear">
</form>
<%
frmcustid=Request.Form("custid")
frmcustname=Request.Form("custname")
frmcustuser=Request.Form("custuser")
frmcustpass=Request.Form("custpass")
frmcustemail=Request.Form("custemail")

Set Con = Server.CreateObject("ADODB.Connection")
	Con.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/logins.mdb")
SQL = "INSERT INTO cust_logins (CustID,CUSTUSER,CUSTPASS,CustName,email) VALUES ("& frmcustid &",'"& frmcustuser &"','"& frmcustpass &"','"& frmcustname &"','"& frmcustemail &"')"
	   'response.write(sql)
	   Set rec=Con.execute(sql)

Con.Close  
%>
 
Upvote 0 Downvote
i tried running the above in access again and it cannot append . it is also trying to append all the rows instead of just one, which i didn't realize it would try to do. i just want to edit one row, not all the rows.
Here is the access error:
microsoft Access set 0 fields to Null due to a type conversion failure, and it didn't add 1 record to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.
Do you want to run the query anyway?
To ignore the errors and run the query click yes
For an explanation of the causes of violations, click Help
 
Upvote 0 Downvote
by the way, CustID is an integer. sorry
 
Upvote 0 Downvote
You can't run the query with the same CustID more than once - it's your unique key. that's why you get a key validation error.

That might be your issue too - is CustID an Autonumber field? if so, leave it out of the query.

Post your generated SQL here. I'm betting it is either a string trying to be an integer or an autonumber conflict.
 
Upvote 0 Downvote
No CustID is not an autonumber field, i am going to be manually entering that number into the web form (once i get it to work). I also wrote my own SQL query and tried to run it in access, I didn't try to let access write the SQL for me. Is that what you are suggesting I do? If so, it will take me a bit as I have never tried it that way before.
 
Upvote 0 Downvote
No. I can't see what is wrong until you post the query.

response.write out the SQL in your ASP page and post it here.
 
Upvote 0 Downvote
SQL = "INSERT INTO cust_logins (CustID,CUSTUSER,CUSTPASS,CustName,email) VALUES ("& frmcustid &",'"& frmcustuser &"','"& frmcustpass &"','"& frmcustname &"','"& frmcustemail &"')
 
Upvote 0 Downvote
Here is the response.write query:
INSERT INTO cust_logins (CustID,CUSTUSER,CUSTPASS,CustName,email) VALUES (,'','','','')
 
Upvote 0 Downvote
You don't have any value for CustID. You can't insert a NULL on a required key field.

Enter some values in your form and response.write the query again.

 
Upvote 0 Downvote
travis,
INSERT INTO cust_logins (CustID,CUSTUSER,CUSTPASS,CustName,email) VALUES (1237,'trucks','parts','TrucksnParts','info@trucksnparts.com')
 
Upvote 0 Downvote
And that gives you an error?

Also, you should process the input values so you at least done get single quotes in the way.

Code: 
frmcustid	= Request.Form("custid")
frmcustname	= Request.Form("custname")
frmcustuser	= Request.Form("custuser")
frmcustpass	= Request.Form("custpass")
frmcustemail	= Request.Form("custemail")

frmcustid	= StripSingleQuotes(custid)
frmcustname	= StripSingleQuotes(custname)
frmcustuser	= StripSingleQuotes(custuser)
frmcustpass	= StripSingleQuotes(custpass)
frmcustemail	= StripSingleQuotes(custemail)


FUNCTION StripSingleQuotes(input)
	IF NOT isNumeric(input) THEN
		StripSingleQuotes = REPLACE(input,"'","''")
	ELSE 
		StripSingleQuotes = input
	END IF
END FUNCTION
 
Upvote 0 Downvote
it still generates an error. for right now i am now worried about the above, i just want to get the core basics down and then add all the fluff.
# Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/aspdevelopment/WasteConn/addcust.asp, line 46
 
Upvote 0 Downvote
Did you take the code that was generated in your post of July 5, 16.26 pm and try that in your Access database to see if that would properly insert the record or if it generated an error message? Also, if you want it to only insert one record, you will need to include a "WHERE" statement.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill
 
Upvote 0 Downvote
travis,
i am sorry, but i just don't understand why it isn't working. thanks for your help. when i response.write the sql statement it runs fine but it doesn't work when i actually want it to connect and execute. thanks again and hopefully i will have some sort of epiphany. :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
185
penguinspeaks
penguinspeaks
gcoleman1
  • Locked
  • Question
Insert into Request.form field when empty
Replies
2
Views
103
gcoleman1
gcoleman1
Xsi
  • Locked
  • Question
Store data into an array
Replies
3
Views
104
ChrisHirst
ChrisHirst
leifoet
  • Locked
  • Question
How to insert a variable in a window alert ?
Replies
2
Views
116
leifoet
leifoet
sdagger2
  • Locked
  • Question
Classic asp: Insert new record to access database from successful PayPal payment
Replies
2
Views
109
ChrisHirst
ChrisHirst

Part and Inventory Search

Sponsor

Back
Top