help with sqlclient and select max() as, connection not open

Posted 21 September 2012 - 02:27 AM

Hi guys

I have been tasked to rewrite an application I wrote in VBA to VB. I am literally learning as I go! In VBA I used the ADODB com for connection to a database, but this doesn't seem to work very well in VB. I have changed to the sqlClient com thats in VB 2010 and for some reason I am having issues with counting records in a database. The code I have is:

This always throws the Exception error? I have tried replacing the variable with a constant in the query to no avail. I was originally having issues with my queries as ADOBD recommends you close out the query with a ;, however this throws the exception in VB

Can someone point me in the right direction? Is reader the way to do this or should I be reading about something else?

I have removed the connection string instead of replacing the valuesw with blanks, these values are correct as I can connect with them elsewhere

Re: help with sqlclient and select max() as, connection not open

Posted 21 September 2012 - 05:56 AM

Ok, so the problem is with the variable PolicyNumber. If I recall correctly (and I may not, my memory is awful), DBNull means that there is no value in the database for PolicyNumber, so the cast exception you are getting comes because it is trying to convert a value of nothing (as in, an empty value, not a value of 0) into a number.

Try adding a bit of code in your Catch statement telling it if that error crops up, to give the variable a value of 0. That will probably help.

Re: help with sqlclient and select max() as, connection not open

Posted 21 September 2012 - 06:29 AM

And I would like to add to Bort's suggestion. Do you feel that you should have received a result from your query? If so then you need to check the criteria you're passing in, and make sure that it is correct. Also, when you get this working, check out Command.ExecuteScalar.