Click here to Skip to main content
15,744,304 members
Please Sign up or sign in to vote.
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"))


               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"]);


Updated 21-Nov-22 16:55pm
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.

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.
cmd.Parameters.AddWithValue("@DateTime", importRow["DateTime"]);


// I'm assuming your data can even be cast to a DateTime type
cmd.Parameters.AddWithValue("@DateTime", (DateTime)importRow["DateTime"]);
Share this answer
dhivyah jaiya 21-Nov-22 20:41pm    
yes data is in datatime type. But get error [System.InvalidCastException: 'Specified cast is not valid.'] after i apply this code line [cmd.Parameters.AddWithValue("@DateTime", (DateTime)importRow["DateTime"]);
Dave Kreskowiak 21-Nov-22 20:46pm    
That's because the data in the importRow["DateTime"] field is either not a DateTime value or is null. You also cannot cast a string to a DateTime, so it depends on EXACTLY what is in that importRow["DateTime"] field.

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.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900