Parameterized Query and Record Filters

T

TF

This group came through for me last time so here we go again. My page
shows paint colors, brand name, product code, etc in a gridview with
the background matching the paint color. Several links on the page are
used to call itself with querystring values for brand, thinner,
finish, etc. The page must show all colors if the querystring is
empty and filter out records that don't match the querystring value
when it's not. I've tried building a parameterized query in Vis Web
Dev Exp but can't get it to work right. As a workaround, I wrote code
in the GridView1_RowDataBound event that turns the data rows invisible
if they don't fit the query right before they get the back color set.
Is there a better way (less code, quicker execution) to do this? In
ASP I would set the objPaintsRS.filter to whatever I needed. A couple
lines of code. Here's the code for the page. Thanks very much in
advance.

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://
www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal
e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
If Request.QueryString.Count > 0 Then
'filter to show brand from query string
If Len(Request.QueryString("brand")) <> 0 Then
If LCase(Request.QueryString("brand")) <>
LCase(e.Row.DataItem("brand")) Then
e.Row.Visible = False
End If
End If
'filter to show standards-based from query string
If Len(Request.QueryString("standard")) <> 0 Then
If LCase(Request.QueryString("standard")) = "fs"
Then
If e.Row.DataItem("fsnumber") = "" Then
e.Row.Visible = False
End If
ElseIf LCase(Request.QueryString("standard")) =
"rlm" Then
If e.Row.DataItem("rlmnumber") = "" Then
e.Row.Visible = False
End If
End If
End If
'filter to show thinnedby from query string
If Len(Request.QueryString("thinnedby")) <> 0 Then
If LCase(Request.QueryString("thinnedby")) <>
LCase(e.Row.DataItem("thinnedby")) Then
e.Row.Visible = False
End If
End If
'filter to show type from query string
If Len(Request.QueryString("type")) <> 0 Then
If LCase(Request.QueryString("type")) <>
LCase(e.Row.DataItem("type")) Then
e.Row.Visible = False
End If
End If
'filter to show container from query string
If Len(Request.QueryString("container")) <> 0 Then
If LCase(Request.QueryString("container")) <>
LCase(e.Row.DataItem("container")) Then
e.Row.Visible = False
End If
End If
'filter to show finish from query string
If Len(Request.QueryString("finish")) <> 0 Then
If LCase(Request.QueryString("finish")) <>
LCase(e.Row.DataItem("finish")) Then
e.Row.Visible = False
End If
End If
'filter to show colorgroup from query string
If Len(Request.QueryString("colorgroup")) <> 0 Then
If LCase(Request.QueryString("colorgroup")) <>
LCase(e.Row.DataItem("colorgroup")) Then
e.Row.Visible = False
End If
End If
End If
'set background color
Dim c As String
c = "#" & e.Row.DataItem("RGBHex")
e.Row.BackColor =
System.Drawing.ColorTranslator.FromHtml(c)
'set font color
If e.Row.DataItem("redvalue") +
e.Row.DataItem("greenvalue") + e.Row.DataItem("bluevalue") < 382 Then
e.Row.ForeColor = Drawing.Color.White
End If
End If
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<table width="100%">
<tr>
<td width="1%" valign="top">
<asp:panel ID="Panel1" runat="server"
Height="700px" ScrollBars="Both" Width="1865px">
<table cellpadding="2">
<tr>
<td>
<br />
<asp:GridView ID="GridView1"
runat="server" AutoGenerateColumns="False"
DataSourceID="AccessDataSource1"

