C# SQL query question...

Darragh29

Hi all,

I've a C# codebehind page that uses the following code to read out a few colums from my MS SQL 2005 DB, with a datareader, that works fine...

selectSQLX = "SELECT * FROM Widgets WHERE SessionID = '" + this.Session.SessionID + "'";

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

SqlDataReader DataReader;
DataReader = cmdy.ExecuteReader();

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

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

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


Label11.Text = obj1.ToString();
Label12.Text = obj2.ToString();
Label13.Text = obj3.ToString();
}

This works grand...

When I try to use the same approch as above, but want to run another SQL query that returns a sum for the widget_price DB column...

I've tried to use...

selectSUM = "SELECT SUM(widget_price) FROM Widgets WHERE SessionID = '" + this.Session.SessionID + "'";

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

But this won't work for me. What is happening is when I try to assign the selectSUM variable to a Label:

Label1.Text = string.Format(selectSUM);

Instead of getting a value, I get the whole string of "selectSUM="SELECT SUM......" printed back to the screen. I've done a bit of googling and I think I should be using an executescalar command. I've tried:

cmdP.CommandType = Executescalar(selectSUM);

But I keep getting compilation errors saying I can't do this that and the other no matter what way I try to build the line of code above...

Webmonkey

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.

Darragh29

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

Darragh29 wrote: »

Hi all,

I've a C# codebehind page that uses the following code to read out a few colums from my MS SQL 2005 DB, with a datareader, that works fine...

selectSQLX = "SELECT * FROM Widgets WHERE SessionID = '" + this.Session.SessionID + "'";

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

SqlDataReader DataReader;
DataReader = cmdy.ExecuteReader();

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

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

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


Label11.Text = obj1.ToString();
Label12.Text = obj2.ToString();
Label13.Text = obj3.ToString();
}

This works grand...

When I try to use the same approch as above, but want to run another SQL query that returns a sum for the widget_price DB column...

I've tried to use...

selectSUM = "SELECT SUM(widget_price) FROM Widgets WHERE SessionID = '" + this.Session.SessionID + "'";

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

But this won't work for me. What is happening is when I try to assign the selectSUM variable to a Label:

Label1.Text = string.Format(selectSUM);

Instead of getting a value, I get the whole string of "selectSUM="SELECT SUM......" printed back to the screen. I've done a bit of googling and I think I should be using an executescalar command. I've tried:

cmdP.CommandType = Executescalar(selectSUM);

But I keep getting compilation errors saying I can't do this that and the other no matter what way I try to build the line of code above...

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

Darragh29

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

Darragh29 wrote: »

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...

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

Darragh29

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)
{

}


}

Darragh29

I got this sorted by using a new connection string for the execute scalar command... Thanks to all for help...