Dear All
I am using ASP to retrieve data from a SQL Server database, some of the columns of the database have entries seperated by "/" for eg the entries can be ( CAR,CAR/BIKE, CAR/BIKE/VAN etc). the current system is set up to query the comlpete phrase and not individual items, for eg if there are 100 only Car items and 100 CAR/BIKE items , a search for CAR would only show the exclusive CAR items and not the ones which have CAr as well as BIKE, to over come this problem i used the LIKE statement instead of the "=" and i get the following error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'LCL

the code for the module is given below

function createWhereClauseFromDictionary(search_fields, table_name
dim tmp_str, i, col_index, db_field_val,strReplac
tmp_str = "
for i = 1 to num_of_field
if search_fields("and_flag_" & i) = "1" the
db_field_val = "
col_index = cInt(search_fields("search_fld_" & i)
if Application(table_name)(col_index, 1) = "50" then 'bi
if uCase(search_fields("val_start_" & i)) = "TRUE" or search_fields("val_start_" & i) = "1" the
db_field_val =
elseif uCase(search_fields("val_start_" & i)) = "FALSE" or search_fields("val_start_" & i) = "0" the
db_field_val =
end i

els
db_field_val = search_fields("val_start_" & i
end i

tmp_str = tmp_str & " and " & search_fields("val_not_" & i) & "("
if len(db_field_val) > 0 then
tmp_str = tmp_str & Application(table_name)(col_index, 0
if len(search_fields("val_fin_" & i)) > 0 then 'its a rang
tmp_str = tmp_str & " >= '" & Replace(db_field_val, "'", "''") & "'
tmp_str = tmp_str & " and " & Application(table_name)(col_index, 0) & " <= '" & Replace(search_fields("val_fin_" & i), "'", "''") & "'
els
strReplace = Replace(db_field_val, "'", "''"
strReplace = Trim(strReplace

####################this is where the problem occurs###################

tmp_str = tmp_str & "LIKE '" & strReplace &"'
##############################################################
end i
els
tmp_str = tmp_str & Application(table_name)(col_index, 0) & " is null
end i
tmp_str = tmp_str & ")
end i
nex
createWhereClauseFromDictionary = tmp_st
end functio
%

I would really appreciate it if someone could help me out

thank yo
harsha

Ii is hard for you to debug the so complicated string processing function,
it is even harder for some else to debug it.

I'd suggest you to add following ASP code somewhere in the *.asp file to
show exactly then WHERE clause look like before you send the SQL statement
to SQL Server:

<P><%=createWhereClauseFromDictionary(fields,tblName)%></P>

Now, you may be able to verify whether the WHERE clause is correct or not
easily. You probably want to test this function with different parameter
values passed to make sure you ALWAYS get correct WHERE clause.

"harsha mogaligundla" <(E-Mail Removed)> wrote in message
news:006F2FC7-4CFB-46F6-85AD-(E-Mail Removed)...
> Dear All,
> I am using ASP to retrieve data from a SQL Server database,
some of the columns of the database have entries seperated by "/" for eg
the entries can be ( CAR,CAR/BIKE, CAR/BIKE/VAN etc). the current system is
set up to query the comlpete phrase and not individual items, for eg if
there are 100 only Car items and 100 CAR/BIKE items , a search for CAR would
only show the exclusive CAR items and not the ones which have CAr as well as
BIKE, to over come this problem i used the LIKE statement instead of the
"=" and i get the following error.
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near 'LCL'
>
>
> the code for the module is given below:
>
> function createWhereClauseFromDictionary(search_fields, table_name)
> dim tmp_str, i, col_index, db_field_val,strReplace
> tmp_str = ""
> for i = 1 to num_of_fields
> if search_fields("and_flag_" & i) = "1" then
> db_field_val = ""
> col_index = cInt(search_fields("search_fld_" & i))
> if Application(table_name)(col_index, 1) = "50" then 'bit
> if uCase(search_fields("val_start_" & i)) = "TRUE" or
search_fields("val_start_" & i) = "1" then
> db_field_val = 1
> elseif uCase(search_fields("val_start_" & i)) = "FALSE" or
search_fields("val_start_" & i) = "0" then
> db_field_val = 0
> end if
>
> else
> db_field_val = search_fields("val_start_" & i)
> end if
>
> tmp_str = tmp_str & " and " & search_fields("val_not_" & i) & "("
> if len(db_field_val) > 0 then
> tmp_str = tmp_str & Application(table_name)(col_index, 0)
> if len(search_fields("val_fin_" & i)) > 0 then 'its a range
> tmp_str = tmp_str & " >= '" & Replace(db_field_val, "'", "''") & "'"
> tmp_str = tmp_str & " and " & Application(table_name)(col_index, 0) & " <=
'" & Replace(search_fields("val_fin_" & i), "'", "''") & "'"
> else
> strReplace = Replace(db_field_val, "'", "''")
> strReplace = Trim(strReplace)
>
> ####################this is where the problem occurs####################
>
> tmp_str = tmp_str & "LIKE '" & strReplace &"'"
> ##############################################################
> end if
> else
> tmp_str = tmp_str & Application(table_name)(col_index, 0) & " is null"
> end if
> tmp_str = tmp_str & ") "
> end if
> next
> createWhereClauseFromDictionary = tmp_str
> end function
> %>
>
>
> I would really appreciate it if someone could help me out.
>
> thank you
> harsha

thanks norman that was a lot of help, appreciate it

harsha