OleDBException Overflow

Im getting the following error :

System.Data.OleDb.OleDbException was unhandled

ErrorCode=-2147217833

Message="Overflow"

Source="Microsoft JET Database Engine"

StackTrace:

at

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS

dbParams, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

at

System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,

Object& executeResult)

at

System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior

behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

at

Home_Party_Solutions.PartyDBaseAccess.getCustomerID(Customer cust) in

C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\PartyDBaseAccess.vb:line 138

at

Home_Party_Solutions.Customer.getCustomerID(IPartyDBase& p_dbase)

in C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\Customer.vb:line 212

at

Home_Party_Solutions.PartyOrder.Done_Click(Object sender, EventArgs e)

in C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\PartyOrder.vb:line 150

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at

System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons

button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ButtonBase.WndProc(Message& m)

at System.Windows.Forms.Button.WndProc(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

at

System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32

msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

at

System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32

dwComponentID, Int32 reason, Int32 pvLoopData)

at

System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32

reason, ApplicationContext context)

at

System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32

reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(ApplicationContext context)

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

at

Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

at

Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[]

commandLine)

at

Home_Party_Solutions.My.MyApplication.Main(String[] Args) in

17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

Basically I am inserting a row into a table. The sql line looks like :

Insert into Customer VALUES ('1', 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', '49024', 'a@.a.com', '3335551234')

When I copy and paste the command into Access, it successfully adds the

row into the table. However, I am getting that error when I run

it in my program. I create the string, then this is the code I am

using :

command = New OleDbCommand

command = m_Connection.CreateCommand()

command.CommandText = tempString

Dim tempInt As Integer = -1

tempInt = command.ExecuteNonQuery()

At the last line, I get the overflow.

Just for clarification, the values are (Cust ID as long, firstName as

text, lastName as text, Street as text, City as text, State as text,

Zip as long, email as text, phone as double).

Any insights into the problem? The error message isnt all that insightful.

Thanks

Hi,

first of all name the columns which have to be inserted, this would provide much more concistence accross your code and will be much easier to maintain if error occur.

Do something like the following:

INSERT INTO TableName
(
COL1,
COL2
)
VALUES
(
1,
'2'
)

Perhaps this could already solve the problem or help you to find where the problem is located.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Getting the same uninformative error when I try this.|||

You might want to try Data Access Tracing to find out what happened. Please see the link http://msdn2.microsoft.com/en-us/library/aa964124.aspx.

|||

Hi ab2034,

From the error code it seems you are getting the DB_E_DATAOVERFLOW error from the OLEDB provider. This error might be caused when "Literal value in the command exceeded the range of the type of the associated column.". So it means the provider did not like one or more of your values. I would recommend you to go ahead and try to use smaller input values and see if that works. I would start with the integral/double values first e.g. phone number. For example try putting 1 or 2 as the phone number values to see if that works and continue with other fields.

Thanks

Waseem

|||

Try
Insert into Customer VALUES (1, 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', 49024, 'a@.a.com', 3335551234)

Rather than
Insert into Customer VALUES ('1', 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', '49024', 'a@.a.com', '3335551234')

Or better yet, use placeholders and DbParameter to substitute values in sql string.

sql

OleDbException ErrorCode

When a user finally confirms his order in a shopping cart application (using MS-Access as the backend), the items he has purchased are populated in a Access DB table namedOrders & his personal details (like name, mail, billing address, shipping address etc.) are populated in another table namedCustDetails. Both the DB tables have a column namedOrderID. This column is thePrimary Key in theCustDetails table & theForeign Key in theOrders table. TheSession.SessionID becomes the unique OrderID.

After confirming an order, if the user refreshes the page, the app will try to populate the sameSession.SessionID in theCustDetails table but since the columnOrderID is aPrimary Key column in the tableCustDetails, it won't accept duplicate OrderIDs. Under such circumstances, anOleDbException will be raised.

Since a DB app can throw otherOleDbExceptions other than the one about which I mentioned above, I want to display custom error messages to the user. For e.g. if he refreshes the page after confirming his order, I want to display a message saying "Your order has already been placed".

To do this, I tried using theErrorCode property of theOleDbException class but what I found is theErrorCode changes from time to time! Had a particularErrorCode been assigned to the error, I could have done something like this (assuming that theErrorCode for the above error is-12345 which is constant):

Try
'some code
Catch ex As OleDbException
If (ex.ErrorCode = -12345) Then
Response.Write("Your order has already been placed")
ElseIf (ex.ErrorCode = <some other constant ErrorCode>) Then
Response.Write("Another custom error message")
End If
End Try

But I can't do the above since theErrorCode changes from time to time.

So how do I display custom error messages to users under such circumstances?

Of course, I can use theMessage property of theOleDbException class but that would be a rather tedious workaround.

I would provide user friendly messages for the the common errors in a shared function.

There are too many error codes to redefine them all. Putting the code in a central function allows you to call it from anywhere (excuse my C# but you get the idea)

String ExceptionToFriendlyString(OldDbExcption Ex){switch(Ex.ErrorCode) {case 1234:return"That record has already been inserted";case 5678:return"Friendly error message";default:return Ex.Message; // default to Exception Message }}

|||Thanks for your suggestion, Steve, but as already pointed in post #1 in this thread, the ErrorCode goes on changing. For e.g. when I try to insert a record in a DB table that already exists in that DB table, the ErrorCode turns out to be, say, 1234.

Next I shut down my machine & restart it. Now when I try to insert a record in the same DB table which already exists in the table, then the ErrorCode changes to, say, 5678. Of course, I can use a default message as you have shown in your code but (again) as already pointed out, I want the error messages to be as precise as possible.

Any other suggestions?|||

I think you will find that the reason you are getting different error codes is because you are getting different errors.

|||

Hi RN5A,

I agree that the error code stays fixed when you get certain kind of error. When error code changes, the type of error gets changed.