Stored Procedure Help Needed.

[Bern]

Good afternoon all,

I'm pretty new to VB.Net. Trying to get my head round using stored procedures to manipulate data in a database at the moment. I have managed to use a stored procedure (via my windows form) to insert data into a database but I am struggling to retreive data from my database.

I have a simple stored procedure as follows:

ALTER PROCEDURE dbo.RBTestSP1
(@Number1 Int OUTPUT, @Number2 Int OUTPUT, @Date DateTime OUTPUT)
AS
SELECT top 1 Number1, Number2, Date from RBTest

order by Date desc

I then have then have the following code in my button_click event handler, the aim is to display the data recrieved in 3 textboxes. I don't get any error messages but I don't get the data either:

Dim myConnection As New SqlConnection(connection string goes here)
Dim myCommand As New SqlCommand("Name of stored procedure", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
        Try
            myConnection.Open()

            Dim number1Param As New SqlParameter("@Number1", SqlDbType.Int, 4)
            number1Param.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(number1Param)

            Dim number2Param As New SqlParameter("@Number2", SqlDbType.Int, 4)
            number2Param.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(number2Param)

            Dim dateParam As New SqlParameter("@Date", SqlDbType.DateTime, 8)
            dateParam.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(dateParam)

            Dim reader As SqlDataReader = myCommand.ExecuteReader()

            number1TextBox.Text = CStr(number1Param.Value)
            number2TextBox.Text = CStr(number2Param.Value)
            dateTextBox.Text = CStr(dateParam.Value)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            myConnection.Close()
        End Try

Any help would be greatly appreciated.

Regards,

Bern

[Ranx]

You'll need to select your results INTO the output parameters. Your current sp will return a dataSET, not individual results.

ALTER PROCEDURE dbo.RBTestSP1
(@Number1 Int OUTPUT, @Number2 Int OUTPUT, @Date DateTime OUTPUT)
AS
SELECT top 1 Number1 into @number1, Number2 into @number2, Date into @Date from RBTest

order by Date desc

I think. It's been a while since I've done any SQL Server

[Bern]

Many thanks for your reply but this amendment to the stored procedure results in a syntax error. I will keep looking to see if I can find out whats wrong.

Regards,

Bern

[Bern]

I have managed to get this to work with the following amendment to my stored procedure:

ALTER PROCEDURE dbo.RBTestSP1
(@Number1 Int OUTPUT, @Number2 Int OUTPUT, @Date DateTime OUTPUT)
AS
SELECT top 1 @Number1 = Number1, @Number2 = Number2, @Date = Date from RBTest

order by Date desc

Regards,

Bern.