Autonumber baffeled

I am having trouble getting and displaying the autonumber of OrderNumber from
by access database. I have listed my code below. Whenever I enter my data
from my form and submit I get the page cannot be displayed. Can anyone fix my
code. Thanks for all your help.


<%
Dim Conn
Dim Rs
Dim sql


Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")

'Set an active connection and select fields from the database
Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
Server.MapPath("equipment.mdb")
sql= "SELECT computer, monitor, scanner, printer, deptnum, location,
authorizedby, comments, datesubmitted FROM newrequest;" , Conn, adOpenStatic,
adLockOptimistic

'Set the lock and cursor type
'Rs.CursorType = 2
'Rs.LockType = 3

Rs.Open sql, Conn

Rs.AddNew 'Prepare the database to add a new record and add
Rs.Fields("computer") = Request.Form("computer")
Rs.Fields("monitor") = Request.Form("monitor")
Rs.Fields("scanner") = Request.Form("scanner")
Rs.Fields("printer") = Request.Form("printer")
Rs.Fields("deptnum") = Request.Form("deptnum")
Rs.Fields("location") = Request.Form("location")
Rs.Fields("authorizedby") = Request.Form("authorizedby")
Rs.Fields("comments") = Request.Form("comments")
Rs.Fields("datesubmitted") = Request.Form("datesubmitted")

Rs.Update
'before the requery, the Autonumber field shows as 0
Response.Write "
ID before Requery = " & Rs("ordernumber")

bookmark = Rs.absolutePosition ' First, store the location of you cursor
Rs.Requery ' Next, update your recordset with the data
from the database

'after the requery, the absolutePosition is the first record of the recordset
Response.Write "
ID before setting absolutePosition = " & Rs("ordernumber")

Rs.absolutePosition = bookmark ' Finally, change your cursor back

'now we have the Autonumber value
Response.Write "

Added ID = " & Rs("ordernumber")

Rs.Close
Set Rs = Nothing
Set Conn = Nothing
%>

<%
Dim MyMail
Dim MyBody
Dim MyEmail
Dim computer
Dim monitor
Dim scanner
Dim printer
Dim deptnum
Dim location
Dim authorizedby
Dim comments

'get values for the variables from the form
MyEmail = "rlawrimo@bellsouth.net"
computer = Request.Form("computer")
monitor = Request.Form("monitor")
scanner = Request.Form("scanner")
printer = Request.Form("printer")
deptnum = Request.Form("deptnum")
location = Request.Form("location")
authorizedby = Request.Form("authorizedby")
comments = Request.Form("comments")

'build the body of the email from the data in the form
MyBody = MyBody & "Computer: "& computer & vbcrlf
MyBody = MyBody & "Monitor: "& monitor & vbcrlf
MyBody = MyBody & "Scanner: "& scanner & vbcrlf
MyBody = MyBody & "Printer: "& printer & vbcrlf
MyBody = MyBody & "Department Number: "& deptnum & vbcrlf
MyBody = MyBody & "Location: "& location & vbcrlf
MyBody = MyBody & "Authorized By: "& authorizedby & vbcrlf
MyBody = MyBody & "Comments: "& comments & vbcrlf

'put the variables and other information we need into the mailing script
Set MyMail = CreateObject("CDO.Message")
MyMail.From = MyEmail
MyMail.To = "test@test.com"
MyMail.Subject = "Request for new equipment"
MyMail.TextBody = MyBody
MyMail.Send
Set MyMail= nothing
Response.Write("Your e-mail has been sent for the new equipment.")
%>

Re: Autonumber baffeled

1. When copy/pasting code, copy from a single location, or make sure you
understand the code.
2. Your SQL Select statement does not ask for a field named "ordernumber",
hence there is no such field in the recordset object. Attempting to display
it will fail.

/Leif


