DateTime parse

  • Thread starter Thread starter ahager via DotNetMonster.com
  • Start date 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();

}
}
}
}
 
I actually have this resolved but now have a new error when i try to execute
my update command.

the error is

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.

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



foreach(DataRow dr in RawImport.Tables["ewm_onvia_import"].Rows)

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


drw[0]= dr[0];
drw[1]= dr[1];
drw[2]= dr[2];
drw[3]= dr[3];
drw[4]= dr[4];
drw[5]= dr[5];
drw[6]= dr[6];
drw[7]= dr[7];
drw[8]= dr[8];
drw[9]= dr[9];
drw[10]= dr[10];
drw[11]= dr[11];
drw[12]= dr[12];
drw[13]= dr[13];
drw[14]= dr[14];
drw[15]= dr[15];
drw[16]= dr[16];
drw[17]= dr[17];
drw[18]= dr[18];
drw[19]= dr[19];
drw[20]= dr[20];
OnviaDS.Tables["ewm_onvia_import"].Rows.Add(drw);
}
SqlCommand OnviaInsert = MySqlConnection.CreateCommand();
SDA.InsertCommand = OnviaInsert;
OnviaInsert.CommandType = CommandType.Text;

//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)"+


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

SDA.Update(OnviaDS,"ewm_onvia_import");

MySqlConnection.Close();


}
}
}
}

the error is

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.
 
The SqlException message would have been useful to help diagnose the
problem.
From the insert command text, it looks wrong - maybe a cut and past error.
expecting INSERT INTO table(...) VALUES(...); SELECT ... FROM table
WHERE ...
not INSERT INTO table(...) SELECT ... FROM table VALUES(...)

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

ahager via DotNetMonster.com said:
I actually have this resolved but now have a new error when i try to
execute
my update command.

the error is

An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred
in system.data.dll

Additional information: System error.

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



foreach(DataRow dr in RawImport.Tables["ewm_onvia_import"].Rows)

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


drw[0]= dr[0];
drw[1]= dr[1];
drw[2]= dr[2];
drw[3]= dr[3];
drw[4]= dr[4];
drw[5]= dr[5];
drw[6]= dr[6];
drw[7]= dr[7];
drw[8]= dr[8];
drw[9]= dr[9];
drw[10]= dr[10];
drw[11]= dr[11];
drw[12]= dr[12];
drw[13]= dr[13];
drw[14]= dr[14];
drw[15]= dr[15];
drw[16]= dr[16];
drw[17]= dr[17];
drw[18]= dr[18];
drw[19]= dr[19];
drw[20]= dr[20];
OnviaDS.Tables["ewm_onvia_import"].Rows.Add(drw);
}
SqlCommand OnviaInsert = MySqlConnection.CreateCommand();
SDA.InsertCommand = OnviaInsert;
OnviaInsert.CommandType = CommandType.Text;

//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)"+


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

SDA.Update(OnviaDS,"ewm_onvia_import");

MySqlConnection.Close();


}
}
}
}

the error is

An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred
in system.data.dll

Additional information: System error.
I don't understand your question...parse the date? Are you getting a
error when you run your code...what is the error? I know that if yo
try to pass in a string, formatted in a date format to a DateTime o
SmallDateTime SQL field, i.e., "12/10/1999", SQL server will no
complain..
Click to expand...
Click to expand...
 
Back
Top