Error- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value- sql

Question

I am a secondary school student currently doing a software development project involving databases with have little to no knowledge of sql, and have so far sufficed using a guide book given to me, But I cannot shake this error. I have linked what are to me the required screenshots, but if I have left any out I would be glad to know. I have thoroughly searched for this error both on this website and others, and have decided this is either a different case then what normally creates this error, or my technical knowledge is so limited that I cannot tell this difference. I am aware that no doubt the code I have written is poor, but do not have the time for a complete revamp of everything.(I am not allowed to add on images directly to SO appears to have linked them below)

This is the error ......

Method being used part 1 .......

method being used part 2 ........

code written within the data entry form, should be self explanatory ......

The database table the data is intended to be entered into, named Order .

Please have a look for a dumb school student, and if you have any other advice please give it if you would.

Thanks for the edit and formatting advice, as you can see I'm not really the smartest tool in the shed.

Code for the Method:

public static int AddNewOrder(string OrderAddress, DateTime OrderDate, int 
CustID, String OrderDeliveryDate, bool Delivery)
        {
                using (SqlConnection connection = new 
SqlConnection(_connectionString))
                {
                    connection.Open();
                    string sqlQuery = string.Format("INSERT INTO [Order]
(OrderAddress, OrderDate, CustID, OrderDeliveryDate, Delivery)VALUES('{0}', 
'{1}', '{2}', '{3}','{4}')", OrderAddress, OrderDate, CustID, 
OrderDeliveryDate, Delivery);
                    SqlCommand insertCommand = new 
SqlCommand(sqlQuery,connection);
                    int rowsAffected = insertCommand.ExecuteNonQuery();
                    connection.Close();
                    return rowsAffected;
                }
        }

Code Within the data entry form:

 bool Delivery;
        int CustID;
       string Address;
        DateTime OrderDate;
        DateTime OrderDeliveryDate;

    private void buttonCreateOrder_Click(object sender, EventArgs e)
    {
        Delivery = checkBoxDelivery.Checked;

        CustID = int.Parse(textBoxCustID.Text);

        Address = textBoxOrderAddress.Text;
        OrderDate = DateTime.Now;
        OrderDeliveryDate = dtpOrderDeadlinDate.Value;

        int rowsAffected = DAL.OrderFirstFormDAL.AddNewOrder(Address, OrderDate, CustID, OrderDeliveryDate.ToString("yyyy-MM-dd HH:mm:ss"), Delivery);

Try not to use images at all. Paste formatted text for code, data, errors messages, etc. The editor has mark-down formatting specifically for that. — MatBailie, Feb 14 '18 at 13:52
Please help us help a dumb school student. Post code that we can utilize to procure an answer. — Jacob H, Feb 14 '18 at 13:53
Well your title says it all. You are using a string to write values in a date field. Strings are not dates. — Steve, Feb 14 '18 at 13:56
@ErayBalkanli it was kind of a joke considering the poster used the identical phrase I did. I know SO is a super serious kind of place and all... — Jacob H, Feb 14 '18 at 13:59
You should really read up on using *parameters*. Those let you keep your data in *appropriate* data types rather than treating everything as a string. (They're also a *very good* habit to get into generally). That being said, I'd wager that you've got a `DateTime` value that's being left set at its default value (`01/01/0001`). Which isn't supported by SQL Server's `datetime` data type. Switching to `datetime2` would stop the error *message* but not the logical error that's occurring where that value is being passed. You need to learn to *debug* your code which isn't easily taught in an answer — Damien_The_Unbeliever, Feb 14 '18 at 14:13
Now it is clear where your error is. You are passing two strings when the database expectes two dates. You NEVER write database code in this way because there is no way to propertly handle dates, string with quotes, decimals. You should always use parameters to specify values to be written to the database. By the way, the error message points you exactly to the problem. The db engine is not able to make sense from your string (nvarchar) while it expects a date. — Steve, Feb 14 '18 at 14:59
I'm sorry but I know so little about sql and parameters, could you maybe point in the right way as to how I should actually fix this? Thanks for looking at it at all by the way. — HomoErectus, Feb 14 '18 at 15:19
When trying to use solely date time instead of string yet the error remains exactly the same — HomoErectus, Feb 14 '18 at 15:41
You can look at this answer. I think it contains a summary on how to solve your problem https://stackoverflow.com/questions/21498809/insert-date-in-dd-mm-yyyy-format/21498850#21498850 But just replace the call AddWithValue to the more precise SqlCommand.Add(name, type).Value = ? — Steve, Feb 14 '18 at 15:42
One final question, what exactly do you mean by type in the sqlcommand.Add statement, and also in the answer you have thankfully shown me, are the @ signs meant to be there or are they some sort of formatting tool used on this website, I have never seen anything like that before — HomoErectus, Feb 14 '18 at 16:19
Also, where exactly should I put the code for the answer? I currently have my Sqlcommand statement in a seperate form to the data entry form, and when attempting to "move it over" It isnt working so well — HomoErectus, Feb 14 '18 at 17:09

score 0 · Answer 1 · answered Feb 14 '18 at 13:55

0

For AddNewOrder function, OrderDeliveryDate is defined as a String. However, just above your ButtonCreateOrder_Click function, it is defined as a datetime. Something is definitely wrong for OrderDeliveryDate parameter and I believe you are using a date that not existing (like month value is more than 12 or day value is more than 30/31 based on the month)

answered Feb 14 '18 at 13:55

Eray Balkanli

7,752
11
48
82

I'm selecting the date using a date time picker, could the format be an issue? ATM I have no custom format set. – HomoErectus Feb 14 '18 at 14:08
I don't see how the datepicker is developed, but debug your code and you will most likely see that the error is caused by somewhere you are using OrderDeliveryDate variable, especially if you are doing a conversion at some point. – Eray Balkanli Feb 14 '18 at 14:11
I changed the AddNewOrder function to take a DateTime instead and inputted the OrderDeliveryDate variable to the method in datetime instead, yet the error remains exactly the same, though I may have misunderstood the intent of your advice. – HomoErectus Feb 14 '18 at 14:22
While debugging, can you please check the date and its format coming from datetimepicker? For both OrderDate and OrderDeliveryDate parameters. Are they always exactly same? Also, may I ask which date are you choosing? – Eray Balkanli Feb 14 '18 at 14:24
For the OrderDate I'm just setting it to the value of DateTime.Now, which in this case was '14/02/2018 14:19:41', and for OrderDeliveryDate I am selecting 15th of February from the dtp, which comes out as '15/02/2018 14:19:34' – HomoErectus Feb 14 '18 at 14:28
What is happening when you choose 10th of February for OrderDeliveryDate, same error? – Eray Balkanli Feb 14 '18 at 14:28

Error- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value- sql

1 Answers1