C# WPF mysql 字符串

Question

我正在创建一个连接到本地主机数据库的 wpf 应用程序，它有 2 个表，现在我遇到了错误，但我不确定代码中出了什么问题。有人可以帮忙吗？

我收到此错误:

An unhandled exception of type 'MySql.Data.MySqlClient.MySqlException' occurred in MySql.Data.dll

Additional information: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'left join author on book.author_id=author.id' at line 1

private void Filter_TextChanged(object sender, TextChangedEventArgs e)
    {
        connection.Open();
        MySqlCommand cmd = connection.CreateCommand();  

        cmd.CommandText = "SELECT * FROM book where book.name like ('" + Filter.Text + "%') left join author on book.author_id=author.id";   

        cmd.ExecuteNonQuery();
        DataTable dt = new DataTable();
        MySqlDataAdapter da = new MySqlDataAdapter(cmd);
        da.Fill(dt);
        _dataView = new System.Data.DataView(dt);
        _dataView.Sort = "name ASC,id ASC";
        BooksGrid.DataContext = dt;
        connection.Close();
    }

Answer 1

将您的查询更改为

cmd.CommandText = "SELECT * FROM book left join author on book.author_id=author.id where book.name like ('" + Filter.Text + "%') and book.author_id=author.id"; 

末尾附加的“book.author_id=author.id”子句是为了确保您只获取与author_id匹配的记录。

此外，您应该尝试使用 cmd.ExecuteReader()，而不是 cmd.ExecuteNonQuery()，因为您正在检索行。