"Ronald_L" wrote in message
news:0D8A98B5-CA60-4DF1-BDCD-D74322E3C21A@microsoft.com...
>I am having trouble getting and displaying the autonumber of OrderNumber
>from
> by access database. I have listed my code below. Whenever I enter my
> data
> from my form and submit I get the page cannot be displayed. Can anyone fix
> my
> code. Thanks for all your help.
>
>
> <%
> Dim Conn
> Dim Rs
> Dim sql
>
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Set Rs = Server.CreateObject("ADODB.Recordset")
>
> 'Set an active connection and select fields from the database
> Conn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
> Server.MapPath("equipment.mdb")
> sql= "SELECT computer, monitor, scanner, printer, deptnum, location,
> authorizedby, comments, datesubmitted FROM newrequest;" , Conn,
> adOpenStatic,
> adLockOptimistic
>
> 'Set the lock and cursor type
> 'Rs.CursorType = 2
> 'Rs.LockType = 3
>
> Rs.Open sql, Conn
>
> Rs.AddNew 'Prepare the database to add a new record and add
> Rs.Fields("computer") = Request.Form("computer")
> Rs.Fields("monitor") = Request.Form("monitor")
> Rs.Fields("scanner") = Request.Form("scanner")
> Rs.Fields("printer") = Request.Form("printer")
> Rs.Fields("deptnum") = Request.Form("deptnum")
> Rs.Fields("location") = Request.Form("location")
> Rs.Fields("authorizedby") = Request.Form("authorizedby")
> Rs.Fields("comments") = Request.Form("comments")
> Rs.Fields("datesubmitted") = Request.Form("datesubmitted")
>
> Rs.Update
> 'before the requery, the Autonumber field shows as 0
> Response.Write "
ID before Requery = " & Rs("ordernumber")
>
> bookmark = Rs.absolutePosition ' First, store the location of you cursor
> Rs.Requery ' Next, update your recordset with the
> data
> from the database
>
> 'after the requery, the absolutePosition is the first record of the
> recordset
> Response.Write "
ID before setting absolutePosition = " &
> Rs("ordernumber")
>
> Rs.absolutePosition = bookmark ' Finally, change your cursor back
>
> 'now we have the Autonumber value
> Response.Write "

Added ID = " & Rs("ordernumber")
>
> Rs.Close
> Set Rs = Nothing
> Set Conn = Nothing
> %>
>
> <%
> Dim MyMail
> Dim MyBody
> Dim MyEmail
> Dim computer
> Dim monitor
> Dim scanner
> Dim printer
> Dim deptnum
> Dim location
> Dim authorizedby
> Dim comments
>
> 'get values for the variables from the form
> MyEmail = "rlawrimo@bellsouth.net"
> computer = Request.Form("computer")
> monitor = Request.Form("monitor")
> scanner = Request.Form("scanner")
> printer = Request.Form("printer")
> deptnum = Request.Form("deptnum")
> location = Request.Form("location")
> authorizedby = Request.Form("authorizedby")
> comments = Request.Form("comments")
>
> 'build the body of the email from the data in the form
> MyBody = MyBody & "Computer: "& computer & vbcrlf
> MyBody = MyBody & "Monitor: "& monitor & vbcrlf
> MyBody = MyBody & "Scanner: "& scanner & vbcrlf
> MyBody = MyBody & "Printer: "& printer & vbcrlf
> MyBody = MyBody & "Department Number: "& deptnum & vbcrlf
> MyBody = MyBody & "Location: "& location & vbcrlf
> MyBody = MyBody & "Authorized By: "& authorizedby & vbcrlf
> MyBody = MyBody & "Comments: "& comments & vbcrlf
>
> 'put the variables and other information we need into the mailing script
> Set MyMail = CreateObject("CDO.Message")
> MyMail.From = MyEmail
> MyMail.To = "test@test.com"
> MyMail.Subject = "Request for new equipment"
> MyMail.TextBody = MyBody
> MyMail.Send
> Set MyMail= nothing
> Response.Write("Your e-mail has been sent for the new equipment.")
> %>
>

Re: Autonumber baffeled

And to add ordernumber to the SQL statement perhaps change this:

sql= "SELECT computer, monitor, scanner, printer, deptnum, location,
authorizedby, comments, datesubmitted FROM newrequest;" , Conn,
adOpenStatic, adLockOptimistic

To be more like this instead:

sql= "SELECT ordernumber, computer, monitor, scanner, printer, deptnum,
location, authorizedby, comments, datesubmitted FROM newrequest;" ,
Conn, adOpenStatic, adLockOptimistic

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

<<
2. Your SQL Select statement does not ask for a field named
"ordernumber",
hence there is no such field in the recordset object. Attempting to
display
it will fail.
>>

*** Sent via Developersdex http://www.developersdex.com ***