No problem, you please post me the output of "strList" then lets decide how it goes from there on. Since I have not seen the data that comes along with it in the first place, I am not quite sure to reply on what goes wrong with it. Keep using Response.write in every step to see how it progresses and that is how we can trace where we missing things. Let me see what comes in strList first and will take from there.

_________________________
- Vijay G
Strive for Perfection

hi Vijay,
This is one of the problem. After I removed the [], i get the error "Type mismatch: 'CHARINDEX'" message BEFORE the page get a chance to run the 'Response.write strList' statement. Therefore, I can't tell you what the output is. However, in the error message, I was able to see the POST Data: # FormAction=Step2&fields_of_interest=Accounting&fie lds_of_interest=Biological+Science+&SubmitForm=Sub mit This is the result from me checking "Accounting" and "Biological Science."

Now if I kept the [], it seems like the codes ran find, but there is nothing coming out from the 'Response.write strList.' In other words, the output of strList is blank. I mentioned about this on my second post from above.

Does this help?

Thanks again.
Leon

Hi Leon,

The typemismatch happens, because the "fields_of_interest" used in CHARINDEX is an array, where the second parameter of CHARINDEX should be a string type. I somehow missed that above, as I thought you would have been referring to your tables's columnname AMajor, as it is said in your first post. This FOR ...NEXT part is to construct the SQL statement for you to find the relevant result that would be executed in the pater part of the code. So I don't think it should be "fields_of_interest" there instead should be "AMajor". Hope a relook on my first reply would also help on this.

Sorry about losing my focus.
Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,

Thank you! It is not your fault at all. The column's name is 'Fields_of_Interest." I used AMajor at the beginning for example purpose only. I understand what your codes do, but I just can't figure out why I get the error message. I meant, I know my inputs are string type, and I have double and triple checked to make sure I got column name and table name correctly. Anyway, I will go back to check again and test out with some stuff. Thank you VERY much for all your help. I'll let you know if I find anything.

Leon

Hi Leon,

It was my bad, for having not checked the code at my end and posted here. It should have saved a lot of your precious time, had I tested it first at my end and have posted here. The "Type Mismatch" error with CharIndex is due to "not enclosing that part in Quotes". Here is the corrected code of that line I am terribly sorry about that. Hope this doesn't trouble anymore.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,
Like always, I appreciate very much for all your helps and taking your time to answer my questions. I don't think i am made for programming. I feel like there are so much that I don't know about programming.

Anyway, I added in the quotes, ran the script, but got the "Execution of a full-text operation failed. A clause of the query contained only ignored words" error message. I have no idea what this mean. The good news is, I was able to find an alternative to accomplish the task. If you don't mind, i would like to share it with you. Here are the codes:


'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
strList=trim(Request.Form("fields_of_interest"))
strList = Replace(strList, ",", "' Or '")
strList = "'" & strList & "'"
strList = Replace(strList, "'", Chr(34))
mySQL = mySQL & " And Contains(fields_of_interest, '" & strList & "')"


I replaced the For.....Next loop statement with it. Once again, thank you VERY much for your help.
Until next time....take care! :-)
Leon

Hi Leon,

Is that column a TEXT datatype? CONTAINS() can be used for search operations on a TEXT datatype column and not on VARCHAR()/CHAR() datatypes, thats why I did suggest CHARINDEX(). I wasn't aware of that being a TEXT datatype, until I saw the error message that you posted, and info on usage of CONTAINS().

Also I am not sure if OR between the items that are checked would yield the correct results for you. Is that not an AND kind of operation in your case? If that worked, thats really great.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay,
Thanks for the folllow-up. Good point on the CONTAIN() function issue...my column is a VARCHAR() datatype, but for some reason it works. However, I had to initiate a "Full-text indexing" in SQL 2000, on that column in order for the CONTAIN() function to work. Also, I understand there are some issue when using full-text indexing; for example, it affects performance b/c indexes are stored and managed external to SQL Server. Second, which I am very concern of is that the indexing doesn't get updated when I delete or add records to the database?

If the above issues are true, than I really rather go with your method of the FOR.....NEXT loop, b/c there are new records constantly being added to the table. Howver, when I ran your script, I got the "Execution of a full-text operation failed. A clause of the query contained only ignored words" error message. Any idea of what this mean? I searched the web and MS site for answers, but they did not mention a solution for it, except something about MS Hotfix? What can I do to make your solution work?

The "OR" works better, b/c I am looking for anyone who has selected anything that I have "checked" to search for. Thanks for noticing that!

Leon

Problem solved!!! :-) I figured out what I needed to do for the auto index update to occure. It has to do with setting the indexing to "Change Tracking" with the "Update index in Background" option in SQL 2000. Thank VERY much Vijay for your help! And those who took your time to read my topic.

Leon