Aug 12, 2006

Multiple Radio Buttons DataGrid

How To Create a DataGrid in ASP.NET with multiple radio buttons Per Row.

Sometimes you may be asked to create a datagrid in ASP.NET with multiple radio buttons that can be selected by each row. For example a user may ask for the option to reject, accept or cancel a product from a list of received product. Take a look at Figure 1.


ProductId
Name
AcceptRejectCancel
17
Alice Mutton
AcceptRejectCancel
3
Aniseed Syrup
AcceptRejectCancel
40
Boston Crab Meat
AcceptRejectCancel
60
Camembert Pierrot
AcceptRejectCancel
18
Carnarvon Tigers
AcceptRejectCancel
1
Chai
AcceptRejectCancel
2
Chang
AcceptRejectCancel
39
Chartreuse verte
AcceptRejectCancel
4
Chef Anton's Cajun Seasoning
AcceptRejectCancel
5
Chef Anton's Gumbo Mix
Figure 1. List of products with multiple radio buttons

The question is: is it easy to do it in ASP.NET. The answer is provided in this article.

To proceed you should create a Visual Basic web site.
1. Name the project MultipleRadioButton.
2. Now take a datagrid from the toolbox and post it on webform1.
3. Add a template column to the datagrid
4. Enter template editing for the template column
5. Add a label in the template item field and name it LblStatus
6. End Template editing
7. Go back to the web form and create a button and name it BtnProcess
8. Drag a textbox from toolbox and place it on the webform and name it TxtProcess and set the TextMode to MultiLine

For a complete web form design take a look a figure 2 below.

Figure 2 A complete design of the page

Now, for the page load event you have the following code that should be inserted:
Dim MySQLConnection As New SqlClient.SqlConnection
Dim MySQLCommand As New SqlClient.SqlCommand
Dim MyDataAdapter As New SqlClient.SqlDataAdapter
Dim MyDataSet As New Data.DataSet
MySQLConnection.ConnectionString = "Data Source=.; Initial Catalog=Northwind; User ID=sa; Password=pass@word1"
MySQLConnection.Open()
MySQLCommand.Connection = MySQLConnection
MySQLCommand.CommandType = CommandType.Text
MySQLCommand.CommandText = "Select TOP 10 ProductId, ProductName From Products"
MyDataAdapter.SelectCommand = MySQLCommand
MyDataAdapter.Fill(MyDataSet, "Products")
DataGrid1.DataSource = MyDataSet
DataGrid1.DataMember = "Products"
DataGrid1.DataBind()

Remember that you have to change the connection string as to connect to your database server. Also to create the multiple radio buttons on the fly, copy the following code:

Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound
If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
Dim r As Label
r = e.Item.FindControl("LblStatus")
r.Text = "Accept" & _
"Reject" & _
"Cancel"
End If
End Sub
If you run your project now, you will receive figure 3 but when you click Process nothing will happen:
Figure 3 A run time presentation of your project
In order to process the rows after the user select his option, you have to post the following code for the BtnProcess click event.

Private Sub BtnProcess_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnProcess.Click
Dim RequestString As String, Ind As DataGridItem, RequestValue As String, RowNumber As Long
RowNumber = 1
For Each Ind In DataGrid1.Items
RequestString = "mycheckoption" & Ind.ItemIndex
If Ind.ItemType = ListItemType.AlternatingItem Or Ind.ItemType = ListItemType.Item Then
Dim r As Label
r = Ind.FindControl("LblStatus")
RequestString = "mycheckoption" & Ind.ItemIndex
RequestValue = Request.Form(RequestString)
TxtProcess.Text = TxtProcess.Text & "Row " & RowNumber & " has status " & RequestValue & vbCrLf
RowNumber += 1
End If
Next
End Sub
Now, run your project and make your selections then click the Process button. Something similar to figure 4 should happen depending on your selections.
Figure 4. A complete test of project

Thank you for reading my article and see you in the next one….

How to Create a Complete Dynamic SQL Statement in SQL Server

