navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

Microsoft MVP

 

the LIKE command does not work...

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> the LIKE command does not work... 		Page: [1]
 
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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

 

Posts: 26603
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		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.

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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,

(in reply to TheMythe)
Spooky

 

Posts: 26603
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		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.



_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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,

(in reply to TheMythe)
Spooky

 

Posts: 26603
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		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 %>


_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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.

(in reply to Spooky)
Spooky

 

Posts: 26603
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		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 "%" ?

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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)

(in reply to Spooky)
rdouglass

 

Posts: 9265
From: Biddeford, ME USA
Status: offline

  		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?

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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>

(in reply to rdouglass)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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>



(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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?

(in reply to TheMythe)
BeTheBall

 

Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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?

_____________________________

Duane

Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.

(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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

(in reply to BeTheBall)
BeTheBall

 

Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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>


_____________________________

Duane

Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.

(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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

(in reply to BeTheBall)
BeTheBall

 

Posts: 6362
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		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.

_____________________________

Duane

Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.

(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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,

(in reply to BeTheBall)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		RE: the LIKE command does not work... - 10/20/2005 4:15:04   
Anyone?

(in reply to TheMythe)
TheMythe

 

Posts: 58
Joined: 9/16/2005
From: Ectalion
Status: offline

  		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

(in reply to TheMythe)
Spooky

 

Posts: 26603
Joined: 11/11/1998
From: Middle Earth
Status: offline

  		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.

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

§þ:)k¥


(in reply to TheMythe)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> the LIKE command does not work... 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.