Solved

how can I set sqldatasource and selectparameters in code behind vb.net

Posted on 2014-11-04
16
2,035 Views
Last Modified: 2014-11-06
I would like to move my <asp:SqlDataSource></asp:SqlDataSource> and <SelectParameters> </SelectParameters> tags to  my .vb code behind pages to avoid sql injections. How can I do this?   This is what I tried 1st but it isn't working because I don't know how to actually set the tags correctly or assign controls to actually do the search when the button is clicked.

Here is my .aspx code that worked
    <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
&nbsp;&nbsp;
    <asp:Button ID="Button1" runat="server" Text="Search Providers by County" />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." EnableModelValidation="True" ForeColor="#333333" GridLines="None">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:BoundField DataField="Last name" HeaderText="Last name" SortExpression="Last name"></asp:BoundField>
            <asp:BoundField DataField="First name" HeaderText="First name" SortExpression="First name"></asp:BoundField>
            <asp:BoundField DataField="County" HeaderText="County" SortExpression="County"></asp:BoundField>
            <asp:BoundField DataField="Employer/School System" HeaderText="Employer/School System" SortExpression="Employer/School System"></asp:BoundField>
            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email"></asp:BoundField>
            <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone"></asp:BoundField>
            <asp:BoundField DataField="Date Information Last Updated" HeaderText="Date Information Last Updated" SortExpression="Date Information Last Updated"></asp:BoundField>
        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tbiConnectionString %>" SelectCommand="SELECT * FROM [Providers] WHERE ([County] LIKE '%' + @County + '%')">
        <SelectParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="County" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>
[code]

Now I would like to take that and move it to the .vb code behind file.   This is what I tried and it didn't work.



[code]
Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Imports System

Partial Class SchoolPsychology_tbi_testSearch
    Inherits System.Web.UI.Page
    Private conn As String = ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        TextBox1.Text = ""  'I did this b'c I want the search box to be blank after each search and if the page is refreshed
        If Not IsPostBack Then
            Dim SqlDataSource1 As New SqlDataSource()
            SqlDataSource1.ID = "SqlDataSource1"
            Me.Page.Controls.Add(SqlDataSource1)
            SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString
            SqlDataSource1.SelectParameters.Clear()
            SqlDataSource1.SelectParameters.Add()  'This is where I didn't know how to set the parameters
            BindSubjectData()
        End If

    End Sub

   


    End Sub

    Private Sub BindSubjectData()
        'Dim qrstring As String = "SELECT * FROM [Providers] WHERE ([County] LIKE '%' + @County + '%') "
        Using sqlCon As New SqlConnection(conn)
            Using cmd As New SqlCommand()
                cmd.CommandText = "SELECT * FROM [Providers] WHERE ([County] LIKE '%' + @County + '%')"
                cmd.Connection = sqlCon
                sqlCon.Open()
                Dim da As New SqlDataAdapter(cmd)
                Dim dt As New DataTable()
                da.Fill(dt)
                GridView1.DataSource() = dt
                GridView1.DataBind()
                sqlCon.Close()
                'Label1.Text = "end"

            End Using

        End Using

    End Sub

Open in new window

0
Comment
Question by:cdlciddit
  • 8
  • 6
  • 2
16 Comments
 
LVL 21

Accepted Solution

by:
MogalManic earned 144 total points
ID: 40423772
You do not need to have the SQL Datasource on the codebehind.  The client does not have access to any control that has a runat="Server".  The SQLDataSource does not generate any HTML.

