Trying to get ASP form to show up in Access Database

[jhardman]

What kind of database are you using? and did you actually make the DSN?

Jared

[jerrydigital]

hello, I am using a Microsoft Access database called registration.md b with a table called registration.

I created the DSN by going to Start/Control Panel/Administrative Tools/Data Bases(ODBC).... then I clicked on the System DSN tab and added Microsoft Access Driver (*.mdb) and named it registration.ds n.

It shows up when i open up the Data Bases(ODBC) folder under the System DSN tab.

Not sure if this is the correct method, I am following steps from SAMS Teach Yourself ASP 3.0.

I tried to contact Godaddy.com where I have my hosting account through but they constantly tell me they are not allowed to assist with scripting so I am at a stand still.

However, godaddy.com does have a help section that has the following instructions describing using ASP/ADO to connect to an Access Database. How would I implement these instructions into my code listed above?

<%
Dim oConn, oRs
Dim qry, connectstr
Dim db_path
Dim db_dir
db_dir = Server.MapPath("access_db") & "\"
db_path = db_dir & "yourdatabasefile.mdb"
fieldname = "your_field"
tablename = "your_table"

connectstr = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & db_path

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT * FROM " & tablename

Set oRS = oConn.Execute(qry)

if not oRS.EOF then
while not oRS.EOF
response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & "
"
oRS.movenext
wend
oRS.close
end if

Set oRs = nothing
Set oConn = nothing

%>

[GazMathias]

hello, I am using a Microsoft Access database called registration.md b with a table called registration.

I created the DSN by going to Start/Control Panel/Administrative Tools/Data Bases(ODBC).... then I clicked on the System DSN tab and added Microsoft Access Driver (*.mdb) and named it registration.ds n.

It shows up when i open up the Data Bases(ODBC) folder under the System DSN tab.

Not sure if this is the correct method, I am following steps from SAMS Teach Yourself ASP 3.0.

I tried to contact Godaddy.com where I have my hosting account through but they constantly tell me they are not allowed to assist with scripting so I am at a stand still.

However, godaddy.com does have a help section that has the following instructions describing using ASP/ADO to connect to an Access Database. How would I implement these instructions into my code listed above?

<%
Dim oConn, oRs
Dim qry, connectstr
Dim db_path
Dim db_dir
db_dir = Server.MapPath("access_db") & "\"
db_path = db_dir & "yourdatabasefile.mdb"
fieldname = "your_field"
tablename = "your_table"

connectstr = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & db_path

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT * FROM " & tablename

Set oRS = oConn.Execute(qry)

if not oRS.EOF then
while not oRS.EOF
response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & "
"
oRS.movenext
wend
oRS.close
end if

Set oRs = nothing
Set oConn = nothing

%>

I am starting to think your domain is hosted in shared webspace (on linux using SunOne ASP probably) and not on a dedicated Windows Server.

So can I ask on what machine you created the DSN? Was it your local PC or am I wrong, and you do have a dedicated Windows server and that is where you set it up?

Also the above Go Daddy code is illustrating a DSN-less connection to a database you specify stored in directory called "access_db" within the webspace, which then prints out the contents of the field you specify from the table you specify.

[jerrydigital]

I am not sure exactly if I am answering your question correctly, but I will try my best.

I set up the DSN connection on my personal dell laptop.

I have a hosting account with godaddy.com and I have to upload my webpages(html, asp, mdb) to the FTP client they issued...I am not sure if this is a dedicated or shared site.

Do you think this is why it can't read my dsn connection? if so, is it possible to do what i am trying or do i have to use the godaddy tools? The problem is, when I contact them to ask questions, they simply state they are not allowed to help with 3rd party scripting.

Thank you for clearing up the godaddy code i posted. sounds like that code would assist me if i created a table on their website.

if you know of any way to set up a connection using my database i have created on godaddy.com's hosting account, please let me know

thanks so much for all of your help so far, it is much appreciated.

[GazMathias]

I am not sure exactly if I am answering your question correctly, but I will try my best.

I set up the DSN connection on my personal dell laptop.

I have a hosting account with godaddy.com and I have to upload my webpages(html, asp, mdb) to the FTP client they issued...I am not sure if this is a dedicated or shared site.

Do you think this is why it can't read my dsn connection? if so, is it possible to do what i am trying or do i have to use the godaddy tools? The problem is, when I contact them to ask questions, they simply state they are not allowed to help with 3rd party scripting.

