[RESOLVED] An overflow occurred while converting to datetime

Printable View

Nov 23rd, 2010, 08:00 PM
kevin_10987

[RESOLVED] An overflow occurred while converting to datetime

Here is the error that is getting thrown:

Code:

System.Data.SqlServerCe.SqlCeException was unhandled HResult=-2147467259 Message="An overflow occurred while converting to datetime." NativeError=25933 Source="SQL Server Compact ADO.NET Data Provider" StackTrace: at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor) at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options) at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() at BloodSugarRecorder2.bloodsugarDataSetTableAdapters.recordTableAdapter.InsertQuery(String name, Nullable`1 adate, Nullable`1 atime, Nullable`1 areading, String anote) in C:\Documents and Settings\Owner\My Documents\Visual Studio 2008\Projects\BloodSugarRecorder2\BloodSugarRecorder2\bloodsugarDataSet.Designer.vb:line 1035 at BloodSugarRecorder2.Form1.cmdAdd_Click(Object sender, EventArgs e) in C:\Documents and Settings\Owner\My Documents\Visual Studio 2008\Projects\BloodSugarRecorder2\BloodSugarRecorder2\Form1.vb:line 18 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 BloodSugarRecorder2.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at System.AppDomain._nExecuteAssembly(Assembly 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) at System.Threading.ThreadHelper.ThreadStart() InnerException:

Re: An overflow occurred while converting to datetime

Here is the code for the button click procedure:

VB Code:

Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
 
        Dim howell1 As New howell(txtName.Text, CType(txtDate.Text, Date), _
                                  CType(txtTime.Text, Date), _
                                  txtBSReading.Text, txtNotes.Text)
 
        Dim ablooddataset As New bloodsugarDataSet
        Dim aBloodTableAdapter As New bloodsugarDataSetTableAdapters.recordTableAdapter
 
        aBloodTableAdapter.InsertQuery(howell1.GetName, howell1.GetDate, _
                              howell1.GetTime, _
                              howell1.GetBloodSugarReading, _
                              howell1.GetNote)
 
        MsgBox("Blood Sugar has been recorded.")
 
    End Sub

Here is my form1.designer class

VB Code:

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
    Inherits System.Windows.Forms.Form
 
    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub
 
    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer
 
    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Me.Label1 = New System.Windows.Forms.Label
        Me.Label2 = New System.Windows.Forms.Label
        Me.Label3 = New System.Windows.Forms.Label
        Me.Label4 = New System.Windows.Forms.Label
        Me.Label5 = New System.Windows.Forms.Label
        Me.txtDate = New System.Windows.Forms.TextBox
        Me.txtTime = New System.Windows.Forms.TextBox
        Me.txtBSReading = New System.Windows.Forms.TextBox
        Me.txtNotes = New System.Windows.Forms.TextBox
        Me.GroupBox1 = New System.Windows.Forms.GroupBox
        Me.cboboxBSReading = New System.Windows.Forms.ComboBox
        Me.Label6 = New System.Windows.Forms.Label
        Me.txtName = New System.Windows.Forms.TextBox
        Me.DataGridView1 = New System.Windows.Forms.DataGridView
        Me.GroupBox2 = New System.Windows.Forms.GroupBox
        Me.cmdCopyAll = New System.Windows.Forms.Button
        Me.cmdCopy = New System.Windows.Forms.Button
        Me.cmdAdd = New System.Windows.Forms.Button
        Me.cmdSearch = New System.Windows.Forms.Button
        Me.cmdDelete = New System.Windows.Forms.Button
        Me.cmdViewAllRecords = New System.Windows.Forms.Button
        Me.BindingSource1 = New System.Windows.Forms.BindingSource(Me.components)
        Me.BloodsugarDataSet = New BloodSugarRecorder2.bloodsugarDataSet
        Me.GroupBox1.SuspendLayout()
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.GroupBox2.SuspendLayout()
        CType(Me.BindingSource1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.BloodsugarDataSet, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Label1
        '
        Me.Label1.AutoSize = True
        Me.Label1.Location = New System.Drawing.Point(21, 26)
        Me.Label1.Name = "Label1"
        Me.Label1.Size = New System.Drawing.Size(44, 13)
        Me.Label1.TabIndex = 0
        Me.Label1.Text = "Name:  "
        '
        'Label2
        '
        Me.Label2.AutoSize = True
        Me.Label2.Location = New System.Drawing.Point(11, 44)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(39, 13)
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "Date:  "
        '
        'Label3
        '
        Me.Label3.AutoSize = True
        Me.Label3.Location = New System.Drawing.Point(11, 83)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(39, 13)
        Me.Label3.TabIndex = 2
        Me.Label3.Text = "Time:  "
        '
        'Label4
        '
        Me.Label4.AutoSize = True
        Me.Label4.Location = New System.Drawing.Point(153, 44)
        Me.Label4.Name = "Label4"
        Me.Label4.Size = New System.Drawing.Size(117, 13)
        Me.Label4.TabIndex = 3
        Me.Label4.Text = "Blood Sugar Reading:  "
        '
        'Label5
        '
        Me.Label5.AutoSize = True
        Me.Label5.Location = New System.Drawing.Point(153, 90)
        Me.Label5.Name = "Label5"
        Me.Label5.Size = New System.Drawing.Size(44, 13)
        Me.Label5.TabIndex = 4
        Me.Label5.Text = "Notes:  "
        '
        'txtDate
        '
        Me.txtDate.Location = New System.Drawing.Point(56, 41)
        Me.txtDate.Name = "txtDate"
        Me.txtDate.Size = New System.Drawing.Size(61, 20)
        Me.txtDate.TabIndex = 5
        '
        'txtTime
        '
        Me.txtTime.Location = New System.Drawing.Point(56, 83)
        Me.txtTime.Name = "txtTime"
        Me.txtTime.Size = New System.Drawing.Size(61, 20)
        Me.txtTime.TabIndex = 6
        '
        'txtBSReading
        '
        Me.txtBSReading.Location = New System.Drawing.Point(276, 41)
        Me.txtBSReading.Name = "txtBSReading"
        Me.txtBSReading.Size = New System.Drawing.Size(58, 20)
        Me.txtBSReading.TabIndex = 7
        '
        'txtNotes
        '
        Me.txtNotes.Location = New System.Drawing.Point(203, 72)
        Me.txtNotes.Multiline = True
        Me.txtNotes.Name = "txtNotes"
        Me.txtNotes.ScrollBars = System.Windows.Forms.ScrollBars.Vertical
        Me.txtNotes.Size = New System.Drawing.Size(185, 59)
        Me.txtNotes.TabIndex = 8

Re: An overflow occurred while converting to datetime

VB Code:

'GroupBox1
        '
        Me.GroupBox1.Controls.Add(Me.cboboxBSReading)
        Me.GroupBox1.Controls.Add(Me.Label6)
        Me.GroupBox1.Controls.Add(Me.Label2)
        Me.GroupBox1.Controls.Add(Me.Label5)
        Me.GroupBox1.Controls.Add(Me.txtNotes)
        Me.GroupBox1.Controls.Add(Me.txtDate)
        Me.GroupBox1.Controls.Add(Me.txtBSReading)
        Me.GroupBox1.Controls.Add(Me.Label3)
        Me.GroupBox1.Controls.Add(Me.txtTime)
        Me.GroupBox1.Controls.Add(Me.Label4)
        Me.GroupBox1.Location = New System.Drawing.Point(24, 53)
        Me.GroupBox1.Name = "GroupBox1"
        Me.GroupBox1.Size = New System.Drawing.Size(500, 141)
        Me.GroupBox1.TabIndex = 9
        Me.GroupBox1.TabStop = False
        Me.GroupBox1.Text = "Input"
        '
        'cboboxBSReading
        '
        Me.cboboxBSReading.FormattingEnabled = True
        Me.cboboxBSReading.Location = New System.Drawing.Point(380, 41)
        Me.cboboxBSReading.Name = "cboboxBSReading"
        Me.cboboxBSReading.Size = New System.Drawing.Size(106, 21)
        Me.cboboxBSReading.TabIndex = 10
        '
        'Label6
        '
        Me.Label6.AutoSize = True
        Me.Label6.Location = New System.Drawing.Point(351, 44)
        Me.Label6.Name = "Label6"
        Me.Label6.Size = New System.Drawing.Size(23, 13)
        Me.Label6.TabIndex = 10
        Me.Label6.Text = "OR"
        '
        'txtName
        '
        Me.txtName.Location = New System.Drawing.Point(61, 23)
        Me.txtName.Name = "txtName"
        Me.txtName.Size = New System.Drawing.Size(137, 20)
        Me.txtName.TabIndex = 10
        '
        'DataGridView1
        '
        Me.DataGridView1.AutoGenerateColumns = False
        Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
        Me.DataGridView1.DataSource = Me.BindingSource1
        Me.DataGridView1.Location = New System.Drawing.Point(12, 270)
        Me.DataGridView1.Name = "DataGridView1"
        Me.DataGridView1.Size = New System.Drawing.Size(512, 112)
        Me.DataGridView1.TabIndex = 11
        '
        'GroupBox2
        '
        Me.GroupBox2.Controls.Add(Me.cmdCopyAll)
        Me.GroupBox2.Controls.Add(Me.cmdCopy)
        Me.GroupBox2.Controls.Add(Me.cmdAdd)
        Me.GroupBox2.Controls.Add(Me.cmdSearch)
        Me.GroupBox2.Controls.Add(Me.cmdDelete)
        Me.GroupBox2.Location = New System.Drawing.Point(24, 200)
        Me.GroupBox2.Name = "GroupBox2"
        Me.GroupBox2.Size = New System.Drawing.Size(500, 64)
        Me.GroupBox2.TabIndex = 12
        Me.GroupBox2.TabStop = False
        Me.GroupBox2.Text = "Actions"
        '
        'cmdCopyAll
        '
        Me.cmdCopyAll.Location = New System.Drawing.Point(390, 19)
        Me.cmdCopyAll.Name = "cmdCopyAll"
        Me.cmdCopyAll.Size = New System.Drawing.Size(75, 39)
        Me.cmdCopyAll.TabIndex = 17
        Me.cmdCopyAll.Text = "Copy All"
        Me.cmdCopyAll.UseVisualStyleBackColor = True
        '
        'cmdCopy
        '
        Me.cmdCopy.Location = New System.Drawing.Point(299, 19)
        Me.cmdCopy.Name = "cmdCopy"
        Me.cmdCopy.Size = New System.Drawing.Size(75, 39)
        Me.cmdCopy.TabIndex = 16
        Me.cmdCopy.Text = "Copy"
        Me.cmdCopy.UseVisualStyleBackColor = True
        '
        'cmdAdd
        '
        Me.cmdAdd.Location = New System.Drawing.Point(208, 19)
        Me.cmdAdd.Name = "cmdAdd"
        Me.cmdAdd.Size = New System.Drawing.Size(75, 39)
        Me.cmdAdd.TabIndex = 15
        Me.cmdAdd.Text = "Add"
        Me.cmdAdd.UseVisualStyleBackColor = True
        '
        'cmdSearch
        '
        Me.cmdSearch.Location = New System.Drawing.Point(117, 19)
        Me.cmdSearch.Name = "cmdSearch"
        Me.cmdSearch.Size = New System.Drawing.Size(75, 39)
        Me.cmdSearch.TabIndex = 14
        Me.cmdSearch.Text = "Search"
        Me.cmdSearch.UseVisualStyleBackColor = True
        '
        'cmdDelete
        '
        Me.cmdDelete.Location = New System.Drawing.Point(26, 19)
        Me.cmdDelete.Name = "cmdDelete"
        Me.cmdDelete.Size = New System.Drawing.Size(75, 39)
        Me.cmdDelete.TabIndex = 13
        Me.cmdDelete.Text = "Delete"
        Me.cmdDelete.UseVisualStyleBackColor = True
        '
        'cmdViewAllRecords
        '
        Me.cmdViewAllRecords.Location = New System.Drawing.Point(414, 8)
        Me.cmdViewAllRecords.Name = "cmdViewAllRecords"
        Me.cmdViewAllRecords.Size = New System.Drawing.Size(110, 39)
        Me.cmdViewAllRecords.TabIndex = 13
        Me.cmdViewAllRecords.Text = "View All Records"
        Me.cmdViewAllRecords.UseVisualStyleBackColor = True
        '
        'BindingSource1
        '
        Me.BindingSource1.DataSource = Me.BloodsugarDataSet
        Me.BindingSource1.Position = 0
        '
        'BloodsugarDataSet
        '
        Me.BloodsugarDataSet.DataSetName = "bloodsugarDataSet"
        Me.BloodsugarDataSet.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema
        '
        'Form1
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(547, 394)
        Me.Controls.Add(Me.cmdViewAllRecords)
        Me.Controls.Add(Me.GroupBox2)
        Me.Controls.Add(Me.DataGridView1)
        Me.Controls.Add(Me.txtName)
        Me.Controls.Add(Me.GroupBox1)
        Me.Controls.Add(Me.Label1)
        Me.Name = "Form1"
        Me.Text = "Blood Sugar Recorder"
        Me.GroupBox1.ResumeLayout(False)
        Me.GroupBox1.PerformLayout()
        CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
        Me.GroupBox2.ResumeLayout(False)
        CType(Me.BindingSource1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.BloodsugarDataSet, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)
        Me.PerformLayout()
 
    End Sub
    Friend WithEvents Label1 As System.Windows.Forms.Label
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents Label4 As System.Windows.Forms.Label
    Friend WithEvents Label5 As System.Windows.Forms.Label
    Friend WithEvents txtDate As System.Windows.Forms.TextBox
    Friend WithEvents txtTime As System.Windows.Forms.TextBox
    Friend WithEvents txtBSReading As System.Windows.Forms.TextBox
    Friend WithEvents txtNotes As System.Windows.Forms.TextBox
    Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
    Friend WithEvents Label6 As System.Windows.Forms.Label
    Friend WithEvents cboboxBSReading As System.Windows.Forms.ComboBox
    Friend WithEvents txtName As System.Windows.Forms.TextBox
    Friend WithEvents DataGridView1 As System.Windows.Forms.DataGridView
    Friend WithEvents GroupBox2 As System.Windows.Forms.GroupBox
    Friend WithEvents cmdCopyAll As System.Windows.Forms.Button
    Friend WithEvents cmdCopy As System.Windows.Forms.Button
    Friend WithEvents cmdAdd As System.Windows.Forms.Button
    Friend WithEvents cmdSearch As System.Windows.Forms.Button
    Friend WithEvents cmdDelete As System.Windows.Forms.Button
    Friend WithEvents BloodsugarDataSetBindingSource As System.Windows.Forms.BindingSource
    Friend WithEvents cmdViewAllRecords As System.Windows.Forms.Button
    Friend WithEvents BindingSource1 As System.Windows.Forms.BindingSource
    Friend WithEvents BloodsugarDataSet As BloodSugarRecorder2.bloodsugarDataSet
 
End Class

The error is being thrown at the add button click procedure. I am creating queries through a TableAdapter because I don't know much about database programming.

The two date variables that are stored in the database, the parameter's index for both date variables, and the variables in my business class are all using either date or datetime variable type.

When I create the class that creates all the information to be added to the database; I have to perform a conversion from string to date by using the CType function. But it seems like the ms sql database programming library is causing the error.

Hopefully, somebody can help me figure out why I'm getting an overflow error while converting to datetime.

Thanks!

Nov 23rd, 2010, 08:36 PM
Zeelia

Re: An overflow occurred while converting to datetime
Hi!

Just want to say this quickly; when asking for help, you should only include code relevant to the problem. As for now, I didn't read the whole code cause of the irrelevance.

However, tiny questions which might point you to the right direction:
1. Is your database using the same datetime format as your application?
Remember that different countries use different formats.
2. Did you use the CType like this?
vb.net Code:
```
CType([Enum].Parse(GetType(DayOfWeek), 1), String) 'Monday
```
(Just an example with DayOfWeek)
Nov 23rd, 2010, 08:57 PM
kevin_10987

Re: An overflow occurred while converting to datetime

I just thought there was more of an issue than where the line of code was getting caught at.

No, I didn't use the CType like that. I wrote it like CType(txtDate.text, Date) and CType(txtTime.text, Date), where the date was equal to 11/20/2010 and the time was equal to "3:30 PM". The date and time get set into the text box by using a statement like that, Date.now.toString("MM/dd/yyyy") and the time get formatted as such, Date.now.toString("hh:mm tt")

As far as I can see the database data type is set to datetime, the parameters for the queries are set to datetime, and in my code I'm using date.

Sorry about including too much code. I just thought the issue was somewhere else and didn't know where the problem was lying.
Nov 26th, 2010, 09:20 PM
kevin_10987

Re: An overflow occurred while converting to datetime

I figured out the problem. The number of characters for the date/time in a MS SQL is 8 characters. "11/26/2010" is more than 8 characters so that was causing an error.

Well, I guess I know of one additional thing to add to the things that I look for to avoid errors in the future. Make sure that I am not trying to save more character data in a column than has been allowed by the DBMS.

Thanks for replying Zeelia. You solved my problem with "1. Is your database using the same datetime format as your application?" because that was indeed the problem (Too many characters!).