SQL insert statement error

[daniel50096230]

Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegister.Click
        Dim msg As String = ""
        Dim result1 As Integer
        conKk.Close()
        conKk.Open()
        ''Validate empty field
        If (txtName.Text = "") Then
            msg += "Name is empty" & vbCrLf
        End If

        If (mskIC.Text = "" Or mskIC.MaskCompleted = False) Then
            msg += "IC is empty or incorrect format" & vbCrLf
        End If

        If (cboDate.Text = "") Then
            msg += "Birth date is empty" & vbCrLf
        End If

        If (cboMth.Text = "") Then
            msg += "Birth month is empty" & vbCrLf
        End If

        If (cboYear.Text = "") Then
            msg += "Birth year is empty" & vbCrLf
        End If

        If (radMale.Checked = False And radFemale.Checked = False) Then
            msg += "Gender is empty" & vbCrLf
        End If

        If (mskPhone.Text = "" Or mskPhone.MaskCompleted = False) Then
            msg += "Phone is empty or incorrect format" & vbCrLf
        End If

        If (txtAddress.Text = "") Then
            msg += "Address is empty" & vbCrLf
        End If

        If (txtEmail.Text = "") Then
            msg += "Email is empty" & vbCrLf
        End If

        If (txtPassword.Text = "") Then
            msg += "Password is empty" & vbCrLf
        End If

        If (txtRetype.Text = "") Then
            msg += "Retype password is empty" & vbCrLf
        End If

   


        ''Compare password

        If (txtPassword.Text <> "" And txtRetype.Text <> "") Then
            Dim Pass As String = txtPassword.Text
            Dim RetypeP As String = txtRetype.Text

            result1 = String.Compare(Pass, RetypeP)

            If (result1 = -1) Then
                msg += "Password and retype password is unmatch!,Please enter again." & vbCrLf
                'MessageBox.Show(result1)
            Else
                'MessageBox.Show(result1)
            End If
        End If


        ''Concatenate combo box
        Dim DOB As String = ""
        If (cboDate.Text <> "" And cboMth.Text <> "" And cboYear.Text <> "") Then
            DOB = cboDate.SelectedItem.ToString() & "/" & cboMth.SelectedItem.ToString() & "/" & cboYear.SelectedItem.ToString()

        End If
        ''Get gender text
        Dim Gender1 As String = ""
        If (radMale.Checked = True) Then
            Gender1 += radMale.Text
        End If

        If (radFemale.Checked = True) Then
            Gender1 += radFemale.Text
        End If


        ''Display error message or update to database
        If (msg <> "") Then
            MessageBox.Show("Please enter the following items:" & vbCrLf & vbCrLf & msg, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else

           
            Dim myInsert As SqlCommand
            myInsert = New SqlCommand("Insert into Member([MemberID], [Name], [IC], [DOB], [Gender], [TelNo], [Address], [Email], [Password])  Values('" & txtMID.Text & "','" & txtName.Text & "','" & mskIC.Text & "','" & DOB & "','" & Gender1 & "','" & mskPhone.Text & "','" & txtAddress.Text & "','" & txtEmail.Text & "','" & txtPassword.Text & "'", conKk)
            myInsert.ExecuteNonQuery()

            MessageBox.Show("Thanks for your registration,enjoy our services now!", "Registered", MessageBoxButtons.OK, MessageBoxIcon.Information)

        End If
        conKk.Close()
    End Sub

I get the following errors:
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Data.SqlClient.SqlException: Incorrect syntax near '1'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at FYP.AddMember.btnRegister_Click(Object sender, EventArgs e) in D:\Pendrive\fyp interfaces\FYPvb\FYP\AddMember.vb:line 155
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


It stated that "incorrect syntax near 1"...


Anyone can help me on this matter?

[dglienna]

Format the date like this (m/d/y) is what you had, so I didn't change it

Public Class Form1

    Private Sub Form1_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
        Dim d As String, m As String, y As String
        Dim dte As String = "12/04/71", DOB As Date
        d = dte.Substring(0, 2)
        m = dte.Substring(3, 2)
        y = dte.Substring(6, 2)
        If (d.ToString <> "" And m.ToString <> "" And y.ToString <> "") Then
            DOB = CDate(m & "/" & d & "/" & y)
        End If
        MsgBox(DOB & "   Month: " & DOB.Month)
    End Sub
End Class

Depending on the DB, you might need #'s around the date

[marceln]

First thing first: use parameterized queries! You'll make the code easier to read and safer.
Regarding the date issue: will that conversion work on all systems (for all locales)? You should not hardcode the "/" date separator, but use the one set in the current locale. You can get it from DateTimeFormatInfo.DateSeparator.

Regards