Classic ASP Discussion - web form entry to access database w/asp

Print Page | Close Window

web form entry to access database w/asp

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=1767
Printed Date: 29 March 2026 at 5:17am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: web form entry to access database w/asp

Posted By: swatson
Subject: web form entry to access database w/asp
Date Posted: 11 April 2003 at 10:56am

Hi All:

I followed Bruce Corkhill's tutorials for connecting to and adding records to an access database using asp. My file for connecting to the database and displaying the records works. But, my files for using a web form to enter records to the database do not. I'm copying the files -- can you help identify the problem? The web form connects to the access database but new record does not get added. Thanks for your help!

Stephanie Watson

web form file (form.html):

<html>
<head>
<title>Profile Data Entry Form</title>
</head>

<Body bgcolor="#0099CC" text="white" link="#0000FF" alink="#FF0000" vlink="#663399">
<font face = "Georgia">
<H1 Align="center">Profile Data Entry Form</H1>
</font>

Facility_id
<input type = "text" name="Facility_id" size = 10>
<br>
Facility_name
<input type="text" name="Facility_name" size = 100>
<br>
Facility_description
<input type="text" name="Facility_description" size = 1000>
<br>
Street Address1
<input type="text" name="Street_address1" size = 100>
<br>
Street_Address2
<input type="text" name="Street_address2" size = 100>
City
<input type="text" name="City" size = 50>
State
<input type="text" name="State" size = 10>
<br>
Zipcode
<input type="text" name="Zipcode" size = 12>
<br>
Website
<input type="text" name="Website" size = 50>
<br>
Image
<input type="text" name="Image" size = 50>
<br>

</form>
</body>
</html>

add to database file (add_to_db.asp)

<html>
<head>
<title>Adding data to database</title>
</head>
<body>

Adding data to database...

<%@ Language="VBScript"%>
<%

'Dimension variables
Dim adoCon2 'Holds the database connection object
Dim rsAddFacility 'Holds the recordset for the new record to be added
Dim strSQL2 'Holds the SQL query to query the database

Set adoCon2=Server.CreateObject("ADODB.Connection") 'Create an ADO Connection Object

adoCon2.Open "DSN=profiles"
'Set an active connection to the Connection object using a DSN connection

Set rsAddFacility=Server.CreateObject("ADODB.Recordset")
'Create an ADO recordset object

'strSQL2="SELECT Facility.Facility_id, Facility.Facility_name, Facility.Facility_description, Facility.Street_address1, Facility_Street_address2, Facility.City, Facility.State, Facility.Zipcode, Facility.Website, Facility.Image FROM Facility;"
'Initialize the strSQL variable with an SQL statement to query the database

'rsAddFacility.CursorType=2
'Set the cursor type we are using so we can navigate through the recordset

'rsAddFacility.LockType=3
'Set the lock type so that the record is locked by ADO when it is updated

rsAddFacility.Open strSQL2, adoCon2
'Open the recordset with the SQL query

rsAddFacility.AddNew
'Tell the recordset we are adding a new record to it

rsAddFacility.Fields("Facility_id")=Request.Form("Facility_id")
rsAddFacility.Fields("Facility_name")=Request.Form("Facility_name")
rsAddFacility.Fields("Facility_description")=Request.Form("Facility_description")
rsAddFacility.Fields("Street_address1")=Request.Form("Street_address1")
rsAddFacility.Fields("Street_address2")=Request.Form("Street_address2")
rsAddFacility.Fields("City")=Request.Form("City")
rsAddFacility.Fields("State")=Request.Form("State")
rsAddFacility.Fields("Zipcode")=Request.Form("Zipcode")
rsAddFacility.Fields("Website")=Request.Form("Website")
rsAddFacility.Fields("Image")=Request.Form("Image")
'Add a new record to the dataset

rsAddFacility.Update
'Write the updated recordset to the database

rsAddFacility.Close
Set rsAddFacility=Nothing
Set adoCon2=Nothing
'Reset the server objects

'Response.Redirect "display_profile_db.asp"

</body>
</html>

Replies:

Posted By: pmormr
Date Posted: 11 April 2003 at 3:02pm

do you get an error or do you just not get any results?

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/

Posted By: pmormr
Date Posted: 11 April 2003 at 3:04pm

P.S. your sql statement, just use

SELECT * FROM Facility

Posted By: pmormr
Date Posted: 11 April 2003 at 3:09pm

also watch what you comment and your punctuation

