I have a application which take data from CSV and dump to the respective table
using SQLBULKCOPY
I am using the same connection string for truncating data from the same table
and while writing to server
SqlBulkCopy bc = new SqlBulkCopy(connection_string1.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.WriteToServer(dt);//Its giving exception on this line
I am not getting what is wrong

namespace Test1
{

    public partial class Form1 : Form
    {
        StreamWriter SW;
        string emp_code = "";
        public Form1()
        {
            InitializeComponent();
        }


        private void Uploadbutton_Click(object sender, EventArgs e)
        {

            openFileDialog1.ShowDialog();

            filenametextBox.Text = openFileDialog1.FileName;


        }//Uploadbutton_Click

        private void save_to_DBbutton_Click(object sender, EventArgs e)
        {
            string servername = "";
            string dbname = "";
            string username = "";
            string pwd = "";
            string storeproc = "";
            string tablename = "";
            tablename = Convert.ToString(comboBox1.SelectedItem);
            SqlConnection connection_string = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["database"]);
            try
            {
                
                connection_string.Open();
                
                string query_datasource = "select request_server, request_db, request_user, request_pwd, storeProcedure from mst_csv_upload where request_name = '" + tablename + "'";

                SqlCommand cmd_dtsource = new SqlCommand(query_datasource, connection_string);
                cmd_dtsource.CommandType = CommandType.Text;
                
                SqlDataReader dtreader = cmd_dtsource.ExecuteReader();//cmd.ExecuteReader();

                while (dtreader.Read())      //this is retrieve the connection string from db to dump the data to the table
                {
                    servername = Convert.ToString(dtreader[0]);
                    dbname = Convert.ToString(dtreader[1]);
                    username = Convert.ToString(dtreader[2]);
                    pwd = Convert.ToString(dtreader[3]);
                    storeproc = Convert.ToString(dtreader[4]);
                }

                dtreader.Close();
                
                string conn_string = "Data Source = " + servername + "; " + "Integrated Security = false; "
                    + "Initial Catalog = " + dbname + "; " + "user id = " + username + "; "
                    + "password = " + pwd;
                string query1 = "select is_csv_upload from mst_csv_upload where request_name = '" + tablename + "'";
                SqlConnection connection_string1 = new SqlConnection(conn_string);
                connection_string1.Open();
                SqlCommand cmd1 = new SqlCommand(query1, connection_string1);
                bool is_csv_upload = (bool)cmd1.ExecuteScalar();

                
                

                if (is_csv_upload == true)
                {
                    if (openFileDialog1.FileName == "")
                    {
                        MessageBox.Show("File Not Selected");
                        return;

                    }//if (openFileDialog1.FileName == "")

                    string filepath = openFileDialog1.FileName;

                    string query2 = "select is_Flush from mst_csv_upload where request_name = '" + tablename + "'";
                    
                    SqlCommand cmd = new SqlCommand(query2, connection_string1);
                    bool flush_flag = (bool)cmd.ExecuteScalar();

                    string coundata = "SELECT COUNT(*) FROM " + tablename;
                    SqlCommand rowcount = new SqlCommand(coundata, connection_string1);//to count rows in the table present

                    if (flush_flag == true)
                    {


                        SqlTransaction transaction = connection_string1.BeginTransaction();
                        string query_trunc = "TRUNCATE TABLE " + tablename;
                        SqlCommand cmd_delete = new SqlCommand(query_trunc, connection_string1);
                        
                        cmd_delete.Transaction = transaction;
                        cmd_delete.CommandType = CommandType.Text;

                        cmd_delete.ExecuteNonQuery();
                        transaction.Commit();



                    }//End of If condition

                    long countStart = System.Convert.ToInt32(rowcount.ExecuteScalar());

                    

                    StreamReader sr = new StreamReader(filepath);
                    SW = File.CreateText(openFileDialog1.FileName + ".err");
                    

                    string line = sr.ReadLine();

                    string[] value = line.Split(',');

                    DataTable dt = new DataTable();

                    DataRow row;
                    


                    foreach (string dc in value)
                    {

                        dt.Columns.Add(new DataColumn(dc));
                    }

                    while (!sr.EndOfStream)
                    {
                        value = sr.ReadLine().Split(',');


                        if (value.Length == dt.Columns.Count)
                        {
                            row = dt.NewRow();

                            row.ItemArray = value;

                            dt.Rows.Add(row);
                        }
                        else
                        {
                            SW.WriteLine(value);
                        }
                    }   //End Of while

                    SqlBulkCopy bc = new SqlBulkCopy(connection_string1.ConnectionString, SqlBulkCopyOptions.TableLock);

                    bc.DestinationTableName = tablename;

                    bc.BatchSize = dt.Rows.Count;

                    try
                    {
                        
                        bc.WriteToServer(dt);[B]Error:Login failed for User[/B]
                        
                        bc.Close();


                        long countEnd = System.Convert.ToInt32(rowcount.ExecuteScalar());
                        countEnd = countEnd - countStart; //to check no of rows inserted in the table
                        MessageBox.Show("Data dumped to table : " + tablename + " and " + countEnd + " row(s) affected succesfully and also check the err file for the records which is not inserted");

                        /*if (MessageBox.Show("Do u want to process another file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {

                        }
                        else
                        {
                            foreach (Form ab in Application.OpenForms)
                            {
                                if (ab is Login_Form)
                                {
                                    ab.Close();
                                }

                            }

                            this.Close();


                        }*/


                    }
                    catch (Exception err)
                    {
                        MessageBox.Show(err.Message);
                    }
                
                }//if (is_csv_upload == true)
                
                try
                    {
                        if (storeproc != "" && storeproc != "NULL")    //This is for proc present for selected table
                        {
                            connection_string1.Open();
                            Uploadbutton.Enabled = false;
                            //MessageBox.Show("Proc-Name is present");
                            SqlCommand cmdproc = new SqlCommand(storeproc, connection_string1);
                            cmdproc.CommandType = CommandType.StoredProcedure;
                            Int32 I = (Int32)cmdproc.ExecuteScalar();
                            if (I == 1)
                            {
                                MessageBox.Show("Executed Successfully");
                            }
                            else
                            {
                                MessageBox.Show("Execution Failed");
                            }
                        }
                        if (MessageBox.Show("Do u want to process another file?", "Confirm", MessageBoxButtons.YesNo) == DialogResult.Yes)
                        {

                        }
                        else
                        {
                            foreach (Form ab in Application.OpenForms)
                            {
                                if (ab is Login_Form)
                                {
                                    ab.Close();
                                }

                            }

                            this.Close();


                        }

                        this.Close();
                    } // try 
                    catch (Exception err)
                    {
                        MessageBox.Show(err.Message);
                    }
                    finally
                    {
                        
                        connection_string1.Close(); //closing the connection
                    }

                      

                
            }

            catch (Exception err)
            {
                MessageBox.Show(err.Message);
            }

            finally
            {
                connection_string.Close();
                

            }   //If any exception occurs connection will always close   




        }//private void save_to_DBbutton_Click

            }//public partial class Form1 : Form
}//namespace Test1

Based on the error, I'd say that System.Configuration.ConfigurationManager.AppSettings["database"] doesn't have valid userid/password in it.

Thnaks Momerath and stefan for giving your valuable time
Ya its working fine because I have 2 form one for login ie authenticating emp code
after valid it goes to next page(which i am taking from config file) and it displays data in the combo-box related to that emp which i am connecting to database through connection string as its display tables in the combo-box, table might be from different database and different server thats why i have created a table whose columns contain server name,
database name ,user name and password(using this i am getting a new connection string which can help to dump data from csv to respective table of the respective data base)
For testing purpose i have created table called test and inserted 2 records
and i have one column called is_flush if its return true then it will truncate the data which is previously contain in the table.
This condition is working fine using new connection string(As i check using select * from test it gives 0 rows selected) and while
bc.WriteToServer(dt);It gives error at this point saying that login failed for user
I am not getting what is wrong in my code
Above is my code

This article has been dead for over six months. Start a new discussion instead.