I am using vb6 to control a sqlServer database and need a little help.

I use a form to gather information about part inspections. The form has general information about the inspection (date/time, inspector, location, etc...) and the more specific information about the individual line items (part number, pass/fail, failure mode, etc...).

The general inspection info is in tblInspection.
The more specific info is in tblInspection_Detail.

When I enter a new record into tblInspection - How do I know the Record_ID (the indexing primary key) for the record I just added?

I need to retain the new Record_ID value so that I can use it to identify what inspection each line item is associated with.

I'm sure this is a pretty easy one, but I am at a loss right now.

Many thanks,

Alex

If you table has an identity field then the field gets populated when you add a new record. Once you add the new record just check the field value as the pointer moves to that new record. Here is an example:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset

Set cn = new ADODB.Connection

cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DataBaseName;Data Source=ServerName"

cn.open

set rs = new ADODB.recordset

rs.actiiveconnection = cn

rs.open "SELECT * FROM TABLE"

rs. AddNew 'Adds New Record and moves pointer to the new record.

MsgBox rs.fields("Record_ID").value

rs.close

cn.close

set rs=nothing
set cn = nothing

That didn't really work...here's why:

I'm INSERTing the data from the form into the table with a stored procedure (w/ an INSERT sql statement).

<<<<< CODE Snippet >>>>>>>
Set cmdInfo = New ADODB.Command
With cmdInfo
Set .ActiveConnection = g_cnDB
.CommandText = "usp_Insert_Audit"
.CommandType = adCmdStoredProc
End With

Set m_rsAudit = cmdInfo.Execute(, Array(cboAuditor.ItemData(cboAuditor.ListIndex), _
cboMfgLine.ItemData(cboMfgLine.ListIndex), _
cboPartNum.Text, _
txtSerial))
<<<< End CODE Snippet >>>>>

I use Execute instead of Open, so I can't use the .AddNew method.

I went down this path to minimize the traffic running between the application and the server (it's a remote application).

Anybody know how to find out what Audit_ID I just created with the Execute command?

Thanks,

Alexander