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

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

Answer 1

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.

Answer 2

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.  

Answer 3

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.

Answer 4

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?

Answer 5

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>

 

Answer 6

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

Answer 7

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?

Answer 8

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?

Answer 9

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

Answer 10

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])">

 

 

Answer 11

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>

Answer 12

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.

Answer 13

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

Answer 14

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

Answer 15

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

Answer 16

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.