Results 1 to 5 of 5

Thread: How to insert row into access database with visual basic?

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2009
    Posts
    10

    How to insert row into access database with visual basic?

    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:

    Code:
    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:

    Code:
    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?

  2. #2
    Frenzied Member
    Join Date
    Mar 2009
    Posts
    1,182

    Re: How to insert row into access database with visual basic?

    wrong forum you need .net forum

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: How to insert row into access database with visual basic?

    Thread moved from 'VB6 and Earlier' forum to 'VB.Net' (VB2002 and later) forum
    Quote Originally Posted by azuki
    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?

  4. #4

    Thread Starter
    New Member
    Join Date
    Feb 2009
    Posts
    10

    Re: How to insert row into access database with visual basic?

    StaffID is autonumber.

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: How to insert row into access database with visual basic?

    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:
    Code:
              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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width