6 Replies - 1116 Views - Last Post: 22 January 2016 - 10:14 AM Rate Topic: -----

#1 cmptreasy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-December 14

'DBNull' to type 'String' is not valid

Posted 22 January 2016 - 09:27 AM

I have a web form that I am trying to assign a number to. This number is in the format of a static latter 'A', then a '-' then the submitters initials, which is in the results of an SQL query (inspinit), another '-', and finally an incrementing number beginning at '1'. I thought I had the process, however in the executescalar line I am getting the error Conversion from type 'DBNull' to type 'String' is not valid. I thought I had the trap for this error in the code, but evidently not. Can anyone assist?
Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim conn As New SqlConnection("Data Source=servername;Initial Catalog=databasename;User ID=user;Password=password")
        Dim cmd As New SqlCommand
        Dim reader As SqlDataReader
        Dim maxrptnum As String
        cmd.CommandText = "select max(increment) from forma where rptnum like '[email protected]%'"
        cmd.Parameters.Add("@inspinit", SqlDbType.Char).Value = inspinit.Text
        cmd.Connection = conn
        conn.Open()
        maxrptnum = cmd.ExecuteScalar()
        If maxrptnum Is DBNull.Value Then
            maxrptnum = "A" + "-" + Me.inspinit.Text + "-1"
        End If
        Me.rptnum.Text = ("A" + "-" + Me.inspinit.Text + "-" + CType((maxrptnum + 1), String))
        saverpt()
    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: 'DBNull' to type 'String' is not valid

#2 andrewsw   User is offline

  • head thrashing
  • member icon

Reputation: 6645
  • View blog
  • Posts: 27,201
  • Joined: 12-December 12

Re: 'DBNull' to type 'String' is not valid

Posted 22 January 2016 - 09:32 AM

Please do not use a meaningless title, "Stuck noobie". I have changed it.

What line causes the error?

There is a debugging tutorial linked in my signature below. Take this and you will be able to step through your code and discover what is happening.
Was This Post Helpful? 0
  • +
  • -

#3 cmptreasy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-December 14

Re: 'DBNull' to type 'String' is not valid

Posted 22 January 2016 - 09:40 AM

Thanks - The line causing the error is the executescalar() command. I see this by placing a breakpoint, but I am not sure how to resolve it. As I originally stated, I am rather new at this, so any help would be appreciated.
Was This Post Helpful? 0
  • +
  • -

#4 TechnoBear   User is offline

  • Lady A
  • member icon

Reputation: 276
  • View blog
  • Posts: 1,089
  • Joined: 02-November 11

Re: 'DBNull' to type 'String' is not valid

Posted 22 January 2016 - 09:45 AM

What is the return type for ExecuteScalar()? I haven't got a current DB project I can access.

If it helps, the error is being thrown before your trap for it, it looks like ExecuteScalar() is returning DBNull object and not a string. You could try changing:

maxrptnum = cmd.ExecuteScalar()


To:

maxrptnum = cmd.ExecuteScalar().ToString()


but if I remember with DBNull that might not help. Essentially, your return from ExecuteScalar() is not a string format and so can't be put in a string variable.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • head thrashing
  • member icon

Reputation: 6645
  • View blog
  • Posts: 27,201
  • Joined: 12-December 12

Re: 'DBNull' to type 'String' is not valid

Posted 22 January 2016 - 09:50 AM

        Dim maxrptnum As String

        maxrptnum = cmd.ExecuteScalar()

        If maxrptnum Is DBNull.Value Then

In the meantime, I'll guess that it might be that last line that causes the error, when it attempts to compare the string maxrptnum.

I think DBNull should be checked using If Convert.IsDBNull(maxrptnum) Then, although its a confusing area as there are several ways to do this.

Convert.IsDBNull :msdn

However, you are returning a max() value, which should be a number, not a string.
ExecuteScalar returns an Object, or Nothing if the result set is empty. I would consider something like:

Dim result As Object
Dim maxrptnum As Integer 'or Double, etc.

result = cmd.ExecuteScalar()
If result IsNot Nothing Then
    maxrptnum = DirectCast(result, Integer)

Was This Post Helpful? 0
  • +
  • -

#6 cmptreasy   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 18-December 14

Re: 'DBNull' to type 'String' is not valid

Posted 22 January 2016 - 10:09 AM

Thanks for the suggestion. I implemented your code, and it is now giving an invalid cast exception on the line
'maxrptnum = DirectCast(result, Integer)'. The database data type for the increment field is numeric, and currently there is nothing in the database, so this field will return null or nothing values.

I implemented your change, and now I am getting an invalid case exception on the line 'maxrptnum = DirectCast(result, Integer)'. The database data type for the increment field is numeric, so again, not sure..
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw   User is offline

  • head thrashing
  • member icon

Reputation: 6645
  • View blog
  • Posts: 27,201
  • Joined: 12-December 12

Re: 'DBNull' to type 'String' is not valid

Posted 22 January 2016 - 10:14 AM

So did you explore and investigate the information and links I provided? This is a two-way street.

Before converting to an Integer you could introduce the check If Convert.IsDBNull(result) Then.

However,

Quote

and currently there is nothing in the database,

If there is nothing at all in the database then ExecuteScalar should return Nothing, which my code already checks for.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1