the line in green is commented the semicolin in the blue text is unneeded. by using select * from facility you reduce the chance of typos.

P.S.S.S. you can't have Facility.Facility_id... etc. remove the part that says 'facility.' it is understood by the computer which table to open when you say FROM!

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/

Posted By: swatson
Date Posted: 11 April 2003 at 3:27pm

Thanks for your replies-

I don't get any error messages.

When I insert test data in the web form and hit submit, the html file appears to call add_to_db.asp (because the "Adding data to database" html line from the beginning of add_to_db.asp is returned). However, none of the data that I enter in the form is actually submitted to the database. Any ideas?

Thank you-

Stephanie Watson

Posted By: pmormr
Date Posted: 11 April 2003 at 3:45pm

did you make the changes i said to and then test it again. You don't even need the SQL SELECT statement. I included a rewritten file, try it and see if it works. REMEMBER: in asp coding the shorter the better, it reduces the chance of typo's. Don't add things to the end of the variables like rsaddfacility can be easily misspelled. i use 'rs' to make it simple. Also watch what you comment. you commented out your cursor and lock types.

Happy coding,

Paul Morgan

BEGIN VBSCRIPT

Dim conn
Dim rs

Set conn = Server.CreateObject("ADODB.Connection")

conn.Open "DSN=profiles"

Set rs=Server.CreateObject("ADODB.Recordset")

rs.open "facility", conn
rs.CursorType=2
rs.LockType=3

rs.AddNew
rs.Fields("Facility_id") = Request.Form("Facility_id")
rs.Fields("Facility_name") = Request.Form("Facility_name")
rs.Fields("Facility_description") = Request.Form("Facility_description")
rs.Fields("Street_address1") = Request.Form("Street_address1")
rs.Fields("Street_address2") = Request.Form("Street_address2")
rs.Fields("City") = Request.Form("City")
rs.Fields("State") = Request.Form("State")
rs.Fields("Zipcode") = Request.Form("Zipcode")
rs.Fields("Website") = Request.Form("Website")
rs.Fields("Image") = Request.Form("Image")
rsAddFacility.Update
'Write the updated recordset to the database

conn.close
rs.close
rs = NOTHING
conn = NOTHING

Response.Redirect "display_profile_db.asp"

%> GOOD LUCK!!

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/

Posted By: swatson
Date Posted: 11 April 2003 at 3:55pm

Hello Paul -

Yes, I had made the changes you suggested. Thanks for the tips. I tried the web form with your version of the asp file and what got returned was the asp code in the browser. Data doesn't get inserted into the database. I have the database in the same folder (wwwroot) as the asp files so they should be able to find the database...?

Thank you-

Stephanie

Posted By: pmormr
Date Posted: 11 April 2003 at 4:32pm

i found my error. When i converted the name of rsAddFacility i never told it to update afterwards. change the line where it sayes "rsAddFacility.update" to "rs.update". Make sure you run it through your web server. that should do it

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/

Posted By: pmormr
Date Posted: 11 April 2003 at 4:35pm

if it still doesn't work then can you give me the name of the database so i can use a dsn-less connection.

Posted By: swatson
Date Posted: 11 April 2003 at 4:42pm

Hi Paul - I had actually changed "rsAddFacility.update" to "rs.update". The record still wasn't added to the database. I have the html file, the asp file and the database in a MyWeb folder in wwwroot. The name of the database is mmugprofiles.mdb

Maybe I am not running files properly? To run them, I have been opening my browser (Explorer), File, Open-scroll to the html file. Then, I entered the data in the web form and hit the submit button. My browser returns with the asp code. Is there something else that I should be doing?

thank you!

Posted By: pmormr
Date Posted: 11 April 2003 at 5:03pm

alright, are you running a web server with iis/pws installed? If so can you send me the name of the operating system. Generally you would type this into the-- http://localhost/thenameofyourhtmlfile - http://localhost/thenameofyourhtmlfile your computer needs to have iis or pws installed and running for your computer to run any asp programming

Posted By: swatson
Date Posted: 11 April 2003 at 5:10pm

Hi Paul - Yes, I'm running IIS with Windows 2000. I'm now running the files from http://localhost/MyWeb/form.html - http://localhost/MyWeb/form.html

I'm now trying this cleaned-up code for the asp file:

<html>
<head>
<title>Adding data to database</title>
</head>
<body>

Adding data to database...

<%
'Dimension variables

Dim conn
Dim rs
Dim strSQL

