Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old June 21st, 2005, 01:18 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #12 (permalink)  
Old June 21st, 2005, 10:36 AM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #13 (permalink)  
Old June 22nd, 2005, 08:21 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #14 (permalink)  
Old June 22nd, 2005, 10:51 AM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #15 (permalink)  
Old June 23rd, 2005, 05:07 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Code:
strsql = strsql & "CHARINDEX(" & ArrChecked(i) & ", AMajor) > 0 "
I am terribly sorry about that. Hope this doesn't trouble anymore.

Cheers!

_________________________
- Vijay G
Strive for Perfection
Reply With Quote
  #16 (permalink)  
Old June 23rd, 2005, 10:23 AM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #17 (permalink)  
Old June 24th, 2005, 04:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #18 (permalink)  
Old June 27th, 2005, 04:04 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Reply With Quote
  #19 (permalink)  
Old June 29th, 2005, 02:29 PM
Friend of Wrox
 
Join Date: Jul 2003
Location: , , .
Posts: 128
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Best Searching Criteria Muhammad Zeeshan SQL Server 2000 1 September 30th, 2007 07:14 AM
2 LINK CRITERIA Vision G Access 1 July 14th, 2006 10:33 AM
selection criteria... dbrook007 Other Programming Languages 0 February 21st, 2006 04:41 PM
Criteria in Query lryckman Access 1 June 23rd, 2004 11:11 AM
what is the criteria to get one record yylee Access 1 April 29th, 2004 04:19 PM



All times are GMT -4. The time now is 12:24 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.