Solved

Select parameters ASP.NET

Posted on 2010-08-25
9
322 Views
Last Modified: 2012-05-10
Hello folks,

I have a question, I am tryng to find a record by its name or last name,, I am using an Access Data Source and the following parameters , but the code is not working as I expect,,, If I use only the Name and a control parameter it does work, bu I need both .... how would I go about this? Thanks

Please see the query I am using
<asp:AccessDataSource ID="AccessDataSource2" runat="server" 

        DataFile="~/App_Data/Providers.accdb" 

        SelectCommand="SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax], [lat], [lng] FROM [tbl_Providers] WHERE  

        (@First_Name IS NULL OR First_Name LIKE '%' +  @First_Name + '%') AND

        (@Last_Name IS NULL OR Last_Name LIKE '%' +  @Last_Name + '%') AND

        (@Specialty IS NULL OR Specialty LIKE '%' + @Specialty + '%') AND  

        (@Zip IS NULL OR Zip LIKE '%' + @Zip + '%')"

         CancelSelectOnNullParameter="False">

        <SelectParameters>



        <asp:ControlParameter ControlID="providerTextBox" Name="First_Name"  PropertyName ="Text" Type="String"  ConvertEmptyStringToNull ="true" />

        <asp:ControlParameter ControlID="providerTextBox" Name="Last_Name"  PropertyName ="Text" Type="String"  ConvertEmptyStringToNull ="true" />

        <asp:ControlParameter ControlID="specialityDropDown" Name="Specialty" PropertyName ="SelectedValue" Type="String" ConvertEmptyStringToNull ="true" />

        <asp:ControlParameter ControlID="txtZipCode" Name="Zip" PropertyName ="Text" Type="String" ConvertEmptyStringToNull ="true"  />



        

     </SelectParameters>

Open in new window

0
Comment
Question by:TonyReba
  • 4
  • 3
  • 2
9 Comments
 
LVL 13

Expert Comment

by:Surone1
ID: 33522835
do you want to show a record if the firstname AND the lastname are correct?
or when the firstname OR the lastname is correct?
0
 
LVL 35

Expert Comment

by:YZlat
ID: 33523165
could you explain what exactly are you trying to do here?
0
 
LVL 35

Assisted Solution

by:YZlat
YZlat earned 200 total points
ID: 33523231
if you meant First and Last name together, then try:

<asp:AccessDataSource ID="AccessDataSource2" runat="server"
        DataFile="~/App_Data/Providers.accdb"
        SelectCommand="SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax], [lat], [lng] FROM [tbl_Providers] WHERE  
        ((@First_Name IS NULL AND @Last_Name IS NULL) OR First_Name + ', ' + Last_Name LIKE '%' +  @First_Name + ', ' + @Last-Name + '%') AND
        (@Specialty IS NULL OR Specialty LIKE '%' + @Specialty + '%') AND  
        (@Zip IS NULL OR Zip LIKE '%' + @Zip + '%')"
         CancelSelectOnNullParameter="False">
        <SelectParameters>

        <asp:ControlParameter ControlID="providerTextBox" Name="First_Name"  PropertyName ="Text" Type="String"  ConvertEmptyStringToNull ="true" />
        <asp:ControlParameter ControlID="providerTextBox" Name="Last_Name"  PropertyName ="Text" Type="String"  ConvertEmptyStringToNull ="true" />
        <asp:ControlParameter ControlID="specialityDropDown" Name="Specialty" PropertyName ="SelectedValue" Type="String" ConvertEmptyStringToNull ="true" />
        <asp:ControlParameter ControlID="txtZipCode" Name="Zip" PropertyName ="Text" Type="String" ConvertEmptyStringToNull ="true"  />

       
     </SelectParameters>
0
 
LVL 9

Author Comment

by:TonyReba
ID: 33523574
I am trying to get the results for any part of a name  so whatever in providertextboxm matches ,,, First_Name, Last_Name need to query
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Accepted Solution

by:
Surone1 earned 300 total points
ID: 33523603
then you probably want to use OR instead of AND.
0
 
LVL 9

Author Comment

by:TonyReba
ID: 33523613
i get

No value given for one or more required parameters.
0
 
LVL 9

Author Comment

by:TonyReba
ID: 33523644
this actually did the trick
<asp:AccessDataSource ID="AccessDataSource2" runat="server" 

        DataFile="~/App_Data/Providers.accdb" 

        SelectCommand="SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax], [lat], [lng] FROM [tbl_Providers] WHERE  

        ((@First_Name IS NULL OR First_Name LIKE '%' +  @First_Name + '%') OR

        (@Last_Name IS NULL OR Last_Name LIKE '%' +  @Last_Name + '%')) AND

        (@Specialty IS NULL OR Specialty LIKE '%' + @Specialty + '%') AND  

        (@Zip IS NULL OR Zip LIKE '%' + @Zip + '%')"

         CancelSelectOnNullParameter="False">

        <SelectParameters>

Open in new window

0
 
LVL 9

Author Closing Comment

by:TonyReba
ID: 33523654
thanks
0
 
LVL 13

Expert Comment

by:Surone1
ID: 33523663
our pleasure
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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

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

18 Experts available now in Live!

Get 1:1 Help Now