But, you do need to change your SQL statement.  The String Concationation '%'+County+'%' is still susceptible to SQL injection attack.  Change your SQLDataSource to this:
[<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tbiConnectionString %>" SelectCommand="SELECT * FROM [Providers] WHERE ([County] LIKE @County)" OnSelecting="SqlDataSourceSelectingEventHandler">
        <SelectParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="County" PropertyName="Text" Type="String" />
        </SelectParameters>
    </asp:SqlDataSource>

Open in new window


Then on the code behind, add the % to make the like a Contains operator:
protected Sub SqlDataSourceSelectingEventHandler ( _
	sender As Object, _
	e As SqlDataSourceSelectingEventArgs _
)
    e.Command.Parameters(0).Value = "%" + Me.County.Value + "%"
end sub

Open in new window

0
 
LVL 21

Assisted Solution

by:MogalManic
MogalManic earned 144 total points
ID: 40423787
A more maintainable way would be to use the ObjectDataSource.  It is a little more complex, but it would allow you to use the same search by county on other pages without having to cut-and-paste the SQL code.

The steps to create an object data source is:
  1.  Create a class in APP_CODE that represents the datasource
  2.  In the class create a static public method that takes a string 'County' and returns a DataSet
        i.e. Public Shared Function FindProvidersByCount(County As [String]) As DataSet
  3. In the method you would use ADO.Net (or any other data access methods) to run the SQL statement
  4.  In the page you would use ObjectDataSource instead of SQLDataSource

For a complete example, see http://msdn.microsoft.com/en-us/library/ms227562(v=vs.80).aspx
0
 

Author Comment

by:cdlciddit
ID: 40424608
Thank you so much. I am going to try this now and let you know how it works.
0
 

Author Comment

by:cdlciddit
ID: 40424840
Hello MogalManic2. I just tried the 1st option just to see if I could get it to work and I'm getting an error on the code behind.  On this code:

 
Protected Sub SqlDataSourceSelectingEventHandler( _
    sender As Object, _
    e As SqlDataSourceSelectingEventArgs _
)
        e.Command.Parameters(0).Value = "%" + Me.County.Value + "%"
    End Sub

Open in new window


I'm getting an error saying that " 'County' is not a member of 'SchoolPsychology_tbi_testSearch'"
0
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 356 total points
ID: 40424878
Change County by TextBox1.Text on this line
e.Command.Parameters(0).Value = "%" + Me.County.Value + "%"

will be like this
e.Command.Parameters(0).Value = "%" + Me.TextBox1.Text + "%"
0
 

Author Comment

by:cdlciddit
ID: 40424970
Thank you Jesus Rodriguez. That got rid of the error. But now it just list everything in that table as soon as the page loads. So when you go to the page every record in the county table is listed before you can even type in what you want to search.
0
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 356 total points
ID: 40425074
Is because at the first load is firing the SqlDataSourceSelectingEventHandler and textbox1.text=""

Look at this post http://forums.asp.net/t/1034014.aspx?Filter+GridView+With+TextBox , is similar to what your try to accomplish

your code will be something like this for your case
    
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
&nbsp;&nbsp;
    <asp:Button ID="Button1" runat="server" Text="Search Providers by County" />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." EnableModelValidation="True" ForeColor="#333333" GridLines="None" AllowPagin="True">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:BoundField DataField="Last name" HeaderText="Last name" SortExpression="Last name"></asp:BoundField>
            <asp:BoundField DataField="First name" HeaderText="First name" SortExpression="First name"></asp:BoundField>
            <asp:BoundField DataField="County" HeaderText="County" SortExpression="County"></asp:BoundField>
            <asp:BoundField DataField="Employer/School System" HeaderText="Employer/School System" SortExpression="Employer/School System"></asp:BoundField>
            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email"></asp:BoundField>
            <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone"></asp:BoundField>
            <asp:BoundField DataField="Date Information Last Updated" HeaderText="Date Information Last Updated" SortExpression="Date Information Last Updated"></asp:BoundField>
        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tbiConnectionString %>" 
     SelectCommand="SELECT * FROM [Providers] ">
    FilterExpression="[County] LIKE '%{0}%')
        <FilterParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="County" PropertyName="Text" Type="String" />
        </FilterParameters>
    </asp:SqlDataSource>

Open in new window

0
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 356 total points
ID: 40425080
And also comment this lines in the code behind

TextBox1.Text = ""  'I did this b'c I want the search box to be blank after each search and if the page is refreshed
        If Not IsPostBack Then
            Dim SqlDataSource1 As New SqlDataSource()
            SqlDataSource1.ID = "SqlDataSource1"
            Me.Page.Controls.Add(SqlDataSource1)
            SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString
            SqlDataSource1.SelectParameters.Clear()
            SqlDataSource1.SelectParameters.Add()  'This is where I didn't know how to set the parameters
            BindSubjectData()
        End If

Open in new window


and comment this too
Protected Sub SqlDataSourceSelectingEventHandler( _
    sender As Object, _
    e As SqlDataSourceSelectingEventArgs _
)
        e.Command.Parameters(0).Value = "%" + Me.County.Value + "%"
    End Sub

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:cdlciddit
ID: 40425101
Ok. Thanks. I will try this.
0
 

Author Comment

by:cdlciddit
ID: 40425152
I think I am almost there. I'm getting an error on the code that you sent. I'm sure it is on my end and my lack of .net knowledge. The error says "Parser Error Message: The server tag is not well formed."  It is because of this line.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tbiConnectionString %>" FilterExpression="[County] LIKE '%{0}%' ")
     SelectCommand="SELECT * FROM [Providers] ">
        <FilterParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="County" PropertyName="Text" Type="String" />
        </FilterParameters>
    </asp:SqlDataSource>

Open in new window


I tried the add the FilterExpresson inside the tag b'c it wouldn't work outside of the tag like you had it in the code above.  That made Visual Studio happy, but it still didn't work when I tried to run the page in the browser (Chrome).  I commented everything out in my code behind also. So it looks like this now.

Imports System.Data.SqlClient
Imports System.Data
Imports System.Drawing
Imports System

Partial Class SchoolPsychology_tbi_testSearch
    Inherits System.Web.UI.Page
    Private conn As String = ConfigurationManager.ConnectionStrings("tbiConnectionString").ConnectionString


    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        'TextBox1.Text = ""
        'If Not IsPostBack Then
        '    Dim SqlDataSource1 As New SqlDataSource()
        '    SqlDataSource1.ID = "SqlDataSource1"
        '    Me.Page.Controls.Add(SqlDataSource1)
        '    SqlDataSource1.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("tbiConnectionString").ConnectionString
        '    SqlDataSource1.SelectParameters.Add()
        '    BindSubjectData()
        'End If

    End Sub

    'Private Sub BindDataSource()
    '    SqlDataSource1.SelectParameters.Add("ConnectionSTring", "")
    '    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tbiConnectionString %>" SelectCommand="SELECT * FROM [Providers] WHERE ([County] LIKE '%' + @County + '%')">
    '    <SelectParameters>
    '        <asp:ControlParameter ControlID="TextBox1" Name="County" PropertyName="Text" Type="String" />
    '    </SelectParameters>
    '</asp:SqlDataSource>


    'End Sub

    '    Protected Sub SqlDataSourceSelectingEventHandler( _
    '    sender As Object, _
    '    e As SqlDataSourceSelectingEventArgs _
    ')
    '        e.Command.Parameters(0).Value = "%" + Me.TextBox1.Text + "%"
    '    End Sub

    'Private Sub BindSubjectData()
    '    'Dim qrstring As String = "SELECT * FROM [Providers] WHERE ([County] LIKE '%' + @County + '%') "
    '    Using sqlCon As New SqlConnection(conn)
    '        Using cmd As New SqlCommand()
    '            cmd.CommandText = "SELECT * FROM [Providers] WHERE ([County] LIKE '%' + @County + '%')"
    '            cmd.Connection = sqlCon
    '            sqlCon.Open()
    '            Dim da As New SqlDataAdapter(cmd)
    '            Dim dt As New DataTable()
    '            da.Fill(dt)
    '            GridView1.DataSource() = dt
    '            GridView1.DataBind()
    '            sqlCon.Close()
    '            'Label1.Text = "end"

    '        End Using

    '    End Using

    'End Sub


End Class

Open in new window


This is the code that I inserted that I got from you.

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
&nbsp;&nbsp;
    <asp:Button ID="Button1" runat="server" Text="Search Providers by County" />
    <br />
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." EnableModelValidation="True" ForeColor="#333333" GridLines="None" AllowPagin="True">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:BoundField DataField="Last name" HeaderText="Last name" SortExpression="Last name"></asp:BoundField>
            <asp:BoundField DataField="First name" HeaderText="First name" SortExpression="First name"></asp:BoundField>
            <asp:BoundField DataField="County" HeaderText="County" SortExpression="County"></asp:BoundField>
            <asp:BoundField DataField="Employer/School System" HeaderText="Employer/School System" SortExpression="Employer/School System"></asp:BoundField>
            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email"></asp:BoundField>
            <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone"></asp:BoundField>
            <asp:BoundField DataField="Date Information Last Updated" HeaderText="Date Information Last Updated" SortExpression="Date Information Last Updated"></asp:BoundField>
        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tbiConnectionString %>" FilterExpression="[County] LIKE '%{0}%' ")
     SelectCommand="SELECT * FROM [Providers] ">
        <FilterParameters>
            <asp:ControlParameter ControlID="TextBox1" Name="County" PropertyName="Text" Type="String" />
        </FilterParameters>
    </asp:SqlDataSource>

Open in new window


I'm sure it is something small that I'm not doing.  Thanks for helping me with this.
0
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 356 total points
ID: 40425180
Got an extra ) at the end of this line

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tbiConnectionString %>" FilterExpression="[County] LIKE '%{0}%' ")

Open in new window


must be
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tbiConnectionString %>" FilterExpression="[County] LIKE '%{0}%' "

Open in new window

0
 

Author Comment

by:cdlciddit
ID: 40426762
Thanks. That worked.  Is there a way I can make it so that it doesn't list every record in the table when the page loads?  The search is working but I don't want everything listed before I type in search criteria.
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 40426825
Then how did the user will now what to search for on that Case?? or search for an specific parameter??
0
 

Author Comment

by:cdlciddit
ID: 40426905
The button will say "Search County". They type in the county and hit search and all matching criteria will show up. Right now it all is just listed the page when the page loads.  When you type in your search criteria only what matches your search shows up and the rest disappears.
0
 
LVL 13

Assisted Solution

by:Jesus Rodriguez
Jesus Rodriguez earned 356 total points
ID: 40426953
Then you have to set the default value to something like never will be on the county name

<asp:ControlParameter ControlID="TextBox1" Name="County" PropertyName="Text" Type="String" DefaultValue="1" />

Open in new window

0
 

Author Comment

by:cdlciddit
ID: 40427157
That worked. Thanks.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now