SQL SELECT and Web Service
I have a web form with radiobutton list and checkbox list criterias which calls a Web service to generate SQL's SELECT statement. Then I use the "ExecuteScalar()" method to try and pass the one string value back to the web form that called the web service.
1) How do I concatenate the checkbox list into the SELECT statement, ie.,
"SELECT * FROM Customers
WHERE <radiobutton value> = 'A' + <checkbox value> = '1'
AND <radiobutton value> = 'A' + <checkbox value> = '2'
AND <radiobutton value> = 'A' + <checkbox value> = '3'
AND <radiobutton value> = 'B' + <checkbox value> = '1'"2) How do I return the record count string value of the ExecuteScalar() method back to the calling web form?
[798 byte] By [
techie3] at [2007-12-16]
1)
you should be able to find the radiobuttons and checkboxes by using the FindControl method.
| |
// find the checkbox with the Id corresponding to the one in the aspx file.
CheckBox cb = (CheckBox)FindControl("CheckBox1");
|
Same goes for the RadioButtons, but you might need to loop through the Controls collection of the RadioButtonList, calling FindControl on each control you encounter. (I know this is the fact for the Repeater control, not so sure about the RadioButtonList)
2)
The result of the ExecuteScalar() method, can be stored in a private variable, allowing it to be retrieved from a WebMethod, but that depends on what method you call in the webservice that causes the query to run. Can't you just return the value from there?
Besides that, try to avoid concatenating strings to build a query, instead of that do something like this:
| |
using(SqlConnection conn = new SqlConnection(connectString))
{
conn.Open(); using(SqlCommand command = new SqlCommand())
{
command.Connection = conn;
command.CommandText = "SELECT * WHERE @value1='a' AND @value2='b'"; // Assuming value1 and value2 are defined here
command.Parameters.Add("@value1", value1);
command.Parameters.Add("@value2", value2);
}
}
|
That will save you from injection attacks, and automatically quote the values when necessary.
