Hi,

I am changing the sql database to database access (.mdb). The problem is when i run my application using sql connection its work well and all the tables are updated but when i use mdb connection it update some of the tables and few other table it does not update.
Its pop up with error message OVERFLOW. I have no idea why the error pops up as i don't find anything wrong with the connection or the statement.
Below is my code and the error pops up when it reach the place i highlight with red.

Please Help !!!

Public Function Update_POSBillTransaction() As Boolean
         Dim strSQL As String = ""
        Dim strSerialNo As String = "1"
        Dim myConnection As OleDbConnection
        Dim myCommand As OleDbCommand
         Dim drSerialNo As OleDbDataReader

        myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & GEN_DB_NAME & "\POS.mdb" & ";")
        myConnection.Open()

        StepLog("modGeneral|Update_POSTransaction| GEN_DB_NAME |= " & GEN_DB_NAME)
     
        ' Get LatestTransactionNo from Parameter table
        getLatestTransNo()

        ' Get SerialNo
        sSQL = "SELECT MAX(SerialNo) FROM PaymentDetails WHERE KioskID=" & _clsDBHelper.mQuotedStr(KioskNo)

        myCommand = myConnection.CreateCommand
        myCommand.CommandText = sSQL
        drSerialNo = myCommand.ExecuteReader
        drSerialNo.Read()

        'drSerialNo.Read()

        If drSerialNo.HasRows Then
            ' Get SerialNo
            If Not drSerialNo.IsDBNull(0) Then ' old transaction exist
                strSerialNo = drSerialNo(0) + 1
            Else ' new transaction
                strSerialNo = 1
            End If
        End If
        drSerialNo.Close()

        CurrentTranxDateTime = Now
        CurrentTranxDate = System.DateTime.Now.ToString("dd-MMM-yyyy")
        CurrentTranxTime = System.DateTime.Now.ToString("hh:mm:ss tt")

        BoothNo = BoothNo
        StoreCity = StoreCity
        ostrTransactionNo = TransactionNo
        ostrSerialNo = strSerialNo
        oCurrentTranxDateTime = CurrentTranxDateTime
        oCurrentTranxDate = CurrentTranxDate
        oCurrentTranxTime = CurrentTranxTime
        oPOSTransCode = POSTransCode
     
        Try

            If myConnection.State = 0 Then
                myConnection.Open()
            End If


            strSQL = "INSERT INTO Update_POSPaymentDetails ([KioskID],[KioskLocation],[TransactionNo], " _
                    & "[SerialNo],[TransactionDateTime],[TransactionDate],[TransactionTime],[TransactionCode], " _
                    & "[AgencyCode],[AgencyName],[AccountNo],[BillNo],[BillAmount],[PaidAmount]) " _
                    & "VALUES ('" & BoothNo & "','" & StoreCity & "','" & TransactionNo & "','" _
                    & strSerialNo & "','" & CurrentTranxDateTime & "','" & CurrentTranxDate & "','" _
                    & CurrentTranxTime & "','" & POSTransCode & "','" & SESSION_AGENCY_CODE & "','" _
                    & SESSION_AGENCY_NAME & "','" & SESSION_AGENCY_ACCNO & "','" & SESSION_AGENCY_BILLNO & "','" _
                    & SESSION_AGENCY_BILLAMO & "','" & SESSION_AGENCY_BILLTOT & "')"


            StepLog("modGeneral|Update_POSBillTransaction| Update_POSPaymentDetails |strSQL  |= " & strSQL)

            myCommand = New OleDbCommand(strSQL, myConnection)
            myCommand.ExecuteNonQuery()

            myConnection.Close()

        Catch ex As Exception
            WriteToLogFile("modGeneral|Update_POSBillTransaction| " & ex.Message)
        End Try

    End Function

Just to clarify, in your mdb, all the fields of your table are string?

Usually, the overflow message cames when you try to insert a non numeric value in a numeric field or a non date (or bad formatted date) in a date field.

TIA

Hi,

i'm still getting the same error even i remove '' marks from numeric datatype.
do you have any suggestion from the code and datatype below.

strSQL = "INSERT INTO PaymentDetails ([KioskID],[KioskLocation],[TransactionNo], " _
                            & "[SerialNo],[TransactionDateTime],[TransactionDate],[TransactionTime], " _
                            & "[TransactionCode],[AgencyCode],[AgencyName],[AccountNo], " _
                            & "[BillNo],[BillAmount],[ServiceCharge],[PaidAmount])" _
                            & "VALUES ('" & KioskNo & "','" & StoreCity & "'," _
                            & TransactionNo & "," & strSerialNo & ",'" _
                            & CurrentTranxDateTime & "','" & CurrentTranxDate & "','" _
                            & CurrentTranxTime & "','" & POSTransCode & "','" _
                            & SESSION_AGENCY_CODE & "','" & SESSION_AGENCY_NAME & "','" _
                            & SESSION_AGENCY_ACCNO & "','" & SESSION_AGENCY_BILLNO & "'," _
                            & SESSION_AGENCY_BILLAMO & "," & SESSION_AGENCY_BILLINT & "," _
                            & SESSION_AGENCY_BILLTOT & ")"

this is the datatype

KioskID          Text
KioskLocation          text
TransactionNo          Number (fieldsize = integer)
SerialNo               Number (fieldsize = integer)
TransactionDateTime    Date/Time 
TransactionDate        Text
TransactionTime        Text
TransactionCode        Text
AgencyCode             text
AgencyName             text
AccountNo              text
BillNo                 text
BillAmount             Number (fieldsize = Double)
ServiceCharge          Number (fieldsize = Double)
PaidAmount             Number (fieldsize = Double)

you need to check if the MDB is in sync with the earlier version of database.

and ensure that you are passing proper data to the DB from application.

hi,

As i mention earlier it update some of the tables in the database but some are not and i don't think so its a problem with the sync and as posted above that is my database statement with datatype in (MDB) ....I have checked so many time and i don't find what is the wrong with it....it was working perfectly ok when i use with SQL...Now i need to convert it to .mdb....

you are sure , not passing string value of more the length than permitted

ex--

string of length 12 where the column size is 10

i have check everything and still i'm getting the same error. I create the database structure exactly same as sql database structure. I have no idea what to check and i have spend more than 3 days to find out the solution but still i'm not getting anything. Please Help me !

hi,

ok i found the solution...actually my datatype was correct but i wrongly set in the field property....for TransactionNo & SerialNo its a non fraction number so i set the field size to LongInteger and decimal place to 0....so its working now...

i have refer to this website which is very helpful http://www.databasedev.co.uk/fields_datatypes.html

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.