Hi everyone! Hope you can give m 1

[dbrom]

Hi everyone!

Hope you can give me a hand with this little problem:

I am developing an advanced search page that searches for actors resumes on our website (ASP and SQL Server). On this page, I have a number of fields: drop-down lists, checkboxes, etc.

For example, I have a group of checkboxes that list sports that actors are capable of playing. There are about 30 checkboxes, and when checked, the value of each checkbox is added to a string, in which these values are separated by commas ("soccer,football,karate,volleyball,...&quot. The problem is, I do not know exactly how many of these will be checked, but I need to develop a stored procedure that would get parameters from my ASP page and then return the results.

I thought of taking this string as a varchar parameter and then transform this string into a series of
colName LIKE '%soccer%' AND colName LIKE'%football%' AND ...
statements inside the stored procedure, but I am not sure if it can be done...

I also thought about extracting each of values from comma-separated string inside ASP code and then passing all thirty as parameters having default values, but along with other fields on the search page that would create a stored procedure with more than a hundred parameters, plus this approach is not scalable: each additional checkbox will require SQL code modifications.

Any ideas how it can be done?
I need to use sproc to be able to use server-side paging which seems to be much faster than client-side paging, since we can potentially have several thousand resumes... <Dmitriy>
dbrom@crosswinds.net

 

[vasah20]

The best thing for you to do would be to pass in the comma delim string into the SP, and break it apart in the SP.

I know that there is a left and right function in T-SQL, and a CHARINDEX function... so just do a charindex for each &quot;,&quot; and break the string apart accordingly.

I'm sorry that I can't give you an example of how to do this, as I don't have BOL here at home. I'm sure that if you ask the SQL forum how to break apart a CSV string, they'll have a response for you quickly.

Sidenote:
are you concatinating the string (soccer,football, etc..) through javascript? If you are, that might be unneccessary. If you name all the checkboxes the same name, ASP will automatically create a CSV string for you. I know that you probably can't change your form code, given that it seems pretty big, but this is probably something that you might want to keep in mind for the future.

hth hth
leo

------------
Leo Mendoza
lmendoza@students.depaul.edu

 

[dbrom]

Leo, thanks for response!

I will try to ask fellas in the SQl Server forum.

But maybe you can help me with this little problem (I posted it in the other thread, but what the heck...)

When I run this stored procedure:


CREATE PROC testCDs
@TotalRecs int OUTPUT
AS
SELECT cdID, cdFirstName, cdLastName, cdCompany FROM tviCDs
SELECT @TotalRecs = @@ROWCOUNT


in this code:


objCommand = Server.CreateObject(&quot;ADODB.Command&quot
objCommand.ActiveConnection = connTviDB
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = &quot;testCDs&quot;
objCommand.Parameters.Append(objCommand.CreateParameter(&quot;@TotalRecs&quot;,adInteger,adParamOutput))
rsSearch = objCommand.Execute();
totalRecs = objCommand.Parameters(&quot;@TotalRecs&quot;
Response.Write(&quot;'&quot; + totalRecs + &quot;'&quot;


my code returns 'undefined'. So it does not recieve a value from a stored procedure.

Have anyone had any experience with Sprocs returning recordser along with some output parameters? I have no clue what the problem might be...
Even when I replace
SELECT @TotalRecs = @@ROWCOUNT
with, say
SELECT @TotalRecs = 1
I still get the same thing - undefined.

<Dmitriy>
dbrom@crosswinds.net

 

[vasah20]

hmmm...
I saw this one, and honestly what I would recommend is using the return value instead. It's something like this:




CREATE PROC testCDs
AS
SELECT cdID, cdFirstName, cdLastName, cdCompany FROM tviCDs
RETURN @@ROWCOUNT

objCommand = Server.CreateObject(&quot;ADODB.Command&quot
objCommand.ActiveConnection = connTviDB
objCommand.CommandType = adCmdStoredProc
objCommand.CommandText = &quot;testCDs&quot;
objCommand.Parameters.Append(objCommand.CreateParameter(&quot;@TotalRecs&quot;,adInteger,adParamReturnValue))
rsSearch = objCommand.Execute();
totalRecs = objCommand.Parameters(&quot;@TotalRecs&quot;
Response.Write(&quot;'&quot; + totalRecs + &quot;'&quot;

see if that works. Just a note -- if you decide to use the adParamReturnValue, it _always_ has to be the first parameter in your list. It won't work anywhere else, even if you specify the type. I don't know why that it, it just is

hth hth
leo

------------
Leo Mendoza
lmendoza@students.depaul.edu

 

[ToddWW]

Here's how we do it on our site. When ASP is writing the page that lists the checkboxes, we store the name of each checkbox into a Session Dictionary Object. Then, on the page that the form is submitted to, we take each element in the Session Dictionary Object and examine the Request.Form collection to see which of those checkboxes is checked and which is not. When it's done, we release the Session Dictionary Object from memory. I'm sure you can do the same with a Session Array, but we like dictionary objects because they are searchable, etc...

We create the Session Dictionary Object each time the initial page loads because the number and names of checkboxes change on our site depending on the user, etc...

If you need sample code, let me know.

TW

 

[dbrom]

Thanks!

I have tried that, but I get the same thing - 'undefined'.

I am trying to page the Recordset, and when I use rsObj.RecordCount the page running time is increased from 0.016 sec to about 1 sec. As you can see it makes a huge difference. But I need to have a count of returned records to be able to output navigation links to other pages of the recordset.

I have read up on recordset paging, and I have found that the approach of using rs.GetRows(...) is the fastest.
(

http://www.adopenstatic.com/experiments/recordsetpaging.asp)

But for some reason, as you can see, it does not work for me...

I use Win2k server (actually we use webhosting with HostPro), and SQL Server 7 or 2000 (not sure). So I am totally lost - I have no idea why it does not work...

Any ideas on how I can count returned records via SQL?
It seems I can't use COUNT(*) without running the second query ( which is cost effective :-( ) or aggregative functions which won't do in this case..
<Dmitriy>
dbrom@crosswinds.net

 

[dbrom]

By the way, Leo, thanks for your notes.

That's exactly what I do: i just name all checkboxes the same, so they are separated by commas automatically.

nice feature... <Dmitriy>
dbrom@crosswinds.net

 

[dbrom]

Thanks Todd and Leo,

part of my problem is that I have to write a stored procedure prior to page execution, and if I pass these checkboxes as parameters I can't know what their number is.

But it does not seem to be a problem anymore. I thought about this thing, and decided that I can reasonably assume nobody is going to demand that an actor plays every possible sport, speaks every possible language, etc.

So I can limit the number of parameters, and just have a stored procedure with, say, 100 parameters. It's going to be huge as far as coding is concerned, but if it's going to be faster - why not..

The thing is, however, that I am only using Sproc to be able to return a value containing number of rows from it.
But as you can see from my previous posts, the value is not returned...

I have no idea why both output parameter and return value are returned as undefined when returned along with recordset.

When I execute the Sproc in Query Analizer, I can see correct results. So it seems to be an ASP problem.

So if I could find a way to count records while selecting them it would take care of all my problems.

Same applies to being able to return number of selected rows from sproc, which again for some reason does not work...

If you guys have any ideas on how to do either implementation, I will really appreciate.

Thanks for responses.
<Dmitriy>
dbrom@crosswinds.net