Can't write to Access db

I saw the previous posts about using FrontPage, however I have FTP'd a guestbook application to my site that does allow writing to the database without doing anything else. You can see it at:

http://www.joe13.com/guestbook

It came from a web site called webwizguide.com which has some good apps for free.

I scaled it down to a REALLY simple app, so I can understand how to do the programming. My test app is at:

http://www.joe13.com/test

If you enter a user ID of "jdoe" or "jsmith" it tells you that it is a duplicate user ID, which is true. So, I know it is reading the database ok.

However, when I try to create a record by entering another user ID, I get the error:


==============================================================
The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

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

Please try the following:

Open the www.joe13.com home page, and then look for links to the information you want.
Click the Refresh button, or try again later.

Click Search to look for information on the Internet.
You can also see a list of related sites.




HTTP 500 - Internal server error
Internet Explorer
==============================================================

Here is the code:
=====================================================================
<%

'Dimension variables

Dim adoCon 'Database Connection Variable
Dim strCon 'Holds the Database driver and the path and name of the database
Dim rsRegisterUser 'Database Recordset Variable
Dim strAccessDB 'Holds the Access Database Name
Dim strSQL 'Database query sring

Dim strInputUserID 'Holds the user ID
Dim strInputFirstName 'Holds the user first name
Dim strInputLastName 'Holds the user last name


'Initalise the user entered variables

strInputUserID = Request.Form("userID")
strInputFirstName = Request.Form("firstName")
strInputLastName = Request.Form("lastName")

strAccessDB = "Users"
Set adoCon = Server.CreateObject("ADODB.Connection")
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" & Server.MapPath(strAccessDB)
adoCon.Open strCon
Set rsRegisterUser = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT tblUsers.userID FROM tblUsers WHERE tblUsers.userID ='" & strInputUserID & "'"
rsRegisterUser.Open strSQL, strCon

If NOT rsRegisterUser.EOF Then
Set adoCon = Nothing
Set strCon = Nothing
Set rsRegisterUser = Nothing
Response.Redirect "duplicate_user.htm"
End If

rsRegisterUser.AddNew

Response.Redirect "got_here.htm" '*** it does not get here!! It dies on the line above ***

rsRegisterUser.Fields("userID") = strInputUserID
rsRegisterUser.Fields("firstName") = strInputFirstName
rsRegisterUser.Fields("lastName") = strInputLastName
rsRegisterUser.Update

Set adoCon = Nothing
Set strCon = Nothing
Set rsRegisterUser = Nothing

Response.Redirect"user_added.htm"
%>
=====================================================================

I put in a comment where the code is dying.

My questions:

1) Is there any error handling that I can put in the code so I get more than the 500 error?

2) How do I get this to write to the database. I'd be happy to zip up the app and send it to anyone who is willing to look at it. It is simple as possible. Once I get the basics working, then I'll build a reall application around it.


Thanks!

Brad

 

Oops, the web site from where I downloaded the original application is:

http://www.webwizguide.info

 

Bruce,

Yes, this is the error I now see. Can you (or anyone) help diagnose why? This is a simple application!


Any help is appreciated,

Brad

quote:Originally posted by bruce

quote:Originally posted by bamodeo
1) Is there any error handling that I can put in the code so I get more than the 500 error?

See http://kb.discountasp.net/article.aspx?id=10052

You should see this error once you have done this.

ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

/test/add_user.asp, line 38



I saw the previous posts about using FrontPage, however I have FTP'd a guestbook application to my site that does allow writing to the database without doing anything else. You can see it at:

http://www.joe13.com/guestbook

It came from a web site called webwizguide.com which has some good apps for free.

I scaled it down to a REALLY simple app, so I can understand how to do the programming. My test app is at:

http://www.joe13.com/test

If you enter a user ID of "jdoe" or "jsmith" it tells you that it is a duplicate user ID, which is true. So, I know it is reading the database ok.

However, when I try to create a record by entering another user ID, I get the error:


==============================================================
The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

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

Please try the following:

Open the www.joe13.com home page, and then look for links to the information you want.
Click the Refresh button, or try again later.

Click Search to look for information on the Internet.
You can also see a list of related sites.




HTTP 500 - Internal server error
Internet Explorer
==============================================================

Here is the code:
=====================================================================
<%

'Dimension variables

Dim adoCon 'Database Connection Variable
Dim strCon 'Holds the Database driver and the path and name of the database
Dim rsRegisterUser 'Database Recordset Variable
Dim strAccessDB 'Holds the Access Database Name
Dim strSQL 'Database query sring

Dim strInputUserID 'Holds the user ID
Dim strInputFirstName 'Holds the user first name
Dim strInputLastName 'Holds the user last name


'Initalise the user entered variables

strInputUserID = Request.Form("userID")
strInputFirstName = Request.Form("firstName")
strInputLastName = Request.Form("lastName")

strAccessDB = "Users"
Set adoCon = Server.CreateObject("ADODB.Connection")
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" & Server.MapPath(strAccessDB)
adoCon.Open strCon
Set rsRegisterUser = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT tblUsers.userID FROM tblUsers WHERE tblUsers.userID ='" & strInputUserID & "'"
rsRegisterUser.Open strSQL, strCon

