id after auto increment from a form

F

Faith

Okay I am trying to get the id of the row that this form
inputs into the table. The ID auto increments. Is this
possible? Help or even code snippets would help me!

<%@ Language = "VBScript" %>
<!-- #include file="data/adovbs.inc" -->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
Transitional//EN" "http://www.w3.org/TR/REC-
html40/loose.dtd">
<html>
<head>
<%
dim strHeaderText
dim strHeaderText2
strHeaderText = "Help Desk"
strHeaderText2 = "Enter a Help Desk Ticket"

dim strCurrentPage, lPos, lLastSlash, iPosition

'** This removes everything in front of the last /
character
strCurrentPage = Request.ServerVariables("SCRIPT_NAME")
lPos = 1
while lPos < len(strCurrentPage)
if mid(strCurrentPage,lPos,1) = "/" then
lLastSlash = lPos + 1
end if
lPos = lPos + 1
wend
strCurrentPage = mid(strCurrentPage,lLastSlash)





%>
<!--#include file="Nav-Header.asp" -->
<!---------------------------------------------------------
-----------------------------------------------------------
----------------------------->


<%
' *** Begin DB Setup ***
Dim strConnString

' Override with our site-wide CONN string.
strConnString = "Provider=SQLOLEDB;Data Source=WWW2;" _
& "Initial Catalog=XXXX;User
Id=XXXX;Password=XXXX;" _
& "Connect Timeout=15;Network Library=dbmssocn;"

Dim cnnFormToDB ' CONN object
Dim strSQL ' String in which to build our SQL
command
Dim lngRecsAffected ' # of records affected... just
informational

' Vars for the fields read in from the form.
Dim strID
Dim strLongDescription
Dim strEntryDate
Dim strUserPriority
Dim strNumber
Dim strEmail
Dim strUserName
Dim strErrorMsg ' Holds error message if we catch
any problems.

'Email Variables
Dim objCDO ' Our CDO object
Dim strTo ' Strings to hold our email fields
Dim strBcc
Dim strFrom
Dim strSubject
Dim strBody

' See if we have any info to process.
If Request.Form("action") <> "Save Form Data" and strTo
= "" Then

' Show the form
%>
<p class="body">
Please enter a description of your problem, the
priority level you feel it merits, and a contact number.
&nbsp;&nbsp;That's it!<BR>Someone from the Help
Desk will call you back as soon as they are available.
</p>
<form action="<%= Request.ServerVariables
("SCRIPT_NAME") %>" method="post" name="ticket">
<input type="hidden" name="action" value="Save
Form Data">
<input type="hidden" maxlength="9"
name="entry_date" size="9" value="<%= FormatDateTime(NOW) %
<input type="hidden" maxlength="100"
name="user_name" size="35" value="<%Response.Write(Session
("user_name"))%>">
<table>
<tr>
<td align="right"><strong>User
Name:</strong></td>
<td class="body" nowrap><%Response.Write
(Session("user_name"))%></td>
</tr>

