Solved

Multiple  Command.Parameters.AddWithValue doesn't work?

Posted on 2012-03-22
13
319 Views
Last Modified: 2012-03-23
I have an ASP.NET search page, that is quite simple. It has 5 text boxes and a drop-down list. You can search by any of the search options.
However, in order to bring up a result, you need to enter the EXACT text, but in terms of First Names, this can be a bit of a pain with different and/or shortened spellings of names (Phil, Philip, Leslie, Lesley, Les etc.) so I want to be able to search using just the first few letters on any of the textbox entries.
This works if I just do one field. For example, if I use the code below:-
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.Oledb" %>

<!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 SearchRecords(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim Connection As OledbConnection
                                                                                                                                                                                                                                                                                                                Connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
                                                                                                                                                                                                                                                                                                                                             "Data Source=E:\Company\New_Intranet\Guestbook2\App_Data\twit.mdb")
        Connection.Open()
        Dim Command As OleDbCommand
        Command = New OleDbCommand("select * from requests where username Like @userName", Connection)
        Command.Parameters.AddWithValue("@userName", "%" + textbox1.Text + "%")
        Command.Parameters.Add(New OleDbParameter("@username", textbox1.Text))
        Command.Parameters.Add(New OleDbParameter("@forename", textbox6.Text))
        Command.Parameters.Add(New OleDbParameter("@make", textbox2.Text))
        Command.Parameters.Add(New OleDbParameter("@model", textbox3.Text))
        Command.Parameters.Add(New OleDbParameter("@serial", textbox4.Text))
        Command.Parameters.Add(New OleDbParameter("@location", DropDownList2.Text))
        Dim DataReader As OleDbDataReader
        DataReader = Command.ExecuteReader()
        GridView1.DataSource = DataReader
        GridView1.DataBind()
       
        Connection.Close()
        'Clear the text boxes after a new insert
        textbox1.Text = ""
        textbox2.Text = ""
        textbox3.Text = ""
        textbox4.Text = ""
        textbox6.Text = ""
        
    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server"><link rel="shortcut icon" href="images/headers/favicon.ico">
    <title>Search for Records</title>
<link href="Styles/Site.css" rel="stylesheet" type="text/css" /></head>
<body>
<p align="center"><img src="images/headers/fsblue.png" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<img src="images/headers/fsred.png" />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<img src="images/headers/fsorange.png" /></p>
<p Align="center">| <A href="default.aspx">Home Page</A> | <a href="ListAll.aspx">List All Records</a> | <a href="AddRecord.aspx">Add a New Record</a> | <a href="Search.aspx">Search for Records</a> | <A href="about.aspx">Help</A> |
<HR />
<H1>Search for a Sepcific Hardware Record</H1>
<H2>Instructions for use of this page:</H2>
<P><b>Search Records:</b> Enter the Selected Field and Click the relevant button! <B>Only</B> enter information in <b>One</b> field!
<P><b>IMPORTANT:</b> You must enter the full details of the required item. If searching for a <B>User</b>, you must enter the name exactly as it appears in the database. If an entry does not match <B>exactly</B> with the database the record will NOT be displayed. So, for example, <b>Phil</b> will not bring up a record, you would need to enter <b>Philip</b> instead.
<P>Search is NOT case sensitive.
<HR />
    <form id="form1" runat="server">
    <div><p align="center">
<asp:TextBox id="textbox1" runat="server" />
<asp:Button id="btnSubmit" runat="server" OnClick="SearchRecords" Text="Search Surname" />

<asp:TextBox id="textbox6" runat="server" />
<asp:Button id="Button6" runat="server" OnClick="SearchRecords" Text="Search Forename" />

<asp:TextBox id="textbox2" runat="server" />
<asp:Button id="Button2" runat="server" OnClick="SearchRecords" Text="Search Make" />
<br />
<asp:TextBox id="textbox3" runat="server" />
<asp:Button id="Button3" runat="server" OnClick="SearchRecords" Text="Search Model" />

<asp:TextBox id="textbox4" runat="server" />
<asp:Button id="Button4" runat="server" OnClick="SearchRecords" Text="Search Serial" />

