Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multiple  Command.Parameters.AddWithValue doesn't work?

Posted on 2012-03-22
13
Medium Priority
?
344 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 

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
 
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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

722 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