<tr>
<td align="right"><strong>Date &
Time:</strong></td>
<td class="body" nowrap><%= FormatDateTime
(NOW) %></td>
</tr>
<tr>
<td align="right"><strong>Problem
Description:</strong></td>
<td class="body" nowrap><textarea
cols="55" maxlength="500" rows="3" name="long_description"
id="long_description"></textarea></td>
</tr>
<tr>
<td align="right"><strong>User
Priority:</strong></td>
<td class="body" nowrap>
<select name="user_priority"
id="user_priority">
<option value="1">High</option>
<option value="2">Medium</option>
<option value="3" SELECTED>Low</option>
</select>&nbsp;<A class="nav"
HREF="javascript:" onClick="window.open
('priority.asp','','height=500,width=500,location=no,menuba
r=no,resizable=no,scrollbars=yes,status=no,toolbar=no,direc
tories=no')" >CHOOSING A PRIORITY FOR YOUR CASE</A>
</td>
</tr>
<tr>
<td align="right"><strong>Contact Phone
Number:</strong></td>
<td class="body" nowrap><input type="text"
maxlength="12" name="number" size="35"></td>
</tr>
<tr>
<td
align="right"><strong>Email:</strong></td>
<td class="body" nowrap><input type="text"
maxlength="150" name="email" size="35"></td>
</tr>
<tr>
<td>&nbsp;</td>
<td>
<input type="reset" value="Clear">
<input type="submit" value="Save">
</td>
</tr>
</table>
</form>

<%
Else

' Do our DB insert!
strLongDescription = Request.Form
("long_description")
strEntryDate = Request.Form("entry_date")
strUserPriority = Request.Form("user_priority")
strUserName = Request.Form("user_name")
strEmail = Request.Form("email")
strNumber = Request.Form("number")

strLongDescription = Replace
(strLongDescription, "'", "''")
strEntryDate = Replace(strEntryDate, "'", "''")
strUserPriority = Replace
(strUserPriority, "'", "''")
strUserName = Replace(strUserName, "'", "''")
strEmail = Replace(strEmail, "'", "''")
strNumber = Replace(strNumber, "'", "''")

' Open connection to the DB
Set cnnFormToDB = Server.CreateObject
("ADODB.Connection")
cnnFormToDB.Open strConnString

' Build our SQL String
strSQL = ""
strSQL = strSQL & "INSERT INTO
problem_reports "
strSQL = strSQL & "(long_description,
entry_date, user_priority, user_name, email, number) " &
vbCrLf
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "'" & strLongDescription
& "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strEntryDate & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strUserPriority
& "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strUserName & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strEmail & "'"
strSQL = strSQL & ", "
strSQL = strSQL & "'" & strNumber & "'"
strSQL = strSQL & ");"

'Email Form to the User and Help Desk on what they have
entered
strTo = Request.Form("email")
strFrom = "Help Desk <[email protected]>"
strBcc = "(e-mail address removed)"
strSubject = "New Help Desk Ticket was Created"
strBody = "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0
Transitional//EN"">" & vbCrLf _
& "<html>" & vbCrLf _
& "<head>" & vbCrLf _
& "<title>Your Help Desk Ticket Was
Posted</title>" & vbCrLf _
& "<meta http-equiv=Content-Type
content=""text/html; charset=iso-8859-1"">" & vbCrLf _
& "</head>" & vbCrLf _
& "<body bgcolor=""#b8d1e0"">" & vbCrLf _
& "<h2>Thanks for submitting your
information!</h2>" & vbCrLf _
& "<p>" & vbCrLf _
& "<B>Entry Date:</B> " & strEntryDate
& "<BR>" & vbCrLf _
& "<B>User:</B> " & strUserName & "<BR>" &
vbCrLf _
& "<B>Contact Number:</B> " & strNumber
& "<BR>" & vbCrLf _
& "<B>Problem Description:</B> " &
strLongDescription & "<BR>" & vbCrLf _
& "</p>" & vbCrLf _
& "<font size=""-1"">" & vbCrLf _
& "<p>Please address all concerns to
(e-mail address removed).</p>" & vbCrLf _
& "<p>This message was sent to: " & strTo
& "</p>" & vbCrLf _
& "<p>This message was sent to: " & strBcc
& "</p>" & vbCrLf _
& "</font>" & vbCrLf _
& "</body>" & vbCrLf _
& "</html>" & vbCrLf

Set objCDO = Server.CreateObject("CDO.Message")
With objCDO
.To = strTo
.From = strFrom
.Bcc = strBcc
.Subject = strSubject
.HtmlBody = strBody
.Send
End With
Set objCDO = Nothing

