Overflow Runtime Error with data reader insert in VB wit

Question

I am new to VB. I was trying to insert a record into access database with data reader. I was getting OVERFLOW error at runtime. I tried to google a lot but did not really solve the problem. My understanding is that overflow error occurs when we assign values that to variables that exceeds the maximum size of the data type. I do not see any such problems in my code.  Can anyone please help me to fix this? Very urgent.

Following is my code

 

Private Sub InsertRecord(ByVal CustomerID As Integer) 

      Dim RowArray() As String = Split(Me.lblRow.Text)

        Dim intPrice As Integer

        Dim decTotalPrice As Decimal

 

        Dim mySQL As String

        Dim Lastvalue As Integer = -1

 

        For i As Integer = 0 To RowArray.Length - 1

            If RowArray(i) <> "" Then

                Lastvalue += 1

                RowArray(Lastvalue) = RowArray(i)

            End If

        Next

 

        ReDim Preserve RowArray(Lastvalue)  

 

        Dim myConn As New OleDb.OleDbConnection(Mystrg)

        Dim myComm As New OleDb.OleDbCommand

 

        For i = 0 To RowArray.Length - 1

            mySQL = "Insert Into Cust_Date(CustomerID,LocID,DateID,RowID) Values (" _

                     & CustomerID & "," _

                     & intLocID & "," _

                     & CInt(LTrim(Replace(Mid(frmMain.cmbLoc.Text, InStrRev(frmMain.cmbLoc.Text, " ")), "/", ""))) & ",'" & Mid(RowArray(i), 1, 1) & "'" & ")"

 

            myComm.CommandText = mySQL

            myComm.Connection = myConn

 

          Try

            myConn.Open()

            myComm.ExecuteNonQuery()

           Catch ex As Exception

            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error")

           End Try

            myConn.Close()

        Next

End sub

 

Note: I am using Access database

 

The table structure for Cust_Date is as follows: 

 

CustLocID Auto Number    Size

CustomerID Number

LocID Number

DateID Number

RowID Text            5

 

Thanks

ylsv

Answer 1

I would hazard a guess that the exception occurs when you generate the DateID value. I would perform this operation outside of the SQL statement. I would also recommend using Command Parameters instead of inserting variables in the SQL statement.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

You have not provided a delimiter for Split to use, so You are likely getting back the string you put in, but... you never checked this array, its length or its content at all.

Are you trying to eliminate blank lines in the array? It would be simpler to use the array and its type to do that. Arrays have functions for this and string can tell when they are empty. This reminds me of an issue I see every day, where Basic programmers often use 1 as the starting index of an array, and that introduces a lot of one off errors into the code and the database tends to suffer along with the programmers. Ifthis code is to 'fix', or prevent that...you would be better served by writing code that takes the possibility of one off errors into account. Arrays have methods to check the upper and lower bounds of each dimension they have, and you would be ahead of the game if you checked theing againt Nothing, checked for zero-length and also...many routines that return numbers will return either 0, or -1 when they fail. That will cause issues with looping structures in your code. Another thing to bear in mind is that you can empty out an array by Redimming it with an index of -1, like this:

Redim myArray(-1) As Integer

What about the TextBox? It is multiline? If yes, then you need to Split based on vbCrLf.

Answer 3

I am not very good at using command parameters. Also tried this with just hard coding some value for date field. But the same problem.

Answer 4

I am not very good at using command parameters. Also tried this with just hard coding some value for date field. But the same problem.

On what line of code does the error occur? This info should help nail down the problem.

 

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 5

Yes, which line? You are relating an Exception that comes from System, but you are troublshooting it as if it wre a database issue. I don't think it is.

Why not use breakpoints, Try/Catch, MessageBoxes, or any of the other tools at your disposal?

