Category Archives: SQL Server

vb.net connect to sql server database, build a command and open a data reader

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.

 

Next go to visual studio and create a console project.

Here is the code for the console project:

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:

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

Try adding this just before the while loop.

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:

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.