C# SQL query question...

Webmonkey wrote: »

I know nothing about C# but have you tried changing the query to:

selectSUM = "SELECT SUM(widget_price) [B]as mySum[/B] FROM Widgets WHERE SessionID = '" + this.Session.SessionID + "'";

and then reading the value from data set using 'mySum' as the column.

Hi Webmonkey, yeah I tried that before I posted but no luck. Thanks for the suggestion though...

symbolic wrote: »

label1.Text = convertTo.String(cmdP.ExecuteScalar());

Hi Symbolic...

I've tried that but now I get an error saying:

There is already an open DataReader associated with this Command which must be closed first.

I can't find the other datareader, there is another datareader but that is for reading in the other columns I have queried...

symbolic wrote: »

If you post all the code, I will have a look and see if I can help.

protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=mssql2005express.xxxxxxxxxxxxxx;Initial Catalog=xxxxx;User ID=xxxxxxx;Password=xxxxxxxxxxxxxxxx");
conn.Open();

if (this.Session["dummy"] == null) { this.Session["dummy"] = 1; } lblSessionID.Text = this.Session.SessionID;


string TransDateTime = DateTime.UtcNow.ToString("r");


string dropdown1 = DropDownList1.SelectedValue;
string dropdown2 = DropDownList2.SelectedValue;
string dropdown3 = DropDownList3.SelectedValue;
string Manufacturer = Label5.Text;
string Description = Label4.Text;
string Retail = Label3.Text;
string SessionID = lblSessionID.Text;


string sql = "insert into used_equipment (eq_type, eq_make, eq_model, SessionID, Retail_Price, Component_Manufacturer, Product_Description, Date_Time)\n";
sql += "values(";

sql += "'" + dropdown1 + "',";
sql += "'" + dropdown2 + "',";
sql += "'" + dropdown3 + "',";
sql += "'" + SessionID + "',";
sql += "'" + Retail + "',";
sql += "'" + Manufacturer + "',";
sql += "'" + Description + "',";
sql += "'" + TransDateTime + "')";

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
conn.Close();


////////////////////////////////////////////////////////////////

string selectSQLX;
string selectSUM;


SqlConnection connT = new SqlConnection("Data Source=mssql2005express.xxxxxxxxhelm;Initial Catalog=xxxxxxxxx;User ID=xxxxxxxx;Password=xxxxxxxxxxxxxx");
connT.Open();

selectSQLX = "SELECT * FROM used_equipment WHERE SessionID = '" + this.Session.SessionID + "'";
selectSUM = "SELECT SUM(Retail_Price) FROM used_equipment WHERE SessionID = '" + this.Session.SessionID + "'";

SqlCommand cmdy = new SqlCommand(selectSQLX, connT);
cmdy.CommandType = CommandType.Text;

SqlCommand cmdu = new SqlCommand(selectSUM, connT);
cmdu.CommandType = CommandType.Text;

SqlDataReader DataReader;
DataReader = cmdy.ExecuteReader();

if (DataReader.HasRows)
{
Label13.Text = "";

while (DataReader.Read())
{
object obj1 = new object();
obj1 = string.Format(DataReader["eq_type"].ToString());

object obj2 = new object();
obj2 = string.Format(DataReader["eq_make"].ToString());

object obj3 = new object();
obj3 = string.Format(DataReader["eq_model"].ToString());

object obj4 = new object();
obj4 = string.Format(DataReader["Component_Manufacturer"].ToString());

object obj5 = new object();
obj5 = string.Format(DataReader["Product_Description"].ToString());

object obj6 = new object();
obj6 = string.Format(DataReader["Retail_Price"].ToString());

string newstring = string.Concat(obj1," " + obj2, " " + obj3, "<br>" + obj4, " " + obj5, "<br> <p1>Retail Price: € " + obj6);
Label13.Text += string.Format(newstring.ToString() + "<br><br>");

Label16.Text = Convert.ToString(cmdu.ExecuteScalar());

}

}

DataReader.Close();
connT.Close();

}


protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{

}
protected void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
{

}


}