If NOT rsRegisterUser.EOF Then
Set adoCon = Nothing
Set strCon = Nothing
Set rsRegisterUser = Nothing
Response.Redirect "duplicate_user.htm"
End If

rsRegisterUser.AddNew

Response.Redirect "got_here.htm" '*** it does not get here!! It dies on the line above ***

rsRegisterUser.Fields("userID") = strInputUserID
rsRegisterUser.Fields("firstName") = strInputFirstName
rsRegisterUser.Fields("lastName") = strInputLastName
rsRegisterUser.Update

Set adoCon = Nothing
Set strCon = Nothing
Set rsRegisterUser = Nothing

Response.Redirect"user_added.htm"
%>
=====================================================================

I put in a comment where the code is dying.

My questions:

1) Is there any error handling that I can put in the code so I get more than the 500 error?

2) How do I get this to write to the database. I'd be happy to zip up the app and send it to anyone who is willing to look at it. It is simple as possible. Once I get the basics working, then I'll build a reall application around it.


Thanks!

Brad
</blockquote id="quote"></font id="quote">
</blockquote id="quote"></font id="quote">

 

Bruce, your example worked great! Thanks for the help!

Brad


quote:Originally posted by bruce

Your locktype is wrong..

check out this article : http://www.devguru.com/Technologies/ado/quickref/recordset_locktype.html

If you just want a simple asp sample to insert something into the db, see below

<%

Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")

' DSN
cnnSimple.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\test.mdb"))
cnnSimple.Execute("insert into tblContact (FirstName,LastName,Email) values ('Peter','lucas','[email protected]')")
cnnSimple.close

%>

Hope this helps

quote:Originally posted by bamodeo

Bruce,

Yes, this is the error I now see. Can you (or anyone) help diagnose why? This is a simple application!


Any help is appreciated,

Brad

quote:Originally posted by bruce

quote:Originally posted by bamodeo
1) Is there any error handling that I can put in the code so I get more than the 500 error?

See http://kb.discountasp.net/article.aspx?id=10052

You should see this error once you have done this.

ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

/test/add_user.asp, line 38



I saw the previous posts about using FrontPage, however I have FTP'd a guestbook application to my site that does allow writing to the database without doing anything else. You can see it at:

http://www.joe13.com/guestbook

It came from a web site called webwizguide.com which has some good apps for free.

I scaled it down to a REALLY simple app, so I can understand how to do the programming. My test app is at:

http://www.joe13.com/test

If you enter a user ID of "jdoe" or "jsmith" it tells you that it is a duplicate user ID, which is true. So, I know it is reading the database ok.

However, when I try to create a record by entering another user ID, I get the error:


==============================================================
The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.

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

Please try the following:

Open the www.joe13.com home page, and then look for links to the information you want.
Click the Refresh button, or try again later.

Click Search to look for information on the Internet.
You can also see a list of related sites.




HTTP 500 - Internal server error
Internet Explorer
==============================================================

Here is the code:
=====================================================================
<%

'Dimension variables

Dim adoCon 'Database Connection Variable
Dim strCon 'Holds the Database driver and the path and name of the database
Dim rsRegisterUser 'Database Recordset Variable
Dim strAccessDB 'Holds the Access Database Name
Dim strSQL 'Database query sring

Dim strInputUserID 'Holds the user ID
Dim strInputFirstName 'Holds the user first name
Dim strInputLastName 'Holds the user last name


'Initalise the user entered variables

strInputUserID = Request.Form("userID")
strInputFirstName = Request.Form("firstName")
strInputLastName = Request.Form("lastName")

strAccessDB = "Users"
Set adoCon = Server.CreateObject("ADODB.Connection")
strCon = "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" & Server.MapPath(strAccessDB)
adoCon.Open strCon
Set rsRegisterUser = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT tblUsers.userID FROM tblUsers WHERE tblUsers.userID ='" & strInputUserID & "'"
rsRegisterUser.Open strSQL, strCon

If NOT rsRegisterUser.EOF Then
Set adoCon = Nothing
Set strCon = Nothing
Set rsRegisterUser = Nothing
Response.Redirect "duplicate_user.htm"
End If

rsRegisterUser.AddNew

Response.Redirect "got_here.htm" '*** it does not get here!! It dies on the line above ***

rsRegisterUser.Fields("userID") = strInputUserID
rsRegisterUser.Fields("firstName") = strInputFirstName
rsRegisterUser.Fields("lastName") = strInputLastName
rsRegisterUser.Update

Set adoCon = Nothing
Set strCon = Nothing
Set rsRegisterUser = Nothing

Response.Redirect"user_added.htm"
%>
=====================================================================

I put in a comment where the code is dying.

My questions:

1) Is there any error handling that I can put in the code so I get more than the 500 error?

2) How do I get this to write to the database. I'd be happy to zip up the app and send it to anyone who is willing to look at it. It is simple as possible. Once I get the basics working, then I'll build a reall application around it.


Thanks!

Brad
</blockquote id="quote"></font id="quote">
</blockquote id="quote"></font id="quote">
</blockquote id="quote"></font id="quote">
</blockquote id="quote"></font id="quote">