How to Create a Complete Dynamic SQL Statement in SQL Server
Restrictions
Microsoft SQL Server 2000 does not allow the use of variables in some parts of your SQL Statement. Two examples would be selecting a variable top number of rows or defining a variable where clause. However, there are several ways to overcome those obstacles. In this article, I will define an SQL stored procedure called DynamicSQL. This procedure allows the caller to provide multiple parameters:
Solution
1) How many top rows the user would like to select (0 if all rows are to be selected)?
2) A field list (separated by commas) to be selected or ‘*’ to select all fields in the table(s)
3) A table name or a list of tables separated by comma
4) An order by clause
5) A Where Clause
6) An output parameter if used (null if not provided)
7) An output parameter type (int, bigint, etc…)
A copy of the stored procedure is shown below:
Use Northwind
Create Procedure DynamicSql (@TopRows int, @FieldList varchar(1000), @TableName varchar(100), @OrderDirection varchar(500) = null, @DynamicWhere varchar(1000) = null, @OutParamater varchar(100) = null Output, @OutType varchar(15)= null )
As
Begin
Declare @MyNewSql nvarchar(4000)
Declare @ParamList nvarchar(500)
Declare @ParamString nvarchar(500)
-- insert the top number of rows in the sql as well as the fields list and the table name
If @TopRows <= 0
Begin
Set @MyNewSql = 'Select '
End
Else
Begin
Set @MyNewSql = 'Select Top ' + Convert(varchar,@TopRows) + ' '
End
Set @MyNewSql = @MyNewSql + @FieldList + ' From ' + @TableName
-- Insert the dynamic where now
if not @DynamicWhere is null
Begin
Set @MyNewSql = @MyNewSql + ' ' + @DynamicWhere
End
-- INSERT THE DYNAMIC ORDER BY CLAUSE
if not @OrderDirection is null
Begin
Set @MyNewSql = @MyNewSql + ' ' + @OrderDirection
End
-- CHECK IF THERE IS AN OUTPUT PARAMETER
If Not @OutParamater is null
Begin
Set @ParamList = @OutParamater + ' ' + @OutType + ' Output'
Set @ParamString = @OutParamater + 'Output'
Exec SP_EXECUTESQL @MyNewSql, @ParamList, @ParamString
End
Else
Begin
Execute(@MyNewSql)
End
End
Listing 1: The Complete Copy of The DyanmicSQL Stored Procedure

Explanation
I think that the procedure is self-explainable with some comments inside it. Note that in order to use this procedure it is enough to copy and paste the procedure to any database. (In my case, the procedure was created in the Northwind example database that ships with Microsoft SQL Server 2000).
Note that the execute statement provided at the end of the procedure is the key to this procedure. The Execute statement is provided by SQL Server to execute any dynamic SQL statement at run time. As for the execution of a procedure with parameters, I am calling a stored procedure called SP_EXECUTESQL that would create virtual stored procedure on the fly with the supplied parameters and return the result to you.
However, there are several precautions that you should take into consideration when using this procedure:
1) The SQL Server engine has no way of checking the syntax of the SQL statement that you have provided until it is run. i.e. If you supply an invalid table name or invalid field name, the SQL statement will not run correctly but you will notified of this and you have to catch this error in your code.
2) The procedure provides a way to a hacker to invoke a high risk SQL Injection statement that may affect your database.
3) The new SQL Statement is not compiled in the database i.e. loose the speed of execution that SQL Server provides with a compiled stored procedure.
Examples
An example of calling the stored procedure is provided below that would select the first 5 rows from table called Products, order them by the ProductName field after filtering for the product name that start with the letter A from the Northwind database. You can copy this code directly to SQL Server Query analyzer and
Use Northwind
Exec DynamicSql 5,'ProductName, SupplierID,QuantityPerUnit','Products','Order By ProductName','Where ProductName like ''A%'''
The following result is generated:
ProductName SupplierID QuantityPerUnit
Alice Mutton 7 20 - 1 kg tins
Aniseed Syrup 1 12 - 550 ml bottles
Another example with the usage of the output parameter is presented below:
Exec DynamicSql 1,'@ProdName = ProductName','Products',null,'Where ProductName like ''A%''','@ProdName', 'varchar'
This example would return the string ‘Alice Mutton’ to the calling part.
Risky Point To Avoid
In order to prevent or minimize the probability of SQL injection to happen, you can check for any semicolon in the string and replace it with a space. An example replace function is presented below:
CREATE FUNCTION dbo.CleanChars
(@Str varchar(8000)) Returns Varchar(8000)
Begin
While CHARINDEX(';', @Str) > 0 Set @Str = replace(@Str, Substring(@Str,CHARINDEX(';',@Str),2),' ')
return @Str
End
Listing 2- The CleanChars Function used to prevent SQL Injection

This function would clean the passed string from any semicolon and replace it with a space. Not that every string passed to DynamicSql stored procedure should be checked against the CleanChars function in the following pattern:
Set @FieldList = dbo.CleanChars(@FieldList)
After doing the check for every alphanumeric passed variable, you should be safe when calling the DynamicSql stored procedure.
Thank you for giving the time to read my article.