mysql - VB net 2008 和 MySQL 上的连接字符串错误

Question

我尝试使用 Visual Studio .Net 2008 从 MySql 获取一些数据，在这个特定的连接字符串上，它给出了输入字符串格式不正确的错误。

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

在 PHPmyadmin 上，语法有效

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

结果是:

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

Answer 1

您应该始终将您的连接字符串与 www.connectionstrings.com 上找到的连接字符串进行比较。在这种特殊情况下，您有一些拼写错误:

• UserId 需要是 Uid
• 密码需要Pwd
• 转换零日期时间 需要为ConvertZeroDatetime