insert into database from vb form

[Christopher_12]

i want to save data entered into my form into the database but it is telling "column name or number of supplied values does not match table definition"
here is my code
help please
Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click

    'Error checking:Personal information

    Try
        cmd.CommandType = System.Data.CommandType.Text
        cmd.CommandText = "Insert into Employee values('" & Cmbgender.Text & "','" & CmbTittle.Text & "','" & Txtname.Text & "','" & Txtsurname.Text & "','" & Dateofbirth.Text.ToString & "', '" & TxtPhysicalAddress1.Text & "', '" & TxtPhysicalAddress2.Text & "','" & Cmbcountry.Text & "','" & Txtprovince.Text & "','" & Txtcity.Text & "','" & CmbEmpType.Text & "','" & ComboBox1.Text & "','" & TxtEmail.Text & "','" & Txtphone.Text & "','" & Txtusername.Text & "','" & Txtpassword.Text & "','" & Hiredate.Text.ToString & "' )"

        cmd.Connection = con
        con.Open()

        cmd.ExecuteNonQuery()
        MsgBox("Succesfully Added", MsgBoxStyle.Information, "add")
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try


        If Cmbgender.Text = "" Then
            MessageBox.Show("Selection empty for Employee Gender", "Employee Gender", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Cmbgender.ResetText()
            Cmbgender.Focus()

        ElseIf CmbTittle.Text = "" Then
            MessageBox.Show("Please Select a Tittle for Employee", "Tittle", MessageBoxButtons.OK, MessageBoxIcon.Error)
            CmbTittle.ResetText()
            CmbTittle.Focus()

        ElseIf Txtname.Text = "" Then
            MessageBox.Show("Name text fied is empty", "Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtname.Clear()
            Txtname.Focus()

        ElseIf IsNumeric(Txtname.Text) Then
            MessageBox.Show("Please enter a valid Name" & vbLf & "A number was entered", "Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtname.Clear()
            Txtname.Focus()

        ElseIf (Txtname.TextLength < 2) Then
            MessageBox.Show("Please enter a valid Name" & vbLf & "You entered a Single Letter", "Name", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtname.Clear()
            Txtname.Focus()

        ElseIf IsNumeric(Txtsurname.Text) Then
            MessageBox.Show("Please enter a valid Surname" & vbLf & "A number was entered", "Surname", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtsurname.Clear()
            Txtsurname.Focus()

        ElseIf Txtsurname.Text = "" Then
            MessageBox.Show("Surname text fied is empty", "Surname", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtsurname.Clear()
            Txtsurname.Focus()

        ElseIf (Txtsurname.TextLength < 2) Then
            MessageBox.Show("Please enter a valid surame" & vbLf & "You entered a Single Letter", "Surname", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtsurname.Clear()
            Txtsurname.Focus()

        ElseIf Dateofbirth.Value > Date.Today Then
            MessageBox.Show("Please Select a Date of birth which is Prior to the Current Date", "DOB", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Dateofbirth.ResetText()
            Dateofbirth.Focus()


            'Error checking:Address details

        ElseIf TxtPhysicalAddress1.Text = "" Then
            MessageBox.Show("Physical Address 1 text fied is empty", "Physical Address 1", MessageBoxButtons.OK, MessageBoxIcon.Error)
            TxtPhysicalAddress1.Clear()
            TxtPhysicalAddress1.Focus()

        ElseIf TxtPhysicalAddress1.TextLength < 20 Then
            MessageBox.Show("Add a valid Address Eg:Betrams 1234, Johannesburg", "Physical Address 1", MessageBoxButtons.OK, MessageBoxIcon.Error)
            TxtPhysicalAddress1.Clear()
            TxtPhysicalAddress1.Focus()

        ElseIf TxtPhysicalAddress2.Text = "" Then
            MessageBox.Show("Physical Address 2 text fied is empty", "Physical Address 2", MessageBoxButtons.OK, MessageBoxIcon.Error)
            TxtPhysicalAddress2.Clear()
            TxtPhysicalAddress2.Focus()

        ElseIf TxtPhysicalAddress1.TextLength < 20 Then
            MessageBox.Show("Add a valid Address Eg:Betrams 1534, Johannesburg", "Physical Address 2", MessageBoxButtons.OK, MessageBoxIcon.Error)
            TxtPhysicalAddress2.Clear()
            TxtPhysicalAddress2.Focus()

        ElseIf Cmbcountry.Text = "" Then
            MessageBox.Show("Selection empty for Country", "Employee Country", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Cmbcountry.ResetText()
            Cmbcountry.Focus()

        ElseIf Txtprovince.Text = "" Then
            MessageBox.Show("Province text fied is empty", "Province", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtprovince.Clear()
            Txtprovince.Focus()

        ElseIf IsNumeric(Txtprovince.Text) Then
            MessageBox.Show("Please enter a Province" & vbLf & "A number was entered" & vbLf & "Eg: Pretoria", "Province", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtprovince.Clear()
            Txtprovince.Focus()

        ElseIf (Txtprovince.TextLength < 4) Then
            MessageBox.Show("Please enter a valid Province" & vbLf & "You entered Letters which is not a province" & vbLf & "Eg: Pretoria", "Province", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtprovince.Clear()
            Txtprovince.Focus()

        ElseIf Txtcity.Text = "" Then
            MessageBox.Show("City text fied is empty", "City", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtcity.Clear()
            Txtcity.Focus()

        ElseIf IsNumeric(Txtcity.Text) Then
            MessageBox.Show("Please enter a valid city" & vbLf & "A number was entered" & vbLf & "Eg: Montana", "City", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtcity.Clear()
            Txtcity.Focus()

        ElseIf (Txtcity.TextLength < 4) Then
            MessageBox.Show("Please enter a valid city " & vbLf & "You entered Letters which is not a city" & vbLf & "Eg: Montana", "City", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtcity.Clear()
            Txtcity.Focus()

            'Error Checking: Administrative Information

        ElseIf CmbEmpType.Text = "" Then
            MessageBox.Show("Please select Employee Type", "Employee Type", MessageBoxButtons.OK, MessageBoxIcon.Error)
            CmbEmpType.ResetText()
            CmbEmpType.Focus()

        ElseIf CmbTittle.Text = "" Then
            MessageBox.Show("Please select Employee  Tittle", "Employee Tittle", MessageBoxButtons.OK, MessageBoxIcon.Error)
            CmbTittle.ResetText()
            CmbTittle.Focus()

        ElseIf TxtEmail.Text = "" Then
            MessageBox.Show("Email Address text fied is empty", "Email Address", MessageBoxButtons.OK, MessageBoxIcon.Error)
            TxtEmail.Clear()
            TxtEmail.Focus()

        ElseIf TxtEmail.TextLength < 17 Then
            MessageBox.Show("Add a valid Email Address eg:[e-mail]", "Email Address", MessageBoxButtons.OK, MessageBoxIcon.Error)
            TxtEmail.Clear()
            TxtEmail.Focus()

        ElseIf Txtphone.Text = "" Then
            MessageBox.Show("Phone Number text field is empty" & vbLf & "Or Check The Data Type", "Adding Phone Number", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtphone.Clear()
            Txtphone.Focus()

        ElseIf Txtphone.TextLength < 10 Then
            MessageBox.Show("Wrong Input, Add a complete Phone Number", "Adding Phone Number", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtphone.Clear()
            Txtphone.Focus()

        ElseIf IsNumeric(Txtusername.Text) Or Txtusername.Text = "" Then
            MessageBox.Show("Username text field is empty", "Username", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtusername.Clear()
            Txtusername.Focus()

        ElseIf IsNumeric(Txtpassword.Text) Or Txtpassword.Text = "" Then
            MessageBox.Show("Password  text field is empty", "Password", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Txtpassword.Clear()
            Txtpassword.Focus()

        ElseIf Hiredate.Value > Date.Today Then
            MessageBox.Show("Please Select a Hire Date which is Prior to the Current Date", "DOB", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Hiredate.ResetText()
            Hiredate.Focus()



    End If

End Sub

End Class

[Reverend Jim]

I strongly suggest you use Parameterized Queries. For one thing, you won't have to worry about adding appropriate field delimiters and names containing single quotes (like O'Brien) will be automatically adjusted so that they insert properly. Lacking that I suggest the following format to make it easier to read what you have coded.

cmd.CommandText = "Insert into Employee values(" &
                  "'" & Cmbgender.Text & "'," &
                  "'" & CmbTittle.Text & "'," &
                  "'" & Txtname.Text & "'," &
                  "'" & Txtsurname.Text & "'," &
                  "'" & Dateofbirth.Text & "'," & 
                  "'" & TxtPhysicalAddress1.Text & "'," & 
                  "'" & TxtPhysicalAddress2.Text & "'," &
                  "'" & Cmbcountry.Text & "'," & 
                  "'" & Txtprovince.Text & "'," & 
                  "'" & Txtcity.Text & "'," & 
                  "'" & CmbEmpType.Text & "'," &
                  "'" & ComboBox1.Text & "'," &
                  "'" & TxtEmail.Text & "'," &
                  "'" & Txtphone.Text & "'," &
                  "'" & Txtusername.Text & "'," &
                  "'" & Txtpassword.Text & "'," &
                  "'" & Hiredate.Text.ToString & "')"

However, the proper format for an insert would include the field names. The format for that is (and I am assuming you meant Title rather than Tittle)

cmd.CommandText = "INSERT INTO Employee (Gender, Title, Name, " &
                  "  Surname, DateOfBirth, PyhsicalAdress1, " &                                 "  PhysicalAddress2, Country, Province, City, " &
                  "  EmpType, Box, Email, Phone, " &
                  "  UserName, Password, HireDate) " & 
                  " VALUES(" &
                  "'" & Cmbgender.Text & "'," &
                  "'" & CmbTitle.Text & "'," &
                  "'" & Txtname.Text & "'," &
                  "'" & Txtsurname.Text & "'," &
                  "'" & Dateofbirth.Text & "'," & 
                  "'" & TxtPhysicalAddress1.Text & "'," & 
                  "'" & TxtPhysicalAddress2.Text & "'," &
                  "'" & Cmbcountry.Text & "'," & 
                  "'" & Txtprovince.Text & "'," & 
                  "'" & Txtcity.Text & "'," & 
                  "'" & CmbEmpType.Text & "'," &
                  "'" & ComboBox1.Text & "'," &
                  "'" & TxtEmail.Text & "'," &
                  "'" & Txtphone.Text & "'," &
                  "'" & Txtusername.Text & "'," &
                  "'" & Txtpassword.Text & "'," &
                  "'" & Hiredate.Text.ToString & "')"

I don't know what column name corresponds to ComboBox1.Text. Your error results in a mismatch between the given values and the corresponding database columns. Perhaps you are missing a column. If one of your columns is autogenerated (like a row number) then you must explicitly name the columns in the insert. If, after you compose the query you do

Debug.WriteLine(cmd.CommandText)

It might be more obvious if there is a problem with the query. Depending on your database, Password may be a reserved word in which case you would code the column name as [Password]

[Santanu.Das]

Agreed, with Jim. Using Parameterized Query you can prevent your database system from malicious SQL Injections.

"column name or number of supplied values does not match table definition"

From my opinion, the number of textboxes do not match with total number of fields. Please check & compair your data table fields and the textboxes for field values.

Secondly your error checking portion is too much long and this type of work is not a clever programming. You can create a function for error checking of all textboxes. Always create and use reusable functions and sub procedures, which can give you more flexibility to write and mentain your codes and also they can save time to write same codes for another time and place.

I just want to show you that how you can use functions to avoid long codes.

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        'If there s an error then exit from sub
        If IsErrors() = False Then
            Exit Sub
        End If


        'Do your jobs
        MessageBox.Show("Success")
    End Sub

    Private Function IsErrors() As Boolean
        If Not getError(TextBox1, 4, True) Then Return False
        If Not getError(TextBox2) Then Return False
        '............................................
        '.............................................
        '............................................
        If Not getError(DateTimePicker1) Then Return False

        Return True
    End Function


    Private Function getError(Obj As Object, Optional lnth As Integer = 0, Optional num As Boolean = False) As Boolean


        If TypeOf Obj Is DateTimePicker Then

            Dim dt As DateTimePicker = CType(Obj, DateTimePicker)
            If dt.Value > Date.Today Then
                MessageBox.Show("Please Select a Date of birth which is Prior to the Current Date", "DOB", MessageBoxButtons.OK, MessageBoxIcon.Error)
                DateTimePicker1.Focus()
                Return False
            End If

        ElseIf TypeOf Obj Is TextBox Then

            Dim txt As TextBox = CType(Obj, TextBox)
            If txt.Text.Length = 0 Then
                MessageBox.Show("Empty text")
                txt.Focus()
                Return False
            Else
                If num Then
                    If IsNumeric(txt.Text) Then
                        MessageBox.Show("Numerical text")
                        txt.Focus()
                        Return False
                    End If
                End If

                If txt.Text.Length < lnth Then
                    MessageBox.Show(String.Format("Text length is less than {0}.", lnth))
                    txt.Focus()
                    Return False
                End If
            End If

        End If

        Return True
    End Function

Hope it can help you.

[Miurei]

Kindly check if the datatype length on your database can handle the number of characters you entered on your textboxes...

To avoid such error, go to your textbox properties and set the Max length of your textbox not exceeding the length set on your database.