Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsFree MagazinesWhite PapersSubmit Content
Discussion GroupsASP.NETWindows FormsLanguages.NET FrameworkVisual Studio.NET
Articles.NET FrameworkASP.NETToolsWindows Forms
.NET DirectoryOpen Source ProjectsUser GroupsWeb Resources
Related Topics
Visual Basic 6SQL ServerMS AccessOther DB ProductsMS Server ProductsMore Topics ...

.NET Forum / .NET Framework / ADO.NET / September 2005

Tip: Looking for answers? Try searching our database.

update call not working

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
ahager - 28 Sep 2005 18:38 GMT
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();

            }
        }
        }
    }
Darren Kopp - 29 Sep 2005 00:39 GMT
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
>
[quoted text clipped - 156 lines]
> }
> }
ahager - 29 Sep 2005 17:22 GMT
Parameters of my FDP ask that it be in.net, everything works except for the
update call.

>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.
[quoted text clipped - 6 lines]
>> }
>> }

Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.