System.FormatException: The string was not recognized as a valid DateTime.

  • Thread starter ahager via DotNetMonster.com
  • Start date
A

ahager via DotNetMonster.com

I know I need to use the parse method of the DateTime but am unsure where or
exactly how to do it. I am trying to update a SQL database with an
unspecified amount of data and am passing in several columns whose datatype
is of type DateTime. Below is my code.

using System;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace ConsoleApplication1
{
/// <summary>
/// Summary description for SQLdataUpload.
/// </summary>
public class SQLdataUpload3
{

public SQLdataUpload3()
{
//declare datasets
System.Data.DataSet OnviaDS = new System.Data.DataSet();
System.Data.DataSet RawImport = new DataSet();





{
//connect to excel file and fill data set
OleDbConnection MyConnection = new OleDbConnection("Provider=Microsoft.Jet.
OLEDB.4.0;Data Source=C:\\OnviaData\\OnviaData;Extended Properties=Excel 8.0")
;
MyConnection.Open();

OleDbCommand MyCommand = MyConnection.CreateCommand();
MyCommand.CommandType = CommandType.Text;
MyCommand.CommandText = "Select * From [Sheet1$]";
OleDbDataAdapter MyAdapter = new OleDbDataAdapter();
MyAdapter.SelectCommand = MyCommand;


MyAdapter.Fill(RawImport,"ewm_onvia_import");
MyConnection.Close();








//connect to SQL database and fill data set with existing data
SqlConnection MySqlConnection = new SqlConnection
("uid=sa;password=;Persist Security Info=False;Initial Catalog=db_work2;Data
Source=devsvr1;Packet Size=4096;Workstation ID=QED_IT4;");
MySqlConnection.Open();

SqlCommand ExistingData = MySqlConnection.CreateCommand();
SqlDataAdapter SDA = new SqlDataAdapter();
SDA.SelectCommand = ExistingData;
ExistingData.CommandType = CommandType.Text;
ExistingData.CommandText = "SELECT * FROM ewm_onvia_import";
SDA.Fill(OnviaDS,"ewm_onvia_import");

SqlCommand OnviaInsert = MySqlConnection.CreateCommand();
SDA.InsertCommand = OnviaInsert;
OnviaInsert.CommandType = CommandType.Text;

//step through each row if RawImport dataset
foreach(DataRow dr in RawImport.Tables["ewm_onvia_import"].Rows)

{

//insert updated dataset back into SQL database and table
ewm_onvia_import
OnviaInsert.CommandText = "INSERT INTO ewm_onvia_import(Publish Date,
Sector,Info Type,Onvia Ref Num"+
"Project Num,Project Name,Owner,Location,State,Zip Code,County,
Submittal Date,Contact Name,Phone"+
"Minimum Price,Maximum Price,Plan Price,Pre Bid,URL,Categories,Scope)"+

"SELECT (Publish Date,Sector,Info Type,Onvia Ref Num"+
"Project Num,Project Name,Owner,Location,State,Zip Code,County,
Submittal Date,Contact Name,Phone"+
"Minimum Price,Maximum Price,Plan Price,Pre Bid,URL,Categories,Scope)"+
"FROM [RawImport]"+

"VALUES(@Publish Date,@Sector,@Info Type,@Onvia Ref Num,@Project Num,
@Project Name,@Owner,@Location,"+
"@State,@Zip Code,@County,@Submittal Date,@Contact Name,@Phone"+
"@Minimum Price,@Maximum Price,@Plan Price,@Pre Bid,@URL,@Categories,
Scope)";

//add parameters
OnviaInsert.Parameters.Add("@Publish Date", SqlDbType.DateTime,8,
"Publish Date");
OnviaInsert.Parameters.Add("@Sector",SqlDbType.VarChar,20,"Sector");
OnviaInsert.Parameters.Add("@Info Type",SqlDbType.VarChar,20,"Info Type")
;
OnviaInsert.Parameters.Add("@Onvia Ref Num",SqlDbType.Int,4,"Onvia Ref
Num");
OnviaInsert.Parameters.Add("@Project Num",SqlDbType.VarChar,255,"Project
Num");
OnviaInsert.Parameters.Add("@Project Name",SqlDbType.VarChar,255,
"Project Name");
OnviaInsert.Parameters.Add("@Owner",SqlDbType.VarChar,255,"Owner");
OnviaInsert.Parameters.Add("@Location",SqlDbType.Text,16,"Location");
OnviaInsert.Parameters.Add("@State",SqlDbType.VarChar,255,"State");
OnviaInsert.Parameters.Add("@Zip Code",SqlDbType.VarChar,255,"Zip Code")
;
OnviaInsert.Parameters.Add("@County",SqlDbType.VarChar,255,"County");
OnviaInsert.Parameters.Add("@Submittal Date",SqlDbType.DateTime,8,
"Submittal Date");
OnviaInsert.Parameters.Add("@Contact Name",SqlDbType.VarChar,255,
"Contact Name");
OnviaInsert.Parameters.Add("@Phone",SqlDbType.VarChar,255,"Phone");
OnviaInsert.Parameters.Add("@Mimimum Value",SqlDbType.VarChar,255,
"Minimum Value");
OnviaInsert.Parameters.Add("@Maximum Value",SqlDbType.VarChar,255,
"Maximum Value");
OnviaInsert.Parameters.Add("@Plan Price",SqlDbType.VarChar,255,"Plan
Price");
OnviaInsert.Parameters.Add("@Pre Bid",SqlDbType.DateTime,8,"Pre Bid");
OnviaInsert.Parameters.Add("@URL",SqlDbType.VarChar,255,"URL");
OnviaInsert.Parameters.Add("@Categories",SqlDbType.Text,16,"Categories")
;
OnviaInsert.Parameters.Add("@Scope",SqlDbType.Text,16,"Scope");
OnviaInsert.Parameters["@Onvia Ref Num"].SourceVersion=DataRowVersion.
Original;

//insert rows into ewm_onvia_import dataset from RawImport
DataRow drw = OnviaDS.Tables["ewm_onvia_import"].NewRow();


drw[0]= "@Publish Date";
//error occurs after the above line
drw[1]= "@Sector";
drw[2]= "@Info Type";
drw[3]= "@Onvia Ref Num";
drw[4]= "@Project Num";
drw[5]= "@Project Name";
drw[6]= "@Owner";
drw[7]= "@Location";
drw[8]= "@State";
drw[9]= "@Zip Code";
drw[10]= "@County";
drw[11]= "@Submittal Date";
drw[12]= "@Contact Name";
drw[13]= "@Phone";
drw[14]= "@Minimum Value";
drw[15]= "@Maximum Value";
drw[16]= "@Plan Price";
drw[17]= "@Pre Bid";
drw[18]= "@URL";
drw[19]= "@Catgories";
drw[20]= "@Scope";
OnviaDS.Tables["ewm_onvia_import"].Rows.Add(drw);


SDA.Update(OnviaDS,"ewm_onvia_import");
}
MySqlConnection.Close();


}
}
}
}
 
J

Jon Skeet [C# MVP]

ahager via DotNetMonster.com said:
I know I need to use the parse method of the DateTime but am unsure where or
exactly how to do it. I am trying to update a SQL database with an
unspecified amount of data and am passing in several columns whose datatype
is of type DateTime. Below is my code.
Click to expand...

You're setting the column values themselves to be the literal strings
"@Foo" "@Bar" (...). I don't think that's what you're trying to do, is
it? That's why it's failing - because "@Publish Date" isn't a valid
date/time.

Why are you bothering with a DataRow at all? If you know what values
you want to put in, why not just put them into the command directly?
 
G

Guest

If you already have your time value in a SqlTypes.SqlDateTime object, you can
simply read its value property to get a System.DateTime representation of the
time.

Brendan


ahager via DotNetMonster.com said:
I know I need to use the parse method of the DateTime but am unsure where or
exactly how to do it. I am trying to update a SQL database with an
unspecified amount of data and am passing in several columns whose datatype
is of type DateTime. Below is my code.

using System;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace ConsoleApplication1
{
/// <summary>
/// Summary description for SQLdataUpload.
/// </summary>
public class SQLdataUpload3
{

public SQLdataUpload3()
{
//declare datasets
System.Data.DataSet OnviaDS = new System.Data.DataSet();
System.Data.DataSet RawImport = new DataSet();





{
//connect to excel file and fill data set
OleDbConnection MyConnection = new OleDbConnection("Provider=Microsoft.Jet.
OLEDB.4.0;Data Source=C:\\OnviaData\\OnviaData;Extended Properties=Excel 8.0")
;
MyConnection.Open();

OleDbCommand MyCommand = MyConnection.CreateCommand();
MyCommand.CommandType = CommandType.Text;
MyCommand.CommandText = "Select * From [Sheet1$]";
OleDbDataAdapter MyAdapter = new OleDbDataAdapter();
MyAdapter.SelectCommand = MyCommand;


MyAdapter.Fill(RawImport,"ewm_onvia_import");
MyConnection.Close();








//connect to SQL database and fill data set with existing data
SqlConnection MySqlConnection = new SqlConnection
("uid=sa;password=;Persist Security Info=False;Initial Catalog=db_work2;Data
Source=devsvr1;Packet Size=4096;Workstation ID=QED_IT4;");
MySqlConnection.Open();

SqlCommand ExistingData = MySqlConnection.CreateCommand();
SqlDataAdapter SDA = new SqlDataAdapter();
SDA.SelectCommand = ExistingData;
ExistingData.CommandType = CommandType.Text;
ExistingData.CommandText = "SELECT * FROM ewm_onvia_import";
SDA.Fill(OnviaDS,"ewm_onvia_import");

SqlCommand OnviaInsert = MySqlConnection.CreateCommand();
SDA.InsertCommand = OnviaInsert;
OnviaInsert.CommandType = CommandType.Text;

//step through each row if RawImport dataset
foreach(DataRow dr in RawImport.Tables["ewm_onvia_import"].Rows)

{

//insert updated dataset back into SQL database and table
ewm_onvia_import
OnviaInsert.CommandText = "INSERT INTO ewm_onvia_import(Publish Date,
Sector,Info Type,Onvia Ref Num"+
"Project Num,Project Name,Owner,Location,State,Zip Code,County,
Submittal Date,Contact Name,Phone"+
"Minimum Price,Maximum Price,Plan Price,Pre Bid,URL,Categories,Scope)"+

"SELECT (Publish Date,Sector,Info Type,Onvia Ref Num"+
"Project Num,Project Name,Owner,Location,State,Zip Code,County,
Submittal Date,Contact Name,Phone"+
"Minimum Price,Maximum Price,Plan Price,Pre Bid,URL,Categories,Scope)"+
"FROM [RawImport]"+

"VALUES(@Publish Date,@Sector,@Info Type,@Onvia Ref Num,@Project Num,
@Project Name,@Owner,@Location,"+
"@State,@Zip Code,@County,@Submittal Date,@Contact Name,@Phone"+
"@Minimum Price,@Maximum Price,@Plan Price,@Pre Bid,@URL,@Categories,
Scope)";

//add parameters
OnviaInsert.Parameters.Add("@Publish Date", SqlDbType.DateTime,8,
"Publish Date");
OnviaInsert.Parameters.Add("@Sector",SqlDbType.VarChar,20,"Sector");
OnviaInsert.Parameters.Add("@Info Type",SqlDbType.VarChar,20,"Info Type")
;
OnviaInsert.Parameters.Add("@Onvia Ref Num",SqlDbType.Int,4,"Onvia Ref
Num");
OnviaInsert.Parameters.Add("@Project Num",SqlDbType.VarChar,255,"Project
Num");
OnviaInsert.Parameters.Add("@Project Name",SqlDbType.VarChar,255,
"Project Name");
OnviaInsert.Parameters.Add("@Owner",SqlDbType.VarChar,255,"Owner");
OnviaInsert.Parameters.Add("@Location",SqlDbType.Text,16,"Location");
OnviaInsert.Parameters.Add("@State",SqlDbType.VarChar,255,"State");
OnviaInsert.Parameters.Add("@Zip Code",SqlDbType.VarChar,255,"Zip Code")
;
OnviaInsert.Parameters.Add("@County",SqlDbType.VarChar,255,"County");
OnviaInsert.Parameters.Add("@Submittal Date",SqlDbType.DateTime,8,
"Submittal Date");
OnviaInsert.Parameters.Add("@Contact Name",SqlDbType.VarChar,255,
"Contact Name");
OnviaInsert.Parameters.Add("@Phone",SqlDbType.VarChar,255,"Phone");
OnviaInsert.Parameters.Add("@Mimimum Value",SqlDbType.VarChar,255,
"Minimum Value");
OnviaInsert.Parameters.Add("@Maximum Value",SqlDbType.VarChar,255,
"Maximum Value");
OnviaInsert.Parameters.Add("@Plan Price",SqlDbType.VarChar,255,"Plan
Price");
OnviaInsert.Parameters.Add("@Pre Bid",SqlDbType.DateTime,8,"Pre Bid");
OnviaInsert.Parameters.Add("@URL",SqlDbType.VarChar,255,"URL");
OnviaInsert.Parameters.Add("@Categories",SqlDbType.Text,16,"Categories")
;
OnviaInsert.Parameters.Add("@Scope",SqlDbType.Text,16,"Scope");
OnviaInsert.Parameters["@Onvia Ref Num"].SourceVersion=DataRowVersion.
Original;

//insert rows into ewm_onvia_import dataset from RawImport
DataRow drw = OnviaDS.Tables["ewm_onvia_import"].NewRow();


drw[0]= "@Publish Date";
//error occurs after the above line
drw[1]= "@Sector";
drw[2]= "@Info Type";
drw[3]= "@Onvia Ref Num";
drw[4]= "@Project Num";
drw[5]= "@Project Name";
drw[6]= "@Owner";
drw[7]= "@Location";
drw[8]= "@State";
drw[9]= "@Zip Code";
drw[10]= "@County";
drw[11]= "@Submittal Date";
drw[12]= "@Contact Name";
drw[13]= "@Phone";
drw[14]= "@Minimum Value";
drw[15]= "@Maximum Value";
drw[16]= "@Plan Price";
drw[17]= "@Pre Bid";
drw[18]= "@URL";
drw[19]= "@Catgories";
drw[20]= "@Scope";
OnviaDS.Tables["ewm_onvia_import"].Rows.Add(drw);


SDA.Update(OnviaDS,"ewm_onvia_import");
}
MySqlConnection.Close();


}
}
}
}
Click to expand...
 
A

ahager via DotNetMonster.com

The data I am passing in varies and is not consistent, in this particular
trial I have over 2000 rows and need to pass in the value of each row. The
"@Publish Date" is the name of the column.
 
A

ahager via DotNetMonster.com

It's actually the date I am trying to send in, I don't quite understand what
you mean. I am fairly new to programming and .net.

Brendan said:
If you already have your time value in a SqlTypes.SqlDateTime object, you can
simply read its value property to get a System.DateTime representation of the
time.

Brendan
I know I need to use the parse method of the DateTime but am unsure where or
exactly how to do it. I am trying to update a SQL database with an
Click to expand...
[quoted text clipped - 165 lines]
}
}
Click to expand...
Click to expand...
 
A

ahager via DotNetMonster.com

Problem resolved
It's actually the date I am trying to send in, I don't quite understand what
you mean. I am fairly new to programming and .net.
If you already have your time value in a SqlTypes.SqlDateTime object, you can
simply read its value property to get a System.DateTime representation of the
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
J

Jon Skeet [C# MVP]

ahager via DotNetMonster.com said:
Problem resolved
Click to expand...

How, out of interest? It's usually worth explaining what was wrong, for
the benefit of others in the same situation.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DateTime parse 2
update call not working 2
String was not recognized as a valid DateTime 3
String was not recognized as a valid DateTime. 1
Formatting Dates in ASP.NET / String was not recognized as a valid DateTime 6
WTD: Nvidia Videocard for 4x AGP Slot 0

Top