skip to Main Content

I try to get some data from MySql using Visual Studio .Net 2008 and on this particular connection string it gives me error that input string was not in a correct format.

Imports MySql.Data.MySqlClient

Public Class Form1
    Dim Scaderea As String = "SELECT `No`,`Laps`,`Lap_Tm`, ((SELECT `Lap_Tm` from `raw` where `No` = 7 AND (`Lap_Tm`<>"""") AND `Laps`=1) - `Lap_Tm`) AS 'Diferente' FROM `raw` WHERE `No`=7 AND (`Lap_Tm`<>"""") AND Laps!=1 AND Laps>1"

    Public Const cs As String = "server=************;User Id=**********;PASSWORD=**********;port=3306;Persist Security Info=True;database=circuit;Convert Zero Datetime=True;Allow Zero Datetime=True"

    Public rdr As MySqlDataReader = Nothing
    Public con As MySqlConnection = Nothing
    Public cmd As MySqlCommand = Nothing

    Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DataGridView3.Rows.Clear()
        DataGridView3.SuspendLayout()
        'MsgBox(Scaderea)
        Try
            con = New MySqlConnection(cs)
            con.Open()
            cmd = New MySqlCommand(Scaderea, con)
            rdr = cmd.ExecuteReader()

            While (rdr.Read())
                MsgBox(rdr.Item(0))
                Dim Nr_Conc = rdr("No").ToString
                Dim Tur = rdr("Laps").ToString
                Dim Timp = rdr("Lap_Tm").ToString
                Dim Diferenta = rdr("Diferente").ToString
                DataGridView3.Rows.Add(Nr_Conc, Tur, Timp, Diferenta)
            End While
            con.Close()
        Catch ex As Exception
            If con.State = ConnectionState.Open Then con.Close()
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            con.Close()
            con.Dispose()
        End Try
        DataGridView3.ResumeLayout()
    End Sub
End Class

On PHPmyadmin the syntax is working

SELECT `No`, `Laps`, `Lap_Tm`, (( SELECT `Lap_Tm` FROM `raw` WHERE `No` = 7 AND ( `Lap_Tm` <> "" ) AND `Laps` = 1 ) - `Lap_Tm` 
) AS 'Diferente' 
FROM
    raw 
WHERE
    `No` = 7 
    AND ( `Lap_Tm` <> "" ) 
    AND Laps != 1 
    AND Laps >1

and the result is:

"7" "2" "00:02:46.853"  "5.129"
"7" "3" "00:02:51.203"  "0.779"
"7" "4" "00:02:45.291"  "6.691"

2

Answers


  1. Chosen as BEST ANSWER

    I solved by modify the interrogation syntax to:

    Dim Scaderea As String = "SELECT No, Laps, TIME_FORMAT(Lap_Tm, ""%H:%i:%S.%f"") AS 'timp1', TIME_FORMAT(SEC_TO_TIME(ABS(( SELECT Lap_Tm FROM raw WHERE NO = 7 AND ( Lap_Tm <> '' ) AND Laps = 1 ) - Lap_Tm )) , ""%H:%i:%S.%f"") AS 'timp2' FROM raw WHERE No = 7 AND ( Lap_Tm <> '' ) AND Laps > 1"
    

  2. You should always compare your connection string with the one found on http://www.connectionstrings.com. In this particular case you have a few incorrect spellings:

    • UserId needs to be Uid
    • Password needs to Pwd
    • Convert Zero Datetime needs to be ConvertZeroDatetime
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search