the LIKE command does not work... (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

TheMythe -> the LIKE command does not work... (9/29/2005 4:44:19)

Hi,

It's me again. I got a search page and at first it seems to be working fine. However when I fill in the LIKE command in the combobox, it always gives me no results. Ergo, it isn't working properly, how do I fix it? 

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>uitgebreide zoekpagina</title>
</head>
<body background="../../19sept/henri4/Deventer2004.jpg" bgproperties="fixed">
<!-- #include file="connection modulair.asp" -->
<%
strQuery = "SELECT * FROM stavoor"
rsCommon.Open strQuery, strCon, , , adCmdText %>

<form method="post">
<select name="strField">
<% for each field in rsCommon.Fields %>
   <option><%= field.Name %>
<% next %>
</select>
<select name="strOprtr">
   <option>=
   <option>>=
   <option>>
   <option><=
   <option><
   <option>LIKE
</select>
<input name="strVal">
<input type="submit" value="Geef weer">
<% rsCommon.Close %>
</form>

<% if not isEmpty(Request("strField")) then
   strField=Request("strField") 
   strOprtr=Request("strOprtr")
   if Request("strField")="nummer" then
      strField="nummer"
      strVal=Request("strVal")
   elseif Request("strField")="prijs" then
      strVal=Request("strVal")
   else
      strVal="'"&Request("strVal")&"'"
   end if %>
   <h5>. . . de volgende items zijn uit de Stavoor database gehaald: <%=strField%> <%=strOprtr%> <%=strVal%></h5>

   <% 'define the query
   strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr & " " & strVal & " ORDER BY nummer"
   rsCommon.Open strQuery, strCon, , , adCmdText 
   if not rsCommon.EOF then %>

      <table width=90% cellspacing=1 cellpadding=5 border=0>
      <!-- begin column headers -->
      <tr><% for each field in rsCommon.Fields %>
         <th><%= field.Name %></th>   
      <% next %></tr>
      <% 'cycle thru the record set and display each row results
      do until rsCommon.EOF %>
      <tr><% for each field in rsCommon.Fields %>
         <% if field.Type = 5 or field.Type = 6 then %>
            <td align="right">
         <% else %>
            <td>
         <% end if %>
         <%= field.Value %></td>
      <% next %></tr>
      <!--  next row = next record -->
      <% 'increment record position with MoveNext method
      rsCommon.MoveNext
      loop %>
      </table>
<p>De SQL statement luidt als volgt:</p>
      <blockquote><h5><%= rsCommon.Source %></h5></blockquote>
<p><a href="#">Naar boven</a></p>

   <% else %>
      <blockquote>Niets gevonden, probeer nogmaals...</blockquote>

   <% 'close record set and flush object from memory
   rsCommon.Close
   set rsCommon = Nothing 
   adoCon.close
   set adoCon = Nothing
   end if 
end if %>
</body>
</html>


Thanks in advance,



Spooky -> RE: the LIKE command does not work... (9/29/2005 5:04:34)

The format for LIKE requires one of these examples :

"Contains" : WHERE Field LIKE '%input%'
"Starts with" : WHERE Field LIKE 'input%'
"Ends with" : WHERE Field LIKE '%input'

The "%" character is the wildcard.



TheMythe -> RE: the LIKE command does not work... (9/29/2005 5:23:49)

Hi,

I'm not getting it.
I got this code: 

strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr & " " & strVal & " ORDER BY nummer"


Which I changed into this code: 

strQuery = "SELECT * FROM stavoor WHERE " & strField & " "%" & strOprtr & "%" " & strVal & " ORDER BY nummer"


But it still will not work, maybe getting ' instead of " works? 

strQuery = "SELECT * FROM stavoor WHERE " & strField & " "'% & strOprtr & %'" " & strVal & " ORDER BY nummer"


I've tried this but now it ALWAYS returns EVERY record :( no matter how specific my input it.

Further help needed I think

Thanks in advance,



Spooky -> RE: the LIKE command does not work... (9/29/2005 5:29:09) 


   '%"& strOprtr &" %'


A trick is, after creating the SQL string, write it to the page so that you can see the true syntax

eg : 

response.write strQuery
response.end


If you could do that - itll make it a bit clearer what you are doing.




TheMythe -> RE: the LIKE command does not work... (9/29/2005 7:07:46)

Now it's getting annoying. I seem to be missing out on a simple SQL statement and simply do not see it.
It is giving me an error now: (not even a response.write output command)

Error given:
Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'nummer '%=%' 6'.
/eric/29sept/henri4/zoekpagina deluxe modulair.asp, line 48

I do not see whgat is wrong with that, just display every 'nummer' record which has a 6 in it....or so I would say

My code is now as following: 

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>uitgebreide zoekpagina</title>
</head>
<body background="../../19sept/henri4/Deventer2004.jpg" bgproperties="fixed">
<!-- #include file="connection modulair.asp" -->
<%
strQuery = "SELECT * FROM stavoor"
rsCommon.Open strQuery, strCon, , , adCmdText %>

<form method="post">
<select name="strField">
<% for each field in rsCommon.Fields %>
   <option><%= field.Name %>
<% next %>
</select>
<select name="strOprtr">
   <option>=
   <option>>=
   <option>>
   <option><=
   <option><
   <option>LIKE
</select>
<input name="strVal">
<input type="submit" value="Geef weer">
<% rsCommon.Close %>
</form>

<% if not isEmpty(Request("strField")) then
   strField=Request("strField") 
   strOprtr=Request("strOprtr")
   if Request("strField")="nummer" then
      strField="nummer"
      strVal=Request("strVal")
   elseif Request("strField")="prijs" then
      strVal=Request("strVal")
   else
      strVal="'"&Request("strVal")&"'"
   end if %>
   <h5>. . . de volgende items zijn uit de Stavoor database gehaald: <%=strField%> <%=strOprtr%> <%=strVal%></h5>

   <% 'define the query
   strQuery = "SELECT * FROM stavoor WHERE " & strField & " '%" & strOprtr & "%' " & strVal & " ORDER BY nummer"
   rsCommon.Open strQuery, strCon, , , adCmdText 
   if not rsCommon.EOF then %>

      <table width=90% cellspacing=1 cellpadding=5 border=0>
      
      <!-- begin column headers -->
      <tr><% for each field in rsCommon.Fields %>
         <th><%= field.Name %></th>   
      <% next %></tr>
      <% 'cycle thru the record set and display each row results
      do until rsCommon.EOF %>
      <tr><% for each field in rsCommon.Fields %>
         <% if field.Type = 5 or field.Type = 6 then %>
            <td align="right">
         <% else %>
            <td>
         <% end if %>
         <%= field.Value %></td>
      <% next %></tr>
      <!--  next row = next record -->
      <% 'increment record position with MoveNext method
      rsCommon.MoveNext
      loop %>
      </table>
<p>De SQL statement luidt als volgt:</p>
      <blockquote><h5><%= rsCommon.Source %></h5></blockquote>
<p><a href="#">Naar boven</a></p>

   <% else %>
      <blockquote>Niets gevonden, probeer nogmaals...</blockquote>

   <% 'close record set and flush object from memory
   rsCommon.Close
   set rsCommon = Nothing 
   adoCon.close
   set adoCon = Nothing
   end if 
end if %>
</body>
</html>



I just want it to work with the = and <= and <> and => and LIKE on all fields.

Thanks in advance,



Spooky -> RE: the LIKE command does not work... (9/29/2005 15:07:23)

Like so : 

   strQuery = "SELECT * FROM stavoor WHERE " & strField & " '%" & strOprtr & "%' " & strVal & " ORDER BY nummer"

response.write strQuery 
response.end

   rsCommon.Open strQuery, strCon, , , adCmdText 
   if not rsCommon.EOF then %>



TheMythe -> RE: the LIKE command does not work... (10/6/2005 4:14:12)

Okay,

So now it returns a valid SQL-statement to my knowledge: 

strQuery = "SELECT * FROM stavoor WHERE " & strField & " '%" & strOprtr & "%' " & strVal & " ORDER BY nummer"

response.write strQuery 
response.end

   rsCommon.Open strQuery, strCon, , , adCmdText 
   if not rsCommon.EOF then %>


Answers: SELECT * FROM stavoor WHERE nummer '%=%' 6 ORDER BY nummer

However, it shows only the SQL-statement but the problem remains. At this point I'm thinking I got one of the variabels wrong, question is, which one?

Perhaps: strOprtr?

Here is the complete code again: 

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>uitgebreide zoekpagina</title>
</head>
<body background="../../19sept/henri4/Deventer2004.jpg" bgproperties="fixed">
<!-- #include file="connection modulair.asp" -->
<%
strQuery = "SELECT * FROM stavoor"
rsCommon.Open strQuery, strCon, , , adCmdText %>

<form method="post">
<select name="strField">
<% for each field in rsCommon.Fields %>
   <option><%= field.Name %>
<% next %>
</select>
<select name="strOprtr">
   <option>=
   <option>>=
   <option>>
   <option><=
   <option><
   <option>LIKE
</select>
<input name="strVal">
<input type="submit" value="Geef weer">
<% rsCommon.Close %>
</form>

<% if not isEmpty(Request("strField")) then
   strField=Request("strField") 
   strOprtr=Request("strOprtr")
   if Request("strField")="nummer" then
      strField="nummer"
      strVal=Request("strVal")
   elseif Request("strField")="prijs" then
      strVal=Request("strVal")
   else
      strVal="'"&Request("strVal")&"'"
   end if %>
   <h5>. . . de volgende items zijn uit de Stavoor database gehaald: <%=strField%> <%=strOprtr%> <%=strVal%></h5>

   <% 'define the query
   strQuery = "SELECT * FROM stavoor WHERE " & strField & " '%" & strOprtr & "%' " & strVal & " ORDER BY nummer"
   rsCommon.Open strQuery, strCon, , , adCmdText 
   if not rsCommon.EOF then %>

      <table width=90% cellspacing=1 cellpadding=5 border=0>
      
      <!-- begin column headers -->
      <tr><% for each field in rsCommon.Fields %>
         <th><%= field.Name %></th>   
      <% next %></tr>
      <% 'cycle thru the record set and display each row results
      do until rsCommon.EOF %>
      <tr><% for each field in rsCommon.Fields %>
         <% if field.Type = 5 or field.Type = 6 then %>
            <td align="right">
         <% else %>
            <td>
         <% end if %>
         <%= field.Value %></td>
      <% next %></tr>
      <!--  next row = next record -->
      <% 'increment record position with MoveNext method
      rsCommon.MoveNext
      loop %>
      </table>
<p>De SQL statement luidt als volgt:</p>
      <blockquote><h5><%= rsCommon.Source %></h5></blockquote>
<p><a href="#">Naar boven</a></p>

   <% else %>
      <blockquote>Niets gevonden, probeer nogmaals...</blockquote>

   <% 'close record set and flush object from memory
   rsCommon.Close
   set rsCommon = Nothing 
   adoCon.close
   set adoCon = Nothing
   end if 
end if %>
</body>
</html>


Again, thanks in advance for whoever reads or replies to this.



Spooky -> RE: the LIKE command does not work... (10/6/2005 4:19:32)

Shouldnt it be : 

strQuery = "SELECT * FROM stavoor WHERE " & strField & strOprtr &" '%" & strVal & "%'  ORDER BY nummer"


However - if its not LIKE thats selected - youll want to exclude the "%" ?



TheMythe -> RE: the LIKE command does not work... (10/6/2005 4:46:47)

Okay,

So now I'm thinking I'm totally barking up the wrong tree again. It still gives me errors even with your help. I just want a search page that does supports the, =, <>, =<, => and LIKE command. However, I do not seem to get it working altogether.

Output given at this point:

SELECT * FROM stavoor WHERE artikelLIKE '%'g'%' ORDER BY nummer

I know that the word 'artikel' and the word 'like' could be a problem since they are attached to eachother for some reason.
But even besides that, I get another error which says the same but then only in an IE error form.

Now help me please in understanding this for there is still lots to do for me and that means lots for me to bug you folks with (such as an update page in which I cannot seem to get the row counts working), but 1 problem at a time.

Again thanks in advance for reading and/or replying to all of this (I'm thinking about making this my signature)



rdouglass -> RE: the LIKE command does not work... (10/6/2005 8:46:02)

quote:

artikelLIKE '%'g'%'


You'll need to end up with that looking just like this:

artikel LIKE '%g%'

You have some extra apostrophes. That help any?



TheMythe -> RE: the LIKE command does not work... (10/6/2005 10:17:56)

It seems to be solved now, thanks for all your help. Final codings are: 

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>uitgebreide zoekpagina</title>
</head>
<body background="../../19sept/henri4/Deventer2004.jpg" bgproperties="fixed">
<!-- #include file="connection modulair.asp" -->
<%
strQuery = "SELECT * FROM stavoor"
rsCommon.Open strQuery, strCon, , , adCmdText %>

<form method="post">
<select name="strField">
<% for each field in rsCommon.Fields %>
   <option><%= field.Name %>
<% next %>
</select>
<select name="strOprtr">
   <option>=
   <option>>=
   <option>>
   <option><=
   <option><
   <option>LIKE
</select>
<input name="strVal">
<input type="submit" value="Geef weer">
<% rsCommon.Close %>
</form>

<% if not isEmpty(Request("strField")) then
   strField=Request("strField") 
   strOprtr=Request("strOprtr")
   if Request("strField")="nummer" then
      strField="nummer"
      strVal=Request("strVal")
   elseif Request("strField")="prijs" then
      strVal=Request("strVal")
   else
      strVal=Request("strVal")
   end if %>
   <h5>. . . de volgende items zijn uit de Stavoor database gehaald: <%=strField%> <%=strOprtr%> <%=strVal%></h5>

   <% 'define the query
   strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" '%" & strVal & "%'  ORDER BY nummer"

   
   rsCommon.Open strQuery, strCon, , , adCmdText 
   if not rsCommon.EOF then %>

      <table width=90% cellspacing=1 cellpadding=5 border=0>
      
      <!-- begin column headers -->
      <tr><% for each field in rsCommon.Fields %>
         <th><%= field.Name %></th>   
      <% next %></tr>
      <% 'cycle thru the record set and display each row results
      do until rsCommon.EOF %>
      <tr><% for each field in rsCommon.Fields %>
         <% if field.Type = 5 or field.Type = 6 then %>
            <td align="right">
         <% else %>
            <td>
         <% end if %>
         <%= field.Value %></td>
      <% next %></tr>
      <!--  next row = next record -->
      <% 'increment record position with MoveNext method
      rsCommon.MoveNext
      loop %>
      </table>
<p>De SQL statement luidt als volgt:</p>
      <blockquote><h5><%= rsCommon.Source %></h5></blockquote>
<p><a href="#">Naar boven</a></p>

   <% else %>
      <blockquote>Niets gevonden, probeer nogmaals...</blockquote>

   <% 'close record set and flush object from memory
   rsCommon.Close
   set rsCommon = Nothing 
   adoCon.close
   set adoCon = Nothing
   end if 
end if %>
</body>
</html>



TheMythe -> RE: the LIKE command does not work... (10/7/2005 4:00:28)

UHOH,

Upon testing I found that either the LIKE command works because I put in the:

strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" '%" & strVal & "%' ORDER BY nummer"

OR

I get the =, <=, <, =>, > working by having this:

strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" " & strVal & " ORDER BY nummer"

However, I do not seems to get them all working together :(
Question is once again, what part I need to change: 

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>uitgebreide zoekpagina</title>
</head>
<body background="../../19sept/henri4/Deventer2004.jpg" bgproperties="fixed">
<!-- #include file="connection modulair.asp" -->
<%
strQuery = "SELECT * FROM stavoor"
rsCommon.Open strQuery, strCon, , , adCmdText %>

<form method="post">
<select name="strField">
<% for each field in rsCommon.Fields %>
   <option><%= field.Name %>
<% next %>
</select>
<select name="strOprtr">
   <option>=
   <option>>=
   <option>>
   <option><=
   <option><
   <option>LIKE
</select>
<input name="strVal">
<input type="submit" value="Geef weer">
<% rsCommon.Close %>
</form>

<% if not isEmpty(Request("strField")) then
   strField=Request("strField") 
   strOprtr=Request("strOprtr")
   if Request("strField")="nummer" then
      strField="nummer"
      strVal=Request("strVal")
   elseif Request("strField")="prijs" then
      strVal=Request("strVal")
   else
      strVal=Request("strVal")
   end if %>
   <h5>. . . de volgende items zijn uit de Stavoor database gehaald: <%=strField%> <%=strOprtr%> <%=strVal%></h5>

   <% 'define the query
   strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" '%" & strVal & "%'  ORDER BY nummer"
   rsCommon.Open strQuery, strCon, , , adCmdText 
   if not rsCommon.EOF then %>

      <table width=90% cellspacing=1 cellpadding=5 border=0>
      
      <!-- begin column headers -->
      <tr><% for each field in rsCommon.Fields %>
         <th><%= field.Name %></th>   
      <% next %></tr>
      <% 'cycle thru the record set and display each row results
      do until rsCommon.EOF %>
      <tr><% for each field in rsCommon.Fields %>
         <% if field.Type = 5 or field.Type = 6 then %>
            <td align="right">
         <% else %>
            <td>
         <% end if %>
         <%= field.Value %></td>
      <% next %></tr>
      <!--  next row = next record -->
      <% 'increment record position with MoveNext method
      rsCommon.MoveNext
      loop %>
      </table>
<p>De SQL statement luidt als volgt:</p>
      <blockquote><h5><%= rsCommon.Source %></h5></blockquote>
<p><a href="#">Naar boven</a></p>

   <% else %>
      <blockquote>Niets gevonden, probeer nogmaals...</blockquote>

   <% 'close record set and flush object from memory
   rsCommon.Close
   set rsCommon = Nothing 
   adoCon.close
   set adoCon = Nothing
   end if 
end if %>
</body>
</html>





TheMythe -> RE: the LIKE command does not work... (10/13/2005 5:43:32)

Can someone help me with this one? I got my entire database setup working at the moment *knocks on wood* except for the LIKE command working together with the list of =, <=, =>, <, >, commands.

I just want them all to do what they are there for. 

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>uitgebreide zoekpagina</title>
<base target="_blank">
</head>
<body background="../../19sept/henri4/Deventer2004.jpg" bgproperties="fixed">
<!-- #include file="connection modulair.asp" -->
<%
strQuery = "SELECT * FROM stavoor"
rsCommon.Open strQuery, strCon, , , adCmdText %>

<form method="post">
<select name="strField">
<% for each field in rsCommon.Fields %>
   <option><%= field.Name %>
<% next %>
</select>
<select name="strOprtr">
   <option>=
   <option>>=
   <option>>
   <option><=
   <option><
   <option>LIKE
</select>
<input name="strVal">
<input type="submit" value="Geef weer">
<% rsCommon.Close %>
</form>

<% if not isEmpty(Request("strField")) then
   strField=Request("strField") 
   strOprtr=Request("strOprtr")
   if Request("strField")="nummer" then
      strField="nummer"
      strVal=Request("strVal")
   elseif Request("strField")="prijs" then
      strVal=Request("strVal")
   else
      strVal=Request("strVal")
   end if %>
   <h5>. . . de volgende items zijn uit de Stavoor database gehaald: <%=strField%> <%=strOprtr%> <%=strVal%></h5>

   <% 'define the query
   strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" '%" & strVal & "%'  ORDER BY nummer"
   rsCommon.Open strQuery, strCon, , , adCmdText 
   if not rsCommon.EOF then %>

      <table width=90% cellspacing=1 cellpadding=5 border=0>
      
      <!-- begin column headers -->
      <tr><% for each field in rsCommon.Fields %>
         <th><%= field.Name %></th>   
      <% next %></tr>
      <% 'cycle through the record set and display each row results
      do until rsCommon.EOF %>
      <tr><% for each field in rsCommon.Fields %>
         <% if field.Type = 5 or field.Type = 6 then %>
            <td align="right">
         <% else %>
            <td>
         <% end if %>
         <%= field.Value %></td>
      <% next %></tr>
      <!--  next row = next record -->
      <% 'increment record position with MoveNext method
      rsCommon.MoveNext
      loop %>
      </table>
<p>De SQL statement luidt als volgt:</p>
      <blockquote><h5><%= rsCommon.Source %></h5></blockquote>
<p><a href="#">Naar boven</a></p>

   <% else %>
      <blockquote>Niets gevonden, probeer nogmaals...</blockquote>

   <% 'close record set and flush object from memory
   rsCommon.Close
   set rsCommon = Nothing 
   adoCon.close
   set adoCon = Nothing
   end if 
end if %>
</body>
</html>


Where am I going wrong?



BeTheBall -> RE: the LIKE command does not work... (10/13/2005 9:54:24)

Are all of the fields in strField text fields because LIKE will not work properly with numeric values?



TheMythe -> RE: the LIKE command does not work... (10/13/2005 10:14:19)

Odd,

The strField contains, numbers, articles and price.

The LIKE command works fine with all of them, however I can choose only to get either the LIKE command to work or every other but LIKE.

Please help to get them all to work



BeTheBall -> RE: the LIKE command does not work... (10/13/2005 22:55:21)

Does it make a difference if you add the closing option tags?

<form method="post">
<select name="strField">
<% for each field in rsCommon.Fields %>
<option><%= field.Name %></option>
<% next %>
</select>
<select name="strOprtr">
<option>=</option>
<option>>=</option>
<option>></option>
<option><=</option>
<option><</option>
<option>LIKE</option>
</select>



TheMythe -> RE: the LIKE command does not work... (10/14/2005 8:56:32)

Doesn't seem to make any difference, sorry

It is still either the one or all the other commands :(

I'm annoyed by it and hope someone can help, thanks in advance



BeTheBall -> RE: the LIKE command does not work... (10/14/2005 10:05:28)

I think the best approach would be to use two different SQL strings depending on whether you are dealing with a text field or not. For example:

<% 'define the query
If strField = [Enter name of textfield] Then
strQuery = "SELECT * FROM stavoor WHERE " & strField & " LIKE '%" & strVal & "%' ORDER BY nummer"
Else
strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" " & strVal & " ORDER BY nummer"
End If

You would then maybe throw in some javascript so that if the user chooses a text field, then LIKE becomes selected from the dropdown by default or if the user chooses a numeric field, LIKE becomes disabled.

The problem with the way you have it now is the single quotes in the SQL. They have to be there is your criteria is based on a text field and they can't be there if the criteria is based on a numeric field, hence the two SQL statement option.



TheMythe -> RE: the LIKE command does not work... (10/17/2005 7:27:15)

Hi,

Okay, I got the text field working with both types of commands (eg: LIKE and =)
However, I get in the numeric fields only the LIKE command working and it gives a:

Data type mismatch in criteria expression.

error, I now know this is due to the numeric fields effect, how to solve it though is unknown te me...

This is my code:

strField = text or numeric field in the DB
strOprtr = LIKE, =, <, >, <=, >=
strVal = inputfield for the query, text and/or numeric 

<% 'define the query
if (strOprtr = "LIKE") then
strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" '%" & strVal & "%'"
else
strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" '" & strVal & "'"
end if

rsCommon.Open strQuery, adoCon, 1, 3, 1
if not rsCommon.EOF then %>



Please help me out, I just want to get it working and I'm not familiar with Javascript or what not.

Thanks in advance,



TheMythe -> RE: the LIKE command does not work... (10/20/2005 4:15:04)

Anyone?



TheMythe -> RE: the LIKE command does not work... (10/20/2005 5:11:38)

Okay folks,

I seemingly got it working now, this is my code 

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Zoekpagina</title>
</head>
<body background="Deventer2004.jpg" bgproperties="fixed">
<!-- #include file="connection_modulair.asp" -->
<%
strQuery = "SELECT * FROM stavoor"
rsCommon.Open "stavoor", adoCon, 1, 3, 2
%>

<form method="post"> 
<select name="strField"> 
<% for each field in rsCommon.Fields %> 
	<option><%= field.Name %></option> 
<% next %> 
</select> 
<select name="strOprtr"> 
<option>=</option> 
<option>>=</option> 
<option>></option> 
<option><=</option> 
<option><</option> 
<option>LIKE</option> 
</select>

<input name="strVal">
<input type="submit" value="Geef weer">
<% rsCommon.Close %>
</form>

<% if not isEmpty(Request("strField")) then
      strField=Request("strField") 
      strOprtr=Request("strOprtr")
   if Request("strField")="nummer" then
      strField="nummer"
      strVal=Request("strVal")
   elseif Request("strField")="prijs" then
      strField="prijs"
      strVal=Request("strVal")
   else
      strField="artikel"
      strVal=Request("strVal")
   end if %>
   <h5>. . . de volgende items zijn uit de Stavoor database gehaald: <%=strField%> <%=strOprtr%> <%=strVal%></h5>

<%
'define the query
if (strOprtr = "LIKE") then
strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" '%" & strVal & "%' ORDER BY nummer"
elseif strField="nummer" then
strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" " & strVal & " ORDER BY nummer"
elseif strField="prijs" then
strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" " & strVal & " ORDER BY nummer"
else
strQuery = "SELECT * FROM stavoor WHERE " & strField & " " & strOprtr &" '" & strVal & "' ORDER BY nummer"
end if

rsCommon.Open strQuery, adoCon, 1, 3, 1
   if not rsCommon.EOF then %>

   <table width=90% cellspacing=1 cellpadding=5 border=1 bgcolor="#000000" bordercolorlight="#FFFF00" bordercolordark="#808000" style="color: #FFFF00; text-transform: uppercase; font-weight: bold">
      
      <!-- begin column headers -->
      <tr><% for each field in rsCommon.Fields %>
         <th><font color="#FFFF00"><b><span style="text-transform: uppercase"><%= field.Name %></span></b></font></th>   
      <% next %></tr>
      <% 'cycle through the record set and display each row results
      do until rsCommon.EOF %>
      <tr><% for each field in rsCommon.Fields %>
         <% if field.Type = 5 or field.Type = 6 then %>
            <td align="right">
         <font color="#FFFF00"><b><span style="text-transform: uppercase">
         <% else %>
         </span></b></font>
            <td>
         <font color="#FFFF00"><b><span style="text-transform: uppercase">
         <% end if %>
         </span></b></font>
         <%= field.Value %></td>
      <% next %></tr>
      <!--  next row = next record -->
      <% 'increment record position with MoveNext method
      rsCommon.MoveNext
      loop %>
      </table>
<p>De SQL statement luidt als volgt:</p>
      <blockquote><h5><%= rsCommon.Source %></h5></blockquote>
<p><a href="#">Naar boven</a></p>

   <% else %>
      <blockquote>Niets gevonden, probeer nogmaals...</blockquote>

   <% 'close record set and flush object from memory
   rsCommon.Close
   set rsCommon = Nothing 
   adoCon.close
   set adoCon = Nothing
   end if 
end if %>
</body>
</html>


Cool huh, I got it working, unless any of you sees any problem with this?

Greetings,


TheMythe



Spooky -> RE: the LIKE command does not work... (10/20/2005 14:58:59)

One suggestion - dont use "SELECT * ..."
Use "SELECT Fieldname, fieldname2 ....." and only call the columns you are actually going to use. Its a bit more efficient.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.21875