'Create an ADO connection object
Set conn=Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN connection
conn.Open "DSN=profiles"

'Create an ADO recordset object
Set rs=Server.CreateObject("ADODB.Recordset")

strSQL="SELECT* FROM Facility;"

'Open the recordset with the SQL query
rs.Open strSQL, conn

'rs.CursorType=2
'Set the cursor type we are using so we can navigate through the recordset

'rsDatabase.LockType=3
'Set the lock type so that the record is locked by ADO when it is updated

rs.AddNew
'Tell the recordset we are adding a new record to it

rs.Fields("Facility_id")=Request.Form("Facility_id")
rs.Fields("Facility_name")=Request.Form("Facility_name")
rs.Fields("Facility_description")=Request.Form("Facility_description")
rs.Fields("Street_address1")=Request.Form("Street_address1")
rs.Fields("Street_address2")=Request.Form("Street_address2")
rs.Fields("City")=Request.Form("City")
rs.Fields("State")=Request.Form("State")
rs.Fields("Zipcode")=Request.Form("Zipcode")
rs.Fields("Website")=Request.Form("Website")
rs.Fields("Image")=Request.Form("Image")
'Add a new record to the dataset

rs.Update
'Write the updated recordset to the database

'Reset server objects
rs.Close
Set rs=Nothing
Set conn=Nothing

Response.Redirect "display_profile_db.asp"
'To display all the records in the database, including the new ones

</body>
</html>

But, when I run it -- Line 37 rs.AddNew gives me this error:

Error Type:
ADODB.Recordset (0x800A0CB3)
Object or provider is not capable of performing requested operation.
/MyWeb/add_to_db3.asp, line 37

Posted By: pmormr
Date Posted: 11 April 2003 at 5:46pm

i had some problems with rs.addnew too. THe following is an sql code for adding a new record. It should work.
Your going to have to assign each of the Request.Forms to a seperate variable. the first thing listed after the insert into facility statement in the parenthesis matches up with the first thing after the values statement and so on. Also, make sure that the insert into statment is on one line and the values statement is on another. The bbs might word wrap. I'm a little rusty on sql but it should work according to my reference book. Replace the following code where the rs.addnew and company whould go.

INSERT INTO Facility (Facility_ID, Facility_Name, Facility_Description, Street_address1, street_address2, city, state, zipcode, website, image)
VALUES (Facility_ID, Facility_Name, Facility_Description, Street_address1, street_address2, city, state, zipcode, website, image)

Posted By: pmormr
Date Posted: 11 April 2003 at 5:49pm

also, if that doesn't work. (Again!) then make sure that you have write permissions for your database or it cant write to your database

Posted By: bassy
Date Posted: 13 April 2003 at 9:11am

Hi swatson,

I had exactly the same probleme as you but I've found the following:

file (form.ASP) instead of file (form.html). It 's very misleading.

So you have to run from IIS the form file with the extension .ASP dont use the extension html.

And it will work

good luck, let know if it works

Posted By: swatson
Date Posted: 14 April 2003 at 1:01pm

Hi All -

Thanks to pmormr for all the help and to Bassy for the .asp extension tip. The .asp extension on the form file helped - now my form seems to actually be calling and implementing the add_to_database asp file.

However, I wasn't able to get the sql statement approach to work since the statement continued on more than one line and even breaking it into two parts didn't work.

I'm trying the connection object approach (using sql_insert as a dim variable). But, I'm getting the following error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 10.
/MyWeb/add_to_db6.asp, line 74

Can anyone help with this? I seem to have 10 parameters -- this is all the form fields...

Here is my add to database asp file (add_to_db6.asp):
<html>
<head>
<title>Adding data to database</title>
</head>
<body>
Adding data to database...(to test that form.asp is calling this file)
<%

'Dimension variables
Dim conn
Dim sql_insert

Dim Facility_id
Dim Facility_name
Dim Facility_description
Dim Street_address1
Dim Street_address2
Dim City
Dim State
Dim Zipcode
Dim Website
Dim Image

sql_insert="insert into Facility (Facility_id, Facility_name, Facility_description, Street_address1, Street_address2, City, State, Zipcode, Website, Image) VALUES" & _
"(Facility_id, Facility_name, Facility_description, Street_address1, Street_address2, City, State, Zipcode, Website, Image);"

'Create an ADO connection object
Set conn=Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN connection
conn.Open "DSN=profiles"

