VB.net WHERE Statement for MySQL

Question

大家晚上好。我遇到了一个让我有些困惑的问题。我正在从Access转换到MySQL，看起来MySQL查询的一些语法不同。我所做的是一个操作员从"客户端"表中的记录中选择一个"组合框"形式的客户端。我很顺利地完成了。当用户选择一个客户端时，另一个组合框将填充表"Lines"中的数据。表"Lines"有两列Client和Line。我试图得到每一行WHERE Client=LEXAR。有什么想法吗？

我想应该是这样的：

"SELECT * FROM Lines WHERE Client=LEXAR"
or
"SELECT * FROM Lines WHERE(client = @LEXAR)"
or
"SELECT * FROM Lines WHERE(client = '@LEXAR')"
or
"SELECT * FROM Lines WHERE client LIKE LEXAR)"
or

"SELECT * FROM ``Lines``WHERE ``Client``='LEXAR')"
or
"SELECT * FROM Lines WHERE Client ='LEXAR')"

但都不起作用。这是我的代码：

Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM Lines WHERE(client = @LEXAR)", con)
        Dim dt As DataTable = New DataTable()
        Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter(sql)
        DataAdapter1.Fill(dt)

        If dt.Rows.Count > 0 Then
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "Line"
        End If
        con.Close()

这些是错误：

Fatal error encountered during command execution.

和

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Lines WHERE client =  LEXAR)' at line 1

完整代码：

Imports MySql.Data.MySqlClient
Imports System.Net
Imports System.IO
Dim con As MySqlConnection = New MySqlConnection("Data Source=xxx.xxx.xxx.xxx;Database=productiontrackerpro;User ID=xxxxxxxx;Password=xxxxxxxx;")

Public Sub LoadClients()
        Dim sql As MySqlCommand = New MySqlCommand("SELECT * FROM Lines WHERE Client = 'LEXAR')", con)
        Dim dt As DataTable = New DataTable()
        Dim DataAdapter1 As MySqlDataAdapter = New MySqlDataAdapter(sql)
        DataAdapter1.Fill(dt)

        If dt.Rows.Count > 0 Then
            ComboBox1.DataSource = dt
            ComboBox1.DisplayMember = "Line"
        End If
        con.Close()


    End Sub

错误：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Lines WHERE Client = 'LEXAR')' at line 1

Answer 1

您的第一种方法几乎是正确的。只需将术语放在单引号中：

"SELECT * FROM `Lines` WHERE `Client` = 'LEXAR';"

如果您想使用参数（WHERE somefield=@value），请查看http://dev.mysql.com/doc/refman/5.0/en/connector-net-programming-prepared.html