Shouldn't this get the current record number? 2

[rtshort]

Shouldn't the following statement return the current ClaimNo in the Cookie?

<%
Set cn = Server.CreateObject(&quot;ADODB.Connection&quot
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
cn.ConnectionString = &quot;Driver=SQL Server;uid=sa;pwd=;Server=TMSN_Server;database=Test&quot;
cn.Open
sql = &quot;Select * from VesselOwnerDB, VesselInfoDB where OnrAddress = '&quot; & Request.Form(&quot;txtVloAddress&quot & &quot;'&quot;
rs.Open sql, cn, 3, 3
If rs.BOF = True and rs.EOF = True Then
rs.AddNew
With rs
.Fields(&quot;UserID&quot.Value = Request.Cookies(&quot;UserID&quot
.Fields(&quot;CltRefNo&quot.Value = Request.Form(&quot;txtRefNo&quot
.Fields(&quot;OnrPolicyNo&quot.Value = Request.Form(&quot;txtPolicyNo&quot
.Fields(&quot;OnrLastName&quot.Value = Request.Form(&quot;txtVloLName&quot
.Fields(&quot;OnrFirstName&quot.Value = Request.Form(&quot;txtVloFName&quot
.Fields(&quot;OnrAddress&quot.Value = Request.Form(&quot;txtVloAddress&quot
.Fields(&quot;OnrCity&quot.Value = Request.Form(&quot;txtVloCity&quot
.Fields(&quot;OnrStateProv&quot.Value = Request.Form(&quot;txtVloStProv&quot
.Fields(&quot;OnrPostalCode&quot.Value = Request.Form(&quot;txtVloPostalCode&quot
.Fields(&quot;OnrCountry&quot.Value = Request.Form(&quot;txtVloCountry&quot
.Fields(&quot;OnrPhone1&quot.Value = Request.Form(&quot;txtVloPhone1&quot
.Fields(&quot;OnrPhone2&quot.Value = Request.Form(&quot;txtVloPhone2&quot
.Fields(&quot;OnrCell&quot.Value = Request.Form(&quot;txtVloCell&quot
.Fields(&quot;OnrFax&quot.Value = Request.Form(&quot;txtVloFax&quot
.Fields(&quot;OnrEMail&quot.Value = Request.Form(&quot;txtVloEMail&quot
.Update
Response.Cookies(&quot;ClaimNo&quot = rs.Fields(&quot;ClaimNo&quot.Value
End With

Response.Redirect &quot;VesselInfo.asp&quot;
Else
rs.MoveFirst
Response.Write (&quot;Already in Database!&quot
End If
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
%>

I need to do this to tie the Databases together. In the VesselOwnerDB and the VesselInfoDB the ClaimNo is the Primary Key and they are related in the relationship table. Do I need to go another way or what? Rob
Just my $.02.

 

[CrazedWombat]

I have never had any luck using that method regardless of the Locks and Keys I use in the Open method. Instead, I use the SQL @@Identity query to get the ID of the last updated record. I find that is more reliable that doing the SELECT MAX on the table.

It does require making another query but it's a really fast one

Here's some code that might help:

strSQL = "SELECT @@IDENTITY from TableName;"
oRs.Open strSQL,,2,3
   newRecord = oRs(0)
oRs.Close

 

[rtshort]

As long as it is used in the same session, should it be OK that there is a relationship in the 2 tables? I want to avoid getting the ClaimNo(PrimaryKey) crossed up in the VesselInfoDB and the VesselOwnerDB.

I guess what I'm trying to say is will using both table names in the query ensure that the ClaimNo will not get crossed up even if more than one person is using it at the same time?

Thanks for the info. Rob
Just my $.02.

 

[Funka]

you can always qualify your fieldnames by prefixing the tablename if you are concerned about ambiguity, i.e., rs.Fields(&quot;VesselOwnerDB.ClaimNo&quot.Value...

i am a little confused what you mean by &quot;crossing up the ClaimNo (PrimaryKey)&quot; in your two tables. if these same fieldnames are primary keys in both tables, this is a one-to-one relationship then? one of which would be your Identity/auto-number so i'm confused how they relate/get updated. (you would not expect both to be Identity fields as that would get out of sync.)

if you are inserting information into two seperate tables, where one table contains a foreign key which must relate to the first table, you will need to do these inserts separately so that you can insert the primary key from the first table into the foreign key of the second. for this i would recommend a stored procedure, which will kill all three of your birds with one stone.

perhaps if you posted your table definitions and how they relate to one another i might be able to provide more insight on this and a recommendation for creating your stored proc, but for now i'm not too sure what your intention is.

good luck!

 

[rtshort]

You are correct in saying that the Primary Key of the first table (VesselOwnerDB.ClaimNo) and the second table Foreign Key is (VesselInfoDB.ClaimNo). I guess there is no way they will add information to the wrong ClaimNo as long as everything remains in the same Session?

Rob
Just my $.02.