<asp:DropDownList ID="DropDownList2" runat="server">
    <asp:ListItem Selected="True"> </asp:ListItem>
    <asp:ListItem>Bolton (1st)</asp:ListItem>
    <asp:ListItem>Bolton (2nd)</asp:ListItem>
    <asp:ListItem>Bolton (Ground)</asp:ListItem>
    <asp:ListItem>Banbury</asp:ListItem>
    <asp:ListItem>Beverley</asp:ListItem>
    <asp:ListItem>Bromsgrove</asp:ListItem>
    <asp:ListItem>Cambridge</asp:ListItem>
    <asp:ListItem>Cardiff</asp:ListItem>
    <asp:ListItem>Cheshire</asp:ListItem>
    <asp:ListItem>London</asp:ListItem>
    <asp:ListItem>Merseyside</asp:ListItem>
    <asp:ListItem>North East</asp:ListItem>
    <asp:ListItem>North Wales</asp:ListItem>
    <asp:ListItem>Norwich</asp:ListItem>
    <asp:ListItem>Oundle</asp:ListItem>
    <asp:ListItem>Peterborough</asp:ListItem>
    <asp:ListItem>Scotland</asp:ListItem>
    <asp:ListItem>South (Poles Copse)</asp:ListItem>
    <asp:ListItem>Stafford</asp:ListItem>
    <asp:ListItem>Yorkshire</asp:ListItem>
    <asp:ListItem>Pathway</asp:ListItem>
</asp:DropDownList>
<asp:Button id="Button5" runat="server" OnClick="SearchRecords" Text="Search Location" />
<hr />
<h2 align="center">Search Results</h2>

        <asp:GridView ID=GridView1 runat="server"  AllowSorting="True" align="center"
            AutoGenerateColumns="False" DataKeyNames="ID" EmptyDataText="I'm dreadfully sorry. Something has screwed up, somewhere! I regret to inform you that your request has brought up precisely Zero records! I don't know exactly what has gone on, but - between you and me - there is a very strong liklihood that it is all the fault of the designer!!!">


                
<Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                    ReadOnly="True" SortExpression="ID" Visible="False" />
                    <asp:HyperLinkField
    DataNavigateUrlFields="ID"
    DataNavigateUrlFormatString="EditRecord.aspx?ID={0}"
    DataTextField="ID"
    HeaderText="Record"
    SortExpression="ID" />
                <asp:BoundField DataField="Username" HeaderText="Surname" 
                    SortExpression="Username" />
                <asp:BoundField DataField="Forename" HeaderText="Forename" SortExpression="Forename" />
                <asp:BoundField DataField="Location" HeaderText="Location" SortExpression="Location" />
                <asp:BoundField DataField="Make" HeaderText="Make" SortExpression="Make" />
                <asp:BoundField DataField="Model" HeaderText="Model" SortExpression="Model" />
                <asp:BoundField DataField="Serial" HeaderText="Serial" 
                    SortExpression="Serial" />
                <asp:BoundField DataField="Acquired" HeaderText="Acquired" 
                    SortExpression="Acquired" DataFormatString="{0:d MMMM yyyy}" />
                <asp:BoundField DataField="Expires" HeaderText="Expires" 
                    SortExpression="Expires" DataFormatString="{0:d MMMM yyyy}" />
                <asp:BoundField DataField="Condition" HeaderText="Condition" 
                    SortExpression="Condition" />
                <asp:BoundField DataField="Comments" HeaderText="Comments" 
                    SortExpression="Comments" />
                <asp:CheckBoxField DataField="Retired" HeaderText="Retired" 
                    SortExpression="Retired" />
            </Columns>        </asp:GridView>

<asp:AccessDataSource ID="AccessDataSource1" runat="server"
        DataFile="E:\Company\New_intranet\Guestbook2\App_Data\twit.mdb" SelectCommand="SELECT * FROM [Requests]">
</asp:AccessDataSource>




    </div>
    </form>
</body>
</html>

Open in new window