Thank you for clearing up the godaddy code i posted. sounds like that code would assist me if i created a table on their website.

if you know of any way to set up a connection using my database i have created on godaddy.com's hosting account, please let me know

thanks so much for all of your help so far, it is much appreciated.

Yes the connection is failing because it can't see the DSN on your computer, and nor would you want it to.

You need to upload your access db into your webspace and create a DSN-less connection to it there.

I recommend that you do use Go Daddy's example code just to test the connection. When you know it works, then apply its principles to your page.

Just remember to change the MapPath, "yourfield" , "yourtable" and yourdatabase" stuff to your actual database details. Or better yet use a very simple SQL statement so there's even less margin for error, something like:

SELECT field FROM table WHERE ID = 1

Then just write out that one value with:

<%=oRS("field")%>

[jerrydigital]

ok, thanks. i will work on this tonight and let you know how it works out.

[jerrydigital]

I looked on godaddy and they have a place to set up a DSN. I named my access_register .dsn.

I also found the following code to use to connect to the dsn

<%
Dim oConn, oRs
Dim qry, connectstr, sDSNDir
Dim db_name, db_username, db_userpassword
Dim dsn_name

dsn_name = "your_dsn_name"
fieldname = "your_fieldname"
tablename = "your_tablename"

'assumes that _dsn exists in the root
sDSNDir = Server.MapPath("/_dsn")

connectstr = "filedsn=" & sDSNDir & "/" & dsn_name

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr
qry = "SELECT * FROM " & tablename

Set oRS = oConn.Execute(qry)

if not oRS.EOF then
while not oRS.EOF
response.write ucase(fieldname) & ": " & oRs.Fields(fieldname) & " "
oRS.movenext
wend
oRS.close
end if

Set oRs = nothing
Set oConn = nothing

%>

I am going to continue to play with this but what can I eliminate from the above godaddy code if all I am trying to do is have the user register and have their information store into the database table I created in Access called registration? Do I just need to add in my code? Sorry for all the questions, I am so new to this but feel like i am very close to accomplishing this step.

Below is my current asp page code.

<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<html>
<body>
<%
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = "DSN=access_register.dsn"
objConn.Open
Dim objRS, bolAlreadyExists
If ((Request.Form("firstname") = "") OR (Request.Form("lastname") = "") _
	OR (Request.Form("street") = "") OR (Request.Form("city") = "") _
	OR (Request.Form("state") = "") OR (Request.Form("zipcode") = "") _
	OR (Request.Form("birthdate") = "") OR (Request.Form("email") = "") _
	OR (Request.Form("confirmemail") = "") OR (Request.Form("password") = "") _
	OR (Request.Form("confirmpassword") = "") OR (Request.Form("question") = "") _
	OR (Request.Form("answer") = "") OR (Request.Form("confirmanswer") = "") _
	OR (Request.Form("interest") = "") OR (Request.Form("nonewsemail") = "")) Then
	
	
  Response.Write "<a href='registration.html'>"
  Response.Write "You must enter values for all the fields."
  Response.Write "</a>"
Else
bolAlreadyExists = False
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "registration", objConn, , adLockOptimistic, adCmdTable
Do While Not (objRS.EOF OR bolAlreadyExists)
If (StrComp(objRS("email"), Request.Form("email"), _
vbTextCompare) = 0) Then
Response.Write "<a href='registration.html'>"
Response.Write "Email address already found in table."
Response.Write "</a>"
bolAlreadyExists = True
End If
objRS.MoveNext
Loop
If Not bolAlreadyExists Then

objRS.AddNew

	objRS("firstname") = Request.Form("firstname")
	objRS("lastname") = Request.Form("lastname")
	objRS("street") = Request.Form("street")
	objRS("city") = Request.Form("city")
	objRS("state") = Request.Form("state")
	objRS("zipcode") = Request.Form("zipcode")
	objRS("birthdate") = Request.Form("birthdate")
	objRS("email") = Request.Form("email")
	objRS("confirmemail") = Request.Form("confirmemail")
	objRS("password") = Request.Form("password")
	objRS("confirmpassword") = Request.Form("confirmpassword")
	objRS("question") = Request.Form("question")
	objRS("answer") = Request.Form("answer")
	objRS("confirmanswer") = Request.Form("confirmanswer")
	objRS("awardprogram") = Request.Form("awardprogram")
	objRS("accountnumber") = Request.Form("accountnumber")
	objRS("interest") = Request.Form("interest")
	objRS("nonewsemail") = Request.Form("nonewsemail")
	objRS("hearabout") = Request.Form("hearabout")
	objRS.Update
	Response.Write "Thank you for registering."
  End If
  objRS.Close
  Set objRS = Nothing
