vb.net function error conversion from string to type double

Question

I have this vb.net function:

Function CheckBillingRun(customer, type, month, year)
        Dim conn = New MySqlConnection()
        Dim myCommand As New MySqlCommand
        Dim reader As MySqlDataReader
        Dim SQL As String
        Dim result As String

        conn.ConnectionString = "server=" + global_variables.web_server_ip + "; user id=" + global_variables.web_server_username + "; password=" + global_variables.web_server_password + "; database=" + global_variables.web_server_database + "; "

        conn.Open()
        SQL = "SELECT COUNT(sequence) from billing_runs WHERE customer = '" + customer + "' AND type = '" + type + "' AND MONTH(datetime) = '" + month + "' AND YEAR(datetime) = '" + year + "' "
        myCommand.Connection = conn
        myCommand.CommandText = SQL
        reader = myCommand.ExecuteReader
        reader.Read()
        result = reader.GetString(0)
        conn.Close()

        Return result

    End Function

I am trying to call it in my application using this code:

If CheckBillingRun(reader.GetString(0), "Voice Billing", DateTime.Now.ToString("MM"), DateTime.Now.ToString("yyyy") > 0) Then
                Continue While
            End If

reader.getstring(0) equals 278

but i am getting an error saying:

Additional information: Conversion from string "SELECT COUNT(sequence) from bill" to type 'Double' is not valid.

Steve · Accepted Answer · 2015-03-07 21:38:52Z

3

The string concatenation operators in VB.NET are & or +, but if you use the + operator and you have Option Strict Off set for your project you could have surprises like this. The compiler knows that one or more of the your parameters passed to this function are not strings but number, and in this context the + operator tries to convert everything to a number. Try to use the & operator to concatenate strings.

Said that, DO NOT CONCATENATE strings to build sql commands.
Use a parameterized query to avoid Sql Injection and other parsing problems.

For example, your code could be something like this

Function CheckBillingRun(ByVal customer as String , ByVale type as String, _
                         ByVal month as Integer, ByVal year as Integer) as Integer
    Dim SQL = "SELECT COUNT(sequence) from billing_runs " & _ 
              "WHERE customer = @customer AND type = @type " & _ 
              "AND MONTH(datetime) = @month AND YEAR(datetime) = @year"
    Using conn = New MySqlConnection()
    Using myCommand As New MySqlCommand(SQL, conn)
        Dim result As Integer = 0
        conn.ConnectionString = "......."
        conn.Open()
        myCommand.Parameters.Add("@customer", MySqlDbType.VarChar).Value = customer
        myCommand.Parameters.Add("@type", MySqlDbType.VarChar).Value = type
        myCommand.Parameters.Add("@month", MySqlDbType.Int).Value = month
        myCommand.Parameters.Add("@year", MySqlDbType.Int).Value = year
        Using reader = myCommand.ExecuteReader
            if reader.Read() Then
                result = reader.GetInteger(0)
            End If
        End Using
        Return result
    End Using
    End Using
End Function

call it with

CheckBillingRun(reader.GetString(0), "Voice Billing", _ 
                DateTime.Now.Month, DateTime.Now.Year)

In this version every variable used has its type specified to avoid any possible unwanted automatic conversion by the compiler. Notice also that COUNT returns a number, not a string. Treating numbers like they were strings is a bad habit to dismiss as soon as possible. Look at your project properties an try what happen if you set the Option Strict to ON

edited Mar 7, 2015 at 21:38

answered Mar 7, 2015 at 21:24

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charles Over a year ago

So I should turn on option strict and change all + to be & ?

Steve Over a year ago

That's a good practice. Of course if you are porting an old VB6 program you could introduce these changes in small steps but definitively, yes OPTION STRICT ON will avoid nasty bugs.

Taher A. Ghaleb · Accepted Answer · 2015-03-07 21:17:14Z

0

If the first parameter of your method CheckBillingRun accepts double, you should use the method Convert.ToDouble() to convert the value from a string to double, like this:

instead if

If CheckBillingRun(reader.GetString(0), .....

do the following:

If CheckBillingRun(Convert.ToDouble(reader.GetString(0)), .....

All the best.

answered Mar 7, 2015 at 21:17

Taher A. Ghaleb

5,3005 gold badges33 silver badges47 bronze badges

1 Comment

Charles Over a year ago

i just tried using this however i still get the same error message

Collectives™ on Stack Overflow

vb.net function error conversion from string to type double

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related