and put a Z in textbox1, it brings up only those records where there is a Z in the Username field. BUT; if I change the code to read like below:-
MISSING CODE...
        Command = New OleDbCommand("select * from requests where username Like @userName or forename Like @forename", Connection)
        Command.Parameters.AddWithValue("@userName", "%" + textbox1.Text + "%")
        Command.Parameters.AddWithValue("@foreName", "%" + textbox6.Text + "%")
MISSING CODE...

Open in new window

and put a Z in textbox1 it brings up EVERY record!
Obviously, there is something simple that's wrong, but I don't know what! Please help!

NB - Yes, I know that there are two Command.Parameter references to textbox1.text - but it does the same thing whether the second reference is there or not.
0
Comment
Question by:winstalla
  • 7
  • 6
13 Comments
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37752396
Your syntax works perfectly fine in my tests however if either of those textboxes are blank it will return all records because the parameter will become "%%".
0
 

Author Comment

by:winstalla
ID: 37752601
Oh. I see. Is there a way to allow one (or more) of the boxes to be blank and only refer to the textbox or dropdown list that has data in it?
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37753059
A simple conditional to compose the select statement should do. Something like:

Connection.Open()
Dim selectStr As String = "SELECT * FROM requests WHERE "
Dim Command = New OleDbCommand()
If textbox1.Text != "" Then   'Only run the query if textbox1 has value
    selectStr += "username LIKE @userName ";
    Command.Parameters.AddWithValue("@userName", "%" + textbox1.Text + "%")
    If textbox6.Text != "" Then
        selectStr += "OR forname LIKE @foreName";
        Command.Parameters.AddWithValue("@foreName", "%" + textbox6.Text + "%")
    End If
    Command.Commandtext = selectStr
    Command.Connection = Connection
    ''''''THE REST OF YOUR CODE ''''''
End If
0
 

Author Comment

by:winstalla
ID: 37753137
The original code was written in VB, so the suggestion complained a bit. However, I have got the page to load now, with the code below:-
<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data.Oledb" %>

<!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 SearchRecords(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim Connection As OledbConnection
                                                                                                                                                                                                                                                                                                                Connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;" & _
                                                                                                                                                                                                                                                                                                                                             "Data Source=E:\Company\New_Intranet\Guestbook2\App_Data\twit.mdb")
        Connection.Open()
        Dim selectStr As String = "SELECT * FROM requests WHERE "
        Dim Command = New OleDbCommand()
        If textbox1.Text = "" Then   'Only run the query if textbox1 has value
            selectStr += "username LIKE @userName "
            Command.Parameters.AddWithValue("@userName", "%" + textbox1.Text + "%")
            If textbox6.Text = "" Then
                selectStr += "OR forname LIKE @foreName"
                Command.Parameters.AddWithValue("@foreName", "%" + textbox6.Text + "%")
            End If
            Command.CommandText = selectStr
            Command.Connection = Connection
            Command.Parameters.Add(New OleDbParameter("@username", textbox1.Text))
            Command.Parameters.Add(New OleDbParameter("@forename", textbox6.Text))
            Command.Parameters.Add(New OleDbParameter("@make", textbox2.Text))
            Command.Parameters.Add(New OleDbParameter("@model", textbox3.Text))
            Command.Parameters.Add(New OleDbParameter("@serial", textbox4.Text))
            Command.Parameters.Add(New OleDbParameter("@location", DropDownList2.Text))
            Dim DataReader As OleDbDataReader
            DataReader = Command.ExecuteReader()
            GridView1.DataSource = DataReader
            GridView1.DataBind()
       
            
        End If
        Connection.Close()

Open in new window

However, whatever I enter still brings up every record.
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37753240
That's my fault. I'm a C# programmer these days and I wrote that code directly on the post while trying to convert on the fly to VB.

The conditionals should be:

If Not textbox1.Text = "" Then
...
If Not textbox6.Text = "" Then
...
0
 

Author Comment

by:winstalla
ID: 37753275
Again, the Textbox1.text selection works OK, but not the textbox6.text selection. Entering information in textbox6 brings up no records. If text has previously brought up a record (e.g. you enter something in Textbox1 and it brings up a record) entering text in Textbox6 brings up all records.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 38

