Arithmetic overflow error converting expression to data type datetime.

[primagic]

I am getting this error when I am trying to insert a new record to an sql table.

Arithmetic overflow error converting expression to data type datetime.

Any ideas on how to resolve this issue?

My code for inserting the record is:

Dim conOrders As SqlConnection
        Dim strInsert As String
        Dim cmdInsert As SqlCommand



        conOrders = New SqlConnection("Server=(local);UID=sa;PWD=admin2010;database=PhoenixSQL")
        strInsert = "Insert dbo.Orders (TraderID, OrderNumber, InvoiceNumber, InvoiceDate, Carriage) Values (@TraderID, @OrderNumber, @InvoiceNumber, @InvoiceDate, @Carriage)"
        cmdInsert = New SqlCommand(strInsert, conOrders)
        cmdInsert.Parameters.AddWithValue("@TraderID", HiddenTraderID.Value)
        cmdInsert.Parameters.AddWithValue("@OrderNumber", txtOrderNumber.Text)
        cmdInsert.Parameters.AddWithValue("@InvoiceNumber", txtInvoiceNumber.Text)
        cmdInsert.Parameters.AddWithValue("@InvoiceDate", txtInvoiceDate.Text)
        cmdInsert.Parameters.AddWithValue("@Carriage", txtCarriage.Text)
        conOrders.Open()
        cmdInsert.ExecuteNonQuery()
        conOrders.Close()

 

[jbenson001]

Did you trace through the code? Obviously, the date entered is not a valid date or it to large/small for the datatime column. There are different types of datetime columns in SQL Server. Also, are you validating your input before you Insert? I would use a calendar control to let the user pick the date from that, this way you know it is valid.

 

[jmeckley]

and parse the date from the control to ensure it is a date.

DateTime date;
if(DateTime.TryParse(InvoiceDate.Text, out date) == false)
{
   throw new InvalidOperationException("invoice date is not valid");
}

this is the brute force method. for something more elegant I would create a DTO from the user control, validate the DTO and then then proceed with execution.

A validation library like FluentValidation or Castle.Components.Validation work very well.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[primagic]

Thanks for your replies. I managed to sort it out by adding Language=British to the SQL Connection

 

[jbenson001]

You still better validate the data that is entered by the user. Eventually they will enter somehting that will crash your app.

 

[jmeckley]

along with that... never trust the client. you can validate on the client to reduce round trips, but you still need to validate on the server.

Jason Meckley
Programmer

faq855-7190
faq732-7259