' Execute the SQL command. I pass it a
variable lngRecsAffected
' in which to return the number of records
affected. I also tell
' it that this is a text command and it
won't be returing any
' records... this helps it execute the
script faster!
' And before you ask... I don't know, but
YES IT IS OR!!!
cnnFormToDB.Execute strSQL,
lngRecsAffected, adCmdText Or adExecuteNoRecords

' Dispose of the CONN object
cnnFormToDB.Close
Set cnnFormToDB = Nothing

' Display a verification message and we're
done!
%>
<!---------------------------------------------------------
-----------------------------------------------------------
------------------------------------->


<P class="body">
<!---------------------------------------------------------
-----------------------------------------------------------
------------------------------------->
<% Response.Write "<h2>Your trouble ticket
# was opened as of " & strEntryDate & "</h2>" & vbCrLf %>

<% Response.Write "<B>User:</B> " &
strUserName & "<BR>" & vbCrLf %>
<% Response.Write "<B>Priority:</B> " &
strUserPriority & "<BR>" & vbCrLf %>
<% Response.Write "<B>Contact
Number:</B> " & strNumber & "<BR>" & vbCrLf %>
</P>
<p>
<strong>Number of records
affected:</strong> <%= lngRecsAffected %>
</p>
<UL>
</UL>
<!---------------------------------------------------------
-----------------------------------------------------------
------------------------------------->

<%
End If
'End If
%>
<!--#include file="Nav-Footer.asp" -->
 
K

Ken Schaefer

Use SELECT @@IDENTITY

There is some sample code here:
www.adopenstatic.com/experiments/fastestautonumber.asp

Cheers
Ken