'Set up variables to receive the information from the form
Facility_id=Request.Form("Facility_id")
Facility_name=Request.Form("Facility_name")
Facility_description=Request.Form("Facility_description")
Street_address1=Request.Form("Street_address1")
Street_address2=Request.Form("Street_address2")
City=Request.Form("City")
State=Request.Form("State")
Zipcode=Request.Form("Zipcode")
Website=Request.Form("Website")
Image=Request.Form("Image")

'Write back form submittal to user - to confirm that the form entries are being read. This works.
Response.Write("<br />")
Response.Write("You submitted the following:")
Response.Write("<br />")
Response.Write(Facility_id)
Response.Write("<br />")
Response.Write(Facility_name)
Response.Write("<br />")
Response.Write(Facility_description)
Response.Write("<br />")
Response.Write(Street_address1)
Response.Write("<br />")
Response.Write(Street_address2)
Response.Write("<br />")
Response.Write(City)
Response.Write("<br />")
Response.Write(State)
Response.Write("<br />")
Response.Write(Zipcode)
Response.Write("<br />")
Response.Write(Website)
Response.Write("<br />")
Response.Write(Image)
Response.Write("<br />")

conn.Execute sql_insert
'somehow this is not inserting the form submittals to the database

'Reset server objects
conn.Close
Set conn=Nothing

Response.Redirect "display_profile_db.asp"
'To display all the records in the database, including the new ones

%>
</body>
</html>

And, here is the form.asp file:
<html>
<head>
<title>Profile Data Entry Form</title>
</head>

<Body bgcolor="#0099CC" text="white" link="#0000FF" alink="#FF0000" vlink="#663399">
<font face = "Georgia">
<H1 Align="center">Profile Data Entry Form</H1>
</font>

<form name="Form" method="post" action="add_to_db6.asp">

Facility_id
<input type = "text" name="Facility_id" size = 10>
<br>
Facility_name
<input type="text" name="Facility_name" size = 100>
<br>
Facility_description
<input type="text" name="Facility_description" size = 1000>
<br>
Street Address1
<input type="text" name="Street_address1" size = 100>
<br>
Street_Address2
<input type="text" name="Street_address2" size = 100>
City
<input type="text" name="City" size = 50>
State
<input type="text" name="State" size = 10>
<br>
Zipcode
<input type="text" name="Zipcode" size = 12>
<br>
Website
<input type="text" name="Website" size = 50>
<br>
Image
<input type="text" name="Image" size = 50>
<br>

<p><input type="Submit" name="Submit" value="SUBMIT"></p>

</form>
</body>
</html>

Posted By: bassy
Date Posted: 15 April 2003 at 2:11am

Hi swatson,

you have

first to:'Set up variables to receive the information from the form

Facility_id=Request.Form("Facility_id")
Facility_name=Request.Form("Facility_name")
Facility_description=Request.Form("Facility_description")
Street_address1=Request.Form("Street_address1")
Street_address2=Request.Form("Street_address2")
City=Request.Form("City")
State=Request.Form("State")
Zipcode=Request.Form("Zipcode")
Website=Request.Form("Website")
Image=Request.Form("Image")

after to:

sql_insert="insert into Facility (Facility_id, Facility_name, Facility_description, Street_address1, Street_address2, City, State, Zipcode, Website, Image) VALUES" & _
"(Facility_id, Facility_name, Facility_description, Street_address1, Street_address2, City, State, Zipcode, Website, Image);"

It seem to me you r trying to insert empty variables. You must set up Variables before use them for anything, otherwise they are empty.

good look

Posted By: swatson
Date Posted: 15 April 2003 at 10:29am

Thanks for suggestion, Bassy. Although, I'm executing the sql_insert later -- after setting up the variables to get info from the form -- in the file, using conn.Execute sql_insert

I did try to move the initial sql_insert statement to after setting up the variables to get info from the form but that didn't seem to matter. I got the same error. Any other ideas out there?

Posted By: Bunce
Date Posted: 15 April 2003 at 10:16pm

Your variables need to be escaped from your string:

sql_insert = "INSERT INTO...... VALUES ('" & Facility_ID & "','" _
& Facility_name & "','" & Facility_description & .... )"

Whenever a database call fails you should ALWAYS check to see exactly *what* is being sent to the database.

You can do this by performing a:
Response.Write (sql_Insert)
Response.End
after you have built your string.

Cheers,
Andrew

-------------
There have been many, many posts made throughout the world...
This was one of them.