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.

Answer 1

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

Answer 2

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.