[herghost]

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:

sql = "SELECT MAX(fundDays) AS totals FROM WOFT_tbl_funds WHERE polNumber =  '" & PolNumberField1.Text & "' AND fundReqMeth = 'Options' "

        cnn = New SqlConnection(ConnString)

        Try
            cnn.Open()
            cmd = New SqlCommand(sql, cnn)
            reader = cmd.ExecuteReader()
            While reader.Read()
                optiondays = CInt(CStr(reader.Item(0)))
            End While
            reader.Close()
            cmd.Dispose()
            cnn.Close()
            MsgBox(optiondays)
        Catch ex As Exception
            MsgBox("Can not open connection!")
        End Try

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

Thanks

Dave

[November-06]

What was the exact error you received? Can you attach a screenshot?

[herghost]

November-06, on 21 September 2012 - 02:40 AM, said:

What was the exact error you received? Can you attach a screenshot?

Thanks for the reply, the line

 MsgBox("Can not open connection!") 

is executed.

[Bort]

Try changing "Can not open connection!" to ex.ToString. That should give us a messagebox with the actual error in it.

[herghost]

Bort, on 21 September 2012 - 03:11 AM, said:

Try changing "Can not open connection!" to ex.ToString. That should give us a messagebox with the actual error in it.

Thanks Bort, didnt know that!

I have changed the code from reader to

[code]
Try
cnn.Open()

cmd = New SqlCommand(sql, cnn)
If IsDBNull(cmd) Then
optiondays = 0
Exit Try
End If
cmd.CommandType = CommandType.Text
optiondays = CInt(cmd.ExecuteScalar)
MsgBox(optiondays)
cnn.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
[code]

System.InvalidCaseException: Conversion from type 'DBNull' to type 'Integer' is not valid.


If I take out the PolicyNumber variable then it works as expected. The variable is being set as needed, and it exists in the db

[Bort]

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.

[CharlieMay]

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.

This post has been edited by CharlieMay: 21 September 2012 - 06:31 AM

[November-06]

You cannot convert null to integer so you should use IsDBNull function to validate before converting.

you should change this line...

optiondays = CInt(CStr(reader.Item(0)))  

to

optiondays = IIf(IsDBNull(reader.Item(0)),0,CInt(reader.Item(0)))

This post has been edited by November-06: 23 September 2012 - 06:53 PM