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
I solved by modify the interrogation syntax to:
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 beUid
Password
needs toPwd
Convert Zero Datetime
needs to beConvertZeroDatetime