[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Inserting values from an ASP form into a MySQL Database

Posted on 2012-03-22
5
Medium Priority
?
1,446 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

650 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