| Author |
 Topic  |
|
|
Machina
New Member
Switzerland
74 Posts |
 Posted - 22 June 2004 : 07:11:26
|
Hi all,
the sql server is case sensitive in my company. This was a problem when I was searching in the forum. I worked on that and finally had to change some code AND DATA STRUCTURE in the tables.
Now my search is no more case sensitive with this sql server
If you have any comment, please tell me.
1/Data Structure
It is a very bad idea to have used in FORUM_TOPICS and FORUM_REPLY a data type ntext for the fields T_MESSAGE and R_MESSAGE
I changed these 2 fields to be in VARCHAR, 6000 caracters
2/ Code Changes in search.asp
I added the UPPER and UCASE to make everything not case sensitive.
From line 224 to 251 :
'################# New Search Code #################################################
if Request.Form("SearchMessage") = 1 then
if strAndOr = "phrase" then
strSql3 = strSql3 & " (UPPER(T.T_SUBJECT) LIKE '%" & Ucase(ChkString(Request.Form("Search"), "SQLString")) & "%') "
else
For Each word in keywords
SearchWord = ChkString(word, "SQLString")
strSql3 = strSql3 & " (UPPER(T.T_SUBJECT) LIKE '%" & Ucase(SearchWord) & "%') "
if cnt < keycnt then strSql3 = strSql3 & strAndOr
cnt = cnt + 1
next
end if
else
if strAndOr = "phrase" then
strSql3 = strSql3 & " (UPPER(R.R_MESSAGE) LIKE '%" & Ucase(ChkString(Request.Form("Search"), "SQLString")) & "%'"
strSql3 = strSql3 & " OR UPPER(T.T_SUBJECT) LIKE '%" & Ucase(ChkString(Request.Form("Search"), "SQLString")) & "%'"
strSql3 = strSql3 & " OR UPPER(T.T_MESSAGE) LIKE '%" & Ucase(ChkString(Request.Form("Search"), "SQLString")) & "%') "
else
For Each word in keywords
SearchWord = ChkString(word, "SQLString")
strSql3 = strSql3 & " ((UPPER(R.R_MESSAGE) LIKE '%" & Ucase(SearchWord) & "%')"
strSql3 = strSql3 & " OR (UPPER(T.T_SUBJECT) LIKE '%" & Ucase(SearchWord) & "%')"
strSql3 = strSql3 & " OR (UPPER(T.T_MESSAGE) LIKE '%" & Ucase(SearchWord) & "%')) "
if cnt < keycnt then strSql3 = strSql3 & strAndOr
cnt = cnt + 1
next
end if
'################# New Search Code ################################################# |
Machina
-------
http://www.jaboulay.fr
--------
Snitz based site:
http://www.itu.int/YLinICTs
MODS Used : TMPJ's Header Cell Image, Site Integration, Front Page News, News Events Calendar, Nikkol's Usergroups, Simple Slash, Avatar, File Sharing, Links |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 22 June 2004 : 07:55:56
|
quote:
1/Data Structure
It is a very bad idea to have used in FORUM_TOPICS and FORUM_REPLY a data type ntext for the fields T_MESSAGE and R_MESSAGE
I changed these 2 fields to be in VARCHAR, 6000 caracters
There is nothing wrong with defining them as ntext and it is not a BAD idea either, they are ntext for a reason, you have now imposed a 6000 char limit on your posts, we have not set any limit hence the ntext.
|
 |
|
|
Machina
New Member
Switzerland
74 Posts |
Posted - 22 June 2004 : 10:57:33
|
| Yes I know,but the sql server I work on is case sensitive. This means that any search was hugely limited... I didn't find any other idea on this. ntext is good for it huge amount of data, but how many times will it happen for a post to contain more than 6000 caracters? |
Machina
-------
http://www.jaboulay.fr
--------
Snitz based site:
http://www.itu.int/YLinICTs
MODS Used : TMPJ's Header Cell Image, Site Integration, Front Page News, News Events Calendar, Nikkol's Usergroups, Simple Slash, Avatar, File Sharing, Links |
Edited by - Machina on 22 June 2004 10:57:50 |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 22 June 2004 : 13:36:58
|
more often than you think.
|
|
|
|
sr_erick
Senior Member
USA
1318 Posts |
Posted - 22 June 2004 : 14:58:03
|
| Your original post was very close to 2000 characters alone. It really doesn't take much to go over 6000 characters. |
Erick
Snowmobile Fanatics
|
|
|
| |
Topic |
|
|
|
Topic Locked