: Okay I am trying to get the id of the row that this form
: inputs into the table. The ID auto increments. Is this
: possible? Help or even code snippets would help me!
:
: <%@ Language = "VBScript" %>
: <!-- #include file="data/adovbs.inc" -->
: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0
: Transitional//EN" "http://www.w3.org/TR/REC-
: html40/loose.dtd">
: <html>
: <head>
: <%
: dim strHeaderText
: dim strHeaderText2
: strHeaderText = "Help Desk"
: strHeaderText2 = "Enter a Help Desk Ticket"
:
: dim strCurrentPage, lPos, lLastSlash, iPosition
:
: '** This removes everything in front of the last /
: character
: strCurrentPage = Request.ServerVariables("SCRIPT_NAME")
: lPos = 1
: while lPos < len(strCurrentPage)
: if mid(strCurrentPage,lPos,1) = "/" then
: lLastSlash = lPos + 1
: end if
: lPos = lPos + 1
: wend
: strCurrentPage = mid(strCurrentPage,lLastSlash)
:
:
:
:
:
: %>
: <!--#include file="Nav-Header.asp" -->
: <!---------------------------------------------------------
: -----------------------------------------------------------
: ----------------------------->
:
:
: <%
: ' *** Begin DB Setup ***
: Dim strConnString
:
: ' Override with our site-wide CONN string.
: strConnString = "Provider=SQLOLEDB;Data Source=WWW2;" _
: & "Initial Catalog=XXXX;User
: Id=XXXX;Password=XXXX;" _
: & "Connect Timeout=15;Network Library=dbmssocn;"
:
: Dim cnnFormToDB ' CONN object
: Dim strSQL ' String in which to build our SQL
: command
: Dim lngRecsAffected ' # of records affected... just
: informational
:
: ' Vars for the fields read in from the form.
: Dim strID
: Dim strLongDescription
: Dim strEntryDate
: Dim strUserPriority
: Dim strNumber
: Dim strEmail
: Dim strUserName
: Dim strErrorMsg ' Holds error message if we catch
: any problems.
:
: 'Email Variables
: Dim objCDO ' Our CDO object
: Dim strTo ' Strings to hold our email fields
: Dim strBcc
: Dim strFrom
: Dim strSubject
: Dim strBody
:
: ' See if we have any info to process.
: If Request.Form("action") <> "Save Form Data" and strTo
: = "" Then
:
: ' Show the form
: %>
: <p class="body">
: Please enter a description of your problem, the
: priority level you feel it merits, and a contact number.
: &nbsp;&nbsp;That's it!<BR>Someone from the Help
: Desk will call you back as soon as they are available.
: </p>
: <form action="<%= Request.ServerVariables
: ("SCRIPT_NAME") %>" method="post" name="ticket">
: <input type="hidden" name="action" value="Save
: Form Data">
: <input type="hidden" maxlength="9"
: name="entry_date" size="9" value="<%= FormatDateTime(NOW) %
: >">
: <input type="hidden" maxlength="100"
: name="user_name" size="35" value="<%Response.Write(Session
: ("user_name"))%>">
: <table>
: <tr>
: <td align="right"><strong>User
: Name:</strong></td>
: <td class="body" nowrap><%Response.Write
: (Session("user_name"))%></td>
: </tr>
:
: <tr>
: <td align="right"><strong>Date &
: Time:</strong></td>
: <td class="body" nowrap><%= FormatDateTime
: (NOW) %></td>
: </tr>
: <tr>
: <td align="right"><strong>Problem
: Description:</strong></td>
: <td class="body" nowrap><textarea
: cols="55" maxlength="500" rows="3" name="long_description"
: id="long_description"></textarea></td>
: </tr>
: <tr>
: <td align="right"><strong>User
: Priority:</strong></td>
: <td class="body" nowrap>
: <select name="user_priority"
: id="user_priority">
: <option value="1">High</option>
: <option value="2">Medium</option>
: <option value="3" SELECTED>Low</option>
: </select>&nbsp;<A class="nav"
: HREF="javascript:" onClick="window.open
: ('priority.asp','','height=500,width=500,location=no,menuba
: r=no,resizable=no,scrollbars=yes,status=no,toolbar=no,direc
: tories=no')" >CHOOSING A PRIORITY FOR YOUR CASE</A>
: </td>
: </tr>
: <tr>
: <td align="right"><strong>Contact Phone
: Number:</strong></td>
: <td class="body" nowrap><input type="text"
: maxlength="12" name="number" size="35"></td>
: </tr>
: <tr>
: <td
: align="right"><strong>Email:</strong></td>
: <td class="body" nowrap><input type="text"
: maxlength="150" name="email" size="35"></td>
: </tr>
: <tr>
: <td>&nbsp;</td>
: <td>
: <input type="reset" value="Clear">
: <input type="submit" value="Save">
: </td>
: </tr>
: </table>
: </form>
:
: <%
: Else
:
: ' Do our DB insert!
: strLongDescription = Request.Form
: ("long_description")
: strEntryDate = Request.Form("entry_date")
: strUserPriority = Request.Form("user_priority")
: strUserName = Request.Form("user_name")
: strEmail = Request.Form("email")
: strNumber = Request.Form("number")
:
: strLongDescription = Replace
: (strLongDescription, "'", "''")
: strEntryDate = Replace(strEntryDate, "'", "''")
: strUserPriority = Replace
: (strUserPriority, "'", "''")
: strUserName = Replace(strUserName, "'", "''")
: strEmail = Replace(strEmail, "'", "''")
: strNumber = Replace(strNumber, "'", "''")
:
: ' Open connection to the DB
: Set cnnFormToDB = Server.CreateObject
: ("ADODB.Connection")
: cnnFormToDB.Open strConnString
:
: ' Build our SQL String
: strSQL = ""
: strSQL = strSQL & "INSERT INTO
: problem_reports "
: strSQL = strSQL & "(long_description,
: entry_date, user_priority, user_name, email, number) " &
: vbCrLf
: strSQL = strSQL & "VALUES ("
: strSQL = strSQL & "'" & strLongDescription
: & "'"
: strSQL = strSQL & ", "
: strSQL = strSQL & "'" & strEntryDate & "'"
: strSQL = strSQL & ", "
: strSQL = strSQL & "'" & strUserPriority
: & "'"
: strSQL = strSQL & ", "
: strSQL = strSQL & "'" & strUserName & "'"
: strSQL = strSQL & ", "
: strSQL = strSQL & "'" & strEmail & "'"
: strSQL = strSQL & ", "
: strSQL = strSQL & "'" & strNumber & "'"
: strSQL = strSQL & ");"
:
: 'Email Form to the User and Help Desk on what they have
: entered
: strTo = Request.Form("email")
: strFrom = "Help Desk <[email protected]>"
: strBcc = "(e-mail address removed)"
: strSubject = "New Help Desk Ticket was Created"
: strBody = "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0
: Transitional//EN"">" & vbCrLf _
: & "<html>" & vbCrLf _
: & "<head>" & vbCrLf _
: & "<title>Your Help Desk Ticket Was
: Posted</title>" & vbCrLf _
: & "<meta http-equiv=Content-Type
: content=""text/html; charset=iso-8859-1"">" & vbCrLf _
: & "</head>" & vbCrLf _
: & "<body bgcolor=""#b8d1e0"">" & vbCrLf _
: & "<h2>Thanks for submitting your
: information!</h2>" & vbCrLf _
: & "<p>" & vbCrLf _
: & "<B>Entry Date:</B> " & strEntryDate
: & "<BR>" & vbCrLf _
: & "<B>User:</B> " & strUserName & "<BR>" &
: vbCrLf _
: & "<B>Contact Number:</B> " & strNumber
: & "<BR>" & vbCrLf _
: & "<B>Problem Description:</B> " &
: strLongDescription & "<BR>" & vbCrLf _
: & "</p>" & vbCrLf _
: & "<font size=""-1"">" & vbCrLf _
: & "<p>Please address all concerns to
: (e-mail address removed).</p>" & vbCrLf _
: & "<p>This message was sent to: " & strTo
: & "</p>" & vbCrLf _
: & "<p>This message was sent to: " & strBcc
: & "</p>" & vbCrLf _
: & "</font>" & vbCrLf _
: & "</body>" & vbCrLf _
: & "</html>" & vbCrLf
:
: Set objCDO = Server.CreateObject("CDO.Message")
: With objCDO
: .To = strTo
: .From = strFrom
: .Bcc = strBcc
: .Subject = strSubject
: .HtmlBody = strBody
: .Send
: End With
: Set objCDO = Nothing
:
: ' Execute the SQL command. I pass it a
: variable lngRecsAffected
: ' in which to return the number of records
: affected. I also tell
: ' it that this is a text command and it
: won't be returing any
: ' records... this helps it execute the
: script faster!
: ' And before you ask... I don't know, but
: YES IT IS OR!!!
: cnnFormToDB.Execute strSQL,
: lngRecsAffected, adCmdText Or adExecuteNoRecords
:
: ' Dispose of the CONN object
: cnnFormToDB.Close
: Set cnnFormToDB = Nothing
:
: ' Display a verification message and we're
: done!
: %>
: <!---------------------------------------------------------
: -----------------------------------------------------------
: ------------------------------------->
:
:
: <P class="body">
: <!---------------------------------------------------------
: -----------------------------------------------------------
: ------------------------------------->
: <% Response.Write "<h2>Your trouble ticket
: # was opened as of " & strEntryDate & "</h2>" & vbCrLf %>
:
: <% Response.Write "<B>User:</B> " &
: strUserName & "<BR>" & vbCrLf %>
: <% Response.Write "<B>Priority:</B> " &
: strUserPriority & "<BR>" & vbCrLf %>
: <% Response.Write "<B>Problem
: Description:</B> " & strLongDescription & "<BR>" & vbCrLf %
: >
: <% Response.Write "<B>Contact
: Number:</B> " & strNumber & "<BR>" & vbCrLf %>
: </P>
: <p>
: <strong>Number of records
: affected:</strong> <%= lngRecsAffected %>
: </p>
: <UL>
: </UL>
: <!---------------------------------------------------------
: -----------------------------------------------------------
: ------------------------------------->
:
: <%
: End If
: 'End If
: %>
: <!--#include file="Nav-Footer.asp" -->
 