OnRowDataBound="GridView1_RowDataBound" AllowSorting="True"
CellPadding="3" HeaderStyle-Font-Names="verdana"
HeaderStyle-Font-
Size="Small" RowStyle-Font-Names="verdana" RowStyle-Font-Size="Small">
<Columns>
<asp:BoundField
DataField="Brand" HeaderText="Brand" SortExpression="Brand" />
<asp:BoundField
DataField="Productcode" HeaderText="Prod Code"
SortExpression="Productcode" >
<HeaderStyle
Width="55px" />
</asp:BoundField>
<asp:BoundField
DataField="Colorname" HeaderText="Color Name"
SortExpression="Colorname" >
<HeaderStyle
Width="210px" />
</asp:BoundField>
<asp:BoundField
DataField="ThinnedBy" HeaderText="Thinner" SortExpression="ThinnedBy" /<asp:BoundField
DataField="Type" HeaderText="Type" SortExpression="Type" />
<asp:BoundField
DataField="Container" HeaderText="Container"
SortExpression="Container" >
<HeaderStyle
Width="65px" />
</asp:BoundField>
<asp:BoundField
DataField="Finish" HeaderText="Finish" SortExpression="Finish" >
<HeaderStyle
Width="50px" Wrap="False" />
</asp:BoundField>
<asp:BoundField
DataField="RLMNumber" HeaderText="RLM Std" SortExpression="RLMNumber"<HeaderStyle
Width="60px" />
</asp:BoundField>
<asp:BoundField
DataField="FSNumber" HeaderText="FS Std" SortExpression="FSNumber" >
<HeaderStyle
Width="70px" />
</asp:BoundField>
<asp:BoundField
DataField="RedValue" HeaderText="Red Value" SortExpression="RedValue"
Visible="False" />
<asp:BoundField
DataField="ColorGroup" HeaderText="Color Group"
SortExpression="ColorGroup"
Visible="False" />
<asp:BoundField
DataField="GreenValue" HeaderText="Green Value"
SortExpression="GreenValue"
Visible="False" />
<asp:BoundField
DataField="BlueValue" HeaderText="Blue Value"
SortExpression="BlueValue"
Visible="False" />
<asp:BoundField
DataField="RGBHex" HeaderText="RGB Hex" SortExpression="RGBHex"
Visible="False" />
<asp:TemplateField
HeaderText="Links">
<ItemTemplate>
<asp:HyperLink
ID="HyperLink1" runat="server">HyperLink</asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle Font-
Names="verdana" Font-Size="Small" />
<RowStyle Font-
Names="verdana" Font-Size="Small" />
</asp:GridView>
<asp:AccessDataSource
ID="AccessDataSource1" runat="server" DataFile="~/App_Data/
PaintsDbase.mdb"
SelectCommand="SELECT
[Brand], [Productcode], [ThinnedBy], [Type], [Container], [Finish],
[Colorname], [RLMNumber], [FSNumber], [ColorGroup], [RedValue],
[GreenValue], [BlueValue], [RGBHex] FROM [AllPaints] ORDER BY [Brand],
[Productcode]">
</asp:AccessDataSource>
&nbsp;<br />
<br />
</td>
</tr>
</table>
</asp:panel>
</td>
<td valign="top" nowrap="noWrap">
<br />
<table>
<tr>
<td nowrap="noWrap">
Basic Filters</td>
</tr>
<tr>
<td style="width: 3px">
<span style="color: #0000ff; text-
decoration: underline">Show All</span><br />
</td>
</tr>
<tr>
<td style="width: 3px"
nowrap="noWrap">
Brands<br />
&nbsp; <a href="tool.aspx?
brand=delta ceramcoat">Delta Ceramcoat</a><br />
&nbsp; <a href="tool.aspx?
brand=humbrol">Humbrol</a><br />
&nbsp; <a href="tool.aspx?
brand=model master">Model Master</a><br />
&nbsp; <a href="tool.aspx?
brand=polly scale">Polly Scale</a><br />
&nbsp; <a href="tool.aspx?
brand=tamiya">Tamiya</a><br />
Standards-Based<br />
&nbsp; <a href="tool.aspx?
standard=rlm">RLM</a><br />
&nbsp; <a href="tool.aspx?
standard=fs">FS</a><br />
<br />
Type<br />
&nbsp; <a href="tool.aspx?
type=acrylic">Acrylic</a><br />
&nbsp; <a href="tool.aspx?
type=enamel">Enamel<br />
</a>
<br />
<br />
Thinned By<br />
&nbsp; <a href="tool.aspx?
thinnedby=acetone">Acetone<br />
</a>&nbsp; <a href="tool.aspx?
thinnedby=alcohol">Alcohol<br />
</a>&nbsp; <a href="tool.aspx?
thinnedby=ammonia">Ammonia</a><br />
&nbsp; <a href="tool.aspx?
thinnedby=spirits">Spirits<br />
</a>&nbsp; <a href="tool.aspx?
thinnedby=water">Water</a><br />
&nbsp; <a href="tool.aspx?
thinnedby=xylene">Xylene</a><br />
<br />
Container<br />
&nbsp;&nbsp;<a href="tool.aspx?
Container=bottle">Bottle</a><br />
&nbsp; <a href="tool.aspx?
Container=spray">Spray</a><br />
&nbsp; <a href="tool.aspx?
Container=tinlet">Tinlet</a><br />
<br />
Finish<br />
&nbsp; <a href="tool.aspx?
finish=flat">Flat</a><br />
&nbsp; <a href="tool.aspx?
finish=gloss">Gloss</a><br />
&nbsp; <a href="tool.aspx?
finish=metallic">Metallic</a><br />
&nbsp; <a href="tool.aspx?
finish=metalcote">Metalcote</a><br />
&nbsp; <a href="tool.aspx?
finish=semi gloss">Semi Gloss<br />
</a>
<br />
</td>
</tr>
</table>
<br />
<a href="tool.aspx"></a>
<br />
<span style="text-decoration: underline"><span
style="color: #0000ff"></span></span>
<br />
<a href="tool.aspx?finish=semi-gloss"></a>
<br />
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
</table>
<br />
</div>
</form>
</body>
</html>
 
