How to insert user data into a DB using HTML, ASP.Net and MSMSQL

rgn2121 used Ask the Experts™
I am working on a form that will take user data and input it into a DB that I can later pull out into reports.
Here is a sample from the HTML form...
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>New Patient Information</title>
<form action="patient_info.asp" method="post">
<!--Patient information is below-->
<hr width="75%">
<h2 align="center"><strong>Patient Information</strong></h2><br />
  <table width="200" align="center" border="1">
      <td>Name:(last) <input name="lastname" type="text" maxlength="15" /></td>
      <td>(first) <input name="firstname" type="text" maxlength="15" /></td>
      <td>(MI) <input name="midinitial" type="text" maxlength="4" /></td>
      <td>Nickname: <input name="nickname" type="text" maxlength="15" /></td>
<h1 align="center"><input type="reset" value="Reset" /><input type="submit" value="Submit" /></h1>

ASP code...patient_info.asp

set conn=Server.CreateObject("ADODB.Connection")
conn.Open "LOCATION OF SERVER?????"sql="INSERT INTO Temp_Table (LastName,FirstName,"
sql=sql & "MiddleInitial,NickName)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("lastname") & "',"
sql=sql & "'" & Request.Form("firstname") & "',"
sql=sql & "'" & Request.Form("midinitial") & "',"
sql=sql & "'" & Request.Form("nickname") & "')"on error resume next
conn.Execute sql,recaffected
if err<>0 then
  Response.Write("No update permissions!")
  Response.Write("<h3>" & recaffected & " record added</h3>")
end if



Obviously, the question marks are where I have issues, but also, my DB has a primary keys that are autofill.  How do I work that out so as to not mess up my data?

If I have data going into multiple tables, do I just change the connection to the new table after I close it?...i,e,

conn.Open"Location of server??"sql="INSERT INTO Next_Table ...

I assume I can access this info usinf MS Access to pull the info I need...

Thoughts??  Suggestions??

thanks in advance...
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Do you get any errors with that code? I assume you edited it before pasting - please let me know what the errors you got are.

Sham HaqueSenior SAP CRM Consultant

reference for your ???

It does not matter that your DB table has an "autofill" (I presume you mean IDENTITY, autonumber) field - as long as you specify the field names for which you ARE providing values, you are fine. (Good job there).
Sham HaqueSenior SAP CRM Consultant


also - no need to close the connection until you are done completely with it. just keep it open.

However, you are playing with fire (potentially) by having an INSERT statement fired directly.
Strongly recommended would be to create a stored procedure in MSSQL to do the insert, and verify the parameters passed to it.
If you need guidance with that, please yell.
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).


Thanks for the quick replies...

  No I haven't tested that code...I kid of just cut stuff out of what I was using to give a better picture of what I was doing.

  The autofill that I speak of is my primary key for the table such as CustID that is auto generated by the DB on a new entry.  I have to admit I know next to nothing about SQL and am learning ASP.Net.  WHat I know for sure is that I have an HTML form...that form calls the asp form when the submit button is clicked.  I know that the asp form should pull the info, and pass it to the DB.  I know that I get a SQL DB through my hosting company and I assume I design it and then upload it to them for my use.  

Like I said...trying to learn, but A LOT of gray areas right now.


Also, I was looking at setting up my MS SQL DB and it asked for my DSN Name.  Do you reccoment using that or to go DSN-less.  Everything I do at work, I access Databases through MS Access and ODBC connections, but I am not sure if that is the best way to go for what I want to do.
Sham HaqueSenior SAP CRM Consultant

DSNs have their place, but I avoid them where possible, and use OLE DB in place of ODBC to connect to databases (again, where possible).

so your SQL connection string would be something like:

conn.Open "Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

Senior SAP CRM Consultant
if using SQL 2005, use this string:


(both happily lifted from


Good stuff...SQL 2005 is what I am using.

So lets say I have DB TEST and tables first...second...third.  To be able to insert into these tables I would do the following:

conn.Open Provider=SQLNCLI;Server=myServerAddress;Database=TEST;Uid=myUsername;Pwd=myPassword;
sql="INSERT INTO tbl_first(data,data)
sql=sql & "('" & Request.Form("data") & "',"
sql=sql & "'" & Request.Form("data") &  "')"
sql="INSERT INTO tbl_second(data,data)...
sql="INSERT INTO tbl_third(data,data)...

Also, I don't understand what you meant about
"Strongly recommended would be to create a stored procedure in MSSQL to do the insert, and verify the parameters passed to it."


okay...well I switched and went with PHP and MySQL and below is my .php code.  Foe whatever reason I am getting and error saying that it could not connect to the database.  I know the error comes from my code, I have it report that error if it can't connect, I am just not sure why it isn't connecting.  I thought I would check to see if my code is olay before calling my hosting company.

$DBhost = "";
$DBuser = "username";
$DBpass = "password";
$DBName = "TEST";
$table = "tbl_customers";
mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database");

@mysql_select_db("$DBName") or die("Unable to select database $DBName");

$sqlquery = "INSERT INTO $table VALUES('$custID','$last_name','$first_name','$mid_initial','$nickname')";

$results = mysql_query($sqlquery);


print "<html><body><center>";
print "<p>You have just entered this record<p>";
print "Last Name : $last_name<br>";
print "First Name : $first_name<br>";
print "Middle Initial :$Mid_initial";
print "Nickname :$nickname";
print "</body></html>";

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial