Default value for url parameter

[dischord13]

Hello,

I am using ASP.NET and connecting to an Access database. I read that I could
use the % symbol as the default value to show all records from an Access
dataset. Is that right? I can't get it to work. When I test the page it gives
me an error saying that the input string was not in the correct format.

I have a simple sql statment:
SELECT *
FROM CategoryList
WHERE CategoryID = ?

I have the folloing code for the url parameter @CatID
IIF((Request.QueryString("CatID") <> Nothing),
Request.QueryString("CatID"), "%")

I also tried putting the "%" outside of the iif statement like this.
(IIF((Request.QueryString("CatID") <> Nothing),
Request.QueryString("CatID"), " ")) + %

If I set the default value to something like "2", it works beautifully. But
how can I get the wildcard to work? I just can't figure it out. Does anyone
know? Thanks so much.

James

(Im on XP pro and using IIS with Dreamweaver MX 2004)

[Drew]

Try * instead for Access... % is the wildcard for SQL Server...

HTH,
Drew

"dischord13" <webforumsuser@macromedia.com> wrote in message
news:d437ea$9hj$1@forums.macromedia.com...

> Hello,
>
> I am using ASP.NET and connecting to an Access database. I read that I
> could
> use the % symbol as the default value to show all records from an Access
> dataset. Is that right? I can't get it to work. When I test the page it
> gives
> me an error saying that the input string was not in the correct format.
>
> I have a simple sql statment:
> SELECT *
> FROM CategoryList
> WHERE CategoryID = ?
>
> I have the folloing code for the url parameter @CatID
> IIF((Request.QueryString("CatID") <> Nothing),
> Request.QueryString("CatID"), "%")
>
> I also tried putting the "%" outside of the iif statement like this.
> (IIF((Request.QueryString("CatID") <> Nothing),
> Request.QueryString("CatID"), " ")) + %
>
> If I set the default value to something like "2", it works beautifully.
> But
> how can I get the wildcard to work? I just can't figure it out. Does
> anyone
> know? Thanks so much.
>
> James
>
> (Im on XP pro and using IIS with Dreamweaver MX 2004)
>
>

[dischord13]

I've tried % and * and ? and just about every symbol on the keyboard. I still
get the same message "System.FormatException: Input string was not in a correct
format."

That's good to know that that % works for SQL Server because I will upgrate
eventually. I'm beginning to think that it is not possilbe to do it in Access.
Has anyone managed to do this successfully?

[Calvin Willman]

Might sound obvious, but be sure to wrap the wildcard with apostrophe's a la

I have a simple sql statment:
SELECT *
FROM CategoryList
WHERE CategoryID = '%'

also try the like keyword. so like '%'


On 19/4/05 4:17 pm, in article d437ea$9hj$1@forums.macromedia.com,
"dischord13" <webforumsuser@macromedia.com> wrote:

> I have a simple sql statment:
> SELECT *
> FROM CategoryList
> WHERE CategoryID = ?

[dischord13]

Thanks for the reply. You are right Calvin, I should be using LIKE (how did I
miss that??) , but I am still getting the same error message. "Input string was
not in a correct format"

I found a macromedia help reference that specifically stated that I can use
the % as the default value for microsoft Access in an Asp.net page. It said to
use the following format.

(IIf((Request.Form("txtCity") <> Nothing),
Request.Form("txtCity"), "")) + "%"

Again, this format works great if I delete the "%" and put any default value
like "2" or something. The only thing that doesn't work is the % character!!! I
just can't figure out why not. It's driving me crazy. Am I reading the
reference incorrectly? It shows the same syntax for the SQL statement I am
using with WHERE CategoryID = ? without any apostrophe or quotes.

Here is the reference I am referring to:
[url]http://livedocs.macromedia.com/dreamweaver/mx2004/using/wwhelp/wwhimpl/common/ht[/url]
ml/wwhelp.htm?context=Using_Dreamweaver&file=43_rad10 .htm

Anyone have any other ideas? Feel free to say any solution you can think of
even if it sounds obvious. I am very inexperienced. Thanks!
James

[Calvin Willman]

I'm not too familiar with how Dreamweaver creates it's sql statements...
certainly with reference to using the ? place holder but,

I'm sure the wildcard character explicitly needs to have single quotes
around it in the final statement that gets executed. So try... amending the
IIF line force the wildcard to have apostrophe's "\'%\'".

Other than that... I'm out of ideas with this.

On 19/4/05 7:06 pm, in article d43hab$nq6$1@forums.macromedia.com,
"dischord13" <webforumsuser@macromedia.com> wrote:

> Thanks for the reply. You are right Calvin, I should be using LIKE (how did I
> miss that??) , but I am still getting the same error message. "Input string
> was
> not in a correct format"
>
> I found a macromedia help reference that specifically stated that I can use
> the % as the default value for microsoft Access in an Asp.net page. It said to
> use the following format.
>
> (IIf((Request.Form("txtCity") <> Nothing),
> Request.Form("txtCity"), "")) + "%"
>
> Again, this format works great if I delete the "%" and put any default value
> like "2" or something. The only thing that doesn't work is the % character!!!
> I
> just can't figure out why not. It's driving me crazy. Am I reading the
> reference incorrectly? It shows the same syntax for the SQL statement I am
> using with WHERE CategoryID = ? without any apostrophe or quotes.
>
> Here is the reference I am referring to:
> [url]http://livedocs.macromedia.com/dreamweaver/mx2004/using/wwhelp/wwhimpl/common/[/url]
> ht
> ml/wwhelp.htm?context=Using_Dreamweaver&file=43_rad10 .htm
>
> Anyone have any other ideas? Feel free to say any solution you can think of
> even if it sounds obvious. I am very inexperienced. Thanks!
> James
>

[dischord13]

That's a good idea, but I still can't get it to work. Thanks for the help
anyway. I'm sure there is some way to do it on the code side... I'll try to
figure that out. I don't know asp very well but I've got to learn sometime!
I'll post the solution when I figure it out.

Thanks,
James

[dischord13]

I have a solution!! I just had to change the variable type to VarChar. I knew
it was something obvious I was not doing. Wildcard characters will not work
with integer variables. So now I can just click the build button for the
variable and add the % sign as the defualt value.

I had thought the variable type didn't matter becase in Access you can use the
* for any type of field and it will show all records. But variable types are
different than field types. Programming basics.... I'm learning.

Thanks for the ideas everyone.

James