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?
 
pateljituConnect With a Mentor Commented:
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
 
pateljituConnect With a Mentor Commented:
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
 
gpizzutoConnect With a Mentor Commented:
You need to execute your Insert command before changing the value of strsql:

Insert this command before line n. 19
objconn.Execute(strsql)
0
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.

All Courses

From novice to tech pro — start learning today.