need help getting return value from SQL stored procedure... 1

[fowlerlfc]

First of all, I'm new to VB.net, so please excuse my ignorance.

I'm trying to get a return value from the following stored procedure:

CREATE PROCEDURE cc_add 
(
@username varchar(30),
@orderdate datetime
)

AS

insert into orders (username, orderdate) values (@username, @orderdate)

declare @orderid int

select @orderid = @@identity

return  @orderid
GO

and here's my vb code:

Dim myda As New SqlDataAdapter

        Dim myds As New DataSet
        

        myda.SelectCommand = New SqlCommand

        With myda.SelectCommand
            .Connection = mysqlconn
            .CommandType = CommandType.StoredProcedure
            .CommandText = "cc_add"
            .Parameters.Clear()
            .Parameters.Add("@username", SqlDbType.VarChar, 35, "Item").Value = Session("username")
            .Parameters.Add("@orderdate", SqlDbType.DateTime, 10, "Item").Value = mydate
            .Parameters.Add("@orderid", SqlDbType.BigInt, 8, "Item").Direction = ParameterDirection.ReturnValue




        End With
        

        myda.Fill(myds, "testtable")

        Dim drtt As DataRow
        For Each drtt In myds.Tables("testtable").Rows
            Response.Write("orderid:" & drtt("orderid") & "<br>")


        Next

When I run this I get a bad object reference. I believe that's because nothing is getting returned from SQL?

Thanks in advance for your help!

 

[RiverGuy]

There shouldn't be any DataRows. Its not a Select statement. So you just want to do an .ExecuteNonQuery(), and then check the value of the @orderid parameter afterwards.

 

[jshurst]

I would try executescalar. It will return a single value. For example

Dim orderid As Integer
                comSQL.Connection.Open()
                orderid = (comSQL.ExecuteScalar())

Where comSQL is your SQL command.

 

[fowlerlfc]

jshurst,

your code was what i needed. after executing it, i found that my stored procedure wasn't returning anything, so i edited it, and now everything is working like a charm!

Thanks for your help!