T

TF

This group came through for me last time so here we go again. My page
shows paint colors, brand name, product code, etc in a gridview with
the background matching the paint color. Several links on the page are
used to call itself with querystring values for brand, thinner,
finish, etc. The page must show all colors if the querystring is
empty and filter out records that don't match the querystring value
when it's not. I've tried building a parameterized query in Vis Web
Dev Exp but can't get it to work right. As a workaround, I wrote code
in the GridView1_RowDataBound event that turns the data rows invisible
if they don't fit the query right before they get the back color set.
Is there a better way (less code, quicker execution) to do this? In
ASP I would set the objPaintsRS.filter to whatever I needed. A couple
lines of code. Here's the code for the page. Thanks very much in
advance.

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal
e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
If Request.QueryString.Count > 0 Then
'filter to show brand from query string
If Len(Request.QueryString("brand")) <> 0 Then
If LCase(Request.QueryString("brand")) <>
LCase(e.Row.DataItem("brand")) Then
e.Row.Visible = False
End If
End If
'filter to show standards-based from query string
If Len(Request.QueryString("standard")) <> 0 Then
If LCase(Request.QueryString("standard")) = "fs"
Then
If e.Row.DataItem("fsnumber") = "" Then
e.Row.Visible = False
End If
ElseIf LCase(Request.QueryString("standard")) =
"rlm" Then
If e.Row.DataItem("rlmnumber") = "" Then
e.Row.Visible = False
End If
End If
End If
'filter to show thinnedby from query string
If Len(Request.QueryString("thinnedby")) <> 0 Then
If LCase(Request.QueryString("thinnedby")) <>
LCase(e.Row.DataItem("thinnedby")) Then
e.Row.Visible = False
End If
End If
'filter to show type from query string
If Len(Request.QueryString("type")) <> 0 Then
If LCase(Request.QueryString("type")) <>
LCase(e.Row.DataItem("type")) Then
e.Row.Visible = False
End If
End If
'filter to show container from query string
If Len(Request.QueryString("container")) <> 0 Then
If LCase(Request.QueryString("container")) <>
LCase(e.Row.DataItem("container")) Then
e.Row.Visible = False
End If
End If
'filter to show finish from query string
If Len(Request.QueryString("finish")) <> 0 Then
If LCase(Request.QueryString("finish")) <>
LCase(e.Row.DataItem("finish")) Then
e.Row.Visible = False
End If
End If
'filter to show colorgroup from query string
If Len(Request.QueryString("colorgroup")) <> 0 Then
If LCase(Request.QueryString("colorgroup")) <>
LCase(e.Row.DataItem("colorgroup")) Then
e.Row.Visible = False
End If
End If
End If
'set background color
Dim c As String
c = "#" & e.Row.DataItem("RGBHex")
e.Row.BackColor =
System.Drawing.ColorTranslator.FromHtml(c)
'set font color
If e.Row.DataItem("redvalue") +
e.Row.DataItem("greenvalue") + e.Row.DataItem("bluevalue") < 382 Then
e.Row.ForeColor = Drawing.Color.White
End If
End If
End Sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<br />
<table width="100%">
<tr>
<td width="1%" valign="top">
<asp:panel ID="Panel1" runat="server"
Height="700px" ScrollBars="Both" Width="1865px">
<table cellpadding="2">
<tr>
<td>
<br />
<asp:GridView ID="GridView1"
runat="server" AutoGenerateColumns="False"
DataSourceID="AccessDataSource1"

