How can I work out the number of returned results from an SQL statement

[Tomie]

Hello,

I have a really slim, simple shopping basket type thing where I have a catalog page and a buyIt page. Now I need to be able to return the number of rows which have been returned from the SQL statement which is executed. For example, if the product already exists in the tCart table then it should only update the quantity, otherwise chuck the whole new row in.


void setSQLcommand(string param) { // put SQL into command object
if(command == null) { // if no command object yet, create one
command = new OleDbCommand();
command.Connection = conn;
}
command.CommandText = param; // pass the SQL into the command object
}

void executeNonQuery(string SQL) { // issue a non-Select SQL statement
setSQLcommand(SQL); // put statement into command object
Trace.Write("ExecNQ", SQL);
conn.Open();
command.ExecuteNonQuery(); // execute SQL in DBMS
conn.Close(); // keep open for the shortest time possible
}

// SQL statement to check if the product is present in the cart * need something to count the number of returned rows !
string queryString = "SELECT * FROM tCart WHERE productCode = " + Session["sproductid"].ToString() + " AND orderID = " + "'" + Session["orderID"].ToString() + "'" + ")";
executeNonQuery(queryString );

if() { // if the product already exists in the cart
string updateSQL = "UPDATE tCart SET quantity = quantity + " + txtQuantity.Text + " WHERE WHERE productCode = " + Session["sproductid"].ToString() + " AND orderID = " + "'" + Session["orderID"].ToString() + "'" + ")"; // only update the quantity
executeNonQuery(updateSQL);
}
else {
string insertSQL = "INSERT INTO tCart (productCode, quantity, orderID) VALUES ("+ Session["sproductid"].ToString() + "," + txtQuantity.Text + "," + "'" + Session["orderID"].ToString() + "'" + ")";
executeNonQuery(insertSQL);
}


Anyone any ideas please, something which can return to me the number of rows which were found when a select statement is executed.

Thank you for any advice !

[takkie]

executeNonQuery itself returns the number of rows affected...

do this.

int num = executeNonQuery(sqlstr);

[Cstick]

Or you could use "SELECT COUNT(*) FROM .....", that will return the number of rows which match your search condition.

ExecuteNonQuery will work as well, so long as "SET NOCOUNT" is off. By default it is off.

[Ubik]

Another thing you can do is assign your recordset to an array and use ubound(myarray).

In Classic ASP, it is better to work with arrays rather than recordsets to eliminate multiple db calls, however, I haven't learned .NET yet, and so I don't know if this is the case with .NET applications.

My $.02

[sirpelidor]

i just did something very similiar yesterday, yet it just another idea for you to review:

i copy the result of my sql query to a dataset.
then I used: ds.Tables[0].Rows.Count to determine the # of rows.

my code was alone the line of:
if (ds.Tables[0].Rows.Count == 0) blah;
else blah blah;

[Tomie]

Thank you for the replies.

When I try...


int num = executeNonQuery(queryString);


An error message says:

CS0029: Cannot implicitly convert type 'void' to 'int'

Any ideas please?