Excel data needs to be updated in existing SQL table

Hi,

If issue resolved by my suggestion, please marked it.

Thanks
Alok Saxena

Hi,

I read the all thread. All are correct the issue in Binding() method.
What I feel in this method you are missing the code which fetch data into dtProducts (DataTable). Here in this code you are created a new DataTable and next two lines adding two columns.
And very next lines you are using foreach (DataRow DR in dtProducts.Rows).
There is a issue. Actually dtProducts is empty.




Regards
Alok Saxena

hi,

Try use LinqToExcel (https://code.google.com/p/linqtoexcel/) to retrieve data from Excel to c#object which is more faster and you dont have to have a dependency on OLEDB etc. 

This is my complete code now

Hi before foreach please add below code and please check the value in column and then check in the database.

I am getting in the binding time.

Error in  foreach statement (Error: dtProducts.Rows Does not exist in current context)

Hi, where you are getting this error while updating to database or binding time.

I am getting an error in  dtProducts.Rows(Error: Does not exist in current context)

When I give the following, error disappears

Hi try this code for update

private void BindGrid()


    {
string Constr = null;
            SqlConnection con ;
            SqlCommand cmd ;
            string UpdateQuery = null;
                Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo";
             con = new SqlConnection(Constr);
          
          
              foreach (DataRow DR in dtProducts.Rows)
              {
             con.Open();

                  UpdateQuery = "Update ParcelInf Set Status='"+ DR["Status"]+"',Remarks='"+DR["Remarks"]+"' where RequestID="+DR["Remarks"];
               cmd = new SqlCommand(UpdateQuery, con);
                  cmd.ExecuteNonQuery();
                cmd.Dispose();
                  con.Close();
              }
             
}

If I give the below in SQL, it works fine

Update ParcelInf Set Status='Open',Remarks='Pending' where RequestID=1;

But still the code which you have provided is not updating in SQL

Still it is not giving the output.

I dont know where I am going wrong

sorry i forgot to execute query
try this
private void BindGrid()


    {
                String Constr = "Data Source=WIN-A876U316VGA;integrated security=true;Initial Catalog=CUIDinfo";
            SqlConnection con = new SqlConnection(Constr);
            con.Open();
           
              foreach (DataRow DR in dtProducts.Rows)
              {


                  String UpdateQuery = "Update ParcelInf Set Status='"+ DR["Status"]+"',Remarks='"+DR["Remarks"]+"' where RequestID="+DR["Remarks"];
                  SqlCommand cmd = new SqlCommand(UpdateQuery, con);
                  cmd.CommandText = UpdateQuery;
                  cmd.CommandType = CommandType.Text;

                  cmd.ExecuteNonQuery();

                
                  cmd.Connection = con;
              }
              con.Close();
}

see you r putting  your excel data into data table named dtProducts, if yes then
replace my code ds.Table[0] to dtProducts 

Kindly share your code
By

Thanks Kailash for your reply.

I want to use the update query to update the detail in the existing table.

I already tried to using the bulk data

Hi Azarudeen,
You can use  sqlbulkcopy to insert your bulk data (gridview data) into sql, kindly go throw the link will help you

http://www.c-sharpcorner.com/UploadFile/87b416/performing-bulk-copy-in-ado-net/

Kindly mark as answer, if this helps you
Thanks,
Kailash