get value of autonumber-field

J

Jan Hendrickx

Hi all,

I use (something like) following code to add new records to a database,
but it doesn't work!!!

I need to know the value of an AutoNumber-field when I add the record.

Why doesn't it work and how can I make it work???
I think it should be easy, but I can't figure it out!

Thanks in advance...
Jan H.

<%option explicit
dim cn, rs, v1, v2, v3
set cn=server.createobject("ADODB.Connection")
set rs=server.createobject("ADODB.Recordset")
cn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
server.mappath("./mdb/MyMDB.mdb")
rs.open "SELECT * FROM tblTest",cn,3,3
rs.addnew
rs("Naam")="Some new value"
v1=rs("ID")
rs.update
v2=rs("ID")
rs.moveprevious 'this works,
rs.movenext 'but only when
v3=rs("ID") 'not first record!!!!
rs.close
cn.close
set rs=nothing
set cn=nothing%>
<html>
</head>
<body>
*** <%=v1%> *** <%=v2%> *** <%=v3%> ***
</body>
</html>
 
B

Bob Barrows [MVP]

Jan said:
Hi all,

I use (something like) following code to add new records to a
database, but it doesn't work!!!

I need to know the value of an AutoNumber-field when I add the record.

Why doesn't it work and how can I make it work???
I think it should be easy, but I can't figure it out!

Thanks in advance...
Jan H.
Click to expand...
http://www.aspfaq.com/show.asp?id=2174
 
B

Bullschmidt

Perhaps change this:

rs.open "SELECT * FROM tblTest",cn,3,3

To be more like this instead:

rs.open "SELECT * FROM tblTest",cn,1,3

And here is a memo I previously wrote to myself about the issue:

Use an autonumber in a table whenever can't get a unique record from
just one field. This is needed in subquery on "many" pages and will
make things easier if just have to check one field for the cur rec etc.

And's some sample code I use WHEN FIRST OPENING the recordset (notice
the CursorType) so that later I can easily get the new Autonumber:

' Open rs.
Set objRS = Server.CreateObject("ADODB.Recordset")
' (1=CursorType of adOpenKeyset in case ever want to get an autonumber
of new rec,
' 3=LockType of adLockOptimistic because updating.)
objRS.Open strSQL, objConn, 1, 3

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...

<<
Hi all,

I use (something like) following code to add new records to a database,
but it doesn't work!!!

I need to know the value of an AutoNumber-field when I add the record.

Why doesn't it work and how can I make it work???
I think it should be easy, but I can't figure it out!

Thanks in advance...
Jan H.

<%option explicit
dim cn, rs, v1, v2, v3
set cn=server.createobject("ADODB.Connection")
set rs=server.createobject("ADODB.Recordset")
cn.open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &
server.mappath("./mdb/MyMDB.mdb")
rs.open "SELECT * FROM tblTest",cn,3,3
rs.addnew
rs("Naam")="Some new value"
v1=rs("ID")
rs.update
v2=rs("ID")
rs.moveprevious 'this works,
rs.movenext 'but only when
v3=rs("ID") 'not first record!!!!
rs.close
cn.close
set rs=nothing
set cn=nothing%>
<html>
</head>
<body>
*** <%=v1%> *** <%=v2%> *** <%=v3%> ***
 

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

Similar Threads

DataAdd Error: Arguments are wrong type or are out of acceptable r 1
'80040e10 Error: No value given for one or more required parameters 16
Problem with an .asp page displaying data based on a form field value. 2
How to handle Access stored query with parameter in ASP page 1
Strange Error 1
using Excel as a DB 4
parent.window problem with select list 0
Exporting to Excel (xlsx files) 5

Members online

Total: 20 (members: 1, guests: 19)
Robots: 299

Forum statistics

Threads
473,755
Messages
2,569,535
Members
45,007
Latest member
obedient dusk

Latest Threads

Top