document form options value sql

Imar · September 12th, 2003, 05:31 PM

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.

bubblez · September 15th, 2003, 10:40 PM

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.

Imar · September 16th, 2003, 01:29 AM

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.

bubblez · September 17th, 2003, 08:57 PM

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?

Imar · September 18th, 2003, 03:11 AM

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.

Imar · September 19th, 2003, 11:00 AM

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.

Imar · September 22nd, 2003, 04:51 AM

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.

bubblez · September 23rd, 2003, 01:30 AM

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

Imar · September 23rd, 2003, 01:31 AM

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.

Imar · September 23rd, 2003, 01:39 AM

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.