Insert Max autonumber field to a table

[yacanna]

I have two tables: CONTACTS -- ContactID is autonumber type; now I try to get the Max(ContactID) and insert into VENDORS table. (Below it is the SQL code)

Can you see anything wrong? especially SQL_query

I got syntax error.. Thank you very much!!!

Also, I designed a relational database, I seem remember a artile saying it is NOT good idea to use autonumber field???

================================================
<%@ Language=VBScript%>
<%
On Error Resume Next

.. Create objConn

SQL_contactQuery = "select MAX(ContactID) as maxContact from CONTACTS"
Set RS_contact = MyConn.Execute(SQL_contactQuery)

SQL_query = "insert into VENDORS(VendorName, ContactID) values('" & Query_bizName & "," & RS_contact('maxContact') & ")"
objConn.Execute(SQL_query)

%>
========================================================

 

[DotNetGnat]

firstly, you cannot write into the database when you have your recordset open...

secondly, if you want to get the maximum id which you meant to be the id for recently added record then you need to use

SELECT @@IDENTITY as maxcontact

because your maxid in the table may not be the id of the recently added record...

-DNG

 

[DotNetGnat]

do something like this:

<%
Set RS = Server.CreateObject("ADODB.RecordSet")
' Open the table
RS.Open "table_name", connection_name, adOpenKeySet, adLockPessimistic, adCmdTable
' Add a new record
RS.AddNew
RS("field_name") = request.form("field_name")

' Update the record
RS.update
' Retrive the ID
unique_ID=RS("ID")
' Close the RecordSet
RS.Close
Set RS = Nothing
%>

-DNG

 

[yacanna]

Thank you very much DNG!!

 

[DotNetGnat]

no problem...glad to be of help...

-DNG