OnRowDataBound="GridView1_RowDataBound" AllowSorting="True"
CellPadding="3" HeaderStyle-Font-Names="verdana"
HeaderStyle-Font-
Size="Small" RowStyle-Font-Names="verdana" RowStyle-Font-Size="Small">
<Columns>
<asp:BoundField
DataField="Brand" HeaderText="Brand" SortExpression="Brand" />
<asp:BoundField
DataField="Productcode" HeaderText="Prod Code"
SortExpression="Productcode" >
<HeaderStyle
Width="55px" />
</asp:BoundField>
<asp:BoundField
DataField="Colorname" HeaderText="Color Name"
SortExpression="Colorname" >
<HeaderStyle
Width="210px" />
</asp:BoundField>
<asp:BoundField
DataField="ThinnedBy" HeaderText="Thinner" SortExpression="ThinnedBy" /

<asp:BoundField
DataField="Type" HeaderText="Type" SortExpression="Type" />
<asp:BoundField
DataField="Container" HeaderText="Container"
SortExpression="Container" >
<HeaderStyle
Width="65px" />
</asp:BoundField>
<asp:BoundField
DataField="Finish" HeaderText="Finish" SortExpression="Finish" >
<HeaderStyle
Width="50px" Wrap="False" />
</asp:BoundField>
<asp:BoundField
DataField="RLMNumber" HeaderText="RLM Std" SortExpression="RLMNumber"

