Forums
This topic is locked
dynamic sql statement
28 Mar 2002 16:06:58 Jon Shade posted:
I have a search page with multiple text fields used for search criteria. I thought everything was working ok until recently. And I'm finding out that my sql statement needs some serious work. Right now it is a basic<br>SELECT *<br>FROM tblJobs<br>
WHERE field1 LIKE '%vartextfield1%' AND field2 LIKE '%vartextfield2%' AND field3 LIKE '%vartextfield3%'<br>
with the variables set to vartextfield1...%...Request("textfield1"
, etc.<br><br>
If a user enters a value for textfield1 and nothing else, it should return the results (recordset) for every record that matches the criteria they entered for that textfield in the form. But, what's happening is the recordset only returns the results that fit their criteria **AND** that all other fields are populated. So record 1 has Blue, Shoe, Old; record 2 has Red, Hat, New; record 3 has Blue, Coat, (NULL)<br><br>
If you searched on Blue you should see the results of Blue, Shoe, Old & Blue, Coat, (NULL) But I only get Blue, Shoe, Old. If you search by not filling in any criteria, you get the results of record 1 & 2 but not 3 because it has a null field.<br>
<br>
Burning question is, how can I write the sql statement in UltraDev to include the records that match the search criteria even though they might have NULL fields for the fields that aren't being searched on? Do I have to do some sort of dynamic sql statement? If form uses textfield 1, then SELECT field1 from tbl WHERE field1, etc. And will that still take into account the records that have NULL values? I really don't want to have to trick the database into thinking there is something there by putting in 'filler' data.
<br><br>
Thanks in advance for your help!
JonShade
Replies
Replied 29 Mar 2002 00:55:43
29 Mar 2002 00:55:43 Owen Eastwick replied:
I think you need to generate alternative SQL Statements based on which textfields are filled, something like:
<%
varField1 = Replace(Request("Textfield1", "'", "''"
varField2 = Replace(Request("Textfield2", "'", "''"
varField3 = Replace(Request("Textfield3", "'", "''" 'Replace those pesky single quotes that foul up the SQL Statement.
SQLstring = "WHERE RecordIDfield <> -1" ' This will return all results
AndString = ""
If varField1 <> ""
AndString = AndString & " AND Field1 LIKE '%" & varField1 & "%'"
End If
If varField2 <> ""
AndString = AndString & " AND Field2 LIKE '%" & varField2 & "%'"
End If
If varField3 <> ""
AndString = AndString & " AND Field3 LIKE '%" & varField3 & "%'"
End If
SQLstring = SQLstring & AndString
%>
Then modify recordset code something like:
RecordsetName.Source = SQLstring
Take a look at the tutorial link below for more ideas:
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
<%
varField1 = Replace(Request("Textfield1"
, "'", "''" varField2 = Replace(Request("Textfield2"
, "'", "''"varField3 = Replace(Request("Textfield3"
, "'", "''" 'Replace those pesky single quotes that foul up the SQL Statement.SQLstring = "WHERE RecordIDfield <> -1" ' This will return all results
AndString = ""
If varField1 <> ""
AndString = AndString & " AND Field1 LIKE '%" & varField1 & "%'"
End If
If varField2 <> ""
AndString = AndString & " AND Field2 LIKE '%" & varField2 & "%'"
End If
If varField3 <> ""
AndString = AndString & " AND Field3 LIKE '%" & varField3 & "%'"
End If
SQLstring = SQLstring & AndString
%>
Then modify recordset code something like:
RecordsetName.Source = SQLstring
Take a look at the tutorial link below for more ideas:
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 29 Mar 2002 16:40:29
29 Mar 2002 16:40:29 Jon Shade replied:
Thanks so much for your help!
I think I am a moron though... I went into the Recordset window in Ultradev and changed the SQL statement to
SELECT *
FROM tblTest SQLstring
and set a variable of
SQLstring...WHERE ID <> -1...SQLstring (as in your tutorial) and clicked the test button. This did return all the records, even the ones with blank fields. But now I'm struggling with the rest of the code you sent. Where exactly would that fit with the code Ultradev generated (below)? I tried to change the Recordset1.Source = SQLstring but it bombed, I'm assuming because there is no indication to which table to use.
<%@LANGUAGE="VBSCRIPT"%> <%
Dim Recordset1__SQLstring
Recordset1__SQLstring = "WHERE ID <> -1"
if(SQLstring <> "" then Recordset1__SQLstring = SQLstring
%> <%
set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = "dsn=OnlineJobsDSN;"
Recordset1.Source = "SELECT * FROM tblTest " + Replace(Recordset1__SQLstring, "'", "''" + ""
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open
Recordset1_numRows = 0
%><%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<table width="300" border="1" cellspacing="2" cellpadding="2">
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td><%=(Recordset1.Fields.Item("TextField1".Value)%></td>
<td><%=(Recordset1.Fields.Item("TextField2".Value)%></td>
<td><%=(Recordset1.Fields.Item("TextField3".Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
</body>
</html>
Again, Thanks!!
Jon
JonShade
I think I am a moron though... I went into the Recordset window in Ultradev and changed the SQL statement to
SELECT *
FROM tblTest SQLstring
and set a variable of
SQLstring...WHERE ID <> -1...SQLstring (as in your tutorial) and clicked the test button. This did return all the records, even the ones with blank fields. But now I'm struggling with the rest of the code you sent. Where exactly would that fit with the code Ultradev generated (below)? I tried to change the Recordset1.Source = SQLstring but it bombed, I'm assuming because there is no indication to which table to use.
<%@LANGUAGE="VBSCRIPT"%> <%
Dim Recordset1__SQLstring
Recordset1__SQLstring = "WHERE ID <> -1"
if(SQLstring <> ""
then Recordset1__SQLstring = SQLstring%> <%
set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = "dsn=OnlineJobsDSN;"
Recordset1.Source = "SELECT * FROM tblTest " + Replace(Recordset1__SQLstring, "'", "''"
+ ""Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open
Recordset1_numRows = 0
%><%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<table width="300" border="1" cellspacing="2" cellpadding="2">
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td><%=(Recordset1.Fields.Item("TextField1"
.Value)%></td><td><%=(Recordset1.Fields.Item("TextField2"
.Value)%></td><td><%=(Recordset1.Fields.Item("TextField3"
.Value)%></td></tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table>
</body>
</html>
Again, Thanks!!
Jon
JonShade
Replied 29 Mar 2002 21:39:31
29 Mar 2002 21:39:31 Owen Eastwick replied:
Hi Jon,
As in the tutorial I would probably replace the code that UD generates entirely. You will find that UD no longer recognises it's code afterwoods and will fill the server behaviours window with red exclamaition marks, but the code will work.
Here's how I'd set it up:
<%@LANGUAGE="VBSCRIPT"%>
<%
varField1 = Replace(Request("Textfield1", "'", "''"
varField2 = Replace(Request("Textfield2", "'", "''"
varField3 = Replace(Request("Textfield3", "'", "''" 'Replace those pesky single quotes that foul up the SQL Statement.
SQLstring = "SELECT * FROM tblTest WHERE RecordIDfield <> -1" ' This will return all results
AndString = ""
If varField1 <> ""
AndString = AndString & " AND Field1 LIKE '%" & varField1 & "%'"
End If
If varField2 <> ""
AndString = AndString & " AND Field2 LIKE '%" & varField2 & "%'"
End If
If varField3 <> ""
AndString = AndString & " AND Field3 LIKE '%" & varField3 & "%'"
End If
SQLstring = SQLstring & AndString
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = "dsn=OnlineJobsDSN;"
Recordset1.Source = SQLstring
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open
Recordset1_numRows = 0
%>
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
As in the tutorial I would probably replace the code that UD generates entirely. You will find that UD no longer recognises it's code afterwoods and will fill the server behaviours window with red exclamaition marks, but the code will work.
Here's how I'd set it up:
<%@LANGUAGE="VBSCRIPT"%>
<%
varField1 = Replace(Request("Textfield1"
, "'", "''" varField2 = Replace(Request("Textfield2"
, "'", "''"varField3 = Replace(Request("Textfield3"
, "'", "''" 'Replace those pesky single quotes that foul up the SQL Statement.SQLstring = "SELECT * FROM tblTest WHERE RecordIDfield <> -1" ' This will return all results
AndString = ""
If varField1 <> ""
AndString = AndString & " AND Field1 LIKE '%" & varField1 & "%'"
End If
If varField2 <> ""
AndString = AndString & " AND Field2 LIKE '%" & varField2 & "%'"
End If
If varField3 <> ""
AndString = AndString & " AND Field3 LIKE '%" & varField3 & "%'"
End If
SQLstring = SQLstring & AndString
%>
<%
set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = "dsn=OnlineJobsDSN;"
Recordset1.Source = SQLstring
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 3
Recordset1.Open
Recordset1_numRows = 0
%>
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 29 Mar 2002 22:41:04
29 Mar 2002 22:41:04 Jon Shade replied:
Owen,
Thanks again for all your help! I am starting to see it all come together now...
The only thing that keeps happening now is I get an error of
Error Type:
Microsoft VBScript compilation (0x800A03F9)
Expected 'Then'
/online/asp/results.asp, line 10, column 17
which points to:
If varField1 <> ""
AndString = AndString & " AND Field1 LIKE '%" & varField1 & "%'"
End If
could there be something else I'm missing further down the road?
Jon
Thanks again for all your help! I am starting to see it all come together now...
The only thing that keeps happening now is I get an error of
Error Type:
Microsoft VBScript compilation (0x800A03F9)
Expected 'Then'
/online/asp/results.asp, line 10, column 17
which points to:
If varField1 <> ""
AndString = AndString & " AND Field1 LIKE '%" & varField1 & "%'"
End If
could there be something else I'm missing further down the road?
Jon
Replied 30 Mar 2002 00:18:42
30 Mar 2002 00:18:42 Owen Eastwick replied:
Oops!, sorry about that Jon, that's me being sloppy. All the If's should have a Then, like this:
If varField1 <> "" Then
AndString = AndString & " AND Field1 LIKE '%" & varField1 & "%'"
End If
If varField2 <> "" Then
AndString = AndString & " AND Field2 LIKE '%" & varField2 & "%'"
End If
If varField3 <> "" Then
AndString = AndString & " AND Field3 LIKE '%" & varField3 & "%'"
End If
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
If varField1 <> "" Then
AndString = AndString & " AND Field1 LIKE '%" & varField1 & "%'"
End If
If varField2 <> "" Then
AndString = AndString & " AND Field2 LIKE '%" & varField2 & "%'"
End If
If varField3 <> "" Then
AndString = AndString & " AND Field3 LIKE '%" & varField3 & "%'"
End If
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 30 Mar 2002 04:37:37
30 Mar 2002 04:37:37 Jon Shade replied:
Owen,
Please don't hate me!
Putting the 'Then' worked, I mean the page doesn't blow up any more. But now it's not displaying any of the recordset items. Does the tag of <%=(Recordset1.Fields.Item("TextField1".Value)%> need to be changed to accomodate the new SQL etc.?
(honestly, I'm not trying to get you to do my project for me... I will get it sooner or later! I have 7 week old twins and a 2 1/2 year old so my brain is mush lately!)
Again, thanks for the help!
Jon
Please don't hate me!
Putting the 'Then' worked, I mean the page doesn't blow up any more. But now it's not displaying any of the recordset items. Does the tag of <%=(Recordset1.Fields.Item("TextField1"
.Value)%> need to be changed to accomodate the new SQL etc.?(honestly, I'm not trying to get you to do my project for me... I will get it sooner or later! I have 7 week old twins and a 2 1/2 year old so my brain is mush lately!)
Again, thanks for the help!
Jon
Replied 30 Mar 2002 11:39:42
30 Mar 2002 11:39:42 Owen Eastwick replied:
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td><%=(Recordset1.Fields.Item("TextField1".Value)%></td>
<td><%=(Recordset1.Fields.Item("TextField2".Value)%></td>
<td><%=(Recordset1.Fields.Item("TextField3".Value)%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
If the Recorset is still called Recordset1 and the fields in your database that are being selected are TextField1, 2 and 3 then this should be OK.
However notice the SELECT statement:
"SELECT * FROM tblTest WHERE RecordIDfield <> -1"
"RecordIDfield" - did you rename this to match whatever you have named the Autonumber colum in your database table?
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF))
%>
<tr>
<td><%=(Recordset1.Fields.Item("TextField1"
.Value)%></td><td><%=(Recordset1.Fields.Item("TextField2"
.Value)%></td><td><%=(Recordset1.Fields.Item("TextField3"
.Value)%></td></tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
If the Recorset is still called Recordset1 and the fields in your database that are being selected are TextField1, 2 and 3 then this should be OK.
However notice the SELECT statement:
"SELECT * FROM tblTest WHERE RecordIDfield <> -1"
"RecordIDfield" - did you rename this to match whatever you have named the Autonumber colum in your database table?
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 30 Mar 2002 13:47:04
30 Mar 2002 13:47:04 Jon Shade replied:
I did rename RecordIDfield to ID as in my db. There just seems to be a problem with the repeat code. I took one of the <%=Recordset1.Fields.Item("field1".Value%> out of the repeat region and it did finally display it correctly. This repeat region code was generated by UD. Could that be a problem with the 'custom' code?
JonShade
.Value%> out of the repeat region and it did finally display it correctly. This repeat region code was generated by UD. Could that be a problem with the 'custom' code?JonShade
Replied 30 Mar 2002 14:35:26
30 Mar 2002 14:35:26 Owen Eastwick replied:
Possibly, heres what I suggest:
Cut and paste the custom recordset code you have created into a text document, just to keep it somewhere safe.
Get rid of the repeat region and all the dynamic text.
Set up a Recordset using "SELECT * FROM tblTest", drag the dynamic text onto the page.
Test the page and check that the records are displayed.
Set up the repeat region.
Test the page and check that all the records are displayed.
Replace the Standard Recordset code with the custom recordset code stored in the text document, Test the page again.
NOTE - make sure that the recordset names and field names match.
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Cut and paste the custom recordset code you have created into a text document, just to keep it somewhere safe.
Get rid of the repeat region and all the dynamic text.
Set up a Recordset using "SELECT * FROM tblTest", drag the dynamic text onto the page.
Test the page and check that the records are displayed.
Set up the repeat region.
Test the page and check that all the records are displayed.
Replace the Standard Recordset code with the custom recordset code stored in the text document, Test the page again.
NOTE - make sure that the recordset names and field names match.
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 02 Apr 2002 05:12:32
02 Apr 2002 05:12:32 Jon Shade replied:
Owen,
I finally got it working. It seems that my cutting and pasting needs help (or at least my attention to detail on this project!) When I pasted in your recordset code, I pasted over
><%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
and that is why the repeat region wouldn't work.
Again, much thanks for all your help!
Jon
JonShade
I finally got it working. It seems that my cutting and pasting needs help (or at least my attention to detail on this project!) When I pasted in your recordset code, I pasted over
><%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
and that is why the repeat region wouldn't work.
Again, much thanks for all your help!
Jon
JonShade