Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Returning a Value from a new record????

Status
Not open for further replies.
fireballuk2001

fireballuk2001

IS-IT--Management
Nov 29, 2001
6
0
0
GB
I have a form that posts a new "Tech Job" to a access database. This creates a new record in the database and has a autonumber field called ID. What i want to do is when a user enters their details, and the new record is created, and a ID number automatically generated by access, i want it to tell the user the "jobs ID number" so that they can search the db for their job to see its status based on their job id... heres part of my script............

<%@ LANGUAGE=&quot;VBSCRIPT&quot;%>
<% 'on error resume next %>

<%
Function StrQuoteReplace(Value)
StrQuoteReplace = Replace(Value, &quot;'&quot;, &quot;''&quot;)
End Function

Dim name, location, computer, priority, details
Dim memberSQL , checkSet
' put the form content into variables
name = request(&quot;name&quot;)
location = request(&quot;location&quot;)
computer = request(&quot;computer&quot;)
priority = request(&quot;priority&quot;)
details = request(&quot;details&quot;)

%>

<!---#include file =&quot;config-start.asp&quot;--->

<%

jobSQL = &quot;insert into jobs &quot;
jobSQL = jobSQL & &quot;(name, location, computer, priority, details)&quot;
jobSQL = jobSQL & &quot; values ('&quot;&name&&quot;', '&quot;&location&&quot;', '&quot;&computer&&quot;', '&quot;&priority&&quot;', '&quot;& StrQuoteReplace(Details) &&quot;')&quot;

canvasDB.Execute(jobSQL)
%>

<html>
<head>
<title>newjob.asp</title>
<meta http-equiv=&quot;Content-Type&quot; content=&quot;text/html; charset=iso-8859-1&quot;>
</head>

<body bgcolor=&quot;#FFFFFF&quot; text=&quot;#000000&quot;>
<% ' connect to database using our include file %>
<!---#inlclude file=&quot;config-end.asp&quot;--->

<% if err.number = 0 then ' check to see if there were any errors%>
<B><CENTER>Job Details have been saved!</CENTER></B>

<%else%>

<center>
<B>Sorry there was a problem with your entry, Try again!</B>

<% end if%>
</html>





its very messy because im a beginner and this is my first site with asp. Ive got to do this cos its for my ICT Coursework (yea, im still at school doing A-levels in england :)

Please HELP!
 
Sort by date Sort by votes
you select the max id from the table and display it

set rsMax = cn.execute &quot;Select Max(id) AS MaxID from Table&quot;

response.write rsMax(&quot;MaxID&quot;)

dan0
 
Upvote 0 Downvote
If it's a single-user application, querying the MAX() per pyttviper's comments will suffice.

However, the more reliable way (especially in a multi-user environment) is to query the @@Identity variable if it's an Access2K database.

SET oRS = oConn.Execute &quot;Select @@Identity AS LastID&quot;
Response.Write oRS.Fields(&quot;LastID&quot;).Value Jon Hawkins
 
Upvote 0 Downvote
On both of your examples, i seem to get the following VBScript error:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

and it seems to point to the &quot; marks... any ideas of as what im doing wrong?
 
Upvote 0 Downvote
SET oRS = oConn.Execute &quot;Select @@Identity AS LastID&quot;

should be

SET oRS = oConn.Execute(&quot;Select @@Identity AS LastID&quot;) Jon Hawkins
 
Upvote 0 Downvote
Thanks! that worked... told you i was a beginner. just experimenting with others codes and mangling them up to something that is useful to me. thanks for your help!
 
Upvote 0 Downvote
There is a problem, if more than one person is submitting the form at a time, one will create an ID, the other will create an ID, then when the first is searching for the &quot;max&quot; it get's the ID from the second user. However, if you use the &quot;AddNew&quot; method with a server side cursor location while appending to the database, you can pull the new autonumber at the same time as submitting the information to the DB.

Here is a sample:


<%@ Language=VBScript%>
<%
Option Explicit
Response.Expires = 0
%>
<!-- #include file=&quot;adovbs.inc&quot; -->
<%
Dim conn, rs, connString, sql
Dim cname, cage, cemail
Dim autoNumberID

cname = Request.Form(&quot;cname&quot;)
cage = Request.Form(&quot;cage&quot;)
cemail = Request.Form(&quot;cemail&quot;)

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot;)
connString = &quot;Data Source=Northwind;User ID=sa;Password=;&quot;
conn.Open conn
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)

rs.CursorLocation = adUseServer
rs.CursorType = adOpenKeyset
rs.LockType = adLockOptimistic

rs.Open &quot;myTableName&quot;, conn, , , adCmdTable
' We leave the 3rd and 4th parameters for the
' open recordset command blank since
' they were already declared above.
' The adCmdTable says the table is being used for
' the data source rather than a text-sql statement.

rs.AddNew

rs(&quot;userName&quot;) = cname
rs(&quot;userAge&quot;) = cage
rs(&quot;userEmail&quot;) = cemail
rs.Update

autoNumberID = rs(&quot;autoNumberPK&quot;)

Response.write(&quot;Your New Record ID is &quot;)
Response.write(autoNumberID & &quot;<BR>&quot;)

rs.Close : conn.Close
Set rs = Nothing : Set conn = Nothing

%>


Hope this helps!
-Ovatvvon :-Q
 
Upvote 0 Downvote
That sounds like a better solution... thankyou for your help.. ill try and implement that tomorrow, its 1:30am and i have school in the morning :eek:)

I'll let you know how i go with this script... and again, i thankyou.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Start loop from a specified point
Replies
0
Views
67
penguinspeaks
penguinspeaks
penguinspeaks
  • Locked
  • Question
Parse a database field that is comma delimited and input the results into another table
Replies
0
Views
77
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
First execute an input command and only then proceed with displaying the next record of the table
Replies
0
Views
62
leifoet
leifoet
leifoet
  • Locked
  • Question
Wrong code in a href ?
Replies
1
Views
81
leifoet
leifoet
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
79
leifoet
leifoet

Part and Inventory Search

Sponsor

Back
Top