Problem with converting to Time type

Question

Hello I have a datatable which contains data from a csv file and also from this data table the data should be inserted into sql table One of the fields in the datatable is Time and I have problem when trying to insert it in the sql table In the sql table it is of format Time (not datatime) Here is my code: SqlParameter timefromParameter = new SqlParameter (@TimeFromSqlDbType.DateTime ); DateTime timefrom = DateTime.ParseExact(Convert.ToString(row[Time-from]) MM/dd/yyyy HH:mm null); insertTable.Parameters.AddWithValue(@TimeFrom timefrom.TimeOfDay); but I get the following error: An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code Additional information: String was not recognized as a valid DateTime. on line: DateTime timefrom = DateTime.ParseExact(Convert.ToString(row[Time-from]) MM/dd/yyyy HH:mm null); Can anybody help me please? Thanks

Vulpes · Answer

Well I'm pleased (and relieved!) that you've finally solved it though if all it was is the time format the solution seems to go against all the published advice. Whilst it's certainly true that if you're inserting a TIME column directly in SQL Server the format should be hh:mm:ss if you use a TimeSpan to set the parameter then it shouldn't matter what the original format was in the text file because the ADO.NET infrastructure should present the database with the correct format. It appears that you've used a string instead of a TimeSpan and so I assume you must have altered the file to the correct format manually. Anyway whether we understand it or not the important thing is that it now works and that persistence has had its reward:)

Nel · Answer

I put Convert.ToString and it's good now thanks to both of youy Let's check the rest of the code now Vulpes:) I will let you know soon

Vulpes · Answer

What does row[Time-from] actually contain when the error occurs?

Nel · Answer

Yes thank you to both of you:)

Nel · Answer

