Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #11 (permalink)  
Old September 12th, 2003, 05:31 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi charlene,

Right, I think I see. IMO, you should restructure your code a little, to make it easier to understand and debug. Here's a way to do this; there are more methods, but this is one I like best. Notice I am not going to write the entire code for you, but I'll try to give you the general idea:
Code:
<%
  ' Start the page with an ASP code block. Try not to mix it with HTML
  Dim User
  Dim Task
  Dim Country
  Dim SQLTotal
  Dim SQLBase
  Dim SQLWhere

  ' Request all the different lists:
  User = Request.Form("lstUser")
  Task = Request.Form("lstTask")
  Country = Request.Form("lstCountry")

  SQLBase = "SELECT Field1, Field2, Field3 FROM YourTable"

  If Len(User) > 0
    ' The User list box has been selected, so append to WHERE
    ' Check if we already have a WHERE clause
    If Len(SQLWHERE) = 0 Then
        SQLWhere = SQLWhere & " WHERE User = '" & User & "'"
    Else
        SQLWhere = SQLWhere & " AND User = '" & User & "'"
    End If
  End If

  If Len(Task) > 0
    ' The Task list box has been selected, so append to WHERE
    ' Check if we already have a WHERE clause
    If Len(SQLWHERE) = 0 Then
        SQLWhere = SQLWhere & " WHERE Task = '" & Task & "'"
    Else
        SQLWhere = SQLWhere & " AND Task = '" & Task & "'"
    End If
  End If

  If Len(Country) > 0
    ' The Country list box has been selected, so append to WHERE
    ' Check if we already have a WHERE clause
    If Len(SQLWHERE) = 0 Then
        SQLWhere = SQLWhere & " WHERE Country = '" & Country & "'"
    Else
        SQLWhere = SQLWhere & " AND Country = '" & Country & "'"
    End If
  End If

  SQLTotal = SQLBase & SQLWhere
  ' Comment the next two lines when you have examined the output
  ' of Response.Write(SQLTotal) in the browser
  Response.Write(SQLTotal)
  Response.End
  ' At this point, you have a valid SQL statement,
  ' so you can execute it.
  ' Create ADODB Connection, open it, etc
  Set MyRecordset = Connection.Execute(SQLTotal)
  ' Close connection here. Recordset will be off-line
%>
<html>
<head>
</head>
<body>
<% 
  If Not MyRecordset.EOF Then
    Do While Not MyRecordset.EOF
        Response.Write(MyRecordset("Field1") & " - " & _
           MyRecordset("Field2") & "<br /><br />")
        MyRecordset.MoveNext()
    Loop
  Else
    Response.Write("No records found")
  End If
  MyRecordset.Close()
  Set MyRecordset = Nothing
%>
</body>
</html>
By using this structure, you do all you coding decisions at the top of your page, without mixing it up with HTML. Inside the body, you can loop through the recordset with the records you have retrieved from the database.

Does this make sense?

Sorry I couldn't response earlier....


Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #12 (permalink)  
Old September 15th, 2003, 10:40 PM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yup thanks. i'm relaly new at this so any suggestions are welcome. anyhow, i checked for wether it is an integer, as oppose to ur Len() , but i think they both work the same.
Reply With Quote
  #13 (permalink)  
Old September 16th, 2003, 01:29 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

I think creating your own code is easier that maintaining someone else's, especially when it's not structured too well or is missing correct comments.

Checking whether it is an int or not is even better. Not only are you checking if there is a value, you also examine its type to prevent passing rubbish to the database.

Does the page run now?

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #14 (permalink)  
Old September 17th, 2003, 08:57 PM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

yes. thanks a lot.
however, there's another problem. as u know i have dynamic listboxes. when i click on the user name, i would show all records with that username. however, each page displays only 10 records. i have to pass the user listbox value to the other pages. not sure how i can do that...?

do i have to number my dynamic listbox value? how?or can i just pass a string to the link?
Reply With Quote
  #15 (permalink)  
Old September 18th, 2003, 03:11 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi there,

If you want, you can pass those values through the querystring of the page you are linking to. From your own example, you can see how this works. At the end of the page you're linking to (pageURL) you can add variables and their values (page and iPageCurrent for example) and pass those to the next page.

On the next (which can also be the same page when you are linking to the same page again) you can retrieve these values again.

So, in order to get the value for page in pageURL, use this:

iPageCurrent = Request.QueryString("page")

At this point, iPageCurrent will contain the same value as it did on the previous page.

It's hard for me to guess if you need sortsel or not. If it isn't used anywhere else, you can remove it. But are you sure that sortsel isn't used anywhere else? It seems to me that this variable keeps track of the sort order or sort column in your page?

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #16 (permalink)  
Old September 19th, 2003, 11:00 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi Charlene,

Sorry I didn't have the time to investigate this any earlier. I was pretty busy lately.

It makes sense that your page still shows the second page, even when you select a new item from a drop down.

Consider this URL:

http://localhost/MyPage.asp?Page=2

The code you have is able to maintain its selected page by requesting it from the QueryString bu using Request.QueryString("page")

So, even when you select a new item from the drop down, this value remains in the iSelected variable.

So to be able to create a new record selection that starts at the first page, you'll need a way to determine that a new item has been chosen from one of your listboxes. There are a few ways around this.

