You have an error in your SQL syntax, Visual Basic on visual studio with MySQL [closed]

Question

I'm new to coding. I'm trying to create a login form with MySQL 8 in visual studio 2019 Please can anyone help me find out what is wrong with my code. When I run the code it executes MsgBox("Dublicate Entry...Contact Costumer Service at 1010) and It gives the following error.

'MySql.Data.MySqlClient.MySqlException: '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 'Where Title='CEO' and Name='Demeth' and Pin Code='1234'' at line 1'

I have doubt on ; at the end of employee.empde in the MySQL query but when I remove that then MsgBox("Login Failed!", MsgBoxStyle.Critical) gets executed.

Here is my full code. Thank you.

Imports MySql.Data.MySqlClient

Public Class Form1

    Public cmd As MySqlCommand

    Private Sub btnSign_Click(sender As Object, e As EventArgs) Handles btnSign.Click

        Dim conn As MySqlConnection
        conn = New MySqlConnection
        conn.ConnectionString = "server=localhost;user id=demeth;Password=*****;database=employeeinfo"

        Dim rdr As MySqlDataReader
        Dim qry As String

        Try
            conn.Open()

            qry = "SELECT * FROM employeeinfo.empde; Where Title='" & cmbTitle.Text & "' and Name='" & txtName.Text & "' and Pin Code='" & txtPin.Text & "'"
            cmd = New MySqlCommand(qry, conn)
            rdr = cmd.ExecuteReader

            Dim count As Integer
            count = 0

            While rdr.Read
                count += 1
            End While

            If count = 1 Then
                MsgBox("Connected Successfully", MsgBoxStyle.Information, "Login Successful")
            ElseIf count > 1 Then
                MsgBox("Dublicate Entry...Contact Costumer Service at 1010", MsgBoxStyle.Critical)
            Else
                MsgBox("Invalid Name/Pin Code. Try again", MsgBoxStyle.Critical)
            End If

        Catch ex As MySqlException

            MsgBox("Login Failed!", MsgBoxStyle.Critical)

            conn.Close()

        Finally
            conn.Dispose()

        End Try
    End Sub
End Class

Answer 1

On your query

qry = "SELECT * FROM employeeinfo.empde; Where Title=

you have a ';' after the table name. Remove it.

 qry = "SELECT * FROM employeeinfo.empde Where Title=

Also: the field PinCode have a whitespace on it. Remove it too.

Answer 2

This will fix several issues in the original:

Private Sub btnSign_Click(sender As Object, e As EventArgs) Handles btnSign.Click

    Dim count As Integer = 0
    Dim qry As String = 
"SELECT COUNT(*) 
 FROM employeeinfo.empde 
 WHERE Title= @Title
     AND Name = @Name
     AND `PIN Code`= @PIN;"

    Using conn As New MySqlConnection("server=localhost;user id=demeth;Password=*****;database=employeeinfo"), _
          cmd As New MySqlCommand(qry, conn)

        cmd.Parameters.AddWithValue("@Title", cmbTitle.Text)
        cmd.Parameters.AddWithValue("@Name", txtName.Text)
        cmd.Parameters.AddWithValue("@PIN", txtPIN.Text)

        Try
            conn.Open()
            count = CInt(cmd.ExecuteScalar())
        Catch Ex As MySqlException
            MsgBox($"Login Failed!{vbCrLf}{Ex.Message}", MsgBoxStyle.Critical)
        End Try
    End Using

    If count = 1 Then
        MgBox("Connected Successfully", MsgBoxStyle.Information, "Login Successful")
    ElseIf count > 1 Then
        MsgBox("Dublicate Entry...Contact Costumer Service at 1010", MsgBoxStyle.Critical)
    Else
        MsgBox("Invalid Name/Pin Code. Try again", MsgBoxStyle.Critical)
    End If

End Sub

There's still the issue (and it's a big one!) where it appears the PIN codes are stored in plain-text.

Answer 3

The qry formulation is wrong. You should remove the semicolon in the string. Also the formulation os the question is not well defined. Ps. as one comment said this code is vulnerable to SQL injection. You should check it. If it's a project for self-learning you should not care enough.