How to insert row into access database with visual basic?

[azuki]

My Form 1 has a drop down list for user to select username, followed by a textbox to enter password. If correct password is entered, Form 2 will show.

Form 2 start from cbxCategory for category, then another cbxItem for item.
Then a button to click “Borrow”
Then it should insert.

This is my form 2_load codes:

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Form1.Hide()
        'set the minimum date for loan date
        dtpLoanDate.MinDate = DateAdd(DateInterval.Day, 0, Date.Today)
        Dim conn As New OleDbConnection(connString)

        Try
            Dim comm_Category As New OleDbCommand("Select CategoryName from Category", conn)
            conn.Open()
            Dim da_item As New OleDbDataAdapter(comm_Category)
            Dim da_category As New OleDbDataAdapter(comm_Category)
            Dim ds As New DataSet
            da_category.Fill(ds, "Category")
            cbxCategory.DataSource = ds.Tables("Category")
            cbxCategory.DisplayMember = "CategoryName"
            cbxCategory.ValueMember = "CategoryName"
            cbxCategory.SelectedIndex = -1

            'Not sure if need these 3 lines below
            Dim comm_Staff As New OleDbCommand("Select * from Staff", conn)
            Form1.cbxStaff.DataSource = ds.Tables("Staff")
            Form1.cbxStaff.ValueMember = "StaffID"

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
End Sub

My "borrow" button codes:

Private Sub btnBorrow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBorrow.Click
        Dim conn As New OleDbConnection(connString)

        Try
            
            Dim comm_loan As New OleDbCommand("Insert into loan(staffid, itemid,loandate,estreturndate)" & _
             " values (@staffid, @itemid,@loandate,@estreturndate)", conn)
            comm_loan.Parameters.AddWithValue("@staffid", Form1.cbxStaff.SelectedValue)
            comm_loan.Parameters.AddWithValue("@itemid", cbxItem.SelectedValue)
            comm_loan.Parameters.AddWithValue("@loandate", dtpLoanDate.Value)
            comm_loan.Parameters.AddWithValue("@returndate", dtpReturnDate.Value)
            conn.Open()
            'The line below has run time error: Data type mismatch in criteria expression.
            comm_loan.ExecuteNonQuery()
            MessageBox.Show("Loan record created")

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
End Sub

I keep getting an error when I try to insert the record. What's wrong with my code?

[vb5prgrmr]

wrong forum you need .net forum

[si_the_geek]

Thread moved from 'VB6 and Earlier' forum to 'VB.Net' (VB2002 and later) forum

The line below has run time error: Data type mismatch in criteria expression.

What data type (eg: Integer/Date/...) are each of the database fields?

[azuki]

StaffID is autonumber.

[si_the_geek]

AutoNumber is a numeric data type, so you need to ensure that the parameter uses a numeric data type too.

With .AddWithValue the data type is automatically detected based on the data type of what you pass to it, so you need to make sure what you pass is the right data type, eg:

          comm_loan.Parameters.AddWithValue("@staffid", CInt(Form1.cbxStaff.SelectedValue))

(I think CInt is not ideal, but am not sure what should be used instead).

Every time you use .AddWithValue, you need to do similar (unless the data type of the value is already apt).