<HeaderStyle
Width="60px" />
</asp:BoundField>
<asp:BoundField
DataField="FSNumber" HeaderText="FS Std" SortExpression="FSNumber" >
<HeaderStyle
Width="70px" />
</asp:BoundField>
<asp:BoundField
DataField="RedValue" HeaderText="Red Value" SortExpression="RedValue"
Visible="False" />
<asp:BoundField
DataField="ColorGroup" HeaderText="Color Group"
SortExpression="ColorGroup"
Visible="False" />
<asp:BoundField
DataField="GreenValue" HeaderText="Green Value"
SortExpression="GreenValue"
Visible="False" />
<asp:BoundField
DataField="BlueValue" HeaderText="Blue Value"
SortExpression="BlueValue"
Visible="False" />
<asp:BoundField
DataField="RGBHex" HeaderText="RGB Hex" SortExpression="RGBHex"
Visible="False" />
<asp:TemplateField
HeaderText="Links">
<ItemTemplate>
<asp:HyperLink
ID="HyperLink1" runat="server">HyperLink</asp:HyperLink>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle Font-
Names="verdana" Font-Size="Small" />
<RowStyle Font-
Names="verdana" Font-Size="Small" />
</asp:GridView>
<asp:AccessDataSource
ID="AccessDataSource1" runat="server" DataFile="~/App_Data/
PaintsDbase.mdb"
SelectCommand="SELECT
[Brand], [Productcode], [ThinnedBy], [Type], [Container], [Finish],
[Colorname], [RLMNumber], [FSNumber], [ColorGroup], [RedValue],
[GreenValue], [BlueValue], [RGBHex] FROM [AllPaints] ORDER BY [Brand],
[Productcode]">
</asp:AccessDataSource>
&nbsp;<br />
<br />
</td>
</tr>
</table>
</asp:panel>
</td>
<td valign="top" nowrap="noWrap">
<br />
<table>
<tr>
<td nowrap="noWrap">
Basic Filters</td>
</tr>
<tr>
<td style="width: 3px">
<span style="color: #0000ff; text-
decoration: underline">Show All</span><br />
</td>
</tr>
<tr>
<td style="width: 3px"
nowrap="noWrap">
Brands<br />
&nbsp; <a href="tool.aspx?
brand=delta ceramcoat">Delta Ceramcoat</a><br />
&nbsp; <a href="tool.aspx?
brand=humbrol">Humbrol</a><br />
&nbsp; <a href="tool.aspx?
brand=model master">Model Master</a><br />
&nbsp; <a href="tool.aspx?
brand=polly scale">Polly Scale</a><br />
&nbsp; <a href="tool.aspx?
brand=tamiya">Tamiya</a><br />
Standards-Based<br />
&nbsp; <a href="tool.aspx?
standard=rlm">RLM</a><br />
&nbsp; <a href="tool.aspx?
standard=fs">FS</a><br />
<br />
Type<br />
&nbsp; <a href="tool.aspx?
type=acrylic">Acrylic</a><br />
&nbsp; <a href="tool.aspx?
type=enamel">Enamel<br />
</a>
<br />
<br />
Thinned By<br />
&nbsp; <a href="tool.aspx?
thinnedby=acetone">Acetone<br />
</a>&nbsp; <a href="tool.aspx?
thinnedby=alcohol">Alcohol<br />
</a>&nbsp; <a href="tool.aspx?
thinnedby=ammonia">Ammonia</a><br />
&nbsp; <a href="tool.aspx?
thinnedby=spirits">Spirits<br />
</a>&nbsp; <a href="tool.aspx?
thinnedby=water">Water</a><br />
&nbsp; <a href="tool.aspx?
thinnedby=xylene">Xylene</a><br />
<br />
Container<br />
&nbsp;&nbsp;<a href="tool.aspx?
Container=bottle">Bottle</a><br />
&nbsp; <a href="tool.aspx?
Container=spray">Spray</a><br />
&nbsp; <a href="tool.aspx?
Container=tinlet">Tinlet</a><br />
<br />
Finish<br />
&nbsp; <a href="tool.aspx?
finish=flat">Flat</a><br />
&nbsp; <a href="tool.aspx?
finish=gloss">Gloss</a><br />
&nbsp; <a href="tool.aspx?
finish=metallic">Metallic</a><br />
&nbsp; <a href="tool.aspx?
finish=metalcote">Metalcote</a><br />
&nbsp; <a href="tool.aspx?
finish=semi gloss">Semi Gloss<br />
</a>
<br />
</td>
</tr>
</table>
<br />
<a href="tool.aspx"></a>
<br />
<span style="text-decoration: underline"><span
style="color: #0000ff"></span></span>
<br />
<a href="tool.aspx?finish=semi-gloss"></a>
<br />
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
</table>
<br />
</div>
</form>
</body>
</html>
Click to expand...

Nobody's going to take a shot at this?

TF
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Incorrect syntax near 'nvarchar'. 2
Match width of column heading in row with GridView BoundFields in 3
Question on accessing a nested GridViews Events 1
Problem with Postback 1
UpdatePanel Question 1
Fixed size of GridView to allow continuous paging? 0
enter key causing postback and then errors 5
Text box and Gridview on same page 2

Top