Get the Data from Database to datagridview column.

See more:

.NET2.0

.NET3.5

.NET

, +

I have a datagridview in which there are 4 columns
________________________________________________
|Product Name | Old Stock | New Stock | Current Stock|
|A|
|B|
|C|
|D|
|E|_____________________________________________
in first Column there are products A, B,C,D,E. that is connected to database.
I want to load values in New Stock column i.e. 3rd column from table New_Stk on form Load event. The New_Stk table has following columns Date, Product_Name, Qty. Value should be loaded in following way: for eg. in datagridview first column vales are loaded, so the first cell value is A, then while getting value from New_Stk table, it should match the product name i.e A and current date and then show value in cell of 3rd column in datagridview.

I have written following Code:-

string date, M_name, cnstp, query;
            date = DateTime.Today.ToShortDateString();            
            cnstp = ConfigurationManager.ConnectionStrings["Your_ConnectionString"].ConnectionString;
            SqlConnection conr = new SqlConnection(cnstp);
            for (int i = 0; i <= dgvStock.Rows.Count; i++)
            {
                M_name = dgvStock.Rows[i].Cells[0].Value.ToString();
                query = "Select distinct Qty from New_Stk where Product_Name = '" + "M_name" + " and Date = " + "date" + "'";
                SqlDataAdapter adpi = new SqlDataAdapter("query", conr);
                DataSet ds = new DataSet();
                adpi.Fill(ds);
                dgvStock.DataSource = ds.Tables[0].DefaultView;
                dgvStock.Rows[i].Cells[0].Value = query;

Thanks in Advance !!!