hi
I am developing an application where, I am retriving values from one table in to a gridview and all rows of that gridview should be inserted to the other table in DB.with some additional values which I select from the dropdown list.on some condn if that particular row already exists in a table it should just get update otherwise it should get inserted into table.
I tried this code but its not working properly please advice me.
protected void btnview0_Click(object sender, EventArgs e)
{
callgrid();
}
protected void Btnrecv_Click(object sender, EventArgs e)
{
int wid = Convert.ToInt32( ddlwarehouseid.SelectedItem.ToString());
for (int i = 0; i < GridView1.Rows.Count; i++)
{
cmd = new SqlCommand("SELECT I.QTY FROM PROD_INVENTORY I, WAREHOUSES W, REQUESTED_PROD_DETAIL P WHERE W.WAREHOUSE_ID=" +wid + " AND P.PROD_GRP_ID=" + GridView1.Rows[i].Cells[1].Text + " AND P.PRODUCT_ID=" + GridView1.Rows[i].Cells[2].Text +"" , cn);
cn.Open();
dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows == true)
{
try{
cn.Close();
cmd = new SqlCommand("update PROD_INVENTORY set QTY=QTY+" + GridView1.Rows[i].Cells[3].Text + " where WAREHOUSE_ID=" + wid+ " AND PROD_GRP_ID=" + GridView1.Rows[i].Cells[1].Text + " AND PRODUCT_ID=" + GridView1.Rows[i].Cells[2].Text + "", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
catch (Exception ex)
{
Label3.Text= ex.Message.ToString();
}
}
else
{
try
{
cn.Close();
cmd = new SqlCommand(" insert into PROD_INVENTORY values (" + GridView1.Rows[i].Cells[1].Text + "," + GridView1.Rows[i].Cells[2].Text + ",'1','1'," + GridView1.Rows[i].Cells[3].Text + ",'sajid','" + DateTime.Now.ToString() + "','sajid','" + DateTime.Now.ToString() + "','Y','" + ddlwarehouseid.SelectedValue.ToString() + "')", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
catch (Exception ex)
{
Label3.Text= ex.Message.ToString();
}
}
}
}
public void warehouseid()
{
da=new SqlDataAdapter("select WAREHOUSE_ID from WAREHOUSES",cn);
DataSet ds = new DataSet();
da.Fill(ds);
ddlwarehouseid.DataSource = ds;
ddlwarehouseid.DataTextField = "WAREHOUSE_ID";
ddlwarehouseid.DataValueField = "WAREHOUSE_ID";
ddlwarehouseid.DataBind();
}
public void callgrid()
{
string id = ddlreqid.SelectedItem.ToString();
da = new SqlDataAdapter(" select rd.REQUEST_ID,rd.PROD_GRP_ID,rd.PRODUCT_ID,rd.QTY,rd.UNIT_PRICE,rm.SUPPLIER_ID,rm.TOTAL_AMOUNT,rm.EFFECTIVE_END_DATE,rm.EFFECTIVE_START_DATE,rm.DESCRIPTION,rm.STATUS from REQUESTED_PROD_DETAIL rd,REQUESTED_PROD_MST rm where rd.REQUEST_ID= rm.REQUEST_ID and rd.REQUEST_ID='" + id + "'", cn);
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
}
please give me some advice