Listbox Filter 1

[southbean]

Hello All,

I’m not that familiar with ASP (more on the database side) and have been given a task that I’ve been struggling with. It seemed simple enough at the beginning but I just can’t seem to get it to work.

I have a Listbox that pulls First_Name, Last_Name from a table. No problem there. However, the table to quite large (50,000 records) and so the page takes too long to load.

I’m trying to filter the Listbox by having users enter a last name in a Textbox then click a button that would reload the page. The SELECT statement for the Listbox would then use the value in the Textbox using a LIKE clause.

Can anyone give me some help or direction with this?

Any/all help will be Greatly appreciated!

- tl

 

[southbean]

Here's my code:

<SELECT name="UserName">
<%
'open DB
set conn=createobject("ADODB.Connection")
conn.ConnectionString = "DSN=OracleDB1.world;Uid=user;Pwd=password"
conn.open

'retrieve dropdown menu contents
sql = "SELECT table1.last_name, " &_
		"table1.first_name, " &_
		"table1.middle_name, " &_
		"table1.person_id, " &_
		"table1.state " &_
	"FROM table1 " 

‘ Textbox filter
if len(TxtLastName) > 0 then 
	sql=sql & " WHERE table1.last_name LIKE '" & replace(TxtLastName,"'","''") & "%' " 
end if

set rs = conn.execute(sql)

'populate dropdown
While Not rs.EOF
    strLName = rs("last_name")
	strFName = rs("first_name")
	strState = rs("state")
	strUID = rs("person_id")
        response.write "<OPTION>" & strLName + ", " +  strFName + ", " + strState + ": " + strUID & "</OPTION>"
    rs.MoveNext
Wend
rs.close
Set rs = Nothing
%>
</SELECT>

 

[vicvirk]

Something like this?


<form action="thispageurl.asp" method="get">
<input type="text" name="tbxSearch" id="tbxSearch" value=""/><br />
<input type="submit" name="btnSubmit" id="btnSubmit" value="Search" />
<% if request.querystring("btnSubmit") = "Search" then
strSearch = request.querystring("tbxSearch")
%>

<SELECT name="UserName">
<%
'open DB
set conn=createobject("ADODB.Connection")
conn.ConnectionString = "DSN=OracleDB1.world;Uid=user;Pwd=password"
conn.open

'retrieve dropdown menu contents
sql = "SELECT table1.last_name, " &_
"table1.first_name, " &_
"table1.middle_name, " &_
"table1.person_id, " &_
"table1.state " &_
"FROM table1 " &_
"WHERE table1.last_name LIKE '%" & strSearch & "%'"

' Textbox filter
if len(TxtLastName) > 0 then
sql=sql & " WHERE table1.last_name LIKE '" & replace(TxtLastName,"'","''") & "%' "
end if

set rs = conn.execute(sql)

'populate dropdown
While Not rs.EOF
strLName = rs("last_name")
strFName = rs("first_name")
strState = rs("state")
strUID = rs("person_id")
response.write "<OPTION>" & strLName + ", " + strFName + ", " + strState + ": " + strUID & "</OPTION>"
rs.MoveNext
Wend
rs.close
Set rs = Nothing
%>
</SELECT>

<% end if %>
</form>



--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[vicvirk]

^ quick edit

you don't need this:

' Textbox filter
if len(TxtLastName) > 0 then 
    sql=sql & " WHERE table1.last_name LIKE '" & replace(TxtLastName,"'","''") & "%' " 
end if

Basiclly if the search was performed, the select box will show otherwise all the user will see is the input box.

Hope that helps


--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[southbean]

Hello vicvirk,

Thank you VERY much for your reply. That worked perfectly!

However, those two fields are just two of many on this form. So, the submit button isn't going to work for this situation.
Is there anyway to use an icon/button to requery the Listbox - using the Textbox filter value - without submitting the form?

Again, thank you very much for your excellent reply!

- tl

 

[vicvirk]

However, those two fields are just two of many on this form. So, the submit button isn't going to work for this situation.
Is there anyway to use an icon/button to requery the Listbox - using the Textbox filter value - without submitting the form?

Yeah, but then you're getting into AJAX where you need to attach an "onclick" event to the button which goes off fires off a javascript function that looks up the values from a dynamic XML file. These values are then parsed out and each item is added as an option to your select box.

Are you up for it?




--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[southbean]

Yeah, I thought it might come to that.

Not sure if I am up for that level of coding. My ASP is weak as it is. I tried to craft an 'OnClick' event using a button/icon. But I was not successful.

Is that the only way?

Thanks again for all your help!

-tl

 

[vicvirk]

Yeah, I thought it might come to that.

Not sure if I am up for that level of coding. My ASP is weak as it is. I tried to craft an 'OnClick' event using a button/icon. But I was not successful.

Is that the only way?

Thanks again for all your help!

Yeah, that is the only way I know how to do it - refreshing a listbox/dropdown without submitting the whole form requires an AJAX request.

AJAX isn't hard to learn, especially for something like this. If you have a few moments, go through the tutorial on W3Schools - it's where I learned how to make a simple request and then it was fairly straight forward from there to do "almost" anything I wanted.

http://www.w3schools.com/Ajax/Default.Asp

If you can get part of it working, I'd be glad to help with the rest.




--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------