Simple SQL string question for totaling

[FoxT2]

Hello,

I need to build a SQL string that with return a total from a SQL database table field that I can pass to a textbox. I believe I have the correct syntax for doing this, however it is not working. Here is a brief example of my code. Also, when I execute this same SQL string using MS Visual FoxPro as my front end it returns a total to the variable total1 just fine.

strSQL = "SELECT SUM(units) AS total1 FROM cart"

cnSQL = New SqlConnection(ConnectionString)
cnSQL.Open()

cmSQL = New SqlCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()

txttotal.Text = CStr(total1)

Thanks,

FoxT

 

[mirirom]

hi FoxT,

the method you're calling, ExecuteNonQuery(), is the issue. it is infact executing your select statement, but this method doesn't return any values (except if you're using out params - see the documentation in VS.NET or MSDN). this method is typically called for INSERTs, UPDATEs, DELETEs, or stored procs that don't return rowsets. although it's possible to use this method for what you're trying to do, the workaround is a bit much for the task at hand.

try using ExecuteScalar(), which will return the first value in the first column of the resultset. be sure to define a variable of the appropriate type for the return value.

e.g.
if the value returned from your SUM fctn is an integral type....

//c#
int iMyValue = cmSQL.ExecuteScalar(strSQL, cnSQL);
txtTotal.Text = iMyValue;

//alternatively, this should also work
txtTotal.Text = cmSQL.ExecuteScalar(strSQL, cnSQL);

hope this helps.

..:: mirirom ::..

 

[FoxT2]

Thanks Mirriom, That worked great! The .ExecuteScalar method was a better solution.

FoxT