locked
ASP.NET Search Engine with MS Access for optional search criteria RRS feed

  • Question

  • User-1124826591 posted

    Hello,

     

    I am creating a simple search engine similiar to:  

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

    I have a text box for the last name and a dropdown list for the club name. If one value is entered, it will search for just one value, and ignore the blank value in the other. If there are 2 values it will search for 2 values, and no values should pull no results.

    Using VS 2005 and Access 2003-2005.  My statement works fine in access but in the accessdatasource there are issues with the @ symbol in VS 2005, the ? symbol works, but generates multiple parameters, when I only need 2.

    This statement works fine in the access query: (I do believe the statement is correct), but then again im using a drop down list, does that change the rules? Such as dropdownlist.selecteditem.text = ???

    SELECT Directory.First_Name, Directory.Last_Name, Directory.Vocation, Directory.R_Club, Directory.Work_Num, Directory.Cell_Num, Directory.Email, Directory.Hobbies_Interests
    FROM Clubs INNER JOIN Directory ON Clubs.C_Name = Directory.R_Club
    WHERE ([@LName] Is Null OR Directory.Last_Name Like [@LName]) AND ([@CName] Is Null OR Clubs.C_Name = [@CName]);

    I need 2 parameters LName and CName.

    I need to transfer this statement over to my accessdatasource select sql statement and remove the semi colon, then hit the query builder button, ok, next and test query,  (suppose to generate 2 parameters) but it gives me an error: please check syntax of command, no value given for one of more parameter.

    How do I make the access query work in the accessdatasource in VS2005???

     

    Thankyou


    Monday, October 25, 2010 11:35 PM

Answers

  • User-1199946673 posted

    I've tried the 4 parameters
     

    4 parameters? I said you could better use 2!!!

    and the errors still persists

    Which errors?

    Try

     

            <asp:AccessDataSource
                ID="AccessDataSource1"
                runat="server"
                DataFile="~/App_Data/database.mdb"
                CancelSelectOnNullParameter="false"
                SelectCommand="SELECT Directory.First_Name, Directory.Last_Name, Directory.Vocation, Directory.R_Club, Directory.Work_Num, Directory.Cell_Num, Directory.Email, Directory.Hobbies_Interests FROM Clubs INNER JOIN Directory ON Clubs.C_Name = Directory.R_Club WHERE ([@LName] Is Null OR Directory.Last_Name Like [@LName]) AND ([@CName] Is Null OR Clubs.C_Name = [@CName])">
                <SelectParameters>
                    <asp:ControlParameter ControlID="TextBox1" Name="LName" PropertyName="Text" /> 
                    <asp:ControlParameter ControlID="DropDownList1" Name="CName" PropertyName="SelectedValue" /> 
                </SelectParameters>
            </asp:AccessDataSource>


     

    Also note that OleDb parameters are not recognized by their name but by their position, so they must be added in the same order they (first) appear in the Command....

    If it doesn't work, please tell where you get errors, what they are and show the relevant code....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 7, 2010 4:54 AM

