ExecuteNonQuery Error

[kenomote]

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

[Reverend Jim]

After line 65, please add

MsgBox(SqlStatement)

and post the results here. Also, what database engine are you using?

[pritaeas]

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

[kenomote]

@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]

The solution was already in my previous reply. Use:

`Contact#`

[kenomote]

@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

[kenomote]

@Pritaeas i just replaced the Contact# to ContactNum

[pritaeas]

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.

[kenomote]

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

[pritaeas]

http://www.startvbdotnet.com/oop/constructor.aspx

[kenomote]

i havent have any expirienced using Modules

[pritaeas]

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

[kenomote]

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.