Connecting Forms to a Access Database

Im am trying to use ASP for a booking system so that users can insert information iinto forms in a HTML page. I want this data to then be sent to a MS Access Database.

Code for Forms -
[CODE]

<form method="POST" action="finalacknowlegdement.asp">
<table width="408">
<tr>
<td width="192"><p>DATE REQUIRED FROM</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Date Required From" type="text" id="Date Required From">
</td>
</tr>
<tr>
<td><p>DATE REQUIRED TO </p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Date Required To" type="text" id="Date Required To">
</td>
</tr>
<tr>
<td><p>FIRST NAME</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Firstname" type="text" id="Firstname">
</td>
</tr>
<tr>
<td><p>SURNAME</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<asp:sqldatasource id="Sqldatasource3" runat="server" connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>"
providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
<input name="Surname" type="text" id="Surname">
<asp:sqldatasource id="Sqldatasource4" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>NUMBER OF PEOPLE IN PARTY</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Number of people" type="text" id="Number of people">
<asp:sqldatasource id="Sqldatasource5" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>NAMES OF PEOPLE</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<textarea name="Names of people" rows="5" id="Names of people"></textarea>
<asp:sqldatasource id="Sqldatasource6"
runat="server" connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>"
providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>ADDRESS LINE 1</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Address Line 1" type="text" id="Address Line 1">
<asp:sqldatasource id="Sqldatasource7" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td style="height: 59px"><p>ADDRESS LINE 2</p>
<p>&nbsp; </p></td>
<td style="width: 202px; height: 59px">
<input name="Address Line 2" type="text" id="Address Line 2">
<asp:sqldatasource id="Sqldatasource8" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>TOWN/CITY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Town/City" type="text" id="Town/City">
<asp:sqldatasource id="Sqldatasource9" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>COUNTY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="County" type="text" id="County">
<asp:sqldatasource id="Sqldatasource10" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>POSTCODE</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Postcode" type="text" id="Postcode">
<asp:sqldatasource id="Sqldatasource12" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource><asp:sqldatasource
id="Sqldatasource11" runat="server" connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>"
providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>COUNTRY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Country" type="text" id="Country">
<asp:sqldatasource id="Sqldatasource13" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>PHONE NUMBER</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Phone Number" type="text" id="Phone Number">
<asp:sqldatasource id="Sqldatasource14" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>MOBILE NUMBER</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Mobile Number" type="text" id="Mobile Number">
<asp:sqldatasource id="Sqldatasource15" runat="server"
connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>" providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>EMAIL ADDRESS</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Email Address" type="text" id="Email Address" value="">
<asp:sqldatasource id="Sqldatasource16"
runat="server" connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>"
providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
</td>
</tr>
<tr>
<td><p>&nbsp;</p>
<p>&nbsp;</p></td>
<td style="width: 202px"><input type="submit" name="Submit" value="Submit"> <asp:sqldatasource id="Sqldatasource17"
runat="server" connectionstring="<%$ ConnectionStrings:bookingcalendarConnectionString %>"
providername="<%$ ConnectionStrings:bookingcalendarConnectionString.ProviderName %>"
selectcommand="SELECT [First name] AS First_name, [Surname], [Address Line 2] AS Address_Line_2, [Address Line 1] AS Address_Line_1, [Names of People] AS Names_of_People, [Number of People] AS Number_of_People, [Town/City] AS column1, [County], [Postcode], [Country], [Email Address] AS Email_Address, [Mobile Number] AS Mobile_Number, [Phone Number] AS Phone_Number FROM [Client Database]"></asp:sqldatasource>
<input type="reset" value="Clear" /> </td></tr>
</table>


</form>

[CODE/]

Does anyone have any ideas on how to do this?

Thanks

Are you using asp classic or asp.net? because you have ASP 3.0 code and ASP.Net code interspersed in your code example. You need to pick one or the other because the two are not interchangeable! There was major changes from asp 3.0 to asp.net



This tells me you want to use asp classic because the extension .asp is for classic asp.
finalacknowlegdement.asp

