Click here to Skip to main content
14,870,621 members
Please Sign up or sign in to vote.
3.50/5 (2 votes)
See more:
Hello Friends and Colleagues,
I am using a Microsoft Access 2007/Office 2007 Database with Visual Basic 2010 Winforms and the application will not insert new records.
I am working with it through the OLE DB Provider. My connection string is as follows:
        <add name="QFSPOS.My.MySettings.QFSDataConnectionString" connectionstring="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\QFSData.accdb">
            providerName="System.Data.OleDb" />

My code is like this:
Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click
        ' Acceprt the Payment and Save the Transaction
        Dim dsPT As New QFSPOS.dstblPaymentTypes, dsP As New QFSPOS.dstblPayments
        Dim taPT As New QFSPOS.dstblPaymentTypesTableAdapters.tblPaymentTypesTableAdapter
        Dim taP As New QFSPOS.dstblPaymentsTableAdapters.tblPaymentsTableAdapter
        Dim rowP As QFSPOS.dstblPayments.tblPaymentsRow, rowPT As QFSPOS.dstblPaymentTypes.tblPaymentTypesRow

            taPT.FillByPaymentName(dsPT.tblPaymentTypes, cmbPaymentType.Text)
            If dsPT.tblPaymentTypes.Rows.Count = 0 Then
                MsgBox("Pay method not found or does not exist", vbInformation, strProgTitle)
                Exit Sub
            End If
            rowPT = dsPT.tblPaymentTypes.Rows(0)

            ' Now Create a Payment
            rowP = dsP.tblPayments.NewtblPaymentsRow
            'rowP.PaymentID is an AutoNumber Row
            rowP.PaymentTypeID = rowPT.PaymentTypeID
            rowP.PaymentAmount = CDec(txtTransCost.Text)
            rowP.PaymentDateTime = CDate(txtTransDateTime.Text)

        Catch ex As Exception
            MsgBox(ex.Message, vbExclamation, strProgTitle)
        End Try

    End Sub

I have also tried an insert statement as in:

taP.Insert(rowPT.PaymentTypeID, CDec(txtPaymentAmount.Text), CDate(txtTransDateTime.Text))

The System simply refuses to insert the row. Is there something I am doing wrong or are there peculiarities to using the Jet OLE DB that I am missing. This same code works with an SQL Server Express database with no trouble. Please lend an expert hand.
Updated 10-Jan-13 4:05am
ZurdoDev 10-Jan-13 11:56am
What's the error?
Utuba 11-Jan-13 12:41pm
Hello ryanb31, there is no Error message given...the system triggers to no run time error. When I examine the row in break mode, the Auto number key field is assigned the number -1 but the system triggers no error message

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900