Distinct Qry err:
cmd = new SqlCommand("select sum(DISTINCT credit) AS total from creditadd where cid<=cid and userid='" + TextBox1.Text + "' and username='" + TextBox2.Text + "'", con);
in updating:
i have updated total amount successfully but i am not getting correct total after updating it shows only already entered. what i have modify in my codings:
full codings:
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd =
new SqlCommand("select Count(*) from agentreg where userid='" + TextBox1.Text + "' and username='" + TextBox2.Text + "'", con);
int i = (int)cmd.ExecuteScalar();
if (i != 0)
{
cmd =
new SqlCommand("select count (username) from creditadd where username='" + TextBox2.Text + "' and userid='" + TextBox1.Text + "'", con);
int j = (int)cmd.ExecuteScalar();
if (j != 0)
{
cmd =
new SqlCommand("select sum(DISTINCT credit) AS total from creditadd where cid<=cid and userid='" + TextBox1.Text + "' and username='" + TextBox2.Text + "'", con);
dr = cmd.ExecuteReader();
dr.Read();
if (dr.HasRows)
{
total =
float.Parse(dr[0].ToString());
float f = total + float.Parse(TextBox3.Text);
dr.Close();
cmd =
new SqlCommand("insert into creditadd(userid,username,services,credit,total)values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + DropDownList1.SelectedItem.Value + "','" + TextBox3.Text + "','"+f+"')", con);
cmd.ExecuteNonQuery();
TextBox1.Text =
"";
TextBox2.Text =
"";
TextBox3.Text =
"";
Response.Write(
"<script>alert('Successfull');</script>");
bindgrid();
dropbind();
}
else
{
dr.Close();
}
}
else
{
cmd =
new SqlCommand("insert into creditadd(userid,username,services,credit,total)values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + DropDownList1.SelectedItem.Value + "','" + TextBox3.Text + "','" + TextBox3.Text + "')", con);
cmd.ExecuteNonQuery();
TextBox1.Text =
"";
TextBox2.Text =
"";
TextBox3.Text =
"";
Response.Write(
"<script>alert('Successfull');</script>");
bindgrid();
dropbind();
}
}
else
{
Response.Write(
"<script>alert('Not Registered');</script>");
}
}
}
catch (Exception ex)
{
Response.Write(
"Error:" + ex.ToString());
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
edit/update/del codings :
protected
void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
if (e.CommandName == "can")
{
GridView1.EditIndex = -1;
bindgrid();
dropbind();
}
else if (e.CommandName == "upd")
{
LinkButton lkbupd = (LinkButton)e.CommandSource as LinkButton;
GridViewRow row = lkbupd.NamingContainer as GridViewRow;
Label lbid = (Label)row.FindControl("lblid");
TextBox txcredit = (TextBox)row.FindControl("txtcredit"
);
TextBox txtotal = (TextBox)row.FindControl("txttotal");
cmd =
new SqlCommand("update creditadd set credit='" + txcredit.Text + "',total='"+txtotal.Text+"' where cid ='" + lbid.Text + "'", con);
cmd.ExecuteNonQuery();
GridView1.EditIndex = -1;
Response.Write(
"Updated Successfully");
bindgrid();
dropbind();
}
else if (e.CommandName == "del")
{
LinkButton lnkdel = (LinkButton)e.CommandSource as LinkButton;
GridViewRow row = lnkdel.NamingContainer as GridViewRow;
Label lid = (Label)row.FindControl("lblid");
cmd =
new SqlCommand("delete from creditadd where cid='" + lid.Text + "'", con);
cmd.ExecuteNonQuery();
Response.Write(
"Deleted Successfully");
bindgrid();
dropbind();
}
}
catch (Exception ex)
{
Response.Write(
"Error:" + ex.ToString());
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}