One of them is to set a hidden variable in the onchange event of the listbox, to indicate that the page has been submitted by choosing an item from the listbox. Then, at the server, you can look at this variable and then determine to reset the iPageCurrent variable to 0 or 1 because apparently your query has changed.

There are alternatives as well, but they are hard to explain without having seen the entire page.

Could you post a (stripped) version of the page? With stripped I mean not too much distracting HTML, CSS and header stuff. In this case, it's just the ASP and any HTML / ASP mixes that are important.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #17 (permalink)  
Old September 22nd, 2003, 04:51 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi,

I think the problem is caused by this piece of code:

Code:
' Retrieve page to show or default to 1
If Request.QueryString("page") = "" Then
    iPageCurrent = 1
Else
    iPageCurrent = CInt(Request.QueryString("page"))
End If
Whenever there is a querystring with a name of page, iPageCurrent will get that value. So, when you are on page 2, and change one of the drop-downs, you're still on page 2.

I can think of two solutions for this, but there may be more.

1. After all the drop-downs, add a submit button (if it isn't already there). When the user chooses a new value from the drop-down and presses the submit button, you can be sure that they requested a new item from the drop-down, instead of using one of your navigation links. Example:

HTML:
Code:
<input type="submit" name="btnSubmit" value="Search">
At the server, you can use this:

ASP:
Code:
If Request.Form("btnSubmit") <> "" Then
    ' Submit button pressed, so reset pagenumber
    iPageCurrent = 1
End If
This is pretty easy to do. I think you already have a submit button (<input type="image" src="btn_submit.gif" name="submitbtn"), so you can easily check whether it has been clicked or not.

2. Another approach is to have the drop-downs add a hidden field value to your page in their onchange event:

HTML:
Code:
<select name="lstCategory" onchange="document.YourForm.txtHidden.value='NewValueChosen';">

<input type="hidden" name="txtHidden">
ASP:
Code:
If Request.Form("txtHidden") = "NewValueChosen" Then
    iPageCurrent = 1
End If
The first method requires an extra action from the user because they need to click a button. The second solution requires that your user has JavaScript enabled.

There may be other / better solutions as well, but IMO, these ones will be the easiest to implement with the current code.

Does this help?


Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #18 (permalink)  
Old September 23rd, 2003, 01:30 AM
Authorized User
 
Join Date: Sep 2003
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default

think i have to have a stored variable or hidden file to store my dynamic listbox?

Reply With Quote
  #19 (permalink)  
Old September 23rd, 2003, 01:31 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi there,

I am not sure I can solve these problems with your current page. It's just a bit too much to get into and solve, without actually being able to run the page. I can give you a few tips, though. You retrieve usersel like this:

usersel=Request.QueryString("userOrdersel")

So, if you want to maintain its value in the QueryString, you'll need this:

"<a href="<%=pageUrl%>?page=<%= I %>&sortordersel=<%=sort%>&userOrdersel=<%=usersel%>"><%= I %></a>"

This way, you pass the value of usersel to the QueryString. On the next page, you can retrieve it again with usersel=Request.QueryString("userOrdersel").

You'll need to use the same stuff for the sort stuff:

sortsel= Request.QueryString("sortOrdersel")

If you want to maintain that in the QueryString, use this:

"<a href="<%=pageUrl%>?page=<%= I %>&sortOrdersel=<%=sortsel%>&userOrdersel=<%=usersel%>"><%= I %></a>"

If I were you, I'd try to clean up the page a bit before you continue. All these sortsel sortordersel sortorderusersel and usersel are not necessarily helping you in understanding paging better. Try to stick to something like this:

Dim SortOrder
SortOrder = Request.QueryString("SortOrder")
If SortOrder = "" Then
  SortOrder = "NAME" ' default, just making something up
End If

' Maintain SortOrder
<a href="somepage.asp?SortOrder=<%=SortOrder%>">Some Link Text</a>

This way, you keep giving SortOrder the same name in all locations where it matters. Makes it easier to pass values around, retrieve them and still understand what happens.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #20 (permalink)  
Old September 23rd, 2003, 01:39 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

P.S. One important concept to remember: You can't mix client side stuff with the server side. This means, that you can't pass the new selected item, like Sally, in the <a href> tag for your navigation links, because they have already been written out.

So to make you SQL statement use the new value, you'll need to add it your statement at the server:

Code:
' Image Listbox users, called lstUsers
If Request.Form("lstUsers") <> "" Then
  SQL = SQL & " AND Users = '" & Request.Form("lstUsers") & "'"
End If
This will add AND Users = 'Sally' to the WHERE clause of your SQL statement whenever the user Sally is chosen from the drop-down.

If you want, to maintain this value, add some code that preselects this drop-down again:

Dim Users
Users = Request.Form("lstUsers")


' Later in the page
' A loop to generate the Users drop-down
While .....
  If ListBox Item = Users ' if the item is Sally
     Make item selected
Loop

This way, your listbox will be selected with Sally again, and its value will be passed to the server the next time the user clicks one of your navigation links.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
IE 7 form select options harpua HTML Code Clinic 0 May 9th, 2007 11:59 PM
Document.form can't find Label bekim Javascript How-To 1 February 13th, 2006 07:39 PM
Print Word document from Access Form ppenn Access VBA 2 December 14th, 2005 07:51 AM
display word document in form collie VB.NET 2002/2003 Basics 1 September 12th, 2004 09:53 PM



All times are GMT -4. The time now is 07:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.