Using The SqlDataSource.SelectCommand To Create Parameterized SQL Statements - Search Box

Although writing a parameterized SQL statement has been simplified using the asp:parameter options, it still may benefit to use the old fashioned method of writing a sql statement using an input string. I have noticed this for wanting to make a parameter to select which table I want to pull from. Here is some code I wrote to pull information from a database based on input from a search box and write it to a gridview.

Partial Class Private_SearchResultsInherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim Table As String

Dim SearchString As StringSearchString = Request.QueryString("SearchString")

Table = Request.QueryString("Table")

If Len(Trim(SearchString)) > 0 ThenSelect Case Table

Case "Plant"

SqlDataSource1.SelectCommand = "SELECT PlantName as 'Plant',PlantAddr as 'Address',PlantCity as 'City',PlantState as 'State',PlantCountry as 'Country',PlantZip as 'ZIP' FROM Plant WHERE PlantName LIKE '%" & SearchString & "%' OR PlantCity LIKE '%" & SearchString & "%' OR PlantState LIKE '%" & SearchString & "%' OR PlantCountry LIKE '%" & SearchString & "%' OR PlantZip LIKE '%" & SearchString & "%' ORDER BY PlantName"

Case "Contacts"

SqlDataSource1.SelectCommand = "SELECT ContactPosition as'Position',ContactTitle as 'Title',ContactLast as 'Last Name',ContactFirst as 'First Name',ContactPhone as 'Phone No' FROM Contacts WHERE ContactLast LIKE '%" & SearchString & "%' OR ContactFirst LIKE '%" & SearchString & "%' OR ContactPosition LIKE '%" & SearchString & "%' OR ContactPhone LIKE '%" & SearchString & "%' OR ContactTitle LIKE '%" & SearchString & "%' ORDER BY ContactLast"

Case "Events"

SqlDataSource1.SelectCommand = "SELECT EventName as 'Event',CONVERT(varchar,EventStartDate,101) as'Start Date',CONVERT(varchar,EventEndDate,101) as 'End Date',EventNotes as 'Notes',EventNoAttendees as 'Attendees',EventType as 'Event Type' FROM Events WHERE EventName LIKE '%" & SearchString & "%' OR EventStartDate LIKE '%" & SearchString & "%' OR EventEndDate LIKE '%" & SearchString & "%' OR EventNotes LIKE '%" & SearchString & "%' OR EventNoAttendees LIKE '%" & SearchString & "%' OR EventType LIKE '%" & SearchString & "%' ORDER BY EventName"

Case ""

Label1.Text = "Nothing Selected in Drop Down Box"

End Select

Else

Label1.Text = "No Search Parameters Entered"

End If

GridView1.DataBind()End Sub

End Class

 

Here is also my code to the front end of the page...

 

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="SearchResults.aspx.vb" Inherits="Private_SearchResults" title="Search Results" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<br />

<strong><span style="font-family: Tahoma">Search Results<br />

<br /></span></strong>

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">

<asp:Label ID="Label1" runat="server" Font-Names="Tahoma" Font-Size="10pt"></asp:Label><br />

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" DataSourceID="SqlDataSource1" Width="1020px">

<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />

<RowStyle BackColor="#F7F6F3" ForeColor="#333333" Font-Names="tahoma" Font-Size="Small" />

<EditRowStyle BackColor="#999999" />

<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />

<HeaderStyle BackColor="Indigo" Font-Bold="True" ForeColor="White" Font-Names="tahoma" Font-Size="Small" />

<AlternatingRowStyle BackColor="White" ForeColor="#284775" /></asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CRMConnectionString %>"></asp:SqlDataSource>

</asp:Content>