Problem with converting to Time type

Nel

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
                  ("@TimeFrom",SqlDbType.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

42 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nel12y ago
Yes, thank you to both of you:)
0
Vulpes12y ago
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:)
+1
Nel12y ago
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 < (dt.Columns.Count))

                    {

                        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 < dt.Rows.Count)



                       {

                           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

0
Nel12y ago
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
0
Nel12y ago

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 < (dt.Columns.Count))
                    {
                        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 < dt.Rows.Count;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:)
0
Ramesh Maruthi12y ago
0
Nel12y ago

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
0
Vulpes12y ago
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.
0
Nel12y ago
Thanks to both of you, but I don't understand this well Ramesh :(
0
Ramesh Maruthi12y ago
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
0
Vulpes12y ago
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.
0
Nel12y ago
Actually I want the sql table to be populated only on first load not on every since the data will be multiplicated inside
0
Vulpes12y ago
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 :(

0
Nel12y ago
there is no error, only no insertions take place no insertions take place
0
Vulpes12y ago
Is there still an error or does the code appear to run OK but no insertions take place?
0
Nel12y ago

Now the GridView is filled with the data but still the sql table remains empty
Can you help me with this please?
Thanks
0
Vulpes12y ago
Yes, dt.Rows[r]["Date"] should have been dt.Rows[r]["Date"].ToString() but Convert.ToString() will do just as well :)
0
Nel12y ago
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
+1
Nel12y ago

Hi Ramesh,
thanks, but I get the same error again
0
Ramesh Maruthi12y ago
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))
0
Nel12y ago

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

0
Vulpes12y ago
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 < (dt.Columns.Count))
{
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 < dt.Rows.Count)
{
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();
}
0
Nel12y ago
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 < (dt.Columns.Count))
{
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 < (dt.Rows.Count))
{
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++;
}

}
0
Vulpes12y ago
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);
}

}

0
Nel12y ago
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
0
Nel12y ago

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
0
Vulpes12y ago
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.
0
Nel12y ago
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 < (dt.Columns.Count))
{
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 < (dt.Rows.Count))
{
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.
0
Vulpes12y ago
Are there any entries in the Time-from column which are either empty or don't fit the H:mm pattern?
0
Nel12y ago
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);
0
Vulpes12y ago
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.
0
Nel12y ago
No Vulpes, some of the data in time columns are for example 10:08 It's not always H:mm
0
Ramesh Maruthi12y ago
Ya the time is not with the seconds, so you can use as vuples sir said
0
Vulpes12y ago
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);
0
Nel12y ago
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
0
Vulpes12y ago
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.
0
Nel12y ago
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
0
Vulpes12y ago
If that's the case, then the corresponding field in the CSV file must be empty.

Is that feasible?
0
Nel12y ago
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
0
Vulpes12y ago
What does row["Time-from"] actually contain when the error occurs?
+1
Nel12y ago

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"]);
0
Ramesh Maruthi12y ago
Datetime timefrom = Convert.todatetime(row["Time-from"]);
timefrom.ToString("hh:mm tt");
0