Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

Select parameters ASP.NET

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
TonyReba
Asked:
TonyReba
  • 4
  • 3
  • 2
2 Solutions
 
Surone1Commented:
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
 
YZlatCommented:
could you explain what exactly are you trying to do here?
0
 
YZlatCommented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
TonyRebaAuthor Commented:
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
 
Surone1Commented:
then you probably want to use OR instead of AND.
0
 
TonyRebaAuthor Commented:
i get

No value given for one or more required parameters.
0
 
TonyRebaAuthor Commented:
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
 
TonyRebaAuthor Commented:
thanks
0
 
Surone1Commented:
our pleasure
0
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.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now