?
Solved

Inserting values from an ASP form into a MySQL Database

Posted on 2012-03-22
5
Medium Priority
?
1,361 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--
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

770 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