[DaveRook]

Hi

I have a simple piece of code which will not accept the wild card!

My SQL looks like

Expand|Select|Wrap|Line Numbers

1. <%
2. if Request.QueryString("num")<>"" then
3. Var_Number = Request.QueryString("num")
4. else
5. Var_Number="%"
6. end if
7. %>
8.  
9.  
10. <%
11.  
12. Dim rs05
13. Dim rs05_numRows
14.  
15. Set rs05 = Server.CreateObject("ADODB.Recordset")
16. rs05.ActiveConnection = conn_05
17. rs05.Source = "SELECT *  FROM 05 WHERE ((MinContacts <= "+ Replace(Var_Number, "'", "''")+") AND (MaxContacts >= "+ Replace(Var_Number, "'", "''")+"))"
18. rs05.CursorType = 0
19. rs05.CursorLocation = 2
20. rs05.LockType = 1
21. rs05.Open()
22. %>

Let's pretend the user doesn't specify a number and so the program uses the wildcard!


If I enter

Var_Number=%

I get the message Invalid character

If I include spech marks

Var_Number="%"

I get: Microsoft JET Database Engine error '80040e14'
((MinContacts <= %) AND (MaxContacts >= %))

I have looked online, and it says the program doesn't know how to handle % as it's probably looking for an int, but I don't know how to correct it!

Thanks for any help

Dave

[codegecko]

Hi Dave,

The answer to your question is remarkably simple - wildcards don't work with numbers! You'd have to move your If block to surround your SQL construct like so:

Expand|Select|Wrap|Line Numbers

1. <%
2. Dim rs05
3. Dim rs05_numRows
4.  
5. Set rs05 = Server.CreateObject("ADODB.Recordset")
6. rs05.ActiveConnection = conn_05
7. rs05.Source = "SELECT *  FROM 05" 
8. If Request.QueryString("num")<>"" Then
9.     Var_Number = Request.QueryString("num")
10.     rs05.Source = rs05.Source + " WHERE ((MinContacts <= "+ Replace(Var_Number, "'", "''")+") AND (MaxContacts >= "+ Replace(Var_Number, "'", "''")+"))"
11. End If
12. rs05.CursorType = 0
13. rs05.CursorLocation = 2
14. rs05.LockType = 1
15. rs05.Open()
16. %>
17.  

Hope this helps.

medicineworker

[DaveRook]

medicineworker,

Thank you for your suggestion and help. This will now allow me to progress with this.

Thank you,

Dave

[codegecko]

You're welcome Dave :-)

med