Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to create filter for recordset used in DTC Drop-down.

Status
Not open for further replies.
Guest_imported

Guest_imported

New member
Jan 1, 1970
0

Need to create filter for recordset used in DTC Drop-down.

Currently, my drop-down box contains values from rsName (DTC control)

I need to filter on rsName where Name = strName
StrName is a session variable set after user logs on.

I tried using the following code, but it just pulls up rsName without the filter:

strName= Session("Name")

If rsName.isOpen() then rsName.close sql = "select * from Myrecordset Where Name= '" & strName & "'" & _
"ORDER BY rsName.tblNALC" rsName.setSQLText(sql)
rsName.open

I tried placing this code in the Sub thisPage_onenter() event. I also tried placing the code in <% tags and placing it right after the <body> tag.

I know I’m getting the correct value for strName (already tested). I also know that it sees that correct record count for rsName after the code for the filter runs(already tested)…it just doesn’t show up in my drop-down.

Any help is really appreciated.
Thanks,
Anna
 
Sort by date Sort by votes
Hi Anna,
I remember having the same problem once and I wasn't succesful in solving it. I also changed the recordset's SQL in code and also between <body> tags and it had no effect whatsoever on Listbox DTC, unless I refreshed the page or used response.redirect(&quot;mypage&quot;).
In the end I disabled automatic opening of the recordset and opened it in thisPage_onenter() event with or without filter.
Listbox DTC seems to hold on to it's former values, don't know why.
you could try setting listbox DataField, RowSource and DataSource in code instead of on the control itself, maybe this would work, but I haven't tried it.
maybe someone else knows a better answer. &quot;Defeat is not the worst of failures. Not to have tried is the true failure.&quot;
-George E. Woodberry
 
Upvote 0 Downvote
Thanks for your help Metka. It helps to know I'm not alone in my frustrations. I'll give your suggestions a try.

Thanks again,
Anna
 
Upvote 0 Downvote
Hello Metka. I worked through all your suggestions but haven't had success. I finally was able to populate my drop-down by unbinding it and using the code below. My problems now is that I can't get the after update of the drop-down to work. I run through my usual code with instructions to open the recordset for my DTC text boxes where value = drop-down.getvalue(). After making a selection, all text boxes are empty. I believe that although I'm adding the items to my drop-down, I need to somehow set the values.

Can you look at my code and tell me how I can do this? Thanks again for your help. Anna

<%
strName=Session(&quot;Name&quot;)


Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot;)
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot;)
conn.Open &quot;PROVIDER=SQLOLEDB;DATA SOURCE=Ein;&quot; & _
&quot;UID=Developer;PWD=k8vie;DATABASE=Baselines&quot;

strN = &quot;SELECT DISTINCT tblCurrentStatTemp.tblNALC&quot; & _
strN = strN & &quot; , tblContacts.Name&quot; & _
strN = strN & &quot; FROM tblNALCs INNER JOIN lblCurrentStatTemp &quot; & _
strN = strN & &quot; ON tblNALCs.NALC = tblCurrentStatTemp.tblNALC INNER JOIN tblContacts &quot; & _
strN = strN & &quot; ON tblNALCs.tblContacts_Name = tblContacts.Name;&quot;

rs.Open strN, conn

rs.Filter = &quot;Name = '&quot; & strName & &quot;'&quot;

If thisPage.firstEntered = true then
while not rs.EOF
drop-down.additem strNalc, -1,-1
rs.MoveNext
wend
End if
%>
 
Upvote 0 Downvote
Metka, wanted to let you know that I found a way to filter on the drop-down. What's interesting is that my first way works on both the grid and listbox DTC - just not on the drop-down. In order to make it filter on the drop-down I did the following 2 steps:


1) Placed the following code after the <body> section of my form:
<%
dim intR
dim intN
dim strName

strName=Session(&quot;Name&quot;)

If qrySelectNalcLead.isOpen() then qrySelectNalcLead.close
sql = &quot;select * from qrySelectNalcLead Where Name= '&quot; & strName & &quot;'&quot; & _
&quot;ORDER BY qrySelectNalcLead.tblNALC&quot;
qrySelectNalcLead.setSQLText(sql)
qrySelectNalcLead.open
%>

2)

Placed the following code in the thisPage_onEnter() event:
If qrySelectNalcLead.isOpen() then qrySelectNalcLead.close
sql = &quot;select * from qrySelectNalcLead Where Name= '&quot; & strName & &quot;'&quot; & _
&quot;ORDER BY qrySelectNalcLead.tblNALC&quot;
qrySelectNalcLead.setSQLText(sql)
qrySelectNalcLead.open
cbodropDown.clear

Thanks again for your help.
 
Upvote 0 Downvote

Glad to hear you were succesful.
Next time I will have to filter a listbox I'll know what to do.
It's strange you have to do so much just to filter drop-downs. &quot;Defeat is not the worst of failures. Not to have tried is the true failure.&quot;
-George E. Woodberry
 
Upvote 0 Downvote
A list and drop-down both populate when BOTH:
1. the list is empty
2. the recordset it is bound to is OPENed.
If that recordset DTC is set to AUTO-OPEN, then it gets the list quite early in the page processing.

A list will usually save its contents to a hidden field on the page - so it WILL NOT REFRESH from the recordset after the first time UNLESS it is cleared, ie
lstMyList.clear

This makes AUTO-OPEN recordset DTC's very inefficient for list/drop-downs. Always TURN-OFF the auto-open option, then populate a list during page load:

thisPage_onenter()
if thisPage.firstEntry then
'populate a list/dropdown...
' (set any filter parameters/sql clauses)
rsListSource.setParameter 0, strName
rsListSource.open
'list will now be populated!!!
rsListSource.close
'do not leave the connection open
'and add any extra (non database) elements
' to the list...
lstList.addItem 'Please select an item', -1, -1
end if


in this case the SQL in rsListSource could be (note the question-mark parameter)

SELECT DISTINCT tblCurrentStatTemp.tblNALC
, tblContacts.Name
FROM tblNALCs
INNER JOIN tblCurrentStatTemp
ON tblNALCs.NALC = tblCurrentStatTemp.tblNALC
INNER JOIN tblContacts
ON tblNALCs.tblContacts_Name = tblContacts.Name
WHERE tblContacts.Name = ?
ORDER BY 2 (Content Management)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mslko
  • Locked
  • Question
Interdev vs6 NTLM authentication failed (code 2146893042) on connecting to existing Project
Replies
0
Views
226
mslko
mslko
szoora
  • Locked
  • Question
Sub/Function that allows me to run various MSSQLServer 2008 stored procedures using vb.net
Replies
3
Views
215
szoora
szoora
CJwater
  • Locked
  • Question
What reference/import doI need for stdole.IUnknown
Replies
1
Views
188
CJwater
CJwater
abraxas
  • Locked
  • Question
Trouble with vs 2008 excel add in walk through
Replies
0
Views
167
abraxas
abraxas
Saturn57
  • Locked
  • Question
Print a report in VB
Replies
0
Views
149
Saturn57
Saturn57

Part and Inventory Search

Sponsor

Back
Top