sql to mdb

[swathys]

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

[lolafuertes]

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

[swathys]

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)

[debasisdas]

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.

[swathys]

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....

[debasisdas]

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

ex--

string of length 12 where the column size is 10

[swathys]

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 !

[swathys]

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