[2008] Data type mismatch in criteria expression. URGENT

[Wesley008]

Alright I fixed my last problem but now I'm getting this error only when Editing:

Data type mismatch in criteria expression.

Here's my Code:

Table Create:

Public Sub CreateDatabase()
        Dim DB As New ADOX.Catalog()
        DB.Create(Connection.ConnectionString)
        Connection.Open()
        SQL = "CREATE TABLE [Students] ([CIndex] AutoIncrement, [StudentID] TEXT, [LastName] TEXT, [FirstName] TEXT, [Grade] TEXT, [Gender] TEXT, [BirthDate] TEXT, [PhoneNum] TEXT, [AddressOne] TEXT, [AddressTwo] TEXT, [EmailOne] TEXT, [EmailTwo] TEXT, [Notes] TEXT)"
        Command = New OleDbCommand(SQL, Connection)
        Command.ExecuteNonQuery()
    End Sub

Student Edit:

Public Sub Students_Edit()
        Students_SQLSelect()
        Adapter.UpdateCommand = New OleDbCommand("UPDATE [Students] SET StudentID = @ID, LastName = @LN, FirstName = @FN, Gender = @Gen, BirthDate = @Birth, [Phone#] = @Phone, Address1 = @AD1, Address2 = @AD2, Email1 = @E1, Email2 = @E2, Notes = @Notes WHERE CIndex = @CIndex")
        Adapter.UpdateCommand.Connection = Connection
        Connection.Open()
        With Adapter.UpdateCommand
            .Parameters.Add("@ID", OleDbType.VarChar, 10, "StudentID")
            .Parameters.Add("@LN", OleDbType.VarChar, 75, "LastName")
            .Parameters.Add("@FN", OleDbType.VarChar, 75, "FirstName")
            .Parameters.Add("@Grade", OleDbType.VarChar, 2, "Grade")
            .Parameters.Add("@Gen", OleDbType.VarChar, 6, "Gender")
            .Parameters.Add("@Birth", OleDbType.VarChar, 100, "BirthDate")
            .Parameters.Add("@Phone", OleDbType.VarChar, 25, "[Phone#]")
            .Parameters.Add("@AD1", OleDbType.VarChar, 100, "Address1")
            .Parameters.Add("@AD2", OleDbType.VarChar, 100, "Address2")
            .Parameters.Add("@E1", OleDbType.VarChar, 100, "Email1")
            .Parameters.Add("@E2", OleDbType.VarChar, 100, "Email2")
            .Parameters.Add("@Notes", OleDbType.VarChar, 500, "Notes")
            .Parameters.Add("@CIndex", OleDbType.Integer, 5, "CIndex")
        End With
        With frmStudentsAdd
            Adapter.Fill(DS)
            Dim DRow As DataRow
            DRow = DS.Tables(0).Rows(frmStudents.RIndex)
            MsgBox(DRow.Item("StudentID"))
            DRow.BeginEdit()
            DRow("StudentID") = .txtStudentID.Text
            DRow("LastName") = .txtLastName.Text
            DRow("FirstName") = .txtFirstName.Text
            DRow("Grade") = .txtGrade.Text
            If .rbMale.Checked Then
                DRow.Item("Gender") = "Male"
            ElseIf .rbFemale.Checked Then
                DRow.Item("Gender") = "Female"
            End If
            DRow("BirthDate") = .dtpBirth.Value.ToShortDateString
            DRow("Phone#") = .txtPhone.Text
            DRow("Address1") = .txtAddressOne.Text
            DRow("Address2") = .txtAddressTwo.Text
            DRow("Email1") = .txtEmailOne.Text
            DRow("Email2") = .txtEmailTwo.Text
            DRow("Notes") = .txtNotes.Text
            DRow.EndEdit()
            Adapter.Update(DS)
            DS.AcceptChanges()
        End With
        Connection.Close()
        Students_LoadToList()
    End Sub

It inserts fine when adding but the moment I try to Edit it, I get that error.

EDIT:

Hmm, I seem to have fixed it, by adding text to all the textboxes. How can I do this without having to add to add textboxes, and When I have Data in all the Textboxes, Everything from Column Genedr is Shifted Over 1.

[Wesley008]

Anyone at all?

[.paul.]

its because your database fields can't accept a null value

[Wesley008]

Is their a way to like Check if the Value is NULL and if so, not add to it? Or do I need to do repeated IF statements

[jmcilhinney]

I can tell you one thing for a start. BirthDate should absolutely NOT be a Text column. It contains dates so it should be a Date column, or DateTime or whatever. Do NOT store data that is not naturally text in text fields.

As for your problem, you've added a @Grade parameter to your command but there isn't one in your SQL code. The Jet OLEDB provider uses position, rather than name, to substitute parameter values into the SQL code. As such, the value of your @Grade parameter is getting inserted into your Gender column, etc. You got the data type mismatch because the value of your @Notes parameter, which is text, was being compared to your CIndex column, which is numeric.

[Wesley008]

Thank You Both, I feel kinda stupid for not seeing I was missing Gender.