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

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

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

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.

This article has been dead for over six months. Start a new discussion instead.