D

dlbjr

Faith,
If using Access on a small web database,

Try his:

strSQL = "SELECT * FROM table WHERE ID=0";"

rs.AddNew
rs(1) = "dsfsdfd"
rs(2) = "dsd"
rs.UpDate
intID = rs(0)

-dlbjr

invariable unerring alien
 
T

Tom B

Faith, don't do that.
Do it the way you are doing it, with Ken's addition of Select @@Identity.
Take a look at... http://www.aspfaq.com/2174


May I also suggest you not post 500 lines of a page and refer to it as a
snippet?
 
D

dlbjr

As you will notice,
The page Ken refers to said the same thing about Access as I did. so what
was the point in saying don't do that.

If you are using SQL Server then use


SCOPE_IDENTITY()
 
M

Mats

Faith said:
Okay I am trying to get the id of the row that this form
inputs into the table. The ID auto increments. Is this
possible? Help or even code snippets would help me!
Your code is v e r y long so I haven't read it :)
But depending on what you want to do you might not have to get the ID
at all.
I had a similar problem and got the following answer from Bob Barrows,
and it works fine for me. (So far only in a test situation)
This is if your just inserting new records to a table with autonumber
Mats

the code:

Well, here is how I would do it (after correcting the permissions
problem,
of course):
I would create a saved query in Access using these steps:

