7 Replies - 2535 Views - Last Post: 23 September 2012 - 06:52 PM Rate Topic: -----

#1 herghost  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 21-September 12

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:

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

Is This A Good Question/Topic? 0
  • +

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

#2 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 408
  • Joined: 04-January 11

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

Posted 21 September 2012 - 02:40 AM

What was the exact error you received? Can you attach a screenshot?
Was This Post Helpful? 0
  • +
  • -

#3 herghost  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 21-September 12

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

Posted 21 September 2012 - 02:47 AM

View PostNovember-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.
Was This Post Helpful? 0
  • +
  • -

#4 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 445
  • View blog
  • Posts: 3,084
  • Joined: 18-September 06

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

Posted 21 September 2012 - 03:11 AM

Try changing "Can not open connection!" to ex.ToString. That should give us a messagebox with the actual error in it.
Was This Post Helpful? 0
  • +
  • -

#5 herghost  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 21-September 12

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

Posted 21 September 2012 - 05:25 AM

View PostBort, 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
Was This Post Helpful? 0
  • +
  • -

#6 Bort  Icon User is offline

  • Ill-informed Mongoloid
  • member icon

Reputation: 445
  • View blog
  • Posts: 3,084
  • Joined: 18-September 06

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.
Was This Post Helpful? 1
  • +
  • -

#7 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,200
  • Joined: 25-September 09

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.

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

Was This Post Helpful? 1
  • +
  • -

#8 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 408
  • Joined: 04-January 11

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

Posted 23 September 2012 - 06:52 PM

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

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1