l solved both the problem with the data and the problem with time format Here is the whole code: protected void btnImport_Click( object sender EventArgs e) { int nRows = 0; dt.Clear(); // int r = 1; try { conn.Open(); var line = reader.ReadLine(); var values = line.Split(delimit); foreach ( var dc in values) { dt.Columns.Add(dc); } reader = new StreamReader ( File .OpenRead( @\path\Incidents1.csv )); while (!reader.EndOfStream) { int k = 0; values = reader.ReadLine().Split( '' ); row = dt.NewRow(); while (k { row[k] = values[k]; k++; } if (nRows != 0) { dt.Rows.Add(row); } nRows++; } GridView2.DataSource = dt; GridView2.DataBind(); decimal latitude; decimal longitude; SqlCommand insertTable = new SqlCommand ( null  conn); insertTable.CommandText = INSERT INTO TrafInc1 (Datei Dayi TimeFrom PoD1 TimeTo PoD2 Location IncidentType Direction LaneBlockage CCTV SignalTimingChanges Benefits Latitude Longitude) + VALUES (@Date @Day @TimeFrom @PoD1 @TimeTo @PoD2 @Location @IncidentType @Direction @LaneBlockage@CCTV @SignalTimingChanges @Benefits @Latitude @Longitude) ; SqlParameter dateParameter = new SqlParameter ( @Date  SqlDbType .Date); dateParameter.Direction = ParameterDirection .Input; // dateParameter.Value = Convert.ToDateTime(row[Date]); SqlParameter dayParameter = new SqlParameter ( @Day  SqlDbType .NVarChar 15); dayParameter.Direction = ParameterDirection .Input; // dayParameter.Value = row[Day]; SqlParameter timefromParameter = new SqlParameter ( @TimeFrom  SqlDbType .Time 7); timefromParameter.Direction = ParameterDirection .Input; // timefromParameter.Value = row[Time-from]; SqlParameter pod1Parameter = new SqlParameter ( @PoD1  SqlDbType .NVarChar 2); pod1Parameter.Direction = ParameterDirection .Input; // pod1Parameter.Value = row[PoD1]; SqlParameter timetoParameter = new SqlParameter ( @TimeTo  SqlDbType .Time 7); timetoParameter.Direction = ParameterDirection .Input; SqlParameter pod2Parameter = new SqlParameter ( @PoD2  SqlDbType .NVarChar 2); pod2Parameter.Direction = ParameterDirection .Input; SqlParameter nameParameter2 = new SqlParameter ( @Location  SqlDbType .NVarChar 60); nameParameter2.Direction = ParameterDirection .Input; SqlParameter nameParameter3 = new SqlParameter ( @IncidentType  SqlDbType .NVarChar 50); nameParameter3.Direction = ParameterDirection .Input; SqlParameter nameParameter4 = new SqlParameter ( @Direction  SqlDbType .NVarChar 60); nameParameter4.Direction = ParameterDirection .Input; SqlParameter nameParameter5 = new SqlParameter ( @LaneBlockage  SqlDbType .NVarChar 60); nameParameter5.Direction = ParameterDirection .Input; SqlParameter nameParameter6 = new SqlParameter ( @CCTV  SqlDbType .NVarChar 60); nameParameter6.Direction = ParameterDirection .Input; SqlParameter nameParameter7 = new SqlParameter ( @SignalTimingChanges  SqlDbType .NVarChar 100); nameParameter7.Direction = ParameterDirection .Input; SqlParameter nameParameter8 = new SqlParameter ( @Benefits  SqlDbType .NVarChar 100); nameParameter8.Direction = ParameterDirection .Input; SqlParameter nameParameter9 = new SqlParameter ( @Latitude  SqlDbType .Decimal 18); nameParameter9.Direction = ParameterDirection .Input; SqlParameter nameParameter10 = new SqlParameter ( @Longitude  SqlDbType .Decimal 18); nameParameter10.Direction = ParameterDirection .Input; insertTable.Parameters.Add(dateParameter); //row[Date] insertTable.Parameters.Add(dayParameter); //was with addwithvalue //!!!!!!!!!!!!!!!!TIME MUST BE ENTERED IN THE TABLE IN FORMAT HH:MM CHECKTHIS FIRST IN THE TABLLE BEFORE RUNNING THE PROGRAM!!!!!!!!!!!!!!! insertTable.Parameters.Add(timefromParameter); insertTable.Parameters.Add(pod1Parameter); //was with addwithvalue insertTable.Parameters.Add(timetoParameter); insertTable.Parameters.Add(pod2Parameter); insertTable.Parameters.Add(nameParameter2); insertTable.Parameters.Add(nameParameter3); insertTable.Parameters.Add(nameParameter4); insertTable.Parameters.Add(nameParameter5); insertTable.Parameters.Add(nameParameter6); insertTable.Parameters.Add(nameParameter7); insertTable.Parameters.Add(nameParameter8); insertTable.Parameters.Add(nameParameter9); insertTable.Parameters[ @Latitude ].Precision = 18; insertTable.Parameters[ @Latitude ].Scale = 6; insertTable.Parameters.Add(nameParameter10); insertTable.Parameters[ @Longitude ].Precision = 18; insertTable.Parameters[ @Longitude ].Scale = 6; int r = 0; while (r { Response.Write(r); dateParameter.Value = Convert .ToDateTime(dt.Rows[r][ Date ]); Response.Write(dt.Rows[r][ Date ]); dayParameter.Value = dt.Rows[r][ Day ]; Response.Write((dt.Rows[r][ Day ]).ToString()); timefromParameter.Value = dt.Rows[r][ Time-from ]; Response.Write(dt.Rows[r][ Time-from ].ToString()); pod1Parameter.Value = dt.Rows[r][ PoD1 ]; timetoParameter.Value = dt.Rows[r][ Time-to ]; pod2Parameter.Value = dt.Rows[r][ PoD2 ]; nameParameter2.Value = dt.Rows[r][ Location ]; nameParameter3.Value = dt.Rows[r][ IncidentType ]; nameParameter4.Value = dt.Rows[r][ Direction ]; nameParameter5.Value = dt.Rows[r][ LaneBlockage ]; nameParameter6.Value = dt.Rows[r][ CCTV ]; nameParameter7.Value = dt.Rows[r][ SignalTimingChanges ]; nameParameter8.Value = dt.Rows[r][ Benefits ]; decimal .TryParse(dt.Rows[r][ Latitude ].ToString() out latitude); nameParameter9.Value = latitude; decimal .TryParse(dt.Rows[r][ Longitude ].ToString() out longitude); nameParameter10.Value = longitude; insertTable.ExecuteNonQuery(); Response.Write(dt.Rows[r][ Date ]); r++; } } catch ( Exception ex) { Response.Write(ex.Message); } finally { conn.Close(); } } Thank you very much to both of you for the try

Nel · Answer

Actually I notice now that all the columns after TimeTo (after first 5 fields/columns) which were problematic have exact/correct values I compared every row from the original table with the one I got in sql and only the first 5 have all same values which are the values of the first 5 fields in the last row. And I also tried with this SqlCommand insertTable = new SqlCommand(null conn); insertTable.CommandText = INSERT INTO TrafInc1 (Datei Dayi TimeFrom PoD1 TimeTo PoD2 Location IncidentType Direction LaneBlockage CCTV SignalTimingChanges Benefits Latitude Longitude) + VALUES (@Date @Day@TimeFrom@PoD1 @TimeTo@PoD2 @Location @IncidentType @Direction @LaneBlockage@CCTV @SignalTimingChanges @Benefits @Latitude @Longitude); instead of using stored procedure And if I chage this insertTable.Parameters.AddWithValue(@Date Convert.ToDateTime(row[Date])); insertTable.Parameters.AddWithValue(@Day row[Day]); insertTable.Parameters.AddWithValue(@TimeFrom row[Time-from]); insertTable.Parameters.AddWithValue(@PoD1 row[PoD1]); insertTable.Parameters.AddWithValue(@TimeTo row[Time-to]); with this lines insertTable.Parameters.Add(dateParameter); insertTable.Parameters.Add(dayParameter); insertTable.Parameters.Add(timefromParameter); insertTable.Parameters.Add(pod1Parameter); insertTable.Parameters.Add(timetoParameter); nothing is inserted into the sqltable

Nel · Answer

Hi to both of you I tried like this and it started to insert data in the sql table but only the last row Actually the date and times are from the last row of the datatable same for all the rows in the sql table but the other columns are mixed by different data from different rows Here is the code int nRows = 0; dt.Clear(); try { // conn.Open(); var line = reader.ReadLine(); var values = line.Split(delimit); foreach (var dc in values) { dt.Columns.Add(dc); } reader = new StreamReader(File.OpenRead(@ path \Incidents1.csv)); while (!reader.EndOfStream) { int k = 0; values = reader.ReadLine().Split(''); row = dt.NewRow(); while (k { row[k] = values[k]; k++; } if (nRows != 0) { dt.Rows.Add(row); } nRows++; } GridView2.DataSource = dt; GridView2.DataBind(); decimal latitude; decimal longitude; SqlCommand insertTable = new SqlCommand(insertIncid1 conn); insertTable.CommandType = CommandType.StoredProcedure; SqlParameter dateParameter = new SqlParameter(@Date SqlDbType.Date); dateParameter.Direction = ParameterDirection.Input; SqlParameter dayParameter = new SqlParameter(@Day SqlDbType.NVarChar 15); dayParameter.Direction = ParameterDirection.Input; SqlParameter timefromParameter = new SqlParameter(@TimeFrom SqlDbType.Time 7); timefromParameter.Direction = ParameterDirection.Input; SqlParameter pod1Parameter = new SqlParameter(@PoD1 SqlDbType.NVarChar 2); pod1Parameter.Direction = ParameterDirection.Input; SqlParameter timetoParameter = new SqlParameter(@TimeTo SqlDbType.Time 7); timetoParameter.Direction = ParameterDirection.Input; SqlParameter pod2Parameter = new SqlParameter(@PoD2 SqlDbType.NVarChar 2); pod2Parameter.Direction = ParameterDirection.Input; SqlParameter nameParameter2 = new SqlParameter(@Location SqlDbType.NVarChar 60); nameParameter2.Direction = ParameterDirection.Input; SqlParameter nameParameter3 = new SqlParameter(@IncidentType SqlDbType.NVarChar 50); nameParameter3.Direction = ParameterDirection.Input; SqlParameter nameParameter4 = new SqlParameter(@Direction SqlDbType.NVarChar 60); nameParameter4.Direction = ParameterDirection.Input; SqlParameter nameParameter5 = new SqlParameter(@LaneBlockage SqlDbType.NVarChar 60); nameParameter5.Direction = ParameterDirection.Input; SqlParameter nameParameter6 = new SqlParameter(@CCTV SqlDbType.NVarChar 60); nameParameter6.Direction = ParameterDirection.Input; SqlParameter nameParameter7 = new SqlParameter(@SignalTimingChanges SqlDbType.NVarChar 100); nameParameter7.Direction = ParameterDirection.Input; SqlParameter nameParameter8 = new SqlParameter(@Benefits SqlDbType.NVarChar 100); nameParameter8.Direction = ParameterDirection.Input; SqlParameter nameParameter9 = new SqlParameter(@Latitude SqlDbType.Decimal 18); nameParameter9.Direction = ParameterDirection.Input; SqlParameter nameParameter10 = new SqlParameter(@Longitude SqlDbType.Decimal 18); nameParameter10.Direction = ParameterDirection.Input; insertTable.Parameters.AddWithValue(@Date Convert.ToDateTime(row[Date])); insertTable.Parameters.AddWithValue(@Day row[Day]); insertTable.Parameters.AddWithValue(@TimeFrom row[Time-from]); insertTable.Parameters.AddWithValue(@PoD1 row[PoD1]); insertTable.Parameters.AddWithValue(@TimeTo row[Time-to]); insertTable.Parameters.Add(pod2Parameter); insertTable.Parameters.Add(nameParameter2); insertTable.Parameters.Add(nameParameter3); insertTable.Parameters.Add(nameParameter4); insertTable.Parameters.Add(nameParameter5); insertTable.Parameters.Add(nameParameter6); insertTable.Parameters.Add(nameParameter7); insertTable.Parameters.Add(nameParameter8); insertTable.Parameters.Add(nameParameter9); insertTable.Parameters[@Latitude].Precision = 18; insertTable.Parameters[@Latitude].Scale = 6; insertTable.Parameters.Add(nameParameter10); insertTable.Parameters[@Longitude].Precision = 18; insertTable.Parameters[@Longitude].Scale = 6; int r = 0; for (r = 0; r { if (dt.Rows[r][Date].ToString().Trim() != ) { DateTime myDate; if (DateTime.TryParseExact(Convert.ToString(dt.Rows[r][Date]) MM/dd/yyyy null DateTimeStyles.None out myDate)) { dateParameter.Value = myDate; Response.Write(myDate); } else { dateParameter.Value = DBNull.Value; } } else { dateParameter.Value = DBNull.Value; } dayParameter.Value = dt.Rows[r][Day]; Response.Write((dt.Rows[r][Day]).ToString()); if (dt.Rows[r][Time-from].ToString().Trim() != ) { DateTime timeFrom; if (DateTime.TryParseExact(Convert.ToString(dt.Rows[r][Time-from]) H:mm null DateTimeStyles.None out timeFrom)) { timefromParameter.Value = timeFrom; // Response.Write(timeFrom); } else { timefromParameter.Value = DBNull.Value; } } else { timefromParameter.Value = DBNull.Value; } pod1Parameter.Value = dt.Rows[r][PoD1]; if (dt.Rows[r][Time-to].ToString().Trim() != ) { DateTime timeTo; if (DateTime.TryParseExact(Convert.ToString(dt.Rows[r][Time-to]) H:mm null DateTimeStyles.None out timeTo)) { timetoParameter.Value = timeTo; Response.Write(timeTo); } else { timetoParameter.Value = DBNull.Value; } } else { timetoParameter.Value = DBNull.Value; } pod2Parameter.Value = dt.Rows[r][PoD2]; nameParameter2.Value = dt.Rows[r][Location]; Response.Write(dt.Rows[r][Location]); nameParameter3.Value = dt.Rows[r][IncidentType]; nameParameter4.Value = dt.Rows[r][Direction]; nameParameter5.Value = dt.Rows[r][LaneBlockage]; nameParameter6.Value = dt.Rows[r][CCTV]; nameParameter7.Value = dt.Rows[r][SignalTimingChanges]; nameParameter8.Value = dt.Rows[r][Benefits]; decimal.TryParse(dt.Rows[r][Latitude].ToString() out latitude); nameParameter9.Value = latitude; decimal.TryParse(dt.Rows[r][Longitude].ToString() out longitude); nameParameter10.Value = longitude; conn.Open(); insertTable.Prepare(); insertTable.ExecuteNonQuery(); conn.Close(); } } catch (Exception ex) { Response.Write(ex.Message); } finally { // conn.Close(); } Does anybody have an idea to help me please? Thank you:)

Ramesh Maruthi · Answer

Nel · Answer

OK I will try with that first I will respond what I got when I am done Thanks to both of you for your help I really appreciate it

Vulpes · Answer

Yes we could try SqlBulkCopy which should be the fastest solution anyway if there are a lot of rows. However having got this far I'd still try to make the insertions one by one from C# first as it's very little work to change the code to do that.

Nel · Answer

Thanks to both of you but I don't understand this well Ramesh :(

Ramesh Maruthi · Answer

Vuples sir can we do column mappings and do the sqlbulkcopy in this scenario ? If yes than that will be the best approach to load complete data into a table at a single time

Vulpes · Answer

Well I'm sorry but I just can't find anything wrong with it :( The stored procedure itself also looks fine. Unless Ramesh has any ideas the only thing I can suggest is that you forget about the stored procedure and try and execute the insert statement directly from the C# code.

Nel · Answer

Actually I want the sql table to be populated only on first load not on every since the data will be multiplicated inside

Vulpes · Answer

The only thing I've found so far is that I think this line: int r = 1; should be: int r = 0; but unless there's only one row that's not going to solve the problem :(

Nel · Answer

there is no error only no insertions take place no insertions take place

Vulpes · Answer

Is there still an error or does the code appear to run OK but no insertions take place?

Nel · Answer

Now the GridView is filled with the data but still the sql table remains empty Can you help me with this please? Thanks

Vulpes · Answer

Yes dt.Rows[r][Date] should have been dt.Rows[r][Date].ToString() but Convert.ToString() will do just as well :)

Nel · Answer

Hi Ramesh thanks but I get the same error again

Ramesh Maruthi · Answer

if (DateTime.TryParseExact(dt.Rows[r][Date] M/d/yyyy null DateTimeStyles.None out myDate)) -- this change it to if (DateTime.TryParseExact(dt.Rows[r][Date] M/d/yyyy CultureInfo . InvariantCulture  DateTimeStyles.None out myDate))

Nel · Answer

Vulpes I tried this code but I get the error: The best overloaded method match for 'System.DateTime.TryParseExact(string string System.IFormatProvider System.Globalization.DateTimeStyles out System.DateTime)' has some invalid arguments  on the line: if (DateTime.TryParseExact(dt.Rows[r][Date] M/d/yyyy null DateTimeStyles.None out myDate)) Can you help me please what to do now how to solve this? Thanks

Vulpes · Answer

OK I've looked at the code from the beginning and I think the problem is that you're adding some of the parameters twice to the parameters collection. You're also setting parameter values before the final while loop which is unnecessary. One other thing I've noticed is that the Time-from and Time-to columns can't possibly be null because of the way you're populating the datatable from the CSV file though they could be empty. Anyway I've rewritten the code to allow for all this. As I can't test it I'll be very surprised if there aren't still some issues but it should at least give us a much firmer base from which they can be debugged: try { var line = reader.ReadLine(); var values = line.Split(delimit); foreach (var dc in values) { dt.Columns.Add(dc); } reader = new StreamReader(File.OpenRead(@\path\Incidents1.csv)); while (!reader.EndOfStream) { int k = 0; values = reader.ReadLine().Split(''); row = dt.NewRow(); while (k { row[k] = values[k]; k++; } if (nRows != 0) { dt.Rows.Add(row); } nRows++; } GridView2.DataSource = dt; GridView2.DataBind(); conn.Open(); SqlCommand insertTable = new SqlCommand(insertIncid1 conn); insertTable.CommandType = CommandType.StoredProcedure; SqlParameter dateParameter = new SqlParameter(@Date SqlDbType.DateTime); dateParameter.Direction = ParameterDirection.Input; SqlParameter dayParameter = new SqlParameter(@Day SqlDbType.NVarChar 15); dayParameter.Direction = ParameterDirection.Input; SqlParameter timefromParameter = new SqlParameter(@TimeFrom SqlDbType.Time); timefromParameter.Direction = ParameterDirection.Input; SqlParameter pod1Parameter = new SqlParameter(@PoD1 SqlDbType.NVarChar 2); pod1Parameter.Direction = ParameterDirection.Input; SqlParameter timetoParameter = new SqlParameter(@TimeTo SqlDbType.Time); timetoParameter.Direction = ParameterDirection.Input; SqlParameter pod2Parameter = new SqlParameter(@PoD2 SqlDbType.NVarChar 2); pod2Parameter.Direction = ParameterDirection.Input; SqlParameter nameParameter2 = new SqlParameter(@Location SqlDbType.NVarChar 60); nameParameter2.Direction = ParameterDirection.Input; SqlParameter nameParameter3 = new SqlParameter(@IncidentType SqlDbType.NVarChar 50); nameParameter3.Direction = ParameterDirection.Input; SqlParameter nameParameter4 = new SqlParameter(@Direction SqlDbType.NVarChar 60); nameParameter4.Direction = ParameterDirection.Input; SqlParameter nameParameter5 = new SqlParameter(@LaneBlockage SqlDbType.NVarChar 60); nameParameter5.Direction = ParameterDirection.Input; SqlParameter nameParameter6 = new SqlParameter(@CCTV SqlDbType.NVarChar 60); nameParameter6.Direction = ParameterDirection.Input; SqlParameter nameParameter7 = new SqlParameter(@SignalTimingChanges SqlDbType.NVarChar 100); nameParameter7.Direction = ParameterDirection.Input; SqlParameter nameParameter8 = new SqlParameter(@Benefits SqlDbType.NVarChar 100); nameParameter8.Direction = ParameterDirection.Input; SqlParameter nameParameter9 = new SqlParameter(@Latitude SqlDbType.Decimal 18); nameParameter9.Direction = ParameterDirection.Input; SqlParameter nameParameter10 = new SqlParameter(@Longitude SqlDbType.Decimal 18); nameParameter10.Direction = ParameterDirection.Input; insertTable.Parameters.Add(dateParameter); insertTable.Parameters.Add(dayParameter); insertTable.Parameters.Add(timefromParameter); insertTable.Parameters.Add(pod1Parameter); insertTable.Parameters.Add(timetoParameter); insertTable.Parameters.Add(pod2Parameter); insertTable.Parameters.Add(nameParameter2); insertTable.Parameters.Add(nameParameter3); insertTable.Parameters.Add(nameParameter4); insertTable.Parameters.Add(nameParameter5); insertTable.Parameters.Add(nameParameter6); insertTable.Parameters.Add(nameParameter7); insertTable.Parameters.Add(nameParameter8); insertTable.Parameters.Add(nameParameter9); insertTable.Parameters.Add(nameParameter10); int r = 1; while (r { if (dt.Rows[r][Date].ToString().Trim() != ) { DateTime myDate; if (DateTime.TryParseExact(dt.Rows[r][Date] M/d/yyyy null DateTimeStyles.None out myDate)) { dateParameter.Value = myDate; } else { dateParameter.Value = DBNull.Value; } } else { dateParameter.Value = DBNull.Value; } dayParameter.Value = dt.Rows[r][Day]; if (dt.Rows[r][Time-from].ToString().Trim() != ) { DateTime timeFrom; if (DateTime.TryParseExact(dt.Rows[r][Time-from] H:mm null DateTimeStyles.None out timeFrom)) { timefromParameter.Value = timeFrom; } else { timefromParameter.Value = DBNull.Value; } } else { timefromParameter.Value = DBNull.Value; } pod1Parameter.Value = dt.Rows[r][PoD1]; if (dt.Rows[r][Time-to].ToString().Trim() != ) { DateTime timeTo; if (DateTime.TryParseExact(dt.Rows[r][Time-to] H:mm null DateTimeStyles.None out timeTo)) { timetoParameter.Value = timeTo; } else { timetoParameter.Value = DBNull.Value; } } else { timetoParameter.Value = DBNull.Value; } pod2Parameter.Value = dt.Rows[r][PoD2]; nameParameter2.Value = dt.Rows[r][Location]; nameParameter3.Value = dt.Rows[r][IncidentType]; nameParameter4.Value = dt.Rows[r][Direction]; nameParameter5.Value = dt.Rows[r][LaneBlockage]; nameParameter6.Value = dt.Rows[r][CCTV]; nameParameter7.Value = dt.Rows[r][SignalTimingChanges]; nameParameter8.Value = dt.Rows[r][Benefits]; decimal latitude; decimal.TryParse(dt.Rows[r][Latitude].ToString() out latitude); nameParameter9.Value = latitude; decimal longitude; decimal.TryParse(dt.Rows[r][Longitude].ToString() out longitude); nameParameter10.Value = longitude; insertTable.ExecuteNonQuery(); r++; } } catch(Exception ex) { MessageBox.Show(ex.Message); } finally { conn.Close(); }

Nel · Answer

Hi Vulpes thanks I tried your code but again I get the same error An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Additional information: Procedure or function insertIncid1 has too many arguments specified. I did all the changes you've mentioned here is the code now try { var line = reader.ReadLine(); var values = line.Split(delimit); foreach (var dc in values) { dt.Columns.Add(dc); } reader = new StreamReader(File.OpenRead(@ \path \Incidents1.csv)); while (!reader.EndOfStream) { int k = 0; values = reader.ReadLine().Split(''); row = dt.NewRow(); while (k { row[k] = values[k]; k++; } if (nRows != 0) { dt.Rows.Add(row); } nRows++; //} } GridView2.DataSource = dt; GridView2.DataBind(); conn.Open(); SqlCommand insertTable = new SqlCommand(insertIncid1 conn); insertTable.CommandType = CommandType.StoredProcedure; SqlParameter nameParameter = new SqlParameter(@Date SqlDbType.DateTime); nameParameter.Direction = ParameterDirection.Input; var sample_for_format = Convert.ToString(row[Date]); if (row[Date] != null && row[Date] != DBNull.Value) { if (DateTime.TryParseExact(sample_for_format M/d/yyyy null DateTimeStyles.None out myDate)) { insertTable.Parameters.AddWithValue(@Date myDate); } } SqlParameter dayParameter = new SqlParameter (@Day SqlDbType.NVarChar 15); dayParameter.Direction = ParameterDirection.Input; dayParameter.Value = row[Day]; SqlParameter timefromParameter = new SqlParameter(@TimeFrom SqlDbType.Time); timefromParameter.Direction = ParameterDirection.Input; DateTime timefrom = DateTime.ParseExact(Convert.ToString(row[Time-from]) H:mm null); insertTable.Parameters.AddWithValue(@TimeFrom timefrom.TimeOfDay); SqlParameter pod1Parameter = new SqlParameter (@PoD1 SqlDbType.NVarChar 2); pod1Parameter.Direction = ParameterDirection.Input; pod1Parameter.Value = row[PoD1]; SqlParameter timetoParameter = new SqlParameter(@TimeTo SqlDbType.Time); timetoParameter.Direction = ParameterDirection.Input; DateTime timeto = DateTime.ParseExact(Convert.ToString(row[Time-to]) H:mm null); insertTable.Parameters.AddWithValue(@TimeTo timeto.TimeOfDay); SqlParameter pod2Parameter = new SqlParameter (@PoD2 SqlDbType.NVarChar 2); pod2Parameter.Direction = ParameterDirection.Input; pod2Parameter.Value = row[PoD2]; SqlParameter nameParameter2 = new SqlParameter (@Location SqlDbType.NVarChar 60); nameParameter2.Direction = ParameterDirection.Input; nameParameter2.Value = row[Location]; SqlParameter nameParameter3 = new SqlParameter (@IncidentType SqlDbType.NVarChar 60); nameParameter3.Direction = ParameterDirection.Input; nameParameter3.Value = row[IncidentType]; SqlParameter nameParameter4 = new SqlParameter (@Direction SqlDbType.NVarChar 60); nameParameter4.Direction = ParameterDirection.Input; nameParameter4.Value = row[Direction]; SqlParameter nameParameter5 = new SqlParameter (@LaneBlockage SqlDbType.NVarChar 60); nameParameter5.Direction = ParameterDirection.Input; nameParameter5.Value = row[LaneBlockage]; SqlParameter nameParameter6 = new SqlParameter (@CCTV SqlDbType.NVarChar 60); nameParameter6.Direction = ParameterDirection.Input; nameParameter6.Value = row[CCTV]; SqlParameter nameParameter7 = new SqlParameter (@SignalTimingChanges SqlDbType.NVarChar 100); nameParameter7.Direction = ParameterDirection.Input; nameParameter7.Value = row[SignalTimingChanges]; SqlParameter nameParameter8 = new SqlParameter (@Benefits SqlDbType.NVarChar 100); nameParameter8.Direction = ParameterDirection.Input; nameParameter8.Value = row[Benefits]; decimal latitude; decimal.TryParse(row[Latitude].ToString() out latitude); SqlParameter nameParameter9 = new SqlParameter(@Latitude SqlDbType.Decimal 18); nameParameter9.Direction = ParameterDirection.Input; nameParameter9.Value = latitude; decimal longitude; decimal.TryParse(row[Longitude].ToString() out longitude); SqlParameter nameParameter10 = new SqlParameter(@Longitude SqlDbType.Decimal 18); nameParameter10.Direction = ParameterDirection.Input; nameParameter10.Value = longitude; insertTable.Parameters.Add(nameParameter); insertTable.Parameters.Add(dayParameter); insertTable.Parameters.Add(timefromParameter); insertTable.Parameters.Add(pod1Parameter); insertTable.Parameters.Add(timetoParameter); insertTable.Parameters.Add(pod2Parameter); insertTable.Parameters.Add(nameParameter2); insertTable.Parameters.Add(nameParameter3); insertTable.Parameters.Add(nameParameter4); insertTable.Parameters.Add(nameParameter5); insertTable.Parameters.Add(nameParameter6); insertTable.Parameters.Add(nameParameter7); insertTable.Parameters.Add(nameParameter8); insertTable.Parameters.Add(nameParameter9); insertTable.Parameters.Add(nameParameter10); int r = 1; while (r { nameParameter.Value =Convert.ToDateTime(dt.Rows[r][Date]); dayParameter.Value = dt.Rows[r][Day]; timefromParameter.Value = dt.Rows[r][Time-from]; pod1Parameter.Value = dt.Rows[r][PoD1]; timetoParameter.Value = dt.Rows[r][Time-to]; pod2Parameter.Value = dt.Rows[r][PoD2]; nameParameter2.Value = dt.Rows[r][Location]; nameParameter3.Value = dt.Rows[r][IncidentType]; nameParameter4.Value = dt.Rows[r][Direction]; nameParameter5.Value = dt.Rows[r][LaneBlockage]; nameParameter6.Value = dt.Rows[r][CCTV]; nameParameter7.Value = dt.Rows[r][SignalTimingChanges]; nameParameter8.Value = dt.Rows[r][Benefits]; decimal.TryParse(dt.Rows[r][Latitude].ToString() out latitude); nameParameter9.Value = latitude; decimal.TryParse(dt.Rows[r][Longitude].ToString() out longitude); nameParameter10.Value = longitude; insertTable.ExecuteNonQuery(); r++; } }

Vulpes · Answer

Latitude and Longitude are of type Decimal rather than NVarChar and so their parameter types should be SqlDbType.Decimal. I'd also make sure that the lengths of the NVarChar parameters tie up with those in the stored procedure. The code for the Date parameter is not quite right. Try this instead: SqlParameter nameParameter = new SqlParameter(@Date SqlDbType.DateTime); nameParameter.Direction = ParameterDirection.Input; var sample_for_format = Convert.ToString(row[Date]); if (row[Date] != null && row[Date] != DBNull.Value) { if (DateTime.TryParseExact(sample_for_format M/d/yyyy  null DateTimeStyles.None out myDate)) { insertTable.Parameters.AddWithValue(@Date myDate); } }

Nel · Answer

Yes I have noticed that unintentionally I have erased the lines where the direction of the parameter is defined so I entered it again. But again I get the same error I just want to ask you kindly is this correct how I define the Date SqlParameter nameParameter = new SqlParameter (@Date SqlDbType.DateTime); nameParameter.Direction = ParameterDirection.Input; var sample_for_format = Convert.ToString(row[Date]); if (row[Date] != null || row[Date] != DBNull.Value) { if (DateTime.TryParseExact(sample_for_format MM/dd/yyyy null DateTimeStyles.None out myDate)) { insertTable.Parameters.AddWithValue(@Date myDate); } } Thanks

Nel · Answer

I checked the stored procedure Vulpes and it seems to me that it's fine Here it is CREATE procedure [insertIncid1] ( @Date DATE  @Day nvarchar (15) @TimeFrom TIME @PoD1 nvarchar(2) @TimeTo TIME @PoD2 NVARCHAR (2) @Location NVARCHAR (60)  @IncidentType NVARCHAR (50)  @Direction NVARCHAR (60)  @LaneBlockage NVARCHAR (60)  @CCTV NVARCHAR (60)  @SignalTimingChanges NVARCHAR (100)  @Benefits NVARCHAR (100)  @Latitude DECIMAL (18 6)  @Longitude DECIMAL (18 6) ) as begin INSERT INTO [TrafInc1] ( Datei Dayi TimeFrom PoD1 TimeTo PoD2 Location IncidentType Direction LaneBlockage CCTV SignalTimingChanges Benefits Latitude Longitude ) VALUES ( @Date @Day @TimeFrom @PoD1 @TimeTo @PoD2 @Location @IncidentType @Direction @LaneBlockage @CCTV @SignalTimingChanges @Benefits @Latitude @Longitude ) end I can not notice any error

Vulpes · Answer

Well on the face of it your stored procedure doesn't take as many as 15 parameters. If it does I'd check that the types match and that they're all input parameters.

Nel · Answer

Yes  there are some empty entries in that column . I erased that rows and here is my code now: while (!reader.EndOfStream) { int k = 0; values = reader.ReadLine().Split(''); row = dt.NewRow(); while (k { row[k] = values[k]; k++; } if (nRows != 0) { dt.Rows.Add(row); } nRows++; } GridView2.DataSource = dt; GridView2.DataBind(); conn.Open(); SqlCommand insertTable = new SqlCommand(insertIncid1 conn); insertTable.CommandType = CommandType.StoredProcedure; SqlParameter nameParameter = new SqlParameter (@Date SqlDbType.DateTime); nameParameter.Direction = ParameterDirection.Input; var sample_for_format = Convert.ToString(row[Date]); if (row[Date] != null || row[Date] != DBNull.Value) { if (DateTime.TryParseExact(sample_for_format MM/dd/yyyy null DateTimeStyles.None out myDate)) { insertTable.Parameters.AddWithValue(@Date myDate); } } SqlParameter dayParameter = new SqlParameter (@Day SqlDbType.NVarChar 15); dayParameter.Direction = ParameterDirection.Input; dayParameter.Value = row[Day]; SqlParameter timefromParameter = new SqlParameter(@TimeFrom SqlDbType.Time); DateTime timefrom = DateTime.ParseExact(Convert.ToString(row[Time-from]) H:mm null); insertTable.Parameters.AddWithValue(@TimeFrom timefrom.TimeOfDay); SqlParameter pod1Parameter = new SqlParameter (@PoD1 SqlDbType.NVarChar 2); pod1Parameter.Direction = ParameterDirection.Input; pod1Parameter.Value = row[PoD1]; SqlParameter timetoParameter = new SqlParameter(@TimeTo SqlDbType.Time); DateTime timeto = DateTime.ParseExact(Convert.ToString(row[Time-to]) H:mm null); insertTable.Parameters.AddWithValue(@TimeTo timeto.TimeOfDay); SqlParameter pod2Parameter = new SqlParameter (@PoD2 SqlDbType.NVarChar 2); pod2Parameter.Direction = ParameterDirection.Input; pod2Parameter.Value = row[PoD2]; SqlParameter nameParameter2 = new SqlParameter (@Location SqlDbType.NVarChar 60); nameParameter2.Direction = ParameterDirection.Input; nameParameter2.Value = row[Location]; SqlParameter nameParameter3 = new SqlParameter (@IncidentType SqlDbType.NVarChar 60); nameParameter3.Direction = ParameterDirection.Input; nameParameter3.Value = row[IncidentType]; SqlParameter nameParameter4 = new SqlParameter (@Direction SqlDbType.NVarChar 60); nameParameter4.Direction = ParameterDirection.Input; nameParameter4.Value = row[Direction]; SqlParameter nameParameter5 = new SqlParameter (@LaneBlockage SqlDbType.NVarChar 60); nameParameter5.Direction = ParameterDirection.Input; nameParameter5.Value = row[LaneBlockage]; SqlParameter nameParameter6 = new SqlParameter (@CCTV SqlDbType.NVarChar 60); nameParameter6.Direction = ParameterDirection.Input; nameParameter6.Value = row[CCTV]; SqlParameter nameParameter7 = new SqlParameter (@SignalTimingChanges SqlDbType.NVarChar 60); nameParameter7.Direction = ParameterDirection.Input; nameParameter7.Value = row[SignalTimingChanges]; SqlParameter nameParameter8 = new SqlParameter (@Benefits SqlDbType.NVarChar 60); nameParameter8.Direction = ParameterDirection.Input; nameParameter8.Value = row[Benefits]; decimal latitude; decimal.TryParse(row[Latitude].ToString() out latitude); SqlParameter nameParameter9 = new SqlParameter(@Latitude SqlDbType.NVarChar 60); nameParameter9.Direction = ParameterDirection.Input; nameParameter9.Value = latitude; decimal longitude; decimal.TryParse(row[Longitude].ToString() out longitude); SqlParameter nameParameter10 = new SqlParameter(@Longitude SqlDbType.NVarChar 60); nameParameter10.Direction = ParameterDirection.Input; nameParameter10.Value = longitude; insertTable.Parameters.Add(nameParameter); insertTable.Parameters.Add(dayParameter); insertTable.Parameters.Add(timefromParameter); insertTable.Parameters.Add(pod1Parameter); insertTable.Parameters.Add(timetoParameter); insertTable.Parameters.Add(pod2Parameter); insertTable.Parameters.Add(nameParameter2); insertTable.Parameters.Add(nameParameter3); insertTable.Parameters.Add(nameParameter4); insertTable.Parameters.Add(nameParameter5); insertTable.Parameters.Add(nameParameter6); insertTable.Parameters.Add(nameParameter7); insertTable.Parameters.Add(nameParameter8); insertTable.Parameters.Add(nameParameter9); insertTable.Parameters.Add(nameParameter10); int r = 1; while (r { nameParameter.Value =Convert.ToDateTime(dt.Rows[r][Date]); dayParameter.Value = dt.Rows[r][Day]; timefromParameter.Value = dt.Rows[r][Time-from]; pod1Parameter.Value = dt.Rows[r][PoD1]; timetoParameter.Value = dt.Rows[r][Time-to]; pod2Parameter.Value = dt.Rows[r][PoD2]; nameParameter2.Value = dt.Rows[r][Location]; nameParameter3.Value = dt.Rows[r][IncidentType]; nameParameter4.Value = dt.Rows[r][Direction]; nameParameter5.Value = dt.Rows[r][LaneBlockage]; nameParameter6.Value = dt.Rows[r][CCTV]; nameParameter7.Value = dt.Rows[r][SignalTimingChanges]; nameParameter8.Value = dt.Rows[r][Benefits]; decimal.TryParse(dt.Rows[r][Latitude].ToString() out latitude); nameParameter9.Value = latitude; decimal.TryParse(dt.Rows[r][Longitude].ToString() out longitude); nameParameter10.Value = longitude; insertTable.ExecuteNonQuery(); r++; } and I get this error: An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Additional information: Procedure or function insertIncid1 has too many arguments specified.

Vulpes · Answer

Are there any entries in the Time-from column which are either empty or don't fit the H:mm pattern?

Nel · Answer

Thanks Vulpes but now I get an error again An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code Additional information: String was not recognized as a valid DateTime. on DateTime timefrom = DateTime.ParseExact(Convert.ToString(row[Time-from]).Trim() H:mm null);

Vulpes · Answer

The H format uses the twenty four hour clock with hours from 0 to 23 so it is in fact what you want. In contrast the h format uses the twelve hour clock with hours from 1 to 12 so is not applicable here. Incidentally after applying Convert.ToString() I'd also apply Trim() in case there's any leading or trailing whitespace which DateTime.TryParse won't like.

Nel · Answer

No Vulpes some of the data in time columns are for example 10:08 It's not always H:mm

Ramesh Maruthi · Answer

Ya the time is not with the seconds so you can use as vuples sir said

Vulpes · Answer

Ah the format of the Time-from column is actually H:mm. So I'd try: SqlParameter timefromParameter = new SqlParameter(@TimeFrom SqlDbType.Time ); DateTime timefrom = DateTime.ParseExact(Convert.ToString(row[Time-from]) H:mm  null); insertTable.Parameters.AddWithValue(@TimeFrom timefrom.TimeOfDay);

Nel · Answer

The csv table sample is this and the dt table which I build with the data from csv table is and the sql table structrure is [ID] INT IDENTITY (1 1) NOT NULL [Datei] DATE NULL [Dayi] NVARCHAR (15) NULL [TimeFrom] TIME (7) NULL [PoD1] NVARCHAR (2) NULL [TimeTo] TIME (7) NULL [PoD2] NVARCHAR (2) NULL [Location] NVARCHAR (60) NULL [IncidentType] NVARCHAR (50) NULL [Direction] NVARCHAR (60) NULL [LaneBlockage] NVARCHAR (60) NULL [CCTV] NVARCHAR (60) NULL [SignalTimingChanges] NVARCHAR (100) NULL [Benefits] NVARCHAR (100) NULL [Latitude] DECIMAL (18 6) NULL [Longitude] DECIMAL (18 6) NULL

Vulpes · Answer

Can you post a specimen value from that column. I just want to check that you're using the correct format in DateTime.ParseExact which is very fussy about what it accepts.

Nel · Answer

No it's not the case. In each field/column Time-from in csv file there is data. Neither one of the rows in that column is empty And I also see that upper in the code in dt which is datatable which contains data from the csv file all the rows and columns have values which are correct

Vulpes · Answer

If that's the case then the corresponding field in the CSV file must be empty. Is that feasible?

Nel · Answer

Hi Vulpes I tried with this as well: if (row[Time-from] != null || row[Time-from] != DBNull.Value) { if (DateTime.TryParseExact(sample_for_timeformat MM/dd/yyyy null DateTimeStyles.None out myTime)) { insertTable.Parameters.AddWithValue(@Time-from myTime.TimeOfDay); } } and this didn't execute so row[Time-from] is either null or dbnull.value row[Time-from] == null || row[Time-from] == DBNull.Value

Nel · Answer

Hi Ramesh thanks but now I get this error An exception of type 'System.FormatException' occurred in mscorlib.dll but was not handled in user code Additional information: String was not recognized as a valid DateTime. on DateTime timefrom = Convert.ToDateTime(row[Time-from]);

Ramesh Maruthi · Answer

Datetime timefrom = Convert.todatetime( row[Time-from]); timefrom.ToString(hh:mm tt);