End If
objConn.Close
Set objConn = Nothing
%>
</body>
</html>

[jerrydigital]

ok, i tried to implement the godaddy code to connect to their shared server but i am coming across the following error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x4500 Thread 0x4b9c DBC 0xa80d39c Jet'.

/registration2.a sp, line 22


I know I have made some errors but I don't know where to begin to fix this error code. Here is my current asp processing page

<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<html>
<body>
<%
Dim oConn, oRs
Dim connectstr, sDSNDir
Dim db_name, db_username, db_userpassword
Dim dsn_name, tablename

dsn_name = "access_register.dsn"
tablename = "registration"

sDSNDir = Server.MapPath("/_dsn")

connectstr = "filedsn=" & sDSNDir & "/" & dsn_name

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr

Dim objRS, bolAlreadyExists
If ((Request.Form("firstname") = "") OR (Request.Form("lastname") = "") _
	OR (Request.Form("street") = "") OR (Request.Form("city") = "") _
	OR (Request.Form("state") = "") OR (Request.Form("zipcode") = "") _
	OR (Request.Form("birthdate") = "") OR (Request.Form("email") = "") _
	OR (Request.Form("confirmemail") = "") OR (Request.Form("password") = "") _
	OR (Request.Form("confirmpassword") = "") OR (Request.Form("question") = "") _
	OR (Request.Form("answer") = "") OR (Request.Form("confirmanswer") = "") _
	OR (Request.Form("interest") = "") OR (Request.Form("nonewsemail") = "")) Then
	
	
  Response.Write "<a href='registration.html'>"
  Response.Write "You must enter values for all the fields."
  Response.Write "</a>"
Else
bolAlreadyExists = False
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "registration", objConn, , adLockOptimistic, adCmdTable
Do While Not (objRS.EOF OR bolAlreadyExists)
If (StrComp(objRS("email"), Request.Form("email"), _
vbTextCompare) = 0) Then
Response.Write "<a href='registration.html'>"
Response.Write "Email address already found in table."
Response.Write "</a>"
bolAlreadyExists = True
End If
objRS.MoveNext
Loop
If Not bolAlreadyExists Then

objRS.AddNew

	objRS("firstname") = Request.Form("firstname")
	objRS("lastname") = Request.Form("lastname")
	objRS("street") = Request.Form("street")
	objRS("city") = Request.Form("city")
	objRS("state") = Request.Form("state")
	objRS("zipcode") = Request.Form("zipcode")
	objRS("birthdate") = Request.Form("birthdate")
	objRS("email") = Request.Form("email")
	objRS("confirmemail") = Request.Form("confirmemail")
	objRS("password") = Request.Form("password")
	objRS("confirmpassword") = Request.Form("confirmpassword")
	objRS("question") = Request.Form("question")
	objRS("answer") = Request.Form("answer")
	objRS("confirmanswer") = Request.Form("confirmanswer")
	objRS("awardprogram") = Request.Form("awardprogram")
	objRS("accountnumber") = Request.Form("accountnumber")
	objRS("interest") = Request.Form("interest")
	objRS("nonewsemail") = Request.Form("nonewsemail")
	objRS("hearabout") = Request.Form("hearabout")
	objRS.Update
	Response.Write "Thank you for registering."
  End If
  objRS.Close
  Set objRS = Nothing
End If
objConn.Close
Set objConn = Nothing
%>
</body>
</html>

[GazMathias]

This Page suggests that the problem is with file permissions. I suggest you look there.

I still also believe you should attempt to connect using a very basic page and, once that works, apply the same principles to your registration page.

[jerrydigital]

thank you so much for all of your help.

i followed your recommendation and cut down my form code. then i kept plugging away at the godaddy.com code they provided and eventually, I got my registration form submit info to show up in the access table.

so, my question has been answered and i am moving on to the next step. thank you all again, I really appreciate the help. this website is a true blessing. have a nice weekend, and i am sure i will be asking questions down the road.....

Jerry