Insert New Row and Retuning IDENTITY Value

[rbyrne1]

I am attempting to write code that should perform the following:

- insert a new data row into the ORDERS table
- obtain the new IDENTITY field value (the field name is actually "ordernum" but it is an identity field type) from the ORDERS table
- insert data row(s) into the ORDERSDET table with the corresponding IDENTITY field value
- do all of the above in a nice, neat transaction

I am using VB.2005, PSQL 9.5, and an ODBC connection. My code (at least the first two steps, step two doesn't work) is included below. The insert works fine but I do not know how to include step 2 in the transaction.

Sub Scan_TICKHISH_For_New_Tickets()
Dim fromdate As Long
Dim ticketnum As Long
Dim transdate As Long
Dim newticket As Boolean
Dim linecount As Integer = 1
Dim transcmd As OdbcCommand
Dim transspcmd As OdbcCommand
Dim SOCmd As OdbcCommand
Dim ordernum As Long

Dim SOTransConn As Odbc.OdbcConnection

Try
fromdate = Year(DateAdd(DateInterval.Month, -1, Now)) * 10000 + Month(DateAdd(DateInterval.Month, -1, Now)) * 100 + 1

ASCconn = New OdbcConnection(ASCConnStr)
ASCconn.ConnectionTimeout = 30
ASCconn.Open()

SOconn = New OdbcConnection(SOConnStr)
SOconn.ConnectionTimeout = 30
SOconn.Open()

'SCAN FOR *POSTED* TICKETS THAT HAVE 999 IN SLSP3
'cmdstring = "select ticketnum, transdateyyyymmdd, tickhish.customernum, locationnum,salesperson1,shiptofirstname,shiptolastname,shiptoaddrline1,shiptoaddrline2,shiptocity,shiptostate,shipto1st5zip,firstname,lastname from tickhish,customer where customer.customernum=tickhish.customernum and transdateyyyymmdd>" & fromdate & " and salesperson3=999 and postedflag='Y'"
cmdstring = "select ticketnum, transdateyyyymmdd from tickhish where transdateyyyymmdd>=" & fromdate & " and salesperson3=999 and postedflag='Y'"

Dim ASCCmd As New OdbcCommand(cmdstring, ASCconn)
ASCreader = ASCCmd.ExecuteReader()
'Process The Result Set
While (ASCreader.Read())
'CHECK TO SEE IF THIS TICKET ALREADY EXISTS IN THE SPECIAL ORDERS TABLE
ticketnum = ASCreader("ticketnum")
transdate = ASCreader("transdateyyyymmdd")

cmdstring = "select ticketnum from orders where ticketnum=" & ticketnum & " and transdateyyyymmdd=" & transdate
SOCmd = New OdbcCommand(cmdstring, SOconn)
SOreader = SOCmd.ExecuteReader()

newticket = True

While SOreader.Read
newticket = False
End While
SOreader.Close()

If newticket Then
'open the connection
SOTransConn = New OdbcConnection(SOConnStr)
SOTransConn.ConnectionTimeout = 30
SOTransConn.Open()

' Make the transaction.
Dim SOtrans As Odbc.OdbcTransaction = SOTransConn.BeginTransaction(IsolationLevel.ReadCommitted)

'Make a Command for this connection
' and this transaction.
cmdstring = "INSERT INTO orders (ordernum,transdateyyyymmdd, ticketnum) values ("
cmdstring += "0,"
cmdstring += ASCreader("transdateyyyymmdd") & ","
cmdstring += ticketnum & ");"

transcmd = New OdbcCommand(cmdstring, SOTransConn, SOtrans)
transcmd.ExecuteNonQuery()

cmdstring = "select ordernum from orders where ticketnum=" & ticketnum & " and transdateyyyymmdd=" & transdate
SOCmd = New OdbcCommand(cmdstring, SOconn)
SOreader = SOCmd.ExecuteReader()

While SOreader.Read
ordernum = SOreader("ordernum")
End While
SOreader.Close()
MsgBox(ordernum)

SOtrans.Commit()

SOTransConn.Close()
SOTransConn.Dispose()

End If
End While
ASCreader.Close()

SOconn.Close()
ASCconn.Close()
SOconn.Dispose()
ASCconn.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
Finally

End Try

End Sub

 

[macleod1021]

In order to get the ID field, create another query right after the insert (before closing the connection) and call
Select @@IDENTITY
that will return the identity created in the previous insert statement for that particular connection.

im in ur stakz, overflowin ur heapz!

 

[AlexCuse]

@@IDENTITY has some problems, especially if you put a trigger on this table later (it would return the identity value inserted by the trigger). I would use SCOPE_IDENTITY()

For a complete listing of ways to get the just-added identity value, and a more thorough explanation of the ins and outs, have a look here:

http://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
The IT Community of the 21st Century

 

[RiverGuy]

Well, what database are you using? @@IDENTITY/SCOPE_IDENTITY() is for SQL Server. What is PSQL 9.5 ? Is this Oracle? Does Oracle have IDENTITY fields?

 

[AlexCuse]

Whoops, I missed that. I'm assuming psql is PostgreSQL. If that's the case, there is a function called currval

SELECT currval('MyIdentityColumn')

or something like that.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page
The IT Community of the 21st Century

 

[macleod1021]

Thanks for catching my error Alex...I always forget about that

im in ur stakz, overflowin ur heapz!

 

[rbyrne1]

Sorry guys, but I've been out of the country for a while. PSQL is my acronym for Pervasive SQL, version 9.5. Anyway, I tried to set up the transaction but the select command doesn't fire until the transaction is committed, then it's too late for me to include the insertion of the detail records in the transaction

 

[RonRepp]

RByrne::

Read these 2 posts which deal with exactly what you are looking for:

thread796-1455460 and thread222-1434291

I hope this helps.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[rbyrne1]

Macleod:
I would do this but I do not know how to set it up in code. I've seen examples of a stored procedure, but I don't know how to code it in VB

Ron:
Thanks for the links, but the NOCOUNT statement does not work in Pervasive SQL.


Man, why did they have to use Pervasive??? I've seen so many examples of how to do this in MS-SQL, but they do not translate into Pervasive.