Me again :D

I've been trying to get the current id of the record to display and I am having on hell of a time doing it. I guess my newbiness shows.
Anyways this is the code i am using.

<%@ LANGUAGE="VBSCRIPT" %>
<%
Set Rs = Server.CreateObject("ADODB.Recordset")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open("ticketbackup")
sql = "INSERT INTO ticket (PSAP, Badge, Contact, Trunk, Traced_Tel, Reason, Ticket_type) VALUES ('"& request.form("PSAP")&"','"& request.form("Badge")&"','"& request.form("Contact")&"','"& request.form("Trunk")&"','"& request.form("Traced_Tel")&"','"& request.form("reason")&"', 'OPEN')"
conn.execute(sql)
set Rs = Conn.execute( "SELECT @@IDENTITY")
NewID = Rs(0)
' Now display the record id.
response.write "The new ID was " & Rs("NewID")


'response.write (sql)
and the error i get is:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/Smc/ticket/traceprocessclose.asp, line 11


ANyone know how to modify this???? so I can get the current ID record displayed.

Thank you in advance

Hi there,

When you use SELECT @@IDENTITY, the che column does not have a name by default. So either change your SQL Statement to this:

set Rs = Conn.execute( "SELECT @@IDENTITY AS NewID")

or retrieve its value like this:

Response.Write("The new ID was " & Rs.Fields(0).Value)

In fact, you already did that when storing the value in the NewID variable. So, I guess all you need to change is this:

set Rs = Conn.Execute("SELECT @@IDENTITY")
NewID = Rs(0)
' Now display the record id.
Response.Write("The new ID was " & NewID)

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hey Imar,

Thank you kindly for y our prompt responnse. your suggestion took care of the problem but returned value of 0 ????
Any idea?
I dont have a primary key in db. only Ticket_Type is set to autonumber.
am I forgetting something?

What database are you using? What happens if you run the INSERT statement in your database program (use Response.Write to dump it to the screen first) and then select @@IDENTITY?

it may be useful to make sure this works in your database before you try to get this to work in ASP.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

You might try adding a field into you database for an Id then try this.

cn = your connection string

set rs = server.createobject("adodb.recordset")
sql = "select * from ticket"
rs.open sql, cn, 3, 3
rs.addnew
rs("PSAP") = request("PSAP")
rs("Badge") = request("Badge")
rs("Contact") = request("Contact")
rs("Trunk") = request("Truck")
rs("Traced_Tel") = request("Traced_Tel")
rs("Reason") = request("Reason")
rs("Ticket_Type") = "Open"
rs.update
Id = rs("Id")
rs.close
set rs = nothing

If it bugs out, try removing the last two lines (rs.close and set rs = nothing).

INSERT INTO ticket (PSAP, Badge, Contact, Trunk, Traced_Tel, Reason, Ticket_type, Comments) VALUES ('AMBULANCE','sdfsad','sadf','asdf','df','Address Verification',
'OPEN','asdfasfasdfassdafsadfsdfsd asdf sadf asd fasd f asdf asd fasd f asd fs adf sdaf sad f sdaf sad f sadf sda')The new ID was 0

is the output from that INSERT statement.

<%@ LANGUAGE="VBSCRIPT" %>
<%
Set Rs = Server.CreateObject("ADODB.Recordset")
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open("ticketbackup")
sql = "INSERT INTO ticket (PSAP, Badge, Contact, Trunk, Traced_Tel, Reason, Ticket_type, Comments) VALUES ('"& request.form("PSAP")&"','"& request.form("Badge")&"','"& request.form("Contact")&"','"& request.form("Trunk")&"','"& request.form("Traced_Tel")&"','"& request.form("reason")&"', 'OPEN','"& request.form("Comments")&"')"
conn.execute(sql)
set Rs = Conn.execute( "SELECT @@IDENTITY")
NewID = Rs(0)
response.write (sql)
' Now display the record id.
response.write ("The new ID was " & NewID)


it still returns 0.

NOt sure what i am doing wrong. once again I AM a noob

This is really weird. I tried this in SQL Server and in Access, and in both cases I get the correct Identity. I also tried removing the Primary Key from the table, but that didn't change anything (Makes sense, as the AutoNumber or Identity determines the Identity, not the fact that the column is a PK, but I wanted to check anyway).

What database are you using? And what version? How does your connectionstring look like? Maybe the driver doesn't support Identity.... (just guessing, as I am pretty stumped here).

If you can't get it to work, try the method that DaveGerard suggested. According to
http://www.adopenstatic.com/Experime...Autonumber.asp it should be just as fast as using @@IDENTITY....

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I am using Access 97(SR-1), the entire script I am using is on here in my previous post. I am STUUUCKK grrr.....

Access driver 4.00.4403.02 any other info ???

Ah, right. That explains a lot. You'll need a Microsoft Access 2000 or later database for the @@identity to work.

According to the KB article: "This feature works with Jet 4.0 databases but not with older formats." I think Access 97 is considered older than 4.0 format, because I downgraded my test database to 97 and then it returned 0 as well.

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Hey Imar,

I confirmed that as well, (@@IDENTITY). Since it is only 5 users using this database i decide to go with SELECT MAX. THis is what I used:

<%@ LANGUAGE="VBSCRIPT" %>
<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.open("ticketbackup")
sql = "INSERT INTO ticket (PSAP, Badge, Contact, Trunk, Traced_Tel, Reason, Ticket_type, Comments) VALUES ('"& request.form("PSAP")&"','"& request.form("Badge")&"','"& request.form("Contact")&"','"& request.form("Trunk")&"','"& request.form("Traced_Tel")&"','"& request.form("reason")&"', 'OPEN','"& request.form("Comments")&"')"
conn.execute(sql)
sql = "SELECT MAX(Ticket_ID) FROM ticket"
set Rs = Conn.execute(sql)
Ticket_ID = Rs.fields.item(0).value

' Now display the record id.
response.write ("The new ID was " & Ticket_ID)



'response.write (sql)


Once again thank you for all the help.