Getting a return value from a sql command

[K B]

Hi, I'm using the following and the insert portion works fine but I'm
not getting the return value (TicketID) which is the identify column of
the table. I can't figure out what I'm missing; all examples I see use
stored procs but I need to use a string command for this.

Dim sSQL As String = "INSERT INTO eTicket(Priority, Category)
VALUES(@iPriority, @iCategory)"
Dim cmd As DbCommand = db.GetSqlStringCommand(sSQL)
db.AddInParameter(cmd, "@iPriority", SqlDbType.Int, iPriority)
db.AddInParameter(cmd, "@iCategory", SqlDbType.Int, iCategory)
db.AddOutParameter(cmd, "@TicketID", SqlDbType.Int, 4)
db.ExecuteNonQuery(cmd)
*******INSERT COMPLETES SUCCESSFULLY******

Dim iTicketID As Integer = db.GetParameterValue(cmd, "TicketID")

Am I doing something wrong in the sql command string?

Thanks,
KB

 

[V]

Hi KB,

As far as I know, the Insert Statement does not return the Identity
Column as a value, and that is why your code will not work.

- Vaibhav

 

[bruce barker \(sqlwork.com\)]

try:

Dim sSQL As String = "INSERT INTO eTicket(Priority, Category)
VALUES(@iPriority, @iCategory)
select scope_identity() as TicketID"

Dim cmd As DbCommand = db.GetSqlStringCommand(sSQL)
db.AddInParameter(cmd, "@iPriority", SqlDbType.Int, iPriority)
db.AddInParameter(cmd, "@iCategory", SqlDbType.Int, iCategory)

Dim iTicketID As Integer = db.ExecuteScaler(cmd) 'needs a cast but i do not
remember vb syntax

-- bruce (sqlwork.com)