如何查询此信息？ [英] How can query this information?

问题描述

我在我的vb项目中使用mysql database查询我的代码中出现此错误。我有这个错误查询中的语法错误这是我的代码。任何帮助都会很棒！提前感谢。

I have this error in my code for query in my vb project with mysql database..i have this error "syntax error in query" this is my code. Any help would be great!thanks in advance.

Dim con As New MySqlConnection("Data Source=localhost;Database=coa;User=root;Password=12345")
       Dim da As New MySqlDataAdapter
       Dim dt As New DataTable
       Dim sql As String

       sql = "Select PhoneNumber, FirstName, LastName, Course, Year From students"
       sql = sql & " Where Course like '" & cbc.Text & "'"
       sql = sql & " Where Year like '" & cby.Text & "'"

       With cmd
           .CommandText = sql
           .Connection = con
       End With

       With da
           .SelectCommand = cmd
           .Fill(dt)
       End With

       'for clearing list view
       myview1.Items.Clear()

       If dt.Rows.Count = 0 Then
           MessageBox.Show("No Record Found.", "Check Again", MessageBoxButtons.OK, MessageBoxIcon.Information)
           Exit Sub
       End If
       For i = 0 To dt.Rows.Count - 1
           With myview1
               .Items.Add(dt.Rows(i)("PhoneNumber"))
               With .Items(.Items.Count - 1).SubItems
                   .Add(dt.Rows(i)("FirstName"))
                   .Add(dt.Rows(i)("LastName"))
                   .Add(dt.Rows(i)("Course"))
                   .Add(dt.Rows(i)("Year"))
               End With
           End With
       Next

   End Sub

推荐答案

0）第二个其中应该是和

1） 不要使用串联在语句中插入值。使用参数化查询！

0) The second where should be an and
1) Do not use concatenation to insert the values in your statement. Use a parameterized query!

将BreakPoint置于CommandText = sql行并启动调试模式以检索此变量的值（SQL）。使用此文本，您可以检索T-SQL文本并编译SQL上的指令并检查错误。



尝试使用QuickWatch，AdWatch或CommandWindow



错误如下：你在两次写下whereclausule：

sql = sql&Where Year like'& cby.Text&'你需要将第二个where clausule替换为和clausle



对源代码构建查询不是最好的实践因为当这个SQL被发送到数据库时，指令在执行之前一直被编译。



其他最好的实践是使用StringBuilder而不是连接为hardCode ，有了这些，你会有更多的表现和更优雅的代码。



最好的问候。



Leonardo Meter

Put a BreakPoint on line "CommandText = sql" and start the debug mode to retrieving the value of the this variable (SQL). With this text you can retrieving the T-SQL text and compile the instruction on your SQL and check the errors.

try this with the QuickWatch, AdWatch or CommandWindow

The error is on following bellow you write the "where" clausule two times:
"sql = sql & " Where Year like '" & cby.Text & "'" you need to replace the second "where clausule" to "and clausle"

Build queries on the source code isn't a best pratice because when this SQL are sended to the database, the instructions are compiled before the execution all the time.

Other best pratice is to use the StringBuilder instead concatenate as a hardCode "", with these you have a little bit of more performance and a more elegant code.

Best Regards.

Leonardo Metre

这篇关于如何查询此信息？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！