1. Open the database in Access
2. Go to the Queries tab and click the New button
3. With Design View selected, click OK
4. Close the Show Table dialog
5. Switch to SQL View (use the toobar button or the View menu)
6. Enter this SQL, correcting the column names, of course (leave the
[Px]
names alone: these will be parameters):
INSERT Customer ([N],[F],[O],[P],[T],[F],[O],[e],[L],[L],[A],[Ma],[Me])
VALUES ([P1], [P2], [P3], [P4], [P5], [P6], [P7], [P8], [P9], [P10],
[P11],
[P12], [P13])
7. Test the query. You should be prompted for 13 values, which will be
inserted into the table if this is done correctly.
8. Save the query (toolbar button, or File|Save, or just close it,
saying
yes when prompted to save it), giving it a name that fits in with your
naming conventions. I will use qInsNewCust.

In asp, do this:
Dim p1, ... , p13
'assign the values to the 13 variables (validating them in the
'process), then
Dim cn
Set cn=Server.CreateObject("ADODB.Connection")
cn.Open strConnect
cn.qInsNewCust p1, ..., p13
cn.close
set cn=nothing

HTH,
Bob Barrows
 
M

Mats

Faith said:
Okay I am trying to get the id of the row that this form
inputs into the table. The ID auto increments. Is this
possible? Help or even code snippets would help me!
An addition to my earlier posting. I'm just coding an application in
this way so I rushed ahead and jumped over this part.
If you really need the ID, to use in more than one table or so, it is
quite simple. After you've inserted using the method presented (with
P1, P2....) you still have theese variables at hand. Take one or two
of them so that you get a uniqe value and use in a recordset. E.g. you
could select email.(Cust_no auto increments and P8 is the variable
assigned for email from your form):
SELECT [Cust_no], FROM Customers where"_
& "[email] = '"&P8&"'"
Voila! there is your auto incrementing ID
In essence, you enter data in the table first, and check for the value
of the ID later, and as your aim is to put data in the table that
should be OK.

This could also be used to see if some data (like names, email) is
already in the table, and then perhaps skip entering data to the
table, thus avoiding a customer getting two Customer numbers
Mats
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,818
Messages
2,569,727
Members
45,661
Latest member
NadineBour

Latest Threads

Top