And this tells me you want to do this in ASP.Net
<asp:sqldatasource id="Sqldatasource3" runat="server"

Next, you DO NOT need to open a database connection when you are gathering input from the user(s), so you will see changes to the form.

Here is an example in asp classic

I am going to put an access OLEDB connection string in place for you. All you need to do is substitute the path of your database and the database name, ensuring that your database is in a folder with read/write permissions set to it (hopefully you placed this in a folder outside your root directory)

Last, you will see an ADO insert. This is faster to code and also prevents SQL interjection attacks on the database


<form method="POST" action="<%=Request.ServerVariables("SCRIPT_NAME")%>">
<table width="408">
<tr>
<td width="192"><p>DATE REQUIRED FROM</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Date Required From" type="text" id="Date Required From">
</td>
</tr>
<tr>
<td><p>DATE REQUIRED TO </p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Date Required To" type="text" id="Date Required To">
</td>
</tr>
<tr>
<td><p>FIRST NAME</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Firstname" type="text" id="Firstname">
</td>
</tr>
<tr>
<td><p>SURNAME</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Surname" type="text" id="Surname">
</td>
</tr>
<tr>
<td><p>NUMBER OF PEOPLE IN PARTY</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Number of people" type="text" id="Number of people">
</td>
</tr>
<tr>
<td><p>NAMES OF PEOPLE</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<textarea name="Names of people" rows="5" id="Names of people"></textarea>
</td>
</tr>
<tr>
<td><p>ADDRESS LINE 1</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Address Line 1" type="text" id="Address Line 1">
</td>
</tr>
<tr>
<td style="height: 59px"><p>ADDRESS LINE 2</p>
<p>&nbsp; </p></td>
<td style="width: 202px; height: 59px">
<input name="Address Line 2" type="text" id="Address Line 2">
</td>
</tr>
<tr>
<td><p>TOWN/CITY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Town/City" type="text" id="Town/City">
</td>
</tr>
<tr>
<td><p>COUNTY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="County" type="text" id="County">
</td>
</tr>
<tr>
<td><p>POSTCODE</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Postcode" type="text" id="Postcode">
</td>
</tr>
<tr>
<td><p>COUNTRY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Country" type="text" id="Country">
</td>
</tr>
<tr>
<td><p>PHONE NUMBER</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Phone Number" type="text" id="Phone Number">
</td>
</tr>
<tr>
<td><p>MOBILE NUMBER</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Mobile Number" type="text" id="Mobile Number">
</td>
</tr>
<tr>
<td><p>EMAIL ADDRESS</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Email Address" type="text" id="Email Address" value="">
</td>
</tr>
<tr>
<td><p>&nbsp;</p>
<p>&nbsp;</p></td>
<td style="width: 202px"><input type="submit" name="Submit" value="Submit">
<input type="reset" value="Clear" name = "Clear" /> </td></tr>
</table>


</form>
<%
If Request.Form("Submit") = "Submit" 'the button was clicked add the information to the database
Dim sSQL
Dim oRs
Dim connString

sSQL = "SELECT * FROM [Client Database] WHERE 1=2" ' where clause will guarantee no rows are returned
Set oRs = Server.CreateObject("ADODB.Recordset")
connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=path_to_your_database\databasename.mdb"
oRs.Open sSQL,connString,1,2
oRs.AddNew()

'now merely loop through the form fields and add the value returned from each field to the appropriate column in the database
For each field in Request.Form
If NOT field = "Submit" ' the submit button
If NOT field = "Clear" ' the reset button
oRs.[field] = Request.Form(field)
End If
End If
Next
oRs.Update()
oRs.Close
Set oRs = Nothing
End If
%>


AS a hint do not use spaces in your column names when you set up your database. Either use the Underscore charater to seperate the words or use Camel Back coding (capitalize the first letter of words) like so FirstName, LastName, StreetAddress1, etc... This way you do not have to enclose the columns in [ ] (as long as you do not use a reserved word to name a column)

Thanks very much for your help.
I will try it and get back to you.
Ryan

