Inserting values from an ASP form into a MySQL Database

Hello,
I have some (mostly) working code below.  However my INSERT INTO statement isn't working when trying to insert data from an ASP webpage form into a MySQL Database.

My database name is persons and I want the data from my form on page index.asp to be uploaded to my DB, called myDB.

The resultset that prints for me only shows one record, that I hard coded into the table persons earlier.


index.asp
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<body>
	Today's Date is: <%response.write(date)%><br />
	<br /><br />

	<form method="POST" action="submitData.asp">
		ID:      <input type="text" name="ID" /><br />
		First Name: <input type="text" name="firstName" /><br />
		Last Name: <input type="text" name="lastName"/><br />
		<input type="submit" value="Submit" />
	</form>
	<br />

</body>
</html>

Open in new window


submitData.asp
'set up database connection 
	sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=myserver.mysite.com; DATABASE=myDB; UID=myUser;PASSWORD=xyz;"
    set objConn = Server.CreateObject("ADODB.Connection")
			
			
	set objconn = Server.CreateObject("ADODB.Connection")
	set objRS = Server.CreateObject("ADODB.RecordSet")

	set objRS = nothing

	'conString = "DSN=forms"
	objconn.Open sConnection		        

	'strsql = "INSERT INTO myUser.persons (ID, firstName, lastName) VALUES (303, 'Eric', 'Abidal');"
	
	strSQL = "INSERT INTO myUser.persons (ID, firstName, lastName) VALUES ("
	strSQL = strSQL & "Null"
	strSQL = strSQL & ", ' " & request.form("ID") & "', '"
	strSQL = strSQL & request.form("firstName")& "', '"
	strSQL = strSQL & request.form("lastName") & "', '"& "')"
	
	
	sql = "Select * from myUser.persons;"
	
	' Prepare a SQL query string
	strsql = "SELECT * FROM myuser.persons"

	' Execute the SQL query and set the implicitly created recordset
	Set objRS = objconn.Execute(strsql)

	' Write out the results in a table by concatenating into a string
	Response.write "<table>"

	Do While Not objRS.EOF
		strTemp = strTemp & "<tr><td>" & objRS("ID") & "</td>"
		strTemp = strTemp & "<td>" & objRS("firstName") & "</td>"
		strTemp = strTemp & "<td>" & objRS("lastName") & "</td></tr>"
		objRS.MoveNext
	Loop

	Response.write strTemp
	Response.write "</table>"

	set objRS = nothing

Open in new window



Thank you.
--TripWire--Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pateljituCommented:
Please change line 16 - 20 to

strSQL = "INSERT INTO myUser.persons (ID, firstName, lastName) VALUES ("
	strSQL = strSQL & request.form("ID") & ", "
	strSQL = strSQL & "'"& request.form("firstName") &"', "
	strSQL = strSQL & "'"& request.form("lastName") & "')"

Open in new window

0
--TripWire--Author Commented:
Thanks, but on resubmitting the form, I still only get that one line of data.  And when I do a SELECT * statement from the MySQL Workbench, only that record shows.
0
gpizzutoCommented:
Is ID primary key on your table ?
0
pateljituCommented:
Try this code and submit OUTPUT for line Response.Write(strSQL &" <br />"), please change values for SERVER / DATABASE / UID and PASSWORD in connection string:

<%
    'set up database connection 
	sConnection = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=myserver.mysite.com; DATABASE=myDB; UID=myUser;PASSWORD=xyz; [b]Option=3;[/b]"
			
	set objconn = Server.CreateObject("ADODB.Connection")
	set objRS = Server.CreateObject("ADODB.RecordSet")

	'conString = "DSN=forms"
	objconn.Open sConnection		        

	'strsql = "INSERT INTO myUser.persons (ID, firstName, lastName) VALUES (303, 'Eric', 'Abidal');"
	
	strSQL = "INSERT INTO myUser.persons (ID, firstName, lastName) VALUES ("
	strSQL = strSQL & request.form("ID") & ", "
	strSQL = strSQL & "'"& request.form("firstName") &"', "
	strSQL = strSQL & "'"& request.form("lastName") & "')"
Response.Write(strSQL &" <br />")	
	
	sql = "Select * from myUser.persons;"
	
	' Prepare a SQL query string
	strsql = "SELECT * FROM myuser.persons"

	' Execute the SQL query and set the implicitly created recordset
	Set objRS = objconn.Execute(strsql)

	' Write out the results in a table by concatenating into a string
	Response.write "<table>"

	Do While Not objRS.EOF
		strTemp = strTemp & "<tr><td>" & objRS("ID") & "</td>"
		strTemp = strTemp & "<td>" & objRS("firstName") & "</td>"
		strTemp = strTemp & "<td>" & objRS("lastName") & "</td></tr>"
		objRS.MoveNext
	Loop

	Response.write strTemp
	Response.write "</table>"

	set objRS = nothing
%>                                  

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gpizzutoCommented:
You need to execute your Insert command before changing the value of strsql:

Insert this command before line n. 19
objconn.Execute(strsql)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.