CODEWITHSUNDEEP
sql-servervb.netstored-procedures
user995689
user995689

Reputation: 825

Return result set from SQL Server stored procedure to vb.net

The following stored procedure works as I want in the Visual Studio designer. The result is a table containing all the race distances for the input @CourseName

ALTER PROCEDURE [dbo].[getCourseDistancesProc]
    @CourseName nvarchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT DISTINCT
        RaceDistances.RaceDistance
    FROM  
        RacingMaster
    JOIN
        RaceDistances ON RacingMaster.Dist_Of_Race_FK = RaceDistances.PKRaceDistancesId
    JOIN
        Courses ON RacingMaster.RM_Course_FK = Courses.PKCourseId
    WHERE
        CourseName = @CourseName
END

I want to call the stored procedure from a vb.net application. What data type do I declare as the output variable so that the full result set is returned to the calling app?

Upvotes: 0

Views: 5269

Answers (1)

user995689
user995689

Reputation: 825

There was obviously more work to be done than I had realized, but just in case anyone else stumbles across this question the solution I finally adapted from elsewhere is:-

Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim results As String
Dim ConnectionString As String

' Create the connection string.
ConnectionString = "Data Source=*********;" & _
                    "Initial Catalog=*******;" & _
                    "Integrated Security=SSPI;"
myConn = New SqlConnection(ConnectionString)

myConn.Open()
Dim InputName As String
InputName = TextBox1.Text

myCmd = New SqlCommand()
myCmd.CommandText = "getCourseDistancesProc"
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.AddWithValue("@CourseName", Odbc.OdbcType.NVarChar).Value = InputName
myCmd.Connection = myConn
Dim myReader As SqlDataReader = myCmd.ExecuteReader()

If myReader.HasRows Then

    Do While myReader.Read()
        Dim var As String
        var = myReader.GetString(0)
        MsgBox(var)
    Loop
Else
        MsgBox("No rows found.")
End If

myReader.Close()

Obviously, the above is just to demonstrate that the requested data is indeed coming back from the database. But now I know that it is I can handle it in a more useful way.

Upvotes: 1

Related Questions