update call not working

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

ahager via DotNetMonster.com

My update method is not working, I am getting

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

Additional information: System error.

Don't know why. Please help!

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;

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

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);
}

SDA.Update(OnviaDS,"ewm_onvia_import");

MySqlConnection.Close();


}
}
}
}
 
D

Darren Kopp

Have you tried SQL Server's DTS Services (Data Trasformation Services).
That would be the easiest way to go about that rather than through .NET.

-Darren Kopp

ahager via DotNetMonster.com said:
My update method is not working, I am getting

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

Additional information: System error.

Don't know why. Please help!

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;

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

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);
}

SDA.Update(OnviaDS,"ewm_onvia_import");

MySqlConnection.Close();


}
}
}
}
Click to expand...
 
A

ahager via DotNetMonster.com

Parameters of my FDP ask that it be in.net, everything works except for the
update call.

Darren said:
Have you tried SQL Server's DTS Services (Data Trasformation Services).
That would be the easiest way to go about that rather than through .NET.

-Darren Kopp
My update method is not working, I am getting
Click to expand...
[quoted text clipped - 156 lines]
}
}
Click to expand...
Click to expand...
 

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
System.FormatException: The string was not recognized as a valid DateTime. 6
WTD: Nvidia Videocard for 4x AGP Slot 0

Top