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???
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.
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.
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.
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?
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?
As I already stated, you'll nee to write the HTML markup yourself, because Access is not fully supported by the wizard.
bahamababe
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?
bahamababe
Member
6 Points
23 Posts
ASP.NET Search Engine with MS Access for optional search criteria
Oct 26, 2010 03:35 AM|LINK
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
accessdatasource Access 2003 ASP.NET searching Access Database
Mikesdotnett...
All-Star
155593 Points
19979 Posts
Moderator
MVP
Re: ASP.NET Search Engine with MS Access for optional search criteria
Oct 26, 2010 04:14 AM|LINK
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.
Web Pages CMS | My Site | Twitter
bahamababe
Member
6 Points
23 Posts
Re: ASP.NET Search Engine with MS Access for optional search criteria
Oct 26, 2010 03:12 PM|LINK
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.
visual studio Access 2003 ASP.NET searching Access Database
hans_v
All-Star
35998 Points
6551 Posts
Re: ASP.NET Search Engine with MS Access for optional search criteria
Oct 26, 2010 06:51 PM|LINK
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.
bahamababe
Member
6 Points
23 Posts
Re: ASP.NET Search Engine with MS Access for optional search criteria
Oct 26, 2010 08:51 PM|LINK
Ok??? How was "Mikedotnetting" able to do his search page with multiple named parameters? Also how do I configure the HTML markup?
hans_v
All-Star
35998 Points
6551 Posts
Re: ASP.NET Search Engine with MS Access for optional search criteria
Oct 26, 2010 09:19 PM|LINK
By configuring the HTML markup without the wizard
<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>bahamababe
Member
6 Points
23 Posts
Re: ASP.NET Search Engine with MS Access for optional search criteria
Oct 27, 2010 12:09 AM|LINK
Even with the HTML markup, Im getting the same error, no value given for one or more parameters. The wizard has the same statement...........
hans_v
All-Star
35998 Points
6551 Posts
Re: ASP.NET Search Engine with MS Access for optional search criteria
Oct 27, 2010 06:57 AM|LINK
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?
bahamababe
Member
6 Points
23 Posts
Re: ASP.NET Search Engine with MS Access for optional search criteria
Nov 05, 2010 03:57 AM|LINK
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?
hans_v
All-Star
35998 Points
6551 Posts
Re: ASP.NET Search Engine with MS Access for optional search criteria
Nov 05, 2010 07:48 AM|LINK
As I already stated, you'll nee to write the HTML markup yourself, because Access is not fully supported by the wizard.
Not really