I have 2 tables, TableA and TableB. IMAT_PRIORITY_ID
is primary key in TableA.
How can I get the value of IMAT_PRIORITY_ID in a textbox after inserting into TableA?
strSQL1 = "INSERT TableA (IMAT_PRIORITY_ID,JOB_NO,BATCH_NO) VALUES (SQ_PRIOTITY_ID.nextval," & JOB_NO.Text & "','" & BATCH_NO.Text & "')"
With adoCommand
.ActiveConnection = adoconn
.CommandType = adCmdText
.Co开发者_C百科mmandText = strSQL1
.Prepared = True
.Execute , , adCmdText + adExecuteNoRecords
End With
strSQL1 = "INSERT TableB (ISBN_SERIAL_NO,IMAT_PRIORITY_ID,ISBN) VALUES (ISBN_SERIAL_NO.NEXTVAL,'" & IMAT_PRIORITY_ID.Text & "','" & ISBN.Text & "')"
With adoCommand
.ActiveConnection = adoconn
.CommandType = adCmdText
.CommandText = strSQL1
.Prepared = True
.Execute , , adCmdText + adExecuteNoRecords
End With
To get the last identity (PRIMARY KEY) generated from the insert statement you can query it as follows..
strSQL1 = "INSERT TableA (IMAT_PRIORITY_ID,JOB_NO,BATCH_NO) VALUES (SQ_PRIOTITY_ID,JOB_NO,BATCH_NO) VALUES (SQ_PRIOTITY_ID.nextval," & JOB_NO.Text & "','" & BATCH_NO.Text & "');SELECT SCOPE_IDENTITY()"
You can find more details over here
精彩评论