retrieve data using LIKE statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
Dear All,
I am using ASP to retrieve data from a SQL Server database,
Click to expand...
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
Click to expand...
search_fields("val_start_" & i) = "1" then
db_field_val = 1
elseif uCase(search_fields("val_start_" & i)) = "FALSE" or
Click to expand...
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) & " <=
Click to expand...
'" & Replace(search_fields("val_fin_" & i), "'", "''") & "'"
 
Back
Top