Anyway, here is a snippet to explain better what I meant about those string and eliminating bad strings from an array...

        Dim theString As String = "one" & vbCrLf & "two" & vbCrLf & "three" & vbCrLf

        Dim theStringArray() As String = Split(theString, vbCrLf)

 

        ReDim Preserve theStringArray(theStringArray.Length + 3)

 

        theStringArray(4) = theStringArray(2)

        theStringArray(2) = theStringArray(1)

        theStringArray(1) = Nothing

        theStringArray(3) = ""

        theStringArray(5) = ""

 

        ' Now, array indices 1, 3 and 5 are empty, and indices 0,2 and 4 are not.

        ' Several indices contain Nothing, which will cause errors in defenseless code.

        ' In my for loop, I am eliminating all empty strings. Notice that String.Empty

        ' and Nothing are the same in the comparison below, which eliminates both.

        ' It is best to start at the end of the array because we are eliminating

        ' indices, which will confound the for loop unnecessarily.

 

        Dim theList As New List(Of String)(theStringArray)

 

        For cnt As Integer = theList.Count - 1 To 0 Step -1

            If theList(cnt) = String.Empty Then

                theList.RemoveAt(cnt)

            End If

        Next

 

        theStringArray = theList.ToArray

 

 

Answer 6

I am using the array value to store the rowid in the table.

Answer 7

myConn.Open() is giving me the error. When I am trying to debug it comes exception from there.

Answer 8

myConn.Open() is giving me the error. When I am trying to debug it comes exception from there.

Could you post your connection string?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 9

OleDbConnection.Open does not throw an Overflow Exception, it throws OleDbException or InvalidOperationException

I was not asking what you are using the array for; I was urging you to program in a way that will not cause issues like Overflow Exception

I apologize -- I cannot assist you.

Answer 10

Public Mystrg As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=CustLoc.mdb"

Answer 11

I don't see any issues with syntax. Can you post the trace from the Output window? There should be an exception message there.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 12

Thanks for your help. I am sorry. It is throwing out the exception at 

 myComm.ExecuteNonQuery()

Following is the error in detail

System.Data.OleDb.OleDbException was unhandled

  ErrorCode=-2147217833

  Message=Overflow

  Source=Microsoft JET Database Engine

  StackTrace:

       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)

       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 FinalProject.FrmSelectSeats.GetROWID(Int32 CustomerID) in C:\Data\Classes\visual basic 3\Final Project\FinalProject\FinalProject\Forms\FrmSelectSeats.vb:line 263

       at FinalProject.FrmSelectSeats.btnSave_Click(Object sender, EventArgs e) in C:\Data\Classes\visual basic 3\Final Project\FinalProject\FinalProject\Forms\FrmSelectSeats.vb:line 283

       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(IntPtr 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.RunDialog(Form form)

       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)

       at System.Windows.Forms.Form.ShowDialog()

       at FinalProject.frmMain.btnRequest_Click(Object sender, EventArgs e) in C:\Data\Classes\visual basic 3\Final Project\FinalProject\FinalProject\Forms\frmMain.vb:line 67

       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(IntPtr 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 FinalProject.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)

       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)

       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()

       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)

       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)

       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

       at System.Threading.ThreadHelper.ThreadStart()

  InnerException: 

Answer 13

OK, that almost nails it down. I would make certain that your Number columns in the Access database table are defined as Long Integer, at least for the DateID column. I'm pretty sure that one or more of those is causing the overflow problem but I can't be sure because I don't know the exact values that are being passed in.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 14

Except the custLocID (Long Integer) auto generated key field, all other numbers are defined as Integer. Could you please explain why dateid column creates problem. Here I was even trying to hard code and pass the value but still the same problem. DateID just contains date value but as a number ex: 11122010. Thanks. Please let me know if you still have any ideas in mind.

Answer 15

Except the custLocID (Long Integer) auto generated key field, all other numbers are defined as Integer. Could you please explain why dateid column creates problem. Here I was even trying to hard code and pass the value but still the same problem. DateID just contains date value but as a number ex: 11122010. Thanks. Please let me know if you still have any ideas in mind.

In the old 32-bit Visual Basic (6.0 or earlier) and VBA world, which would include Microsoft Access, an Integer is 16-bit. There is no way the value 11122010 will fit into a 16-bit signed integer (range: −32768 to 32767). The signed Long data type is 32-bits (range: −2,147,483,648 to 2,147,483,647), which is equivalent to a .NET Integer (which will accomodate the value you are attempting to store).

You could also just use a Date data type with a date value (which is actually stored as a number).

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 16

Thank you very much your help. Finally it works. I changed it to date value.

 

Thanks

ylsv

Answer 17

Sorry to bother you but I am not getting overflow error. But if I use date value why is it inserting zero in the table. This is how i pass the data.

 

