Number of query values and destination fields are not the same. Error Message

Question

I'm having an error message with my codes in visual basic. Please help. Thanks.

The error says:

Number of query values and destination fields are not the same.

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Dim cmd As New OleDb.OleDbCommand

If Not cnn.State = ConnectionState.Open Then
    cnn.Open()
End If

cmd.Connection = cnn

If Me.txtID.Tag & "" = "" Then
    cmd.CommandText = "INSERT INTO ProfessorListTable(ID,LastName,FirstName,MI,Gender,Department,ContactNo,Address,EmailAddress,YearEmployed)" & _
        " VALUES(" & Me.txtID.Text & ",'" & Me.txtLName.Text & "','" & _
        Me.txtFName.Text & "','" & Me.txtMI.Text & "','" & _
        Me.txtGender.Text & "','" & Me.txtDept.Text & "','" & _
        Me.txtNo.Text & "','" & Me.txtAddress.Text & "','" & _
        Me.txtEAdd.Text & "','" & Me.txtYear.Text & "',')"

    cmd.ExecuteNonQuery()
Else
    cmd.CommandText = "UPDATE ProfessorListTable " & _
    " SET txtID=" & Me.txtID.Text & _
    ", LastName='" & Me.txtLName.Text & "'" & _
    ", FirstName='" & Me.txtFName.Text & "'" & _
    ", MI='" & Me.txtMI.Text & "'" & _
    ", Gender='" & Me.txtGender.Text & "'" & _
    ", Department='" & Me.txtDept.Text & "'" & _
    ", ContactNo='" & Me.txtNo.Text & _
    ", Address='" & Me.txtAddress.Text & "'" & _
    ", EmailAddress='" & Me.txtEAdd.Text & "'" & _
    ", YearEmployed='" & Me.txtYear.Text & _
    " WHERE stdid=" & Me.txtID.Tag
    cmd.ExecuteNonQuery()


End If


RefreshData()

End Sub

The

Vignesh Kumar A Vignesh Kumar A · Accepted Answer · 2014-02-14T04:06:09

You had extra Comma (,) here

& Me.txtYear.Text & "','

So it's leads to 11 value instead of 10

And Address is reserved word so use it as [Address]

Try like this

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click Dim cmd As New OleDb.OleDbCommand

If Not cnn.State = ConnectionState.Open Then
    cnn.Open()
End If

cmd.Connection = cnn

If Me.txtID.Tag & "" = "" Then
    cmd.CommandText = "INSERT INTO ProfessorListTable(ID,LastName,FirstName,MI,Gender,Department,ContactNo,[Address],EmailAddress,YearEmployed)" & _
        " VALUES(" & Me.txtID.Text & ",'" & Me.txtLName.Text & "','" & _
        Me.txtFName.Text & "','" & Me.txtMI.Text & "','" & _
        Me.txtGender.Text & "','" & Me.txtDept.Text & "','" & _
        Me.txtNo.Text & "','" & Me.txtAddress.Text & "','" & _
        Me.txtEAdd.Text & "','" & Me.txtYear.Text & "')"

    cmd.ExecuteNonQuery()
Else
    cmd.CommandText = "UPDATE ProfessorListTable " & _
    " SET txtID=" & Me.txtID.Text & _
    ", LastName='" & Me.txtLName.Text & "'" & _
    ", FirstName='" & Me.txtFName.Text & "'" & _
    ", MI='" & Me.txtMI.Text & "'" & _
    ", Gender='" & Me.txtGender.Text & "'" & _
    ", Department='" & Me.txtDept.Text & "'" & _
    ", ContactNo='" & Me.txtNo.Text & _
    ", [Address]='" & Me.txtAddress.Text & "'" & _
    ", EmailAddress='" & Me.txtEAdd.Text & "'" & _
    ", YearEmployed='" & Me.txtYear.Text & _
    " WHERE stdid=" & Me.txtID.Tag
    cmd.ExecuteNonQuery()


End If
RefreshData()
End Sub

Number of query values and destination fields are not the same. Error Message

1 Answers