Hey guys im having this error on my codes but seems to work on the other new projects

it always highlight the

.ExecuteNonQuery {"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 '' at line 1"}

heres the code

 Imports MySql.Data.MySqlClient
    Public Class frmMemberAE
        Dim mystr2 As String = "server=localhost; uid=root; password=root; database=mydatabase"
        Dim myconn2 As MySqlConnection = New MySqlConnection

        Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
            Me.Close()
        End Sub

        Private Sub frmMemberAE_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim fileDateTime As String = DateTime.Now.ToString("yyyy-MM-dd") & " " & DateTime.Now.ToString("hh:mm:ss")

            myconn2.ConnectionString = mystr2

            Try
                If myconn2.State = ConnectionState.Closed Then
                    myconn2.Open()
                Else
                    myconn2.Close()
                    MsgBox("Database Connection Error")
                End If
            Catch ex As Exception
                MsgBox(ex.ToString)


            End Try

            txtDateJ.Text = fileDateTime
        End Sub
        Public Sub Saveinfo(ByRef SqlStatement As String)
            Dim cmd2 As MySqlCommand = New MySqlCommand

            With cmd2

                .CommandText = SqlStatement
                .CommandType = CommandType.Text
                .Connection = myconn2
                .ExecuteNonQuery()
            End With

            myconn2.Close()
            MsgBox("Success!")

            If MsgBox("Do You Want to Add New Record?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then

                txtMemID.Text = ""
                txtFName.Text = ""
                txtLName.Text = ""
                txtMMname.Text = ""
                txtOccu.Text = ""
                cboGender.Text = ""
                txtContact.Text = ""

            Else
                Me.Close()
            End If

            myconn2.Dispose()


        End Sub

        Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click

            Dim SqlStatement As String = "INSERT INTO tblmembers(MemberID, FirstName, LastName, MiddleName, Gender, Contact#, Occupation, DateJoined) VALUES('" & txtMemID.Text & "','" & txtFName.Text & "','" & txtMMname.Text & "','" & cboGender.Text & "','" & txtContact.Text & "','" & txtOccu.Text & "','" & txtDateJ.Text & "')"
            Saveinfo(SqlStatement)

        End Sub


        Private Sub cmdReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdReset.Click
            txtMemID.Text = ""
            txtFName.Text = ""
            txtLName.Text = ""
            txtMMname.Text = ""
            txtOccu.Text = ""
            cboGender.Text = ""
            txtContact.Text = ""
        End Sub
    End Class

Really need your help guys its hard to see what the error is since it only say Line 1 and i have no idea what that Line 1 is.

I am using MySQL as Database

You are trying to insert 8 columns with 7 values, and Contact# must be surrounded with backticks, because # starts a comment.

Edited 4 Years Ago by pritaeas

@Reverend Jim i am using MySQL Server 5.0

@Pritaeas so the # is causing it...isee but its the name of the feild name of my memberslist. how can i do that?

@Pritaeas Thank you man

now that it worked i have a problem that appeared on the same error line this time it says

{"Connection must be valid and open."}

Its this part i think andi dont know how to keep the connection open until i close hte form

Public Sub Saveinfo(ByRef SqlStatement As String)
        Dim cmd2 As MySqlCommand = New MySqlCommand

        With cmd2

            .CommandText = SqlStatement
            .CommandType = CommandType.Text
            .Connection = myconn2
            .ExecuteNonQuery()
        End With

        myconn2.Close()
        MsgBox("Success!")

        If MsgBox("Do You Want to Add New Record?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then

            txtMemID.Text = ""
            txtFName.Text = ""
            txtLName.Text = ""
            txtMMname.Text = ""
            txtOccu.Text = ""
            cboGender.Text = ""
            txtContact.Text = ""

        Else
            Me.Close()
        End If

        myconn2.Dispose()


    End Sub

i dont know how to keep the connection open until i close the form

You can open the connection in the form's constructor, and close it in the destructor.

How? im sorry if im asking too many questions...i am really slow at this and kept understanding the codes.

Am sure you can search a nice example for your form, now you know what to look for.

ill try to thanks for the help anyway.

This question has already been answered. Start a new discussion instead.