Help Using Stored Procedures.

[R J Bern]

Good morning 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

[jmcilhinney]

Your stored procedure doesn't assign a value to any of the parameters so of course they have no value after it's executed. You shouldn't be using output parameters for that anyway. You're creating a DataReader by calling ExecuteReader. You should be calling its Read method and then getting the field values from the DataReader. Follow the Data Access link in my signature for some ADO.NET example code.

[R J Bern]

Many thanks for your reply.

I have managed to get it to work by making an amendment to my stored procedure.

I will also have a read of the thread you wrote and take on board what you said with regards to using the read method to obtain the data.

As I say I am very new to the game so any hints / tips are greatly appreciated.

Regards,

Bern.

[szlamany]

As JM said - you should not have parameters to return "data" from a table. You should simply have a SELECT statement in the SPROC and then use a DATAREADER to get the resultset back into VB.

It makes sense to pass "parameters" to a SPROC that will do an INSERT - that type of SPROC is an action SPROC.

But data retrieval SPROCS should normally return recordsets of data.

SQL is a set-based language - SPROC's are not supposed to be extensions of VB functions in the database.