INSERT INTO Cust_theater_Date ( CustID, LocID, DateID, RowID )

values (1,1, DateValue(Mid("The Hello World  1/20/2010",InStrRev("The Hello World  1/20/2010, " "))), '2F')

 

Could you please correct me if I am wrong some where?

Answer 18

What bothered me is that you said that you were getting the Overflow error, but when I offered the suggestion of improving your code, you flat out ignored me. It seems apparent to me that you do not check return values, or qualify the data you provide to library functions, then question why they do not work.

I can get past all of that; I'm not a complete a$$, but if you want me to help you honestly, I expect you to afford me the same respect and consideration that I provide for you.

Okay....you are trying to use a string for a date, and one that is formed using VB6 code, at that. (Again, not an issue, but...when you string the whole thing together, it is hard to see what is going on when it fails.)

        Dim theDate As Date = DateValue(Mid("The Hello World  1/20/2010", _

                                        InStrRev("The Hello World  1/20/2010", " ")))

        MessageBox.Show(theDate.ToString)

        Dim theDateString As String = _

            DateValue(Mid("The Hello World  1/20/2010", _

                      InStrRev("The Hello World  1/20/2010", " "))).ToString("d")

 

AAR, if you use the second form, it might work better, however....use the first one, so that you can see where the failure occurs. Otherwise, you are shooting in the blind, and that will not be fun at all.

Cheers!

 EDIT : I put a format specifier in the second form because you said you wanted a Date, and I see no time on your post, but...Date provides it by default on my machine. [This is why I use MessageBox, btw.]

Answer 19

It is not ignoring the suggestions. To be frank, I could not really understand your suggestion that well. I am at the very beginning of VB. I am just struggling to just get the things done. Anyway, I will look into that once and try to understand your suggestions. Thanks for your help once again.

Answer 20

I have a date as number in my table and I was trying to take a substring of the user input (entered as a string) from the form and convert it into a number to store it in the table.

Answer 21

Sorry to bother you but I am not getting overflow error. But if I use date value why is it inserting zero in the table. This is how i pass the data.

 

 

INSERT INTO Cust_theater_Date ( CustID, LocID, DateID, RowID )

values (1,1, DateValue(Mid("The Hello World  1/20/2010",InStrRev("The Hello World  1/20/2010, " "))), '2F')

 

 

Could you please correct me if I am wrong some where?

This is why you should use Command Parameters. Otherwise, you need to provide certain syntax to identify the data types. For a string it would be single quotes. For a date value it's pound sign characters. Below is an example:

INSERT INTO Table (NumCol, DateCol, StringCol) Values (666, #1/1/2011#, 'SomeStringValue')

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 22

BTW, below is an example which uses Command Parameters:

AccessConnection.Open()
AccessCommand = New System.Data.OleDb.OleDbCommand
AccessCommand.Connection = AccessConnection
AccessCommand.CommandText = "INSERT INTO Cust_theater_Date (CustID, LocID, DateID, RowID) VALUES (?,?,?,?)"
AccessCommand.CommandType = CommandType.Text

AccessCommand.Parameters.AddWithValue("Param1", CustIDValue)
AccessCommand.Parameters.AddWithValue("Param2", LocIDValue
AccessCommand.Parameters.AddWithValue("Param3", DateTime.Parse(DateIDText))
AccessCommand.Parameters.AddWithValue("Param4", RowIDValue)

AccessCommand.ExecuteNonQuery()

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 23

My sincere apologies -- I could have surmised that myself, has I not taken offense.

I was trying to point out to you that a Date, or DateTime object will also output the time when you ask it for a Date as a string. If your database wants only a date, and that must be a string, then you need to use the format specifier to indicate that you only want the data portion, and not the time.

In my last snippet, I first show how to generate a Date object using your code. Then, I use the ToString member to get a string from that Date object. Notice that the first one also includes the time. The Date object will default to midnight if you do not specify a time. The second one does all the processing inline, and outputs just the date as a string. This way of getting that value might appear to be much more efficient and quicker, but....it is probably not. Once you get the whole thing working, it might be a prudent optimization, but...right now, I think it is better to go in small increments.

I am not sure what your database is looking for, however...in my job, the database takes a Date object already, and there is no need to convert it to a date only.