All replies

  • User-821857111 posted

    Don't rely on the Query Builder. It was optimised for SQL Server. Use it to generate the core code if you like, then replace whatever VS tries to give you in the SelectCommand with the working version from Access.


    Tuesday, October 26, 2010 12:14 AM
  • User-1124826591 posted

    VS has not tried to replace the code, it is not giving me any hit just the error I mentioned above.  Any help on that would be greatly appreciated.  I did not know that taking a query statement from access to VS would be so hard.  

    Tuesday, October 26, 2010 11:12 AM
  • User-1199946673 posted

    Any help on that would be greatly appreciated.
     

    There's no help!!! The VWD query designer simply doesn't understand named parameters, which you need to use because you're using them more than once. You need to configure the HTML markup without the use of the wizard.

    Tuesday, October 26, 2010 2:51 PM
  • User-1124826591 posted

    Ok??? How was "Mikedotnetting" able to do his search page with multiple named parameters?  Also how do I configure the HTML markup?

    Tuesday, October 26, 2010 4:51 PM
  • User-1199946673 posted

    How was "Mikedotnetting" able to do his search page with multiple named parameters?
     

    By configuring the HTML markup without the wizard

    Also how do I configure the HTML markup?
     

            <asp:AccessDataSource
                ID="AccessDataSource1"
                runat="server"
                DataFile="~/App_Data/database.mdb"
                CancelSelectOnNullParameter="false"
                SelectCommand="SELECT Directory.First_Name, Directory.Last_Name, Directory.Vocation, Directory.R_Club, Directory.Work_Num, Directory.Cell_Num, Directory.Email, Directory.Hobbies_Interests FROM Clubs INNER JOIN Directory ON Clubs.C_Name = Directory.R_Club WHERE ([@LName] Is Null OR Directory.Last_Name Like [@LName]) AND ([@CName] Is Null OR Clubs.C_Name = [@CName])">
                <SelectParameters>
                    <asp:Parameter Name="LName" Type="String" />
                    <asp:Parameter Name="CName" Type="String" />
                </SelectParameters>
            </asp:AccessDataSource>
    


     

    Tuesday, October 26, 2010 5:19 PM
  • User-1124826591 posted

    Even with the HTML markup, Im getting the same error, no value given for one or more parameters.  The wizard has the same statement...........

    Tuesday, October 26, 2010 8:09 PM
  • User-1199946673 posted

    Im getting the same error, no value given for one or more parameters
     

    This error occurs in 2 situations. The first one is exactly what it tell. If you don't provide a parameter in the parameterlist for every parametert in the command. In this case, you specify 2 parameters, both in the command and in the collection, so that's OK

    The second reason is when you misspelled one or more field/table names in the command. Access cannopt recognize them, and will think they're parameters. So please take a close look to the field and table names in the command?

    Wednesday, October 27, 2010 2:57 AM
  • User-1124826591 posted

    I have the html markup setup like above but in the define custom statements or stored procedures in the sql select statement this is the statement: SELECT Directory.First_Name, Directory.Last_Name, Directory.Vocation, Directory.R_Club, Directory.Work_Num, Directory.Cell_Num, Directory.Email, Directory.Hobbies_Interests FROM Clubs INNER JOIN Directory ON Clubs.C_Name = Directory.R_Club WHERE ([@LName] Is Null OR Directory.Last_Name Like [@LName]) AND ([@CName] Is Null OR Clubs.C_Name = [@CName])

    It is not generating a parameter list because here will only accept '?' and I only need 2 parameters but as you can see, there are for due to the way the statement is written, is there a way to re-write this statement to check for nulls and check the database with 2 parameters.  Also on the form the LName is a textbox and CName is a dropdown list that is coming from a separate table other than the other fields.  Would that make a difference in the HTML markup and the sql statement?

    Thursday, November 4, 2010 11:57 PM
  • User-1199946673 posted

    It is not generating a parameter list
     

    As I already stated, you'll nee to write the HTML markup yourself, because Access is not fully supported by the wizard.

      Also on the form the LName is a textbox and CName is a dropdown list that is coming from a separate table other than the other fields.  Would that make a difference in the HTML markup and the sql statement?

     

    Not really

    Friday, November 5, 2010 3:48 AM
  • User-1124826591 posted

    Hi,

    This is what I have so far for the HTML Markup and I think I found the problem its with the dropdownlist, not being able to generate a parameter for [@CName]):

    SelectCommand="SELECT Directory.First_Name, Directory.Last_Name, Directory.Vocation, Directory.R_Club, Directory.Work_Num, Directory.Cell_Num, Directory.Email, Directory.Hobbies_Interests FROM Clubs INNER JOIN Directory ON Clubs.C_Name = Directory.R_Club WHERE ([@LName] Is Null OR Directory.Last_Name Like '%' + ? + '%') AND (? Is Null OR Clubs.C_Name = [@CName])">


     

     

    Saturday, November 6, 2010 11:54 AM
  • User-1124826591 posted

    The rest of the HTML Markup:

    <

     

    SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="CName" PropertyName="SelectedItem.Text" />

     

    <asp:ControlParameter ControlID="TextBox1" Name="LName" PropertyName="Text" />

     

    </SelectParameters>

    Saturday, November 6, 2010 12:00 PM
  • User-821857111 posted

    It's been said already a few times, but you cannot rely on Visual Studio Wizards to generate the right code for Access. They simply do not work correctly. And they never will. Microsoft have no interest in helping you to use Access in your web site. They want you to use SQL server. Unless you add the code directly to your aspx file by hand, you are in for a tough time.


    Saturday, November 6, 2010 12:55 PM
  • User-1199946673 posted

    and I think I found the problem its with the dropdownlist, not being able to generate a parameter for [@CName]):
     

    No, you just don't read what we're telling you.

    "WHERE ([@LName] Is Null OR Directory.Last_Name Like '%' + ? + '%') AND (? Is Null OR Clubs.C_Name = [@CName])"

    Mixing up named parameters and questionmarks can be quit confusing. Every questionmark is treated as a new parameter, so in this example you need to specify 4 parameters. Just use the command I showed in my example, and change the parameters with your controlparameters....

    Saturday, November 6, 2010 4:56 PM
  • User-1124826591 posted

    I've tried the 4 parameters along with with your example and the errors still persists! I have done away with the access database and moved along to the sql database with stored proc, now the page just displays brings back no results.  I tested the stored proc, it will only display the LName and not the CName which is weird because they both work in the access query and the queries are both the same???

    Sunday, November 7, 2010 12:32 AM
  • User-1199946673 posted

    I've tried the 4 parameters
     

    4 parameters? I said you could better use 2!!!

    and the errors still persists

    Which errors?

    Try

     

            <asp:AccessDataSource
                ID="AccessDataSource1"
                runat="server"
                DataFile="~/App_Data/database.mdb"
                CancelSelectOnNullParameter="false"
                SelectCommand="SELECT Directory.First_Name, Directory.Last_Name, Directory.Vocation, Directory.R_Club, Directory.Work_Num, Directory.Cell_Num, Directory.Email, Directory.Hobbies_Interests FROM Clubs INNER JOIN Directory ON Clubs.C_Name = Directory.R_Club WHERE ([@LName] Is Null OR Directory.Last_Name Like [@LName]) AND ([@CName] Is Null OR Clubs.C_Name = [@CName])">
                <SelectParameters>
                    <asp:ControlParameter ControlID="TextBox1" Name="LName" PropertyName="Text" /> 
                    <asp:ControlParameter ControlID="DropDownList1" Name="CName" PropertyName="SelectedValue" /> 
                </SelectParameters>
            </asp:AccessDataSource>


     

    Also note that OleDb parameters are not recognized by their name but by their position, so they must be added in the same order they (first) appear in the Command....

    If it doesn't work, please tell where you get errors, what they are and show the relevant code....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 7, 2010 4:54 AM
  • User-1124826591 posted

    That was it! My control parameter list was not in order and also the CancelSelectOnNullParameters and Selecting Event needed some tweaking.  Wow.  I knew it was something simple that I was overlooking, because the sql statement was working! 

    Thankyou for being patient. 

    Sunday, November 7, 2010 9:42 PM