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
Well, it's a valid statement. Fieldnames and tablenames are correct? What error do you get when you run it in Access? Make sure you change the CustID if it's already in the table.

I gave you the singlequote function because singlequotes are the fastest way to SQL syntax errors.
 
Upvote 0 Downvote
Also, as a general rule, you don't need to set a RS when you are doing an INSERT statement. You should be able to just do
Code: 
Con.execute(sql)

------------------------------------------------------------------------------------------------------------------------
"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
Also, if you want it to only insert one record, you will need to include a "WHERE" statement. "

I think you mean this for Update statements, Chopstik.
 
Upvote 0 Downvote
Will this run and insert the record?
Code: 
	sSQL = "INSERT INTO cust_logins (CustID,CUSTUSER,CUSTPASS,CustName,email) VALUES (1230123,'trucks','parts','TrucksnParts','info@trucksnparts.com')"
	Set commInsert = Server.CreateObject("ADODB.Connection")
	commInsert.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/logins.mdb")
	commInsert.Execute(sSQL)
	commInsert.Close()
	Set commInsert = Nothing
 
Upvote 0 Downvote
Travis, you're right. My bad, but I wonder if that may be part of the problem. In his post of July 5, 14.15 pm, the OP says:
airpan said:
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
Click to expand...
Do you want to edit (UPDATE) the record or do you want to append (INSERT) the record? If you have an existing record that you are trying to update/edit (not append), then you have the wrong statement. You need to update your existing record (which may also account for the key violations since you're trying to add the same record to a table where it already exists) using the UPDATE statement. Just a thought, but enough to make me wonder based on your statement of the problem.

------------------------------------------------------------------------------------------------------------------------
"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
Chopstick and travis,
First off, chopstick, good eye on the old post :). But yes, I am still working on the same project. I gave up on the old code chopstick due to several reasons. For whatever reason, the idea of using the buttons, albeit probably the quicker way, for whatever reason was not working for me. I tried several different ways to get the buttons to function for me and it wouldn't work (not the code itself, but me grasping the idea behind the code itself).
What I was trying to state in my former post, and still don't think I am conveying to this forum properly is:
I have my pages set up to initially display all the customers with their customer id's hyperlinked. When you click on the hyperlink of the CustID, it will pull only that record. From there it lists three options: 1, to add a new customer, 2 to edit the displayed existing customer, and 3 to remove the displayed existing customer. For what it is worth I have successfully gotten the remove customer page to work. It is the edit and add new customer that I am having trouble with.
So to answer your question Chopstick, I need to do both Update and Insert, but only to the customer that is chosen when they click on the hyperlink. Does that make sense or did I lose you? I can post my code for the remove customer page if that will help? Also, does it make any difference that I have put the form and the code to pass the information entered into the form on the same page? Just trying to see if I am trying to do too much at once with one page? Thanks for all your help guys and hanging in there with me.
 
Upvote 0 Downvote
Here is the error message I got when I ran travis's code:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
/aspdevelopment/WasteConn/addcust.asp, line 26
 
Upvote 0 Downvote
GUYS OH MY GOD!!!!!!! Travis's code inserted the record in, but it still generated the error(probably because I hit the refresh button, as I am guessing that this is inserting the values the minute it parses the code?). I checked the database to see if added the info and it did. So in short, the code works. Now I just need to figure out to get the information people put in the form to insert into the database for adding a new customer, instead of doing straight from teh SQL line.
 
Upvote 0 Downvote
chopstick said:
Do you want to edit (UPDATE) the record or do you want to append (INSERT) the record? If you have an existing record that you are trying to update/edit (not append), then you have the wrong statement. You need to update your existing record (which may also account for the key violations since you're trying to add the same record to a table where it already exists) using the UPDATE statement. Just a thought, but enough to make me wonder based on your statement of the problem. [/quote chopstick]
Chopstick, when I started writing my SQL statements i was using my textbook and due to the textbook couldn't really figure out which statement applied, until I got help here and read through the text a bit more. For clarity, I have changed those pages to read the correct SQL statements... INSERT for adding a new customer and UPDATE for editing an existing customer.
I think where I am messing up now is with my forms. I think I am getting the forms wrong somehow... due to travis's code actually entering the record, I am now thinking that my form is not coded properly to capture the variables and enter the info into the table, and the same thing with my edit form page. Thanks again guys - I am so excited that at least I know the SQL will now work. You guys rock.
Click to expand...
 
Upvote 0 Downvote
No. I already pointed this out.

You cannot insert duplicate CustIDs. If you run the code again, it will try and insert the record again and the database will reject it. Change the CustID before rerunning and you shouldn't get the error.

You need to put in some logic to determine whether you are updating or inserting. you can write a function or query to check if the CustID exists first. if it doens't, use insert; if it does, use an update statement, or flag it as already existing, or whatever.
 
Upvote 0 Downvote
travis,
i understand now (as of last night when i posted) about rerunning the code (thus my post about hitting the refresh button). i also understand that i still have some stuff to add to it to get it to function in the way i need it to. not trying to be a snot, just running on fumes and not really sure why you pointed that out again. i already have two pages set up, one for adding a new customer and one for editing an existing customer.
travis said:
You need to put in some logic to determine whether you are updating or inserting. you can write a function or query to check if the CustID exists first. if it doens't, use insert; if it does, use an update statement, or flag it as already existing, or whatever.[/quote travis]

airpan said:
I have my pages set up to initially display all the customers with their customer id's hyperlinked. When you click on the hyperlink of the CustID, it will pull only that record.[/quote erin]

i only want people editing one record at a time, and even then, they will not be able to change the cust id. the additional separate page that i will use for adding customers, needs to allow the custid to be entered from a web form. i am keeping the edit page and add page seperate to avoid using some sort of additional code to figure out what needs to be updated or inserted. i want each specific page to handle that specific option.

it is the forms i am trying to fix to capture the variables. thanks for your help again. i may go get some coffee in a bit and try hitting the books again. i have to be missing something with the forms.
Click to expand...
Click to expand...
 
Upvote 0 Downvote
travis,
just wanted to let you know that after hitting the text books, i realized that the form i was trying to put on the same page as the SQL code to insert the customer, wasn't even displaying the form, so the SQL had been working all along.
I took the form code and moved it to another page, left the insertion code on that page and now the form is doing what it is supposed to do. i cannot thank you and chopstick enough for helping me. 30 something posts later, it finally works. again, tek tips is the place to come for help.
 
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