Tried correcting the code to what you said, however the data that i entered into the forms doesnt seem to get sent. The database doesnt contain the data that i have entered in the forms. Do you know why this is.

This is the code now -


<body>
<p class="tit style1">CONTACT AND GROUP DETAILS</p>
<hr>
<p><a href="index.htm">Back to Home Page&gt;&gt; </a>
<p>&nbsp;</p><form method="POST" action="<%=Request.ServerVariables("SCRIPT_NAME")%>" ">
<table width="408">
<tr>
<td width="192"><p>DATE REQUIRED FROM</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Date Required From" type="text" id="Date Required From">
</td>
</tr>
<tr>
<td><p>DATE REQUIRED TO </p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Date Required To" type="text" id="Date Required To">
</td>
</tr>
<tr>
<td><p>FIRST NAME</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Firstname" type="text" id="Firstname">
</td>
</tr>
<tr>
<td><p>SURNAME</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Surname" type="text" id="Surname">
</td>
</tr>
<tr>
<td><p>NUMBER OF PEOPLE IN PARTY</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Number of people" type="text" id="Number of people">
</td>
</tr>
<tr>
<td><p>NAMES OF PEOPLE</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<textarea name="Names of people" rows="5" id="Names of people"></textarea>
</td>
</tr>
<tr>
<td><p>ADDRESS LINE 1</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Address Line 1" type="text" id="Address Line 1">
</td>
</tr>
<tr>
<td style="height: 59px"><p>ADDRESS LINE 2</p>
<p>&nbsp; </p></td>
<td style="width: 202px; height: 59px">
<input name="Address Line 2" type="text" id="Address Line 2">
</td>
</tr>
<tr>
<td><p>TOWN/CITY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Town/City" type="text" id="Town/City">
</td>
</tr>
<tr>
<td><p>COUNTY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="County" type="text" id="County">
</td>
</tr>
<tr>
<td><p>POSTCODE</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Postcode" type="text" id="Postcode">
</td>
</tr>
<tr>
<td><p>COUNTRY</p>
<p>&nbsp;</p></td>
<td style="width: 202px">
<input name="Country" type="text" id="Country">
</td>
</tr>
<tr>
<td><p>PHONE NUMBER</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Phone Number" type="text" id="Phone Number">
</td>
</tr>
<tr>
<td><p>MOBILE NUMBER</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Mobile Number" type="text" id="Mobile Number">
</td>
</tr>
<tr>
<td><p>EMAIL ADDRESS</p>
<p>&nbsp; </p></td>
<td style="width: 202px">
<input name="Email Address" type="text" id="Email Address" value="">
</td>
</tr>
<tr>
<td><p>&nbsp;</p>
<p>&nbsp;</p></td>
<td style="width: 202px"><input type="submit" name="Submit" value="Submit">
<input type="reset" value="Clear" name = "Clear" /> </td></tr>
</table>


</form>
<%
If Request.Form("Submit") = "Submit" 'the button was clicked add the information to the database
Dim sSQL
Dim oRs
Dim connString

sSQL = "SELECT * FROM [Client Database] WHERE 1=2" ' where clause will guarantee no rows are returned
Set oRs = Server.CreateObject("ADODB.Recordset")
connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\Documents and Settings\Ryan\My Documents\Ryan\bookingcalendar.mdb"
oRs.Open sSQL,connString,1,2
oRs.AddNew()

'now merely loop through the form fields and add the value returned from each field to the appropriate column in the database
For each field in Request.Form
If NOT field = "Submit" ' the submit button
If NOT field = "Clear" ' the reset button
oRs.[field] = Request.Form(field)
End If
End If
Next
oRs.Update()
oRs.Close
Set oRs = Nothing
End If
%>
<hr>

Thanks for your help.

The database needs to be put into a file that A can be found from your webpage) and B) can execute read/write permissions on. Create a folder outside the mydocuments folder and Move the database to that location, then open IIS and create a virtual web directory pointing to the directory where your database is located. Next click on properties and set the permissions to the virtual directory to be read and write. Now try it