Click here to Skip to main content
15,896,557 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.


VB
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
Posted
Updated 16-Aug-14 19:17pm
v2

0) The second where should be an and
1) Do not use concatenation to insert the values in your statement. Use a parameterized query!
 
Share this answer
 
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
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900