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

rgn2121
rgn2121 used Ask the Experts™
on
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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>New Patient Information</title>
</head>
<body>
<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">
    <tr>
      <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>
  </tr>
</table>
<h1 align="center"><input type="reset" value="Reset" /><input type="submit" value="Submit" /></h1>

</form>
</body>
</html>
----------------------------------------------------------------------------------------------------------------------------
ASP code...patient_info.asp

<html>
<body><%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="????"
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!")
else
  Response.Write("<h3>" & recaffected & " record added</h3>")
end if
conn.close
%>

</body>
</html>

----------------------------------------------------------------------------------------------------------------

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.Close
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...
RGN
Comment
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.

Stilgar.
Sham HaqueSenior SAP CRM Consultant

Commented:
reference http://www.connectionstrings.com 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

Commented:

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).

Author

Commented:
Thanks for the quick replies...

Stilgar:
  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.

gbshashaq:
  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.
 

Author

Commented:
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

Commented:
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
Commented:
if using SQL 2005, use this string:

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

(both happily lifted from connectionstrings.com)

Author

Commented:
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."

Author

Commented:
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.
Code---------------------------------------------------------------------------------

<?
$DBhost = "myhostingserver.net";
$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);

mysql_close();

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