?
Solved

Inserting values from an ASP form into a MySQL Database

Posted on 2012-03-22
5
Medium Priority
?
1,510 Views
Last Modified: 2012-03-22
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.
0
Comment
Question by:--TripWire--
  • 2
  • 2
5 Comments
 
LVL 15

Assisted Solution

by:pateljitu
pateljitu earned 1068 total points
ID: 37752789
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
 

Author Comment

by:--TripWire--
ID: 37752812
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
 
LVL 8

Expert Comment

by:gpizzuto
ID: 37752908
Is ID primary key on your table ?
0
 
LVL 15

Accepted Solution

by:
pateljitu earned 1068 total points
ID: 37752918
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
 
LVL 8

Assisted Solution

by:gpizzuto
gpizzuto earned 532 total points
ID: 37752932
You need to execute your Insert command before changing the value of strsql:

Insert this command before line n. 19
objconn.Execute(strsql)
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question