Multiple Command.Parameters.AddWithValue doesn't work?

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.
winstallaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tom BeckCommented:
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
winstallaAuthor Commented:
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
Tom BeckCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

winstallaAuthor Commented:
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
Tom BeckCommented:
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
winstallaAuthor Commented:
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
Tom BeckCommented:
"forename" is spelled wrong in line 19.This would result in the parameter being added with "%%" for its value, all records would return.
0
winstallaAuthor Commented:
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
Tom BeckCommented:
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
winstallaAuthor Commented:
Unfortunately, this has not worked - same problem. Thanks for your help on this!
0
Tom BeckCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
winstallaAuthor Commented:
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
Tom BeckCommented:
Nice! I didn't know you could do that. Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.