Expert Comment

by:Tom Beck
ID: 37753567
"forename" is spelled wrong in line 19.This would result in the parameter being added with "%%" for its value, all records would return.
0
 

Author Comment

by:winstalla
ID: 37753592
So it is! D'oh!
Unfortunately, correcting this error now brings up no records when text is entered in textbox6. Though text in Textbox1 works correctly, still.
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37753672
My C# version works perfectly. Maybe there are still problems with the VB syntax. Try pasting this in place of what you have:

           If Not textbox1.Text = "" Then   'Only run the query if textbox1 has value
                selectStr = selectStr & "username LIKE @userName "
                Command.Parameters.AddWithValue("@userName", "%" & textbox1.Text & "%")
                If Not textbox6.Text = "" Then
                    selectStr = selectStr & "OR forename LIKE @foreName"
                    Command.Parameters.AddWithValue("@foreName", "%" & textbox6.Text & "%")
                End If
           
            .....

I have no easy way of testing the VB code.
0
 

Author Comment

by:winstalla
ID: 37756236
Unfortunately, this has not worked - same problem. Thanks for your help on this!
0
 
LVL 38

Accepted Solution

by:
Tom Beck earned 500 total points
ID: 37756897
Okay, you're making me work for it. That's fine. I have created a complete test in VB using some contrived data in a sql database. The source should not matter, I'm just demonstrating the conditional to select certain records.
<%@ Page Language="vb" AutoEventWireup="false" Inherits="testApp.WebForm11" %>
<!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 SearchRecords(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim Connection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TestStuffConnectionString").ConnectionString)
        Dim Command As New Data.SqlClient.SqlCommand
        Dim da As Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter
        Dim dt As New Data.DataTable
        Dim selectStr As String = "SELECT firstname, lastname FROM TableA WHERE "
        If Not textBox1.Text = "" Then   'Only run the query if textbox1 has value
            selectStr = selectStr & "firstname LIKE @firstName "
            Command.Parameters.AddWithValue("@firstName", "%" & textBox1.Text & "%")
            If Not textBox6.Text = "" Then
                selectStr = selectStr & "OR lastname LIKE @lastName"
                Command.Parameters.AddWithValue("@lastName", "%" & textBox6.Text & "%")
            End If 
            Command.CommandText = selectStr
            Command.Connection = Connection
            Command.CommandType = Data.CommandType.Text
            Connection.Open()
            da.SelectCommand = Command
            da.Fill(dt)
            GridView1.DataSource = dt
            GridView1.DataBind()
        Else
            GridView1.DataSource = Nothing
            GridView1.DataBind()
        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>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
            <Columns>
                <asp:BoundField DataField="firstname" HeaderText="First Name" />
                <asp:BoundField DataField="lastname" HeaderText="Last Name" />
            </Columns> 
        </asp:GridView><br />
        <asp:TextBox ID="textBox1" runat="server" Width="15" ></asp:TextBox>
        <label for="textBox1">Letter in First Name</label><br />
        <asp:TextBox ID="textBox6" runat="server" Width="15" ></asp:TextBox>
        <label for="textBox1">Letter in Last Name</label><br /><br />
        <asp:Button ID="searchBtn" runat="server" OnClick="SearchRecords" Text="Search Records" />
    </div>
    </form>
</body>
</html>

Open in new window


Some results using various combinations of letters.Results
0
 

Author Comment

by:winstalla
ID: 37757169
With a bit of tweaking, this seems to work. Thank you!
For your efforts in dealin with my inability to understand, I have increased the points value for this question as a thank you!
0
 
LVL 38

Expert Comment

by:Tom Beck
ID: 37757469
Nice! I didn't know you could do that. Thanks.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Accessibility and Usability are two concepts that seem to be closely related.  But, too many people seem to have a distorted perception of them. During last five years, those two words have come to the day-to-day work of almost every web develope…
"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

760 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

19 Experts available now in Live!

Get 1:1 Help Now