I get error while running the code.below is the error.

Question

1.00/5 (2 votes)

See more:

System.Data.SqlClient.SqlException: 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.'

What I have tried:

private void SaveImportDataToDatabase(DataTable S2P5)
       {

           using (SqlConnection conn = new SqlConnection(@"Data Source=BBKUL035\SQLEXPRESS; Initial Catalog=HodB; User Id=server; Password=1234"))

           {

               conn.Open();
               foreach (DataRow importRow in S2P5.Rows)
               {

                   SqlCommand cmd = new SqlCommand
                       ("INSERT INTO S2P5 (DateTime,Miliseconds,MachineAutoStartStop,Pressure,Batch,UPCurrent,LPCurrent,LinearSensor,Load,SlidePosition,Step1,Step2,Step3,Step4,Step5,Step6,Step7,Step8,Step9,Step10,UPPlatePosition,LPPlatePosition,SunGearPosition,InternalGearPosition)" + "VALUES (@DateTime,@Miliseconds,@MachineAutoStartStop,@Pressure,@Batch,@UpCurrent,@LpCurrent,@LinearSensor,@Load,@SlidePosition,@Step1,@Step2,@Step3,@Step4,@Step5,@Step6,@Step7,@Step8,@Step9,@Step10,@UPPlatePosition,@LPPlatePosition,@SunGearPosition,@InternalGearPosition)", conn);


                   cmd.Parameters.AddWithValue("@DateTime", importRow["DateTime"]);
                   cmd.Parameters.AddWithValue("@Miliseconds", importRow["Miliseconds"]);
                   cmd.Parameters.AddWithValue("@MachineAutoStartStop", importRow["MachineAutoStartStop"]);
                   cmd.Parameters.AddWithValue("@Pressure", importRow["Pressure"]);
                   cmd.Parameters.AddWithValue("@Batch", importRow["Batch"]);
                   cmd.Parameters.AddWithValue("@UPCurrent", importRow["UPCurrent"]);
                   cmd.Parameters.AddWithValue("@LPCurrent", importRow["LPCurrent"]);
                   cmd.Parameters.AddWithValue("@LinearSensor", importRow["LinearSensor"]);
                   cmd.Parameters.AddWithValue("@Load", importRow["Load"]);
                   cmd.Parameters.AddWithValue("@SlidePosition", importRow["SlidePosition"]);
                   cmd.Parameters.AddWithValue("@Step1", importRow["Step1"]);
                   cmd.Parameters.AddWithValue("@Step2", importRow["Step2"]);
                   cmd.Parameters.AddWithValue("@Step3", importRow["Step3"]);
                   cmd.Parameters.AddWithValue("@Step4", importRow["Step4"]);
                   cmd.Parameters.AddWithValue("@Step5", importRow["Step5"]);
                   cmd.Parameters.AddWithValue("@Step6", importRow["Step6"]);
                   cmd.Parameters.AddWithValue("@Step7", importRow["Step7"]);
                   cmd.Parameters.AddWithValue("@Step8", importRow["Step8"]);
                   cmd.Parameters.AddWithValue("@Step9", importRow["Step9"]);
                   cmd.Parameters.AddWithValue("@Step10", importRow["Step10"]);
                   cmd.Parameters.AddWithValue("@UPPlatePosition", importRow["UPPlatePosition"]);
                   cmd.Parameters.AddWithValue("@LPPlatePosition", importRow["LPPlatePosition"]);
                   cmd.Parameters.AddWithValue("@SunGearPosition", importRow["SunGearPosition"]);
                   cmd.Parameters.AddWithValue("@InternalGearPosition", importRow["InternalGearPosition"]);


                   cmd.ExecuteNonQuery();
               }

           }

Posted 16-Nov-22 17:06pm

dhivyah jaiya

Updated 21-Nov-22 16:55pm

v3

Add a Solution

Comments

Richard Deeming 17-Nov-22 3:58am

Firstly, almost no application should ever connect to SQL Server using the sa account. That's an unrestricted superuser account, so any mistakes in your application could result in the complete destruction of your server.

Richard Deeming 17-Nov-22 3:59am

Secondly, if that's your real sa password that you've just posted to a public forum, then change it immediately. And this time, pick a strong password, not something that a hacker could guess within two seconds.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Graeme_Grant · Accepted Answer · 2022-11-16T17:37:00

System.Data.SqlClient.SqlException: 'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Yep. nvarchar is an Sql Alphanumeric field (aka string) and you are trying to push DateTime into it.

The Sql Field should be a DateTime field. If you want to convert a DateTime into a string you need to do it in your code. This is not an optimal solution. You should change the Sql Field to a DataTime field. This is important as string are handled differently to DateTime, especially when it comes to sorting and filtering.

Dave Kreskowiak · Accepted Answer · 2022-11-17T02:31:00

The problem is you don't understand how AddWithValue works.

You're giving those calls two things, the name of a parameter and a value. Well, how does AddWithValue know how to format the data to pass to SQL? It does not look at the data itself, but looks at the type of the data, string, int, DateTime, ...

Since you're passing in nothing but strings to every call to AddWithValue, every parameter your code is generating is being treated as a string to SQL, or what SQL calls an NVARCHAR type. What you have to do to fix this is convert the data you're passing in to an appropriate type before passing it to AddWithValue.

C#

cmd.Parameters.AddWithValue("@DateTime", importRow["DateTime"]);

becomes

// I'm assuming your data can even be cast to a DateTime type
cmd.Parameters.AddWithValue("@DateTime", (DateTime)importRow["DateTime"]);

I get error while running the code.below is the error.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0