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

post a screen shot of the error or some data

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.

Did you put correct username and password for ur apps setting in web config?

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.