So, you want to use vb.net to connect to a sql server database, open a connection, execute a sql server stored procedure  using a parameter and loop through the rows?

It’s not that bad.

I created this sample demo in a short period of time.  In this example I use the connection builder to create the connection string. I’ve never been good at doing connection strings so this is a way that always works.

Ok, where to start? First open sql server using management studio, create a hello world stored procedure.  Create a parameter to accept some text and output a ‘hello world’ message.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create PROCEDURE helloworld
	@welcomeMessage nvarchar(100)
AS
BEGIN

	SET NOCOUNT ON;


	SELECT 'hello world! ' + @welcomeMessage as message

END

GO

Next go to visual studio and create a console project.

Here is the code for the console project:

Module Module1

    Sub Main()

        Dim Builder As New System.Data.SqlClient.SqlConnectionStringBuilder()

        Builder.DataSource = "your.server.com"
        Builder.InitialCatalog = "databaseName"
        Builder.Password = "complex_password"
        Builder.UserID = "username"
        Dim conn As System.Data.SqlClient.SqlConnection _
            = New System.Data.SqlClient.SqlConnection(Builder.ConnectionString)

        conn.Open()
        Console.WriteLine("connection string = {0}", Builder.ConnectionString)
        Console.WriteLine("connection state = {0}", conn.State.ToString)

        Dim cmd As System.Data.SqlClient.SqlCommand = New SqlClient.SqlCommand("helloworld", conn)
        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@welcomeMessage", System.Data.SqlDbType.NVarChar, 50)).Value = " have a nice day!"

        cmd.CommandType = CommandType.StoredProcedure

        'if you forget to set the CommandType you get this error
        'An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
        'Additional information: Procedure or function 'ProcedureName' expects parameter '@parameterName', which was not supplied.

        Dim rdr As System.Data.SqlClient.SqlDataReader

        rdr = cmd.ExecuteReader
        While rdr.Read

            Console.WriteLine(rdr("message"))

        End While

        conn.Close()

        Console.WriteLine("connection state = {0}", conn.State.ToString)
        Console.WriteLine("done!")

        rdr.Close()


    End Sub

End Module

Some things to watch out for, first is getting the connection string correct. As mentioned before, I prefer to use the connection string builder to help make that work.

Common errors include failing to open the connection object. If you fail to do that you get an error like this:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dllAdditional information: ExecuteReader requires an open and available Connection. The connection's current state is closed.

What about if you want to test if the reader got any rows?

Try adding this just before the while loop.

If Not rdr.HasRows Then

Console.WriteLine("Oops, the reader has no rows!")

End If

I’m pretty sure the reader will close automatically, if you want to be sure you can explicitly close it after your loop.

Other errors you might find could be caused if you do not spell the name of the parameter correct. If you make that mistake you would get this error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dllAdditional information: Procedure or function 'helloworld' expects parameter '@welcomeMessage', which was not supplied.

One thing is interesting to look at, is how the sql parameter is created, added to the command object and how the value is set. All that in one line!

Ok so try to run it. Compile and run the test. You should see evidence that the connection opens and the command executes.