InputParameter Limitation?

[Guest]

I am using an input parameter to send the array list of records to retrieve
in the query.

In other words my Stored Procedure is doin WHERE PeopleID IN (1,2,3,ect) and
the "1,2,3,4" ect. is the parameter being passed in the input parameter.

I declared that my parameter in SQL is @pIN Varchar(1000)

And I declared that the input parameter as @pIn varchar(1000) =
"12,2,2,ectt....."

I have also tried declaring it like @pIn varchar = "12,2,2,ectt....."
without the 1000 specified to see if that makes a difference and it doesn't.

Now, I am finding limitation on the InputParameter property for a form. It
works fine when the size of the parameter is low like in the 100-200
character in length.

However, in one example I got the size of the parameter string up to 322
and it bombs. I know the string that it is passing is fine cause I do a
debug on it and get the output then take it over to query analyzer and run it
and it works.

So does anyone know what is the limitation on the passing a varchar
parameter string? I have looked and can not find information on this.

I am actually kinda bumbed here that there seems to be a limitation to this.
I mean just loading up 18 records takes up 100 characters. And as the ID's
get bigger the limitation will be more apparent.

Any help is greatly appreciated!

Angela

 

[Sylvain Lafontaine]

My advice: insert the required IDs in a table and use this table in your
Select query.

S. L.

 

[Guest]

Hi Sylvia,

Thanks for your reply...

Yeah, I could do that. Right now I see it would be a wicked complicated
solution at this point. Because of having to develop a user security system
so I can mark the table's rows for what user it belongs to (max 10 users)...
I would also then have to figure out a way of cleaning up that table for that
particular user when they are done with the specific records (choosing a new
search) and done with the application entirely. Maybe do that on form.close
for each form.

And right now I have 12 different forms (all with different data tables)
that implement this feature in access. And the forms will be going up
probably to 20 by the end of development that will be using this solution.

Is there Anything I didn't think of with this implementation? Like maybe a
simpler implementation of your solution.

The only other solution I can think of is take away the multi-select feature
and just allow double clicking to happen on a record line to pull up for
viewing. As well as putting next and previous at the bottom of the list of
records available for pulling up.

I thought about assigning it a recordset to bind to but that creates
problems with adding new record when binded.

Thanks Alot for you help,

Angela

 

[Sylvain Lafontaine]

Here some ideas:

First, you can setup an another table with an autonumber and a creation
date. Each time you need one, this will return an ID that you can use to
mark the required records and the creation date will help to make the
cleanup; for exemple by seting up a delete statement for anything that is
two days old.

Another solution would be to store all of the required IDs in a single field
somewhere, with one field by user. This will greatly simplify the
procedure.

Second, if we come back to the InputParameter limitation, it is possible
that you may overcome it by storing the ID in a hidden text box and sent
this text box as the parameter by using something like « @pIn varchar =
Forms!MyForm!MyHiddenField »

Finally, you can also explore the possibility of using a « text » field
instead of a varchar.

Hope this will help and Happy New Year!
S. L.

 

[Guest]

Oh Great Sylvain!

Thanks so much for the ideas. I always think of things in a wicked
complicated way. Sometimes not taking a step back.

The ideas you gave are great for me to explore.

Thanks so much for you help!

Have a terrific 2005 ;^)

Yours in deepest gratitude,

Angela