User defined column display [Archive]

Click to See Complete Forum and Search --> : User defined column display

dlacloche

08-10-2006, 01:05 PM

I'm sure this is possible, but I'm probably doing it wrong.

I wanted the user to be able to choose what column from a database displays on an output page, so I made a form with one text field and a submit button.

My idea was to have the display on the output page determined by the form. It would look like below, with the form filling in the Whatever column.

Like: First_Name, Last_Name, [Whatever]

So, I have:
<%=(Recordset1.Fields.Item("First_Name").Value)%><%=(Recordset1.Fields.Item("Last_Name").Value)%><%=(Recordset1.Fields.Item("Request.Form("Whatever")").Value)%>

Should that work?

dlacloche

08-10-2006, 01:11 PM

I think it's a syntax error, but can't figure it out.

russell

08-10-2006, 01:24 PM

remove the quotes around whatever

dlacloche

08-10-2006, 03:23 PM

It didn't work. It got a little further. Now, I get:

ADODB.Fields error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

I'm sure that I'm entering correct column names into the form, but ???

Is it still a syntax thing?

russell

08-10-2006, 03:53 PM

right b4 this:

<%=(Recordset1.Fields.Item("First_Name").Value)%><%=(Recordset1.Fields.Item("Last_Name").Value)%><%=(Recordset1.Fields.Item("Request.Form("Whatever")").Value)%>

try this
<%
Response.Write Recordset1("First_Name") & "<br>"
Response.Write Recordset1("Last_Name") & "<br>"
Response.Write whatever & "<br>"
Response.Write Recordset1(whatever) & "<br>"
%> what line does it error out on?
what is the value of whatever?

dlacloche

08-10-2006, 04:50 PM

My plan was to have 3 fields display on the page. The first 2 are preset by me; first_name and last_name, the 3rd field is the users choice. They enter a field name in the text field of the form--like Title, City, State or Zip--and it should display on the page.

So, the value of whatever is any field name from the recordset that the user enters in the form.

The breakdown occurs on line 54--the same line with the <%=(Recordset1.Fields.Item("Request.Form("Whatever")").Value)%>

russell

08-10-2006, 05:27 PM

what's the value of Request.Form("Whatever") ?

and take away these quotes

<%=(Recordset1.Fields.Item("Request.Form("Whatever")").Value)%>
to

<%=(Recordset1.Fields.Item(Request.Form("Whatever")).Value)%>

dlacloche

08-10-2006, 05:44 PM

The value of Request.Form("Whatever") is whatever the user enters into the text field of the form. An example is Title. That's the name of one of the recordset fields. Others include: City, State, Zip.

I've now removed the quotes. and I get:
ADODB.Fields error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/resourcesfiltered.asp, line 66

russell

08-10-2006, 06:44 PM

don't care what it might be -- what IS it when u run it?

before even accessing your db, do this

Response.Write Request.Form("whatever")

what does it show?

russell

08-10-2006, 06:46 PM

also, what db are u using?

and, u r better off giving 'em a select element than a text box

dlacloche

08-11-2006, 10:43 AM

I put:
<% Response.Write(Request.Form("whatever")) %>
and I got nothing. No errors, but nothing--just looked like an empty page.
I'm using Access as the database.

I'm going to change the input method of the form to a pull down list once I get it working.

russell

08-11-2006, 11:43 AM

that means that you are trying to access a field like this

Recordset1.Fields.Item().Value

thus, your error.

is the form method post?

perhaps if u post the entire script we can better help u out, but u are definitely not passing the textbox value in this case...

dlacloche

08-15-2006, 12:11 PM

Here's the code. The problem is on line 60ish. Thank you for all the help. I've posted this problem to many boards, and this is the only one that answered back.

The Form Method is POST.

<%@LANGUAGE="VBSCRIPT"%>

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conn_whebrd_STRING
Recordset1.Source = "SELECT * FROM tbl_Participants"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>
<HTML>
<HEAD>
<TITLE>Roundtable: </TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<link href="whebr.css" rel="stylesheet" type="text/css">
</HEAD>
<BODY BGCOLOR=#FFEFC3 LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0>

<table width="100%" border="0" cellspacing="2" cellpadding="3">
<tr>
<td align="center" valign="top">
<TABLE WIDTH=720 BORDER=0 CELLPADDING=0 CELLSPACING=0 bgcolor="#FFFFFF">
<TR>
<TD COLSPAN=4>
<IMG SRC="images/generalpage_01.jpg" WIDTH=511 HEIGHT=24 ALT=""></TD>
<TD>
<a href="index.asp"><IMG SRC="images/generalpage_02.jpg" ALT="" WIDTH=52 HEIGHT=24 border="0"></a></TD>
<TD>
<a href="aboutus.asp"><IMG SRC="images/generalpage_03.jpg" ALT="" WIDTH=72 HEIGHT=24 border="0"></a></TD>
<TD>
<a href="contact.asp"><IMG SRC="images/generalpage_04.jpg" ALT="" WIDTH=67 HEIGHT=24 border="0"></a></TD>
<TD ROWSPAN=2>
<IMG SRC="images/generalpage_05.jpg" WIDTH=18 HEIGHT=109 ALT=""></TD>
</TR>
<TR>
<TD ROWSPAN=2>
<IMG SRC="images/generalpage_06.jpg" WIDTH=17 HEIGHT=456 ALT=""></TD>
<TD ROWSPAN=2>
<IMG SRC="images/generalpage_07.jpg" WIDTH=133 HEIGHT=456 ALT=""></TD>
<TD COLSPAN=5>
<IMG SRC="images/generalpage_08.jpg" WIDTH=552 HEIGHT=85 ALT=""></TD>
</TR>
<TR>
<TD>
<IMG SRC="images/generalpage_09.jpg" WIDTH=5 HEIGHT=371 ALT=""></TD>
<TD COLSPAN=5 align="left" valign="top"><table width="100%" border="0" cellspacing="2" cellpadding="3">
<tr>

</tr>
</table>
<table width="100%" border="0" cellspacing="2" cellpadding="3">
<tr>
<td><p><%= Request.Form("whatever") %></p>
<p><%=(Recordset1.Fields.Item(Request.Form("whatever")).Value)%></p></td>
</tr>
</table></TD>
</TR>
<TR>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=17 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=133 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=5 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=356 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=52 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=72 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=67 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=18 HEIGHT=1 ALT=""></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>

</BODY>
</HTML>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

russell

08-15-2006, 01:45 PM

may we see the code containing the form that gets submitted as well?

dlacloche

08-15-2006, 03:11 PM

The form is around line 153. Thanks. I hope this will solve the mystery.

<%@LANGUAGE="VBSCRIPT"%>

<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers=""
MM_authFailedURL="index.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
If (true Or CStr(Session("MM_UserAuthorization"))="") Or _
(InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
MM_grantAccess = true
End If
End If
If Not MM_grantAccess Then
MM_qsChar = "?"
If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
MM_referrer = Request.ServerVariables("URL")
if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
Response.Redirect(MM_authFailedURL)
End If
%>
<%
Dim login__MMColParam
login__MMColParam = "2"
If (Session("svuserID") <> "") Then
login__MMColParam = Session("svuserID")
End If
%>
<%
Dim login
Dim login_numRows

Set login = Server.CreateObject("ADODB.Recordset")
login.ActiveConnection = MM_conn_whebrd_STRING
login.Source = "SELECT * FROM tbl_users INNER JOIN tbl_userLinks ON tbl_userLinks.userLinksID=tbl_users.userLinksID WHERE userID=" + Replace(login__MMColParam, "'", "''") + ""
login.CursorType = 0
login.CursorLocation = 2
login.LockType = 1
login.Open()

login_numRows = 0
%>
<%session("sessFirstName")=Request.Form("name")%>
<HTML>
<HEAD>
<TITLE>Roundtable: Resources</TITLE>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<link href="whebr.css" rel="stylesheet" type="text/css">
</HEAD>
<BODY BGCOLOR=#FFEFC3 LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0>

<table width="100%" border="0" cellspacing="2" cellpadding="3">
<tr>
<td align="center" valign="top">
<TABLE WIDTH=720 BORDER=0 CELLPADDING=0 CELLSPACING=0 bgcolor="#FFFFFF">
<TR>
<TD ROWSPAN=5>
<IMG SRC="images/userMaster_01.jpg" WIDTH=38 HEIGHT=480 ALT=""></TD>
<TD COLSPAN=4 bgcolor="#AA8B4B" class="welcometext"> Welcome, <%= Session("svfirstName") %></TD>
<TD ROWSPAN=3>
<IMG SRC="images/userMaster_03.jpg" WIDTH=322 HEIGHT=108 ALT=""></TD>
<TD>
<a href="index.asp"><IMG SRC="images/userMaster_04.jpg" ALT="" WIDTH=54 HEIGHT=24 border="0"></a></TD>
<TD>
<a href="aboutus.asp"><IMG SRC="images/userMaster_05.jpg" ALT="" WIDTH=72 HEIGHT=24 border="0"></a></TD>
<TD>
<a href="contact.asp"><IMG SRC="images/userMaster_06.jpg" ALT="" WIDTH=72 HEIGHT=24 border="0"></a></TD>
<TD ROWSPAN=3>
<IMG SRC="images/userMaster_07.jpg" WIDTH=13 HEIGHT=108 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=1 HEIGHT=24 ALT=""></TD>
</TR>
<TR>
<TD COLSPAN=4>
<IMG SRC="images/userMaster_08.jpg" WIDTH=149 HEIGHT=25 ALT=""></TD>
<TD COLSPAN=3 ROWSPAN=2>
<IMG SRC="images/userMaster_09.jpg" WIDTH=198 HEIGHT=84 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=1 HEIGHT=25 ALT=""></TD>
</TR>
<TR>
<TD ROWSPAN=3>
<IMG SRC="images/userMaster_10.jpg" WIDTH=9 HEIGHT=431 ALT=""></TD>
<TD ROWSPAN=2 valign="top" bgcolor="#AA8B4B"> <table width="100%" border="0" cellspacing="0" cellpadding="3">
<tr>
<td class="linksText"><a href="participant.asp" class="linksText"><%=(login.Fields.Item("link1").Value)%></a></td>
</tr>
<tr>
<td class="welcometext"> </td>
</tr>
<tr>
<td class="welcometext"><a href="minutes.asp" class="linksText"><%=(login.Fields.Item("link2").Value)%></a></td>
</tr>
<tr>
<td class="welcometext"> </td>
</tr>
<tr>
<td class="littleRed"><%=(login.Fields.Item("link3").Value)%></td>
</tr>
<tr>
<td class="welcometext"> </td>
</tr>
<tr>
<td class="welcometext"><a href="reports.asp" class="linksText"><%=(login.Fields.Item("link4").Value)%></a></td>
</tr>
<tr>
<td class="welcometext"> </td>
</tr>
<tr>
<td class="welcometext"><a href="newsletter.asp" class="linksText"><%=(login.Fields.Item("link5").Value)%></a></td>
</tr>
<tr>
<td class="welcometext"> </td>
</tr>
<tr>
<td><a href="userMaster.asp" class="linksText"><%=(login.Fields.Item("link6").Value)%></a></td>
</tr>
<tr>
<td class="welcometext"> </td>
</tr>
<tr>
<td class="linksText"><%=(login.Fields.Item("link7").Value)%></td>
</tr>
<tr>
<td class="welcometext"> </td>
</tr>
<tr>
<td class="linksText"><%=(login.Fields.Item("link8").Value)%></td>
</tr>
<tr>
<td class="welcometext"> </td>
</tr>
<tr>
<td class="linksText"><%=(login.Fields.Item("link9").Value)%></td>
</tr>
</table></TD>
<TD height="59" COLSPAN=2>
<IMG SRC="images/userMaster_12.jpg" WIDTH=43 HEIGHT=59 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=1 HEIGHT=59 ALT=""></TD>
</TR>
<TR>
<TD ROWSPAN=2>
<IMG SRC="images/userMaster_13.jpg" WIDTH=11 HEIGHT=372 ALT=""></TD>
<TD COLSPAN=6 ROWSPAN=2 align="left" valign="top" class="bodytextwhenoverWhite"><table width="100%" border="0" cellspacing="2" cellpadding="3">
<tr>
<td align="left" valign="top" class="bodytextwhenoverWhite"><p>Please click on the following links for
Roundtable resource materials related to:</p>
<p><a href="universityGov.asp">University Governance </a> </p>
<p><a href="economicDev.asp">Economic Development</a> </p>
<p><a href="uwSystemFact.asp">UW System Facts </a></p>
<form action="resourcesfiltered.asp" method="post" name="frm_columnFilter" id="frm_columnFilter">
<input name="whatever" type="text" id="whatever">
<input type="submit" name="Submit" value="Submit">
</form> <p> </p></td>
</tr>
</table> <p class="bodytextwhenoverWhite"> </p>
</TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=1 HEIGHT=337 ALT=""></TD>
</TR>
<TR>
<TD>
<IMG SRC="images/userMaster_15.jpg" WIDTH=97 HEIGHT=35 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=1 HEIGHT=35 ALT=""></TD>
</TR>
<TR>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=38 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=9 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=97 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=11 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=32 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=322 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=54 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=72 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=72 HEIGHT=1 ALT=""></TD>
<TD>
<IMG SRC="images/spacer.gif" WIDTH=13 HEIGHT=1 ALT=""></TD>
<TD></TD>
</TR>
</TABLE>
</TD>
</TR>
</TABLE>

</BODY>
</HTML>
<%
login.Close()
Set login = Nothing
%>

russell

08-15-2006, 10:32 PM

Basically, it looks like u are not entering a valid field into the "whatever" form field. I may ask to see the names of every column in tbl_Participants, but for now, i want u to try two things:

First here is some debuggin code to add:

Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_conn_whebrd_STRING
Recordset1.Source = "SELECT * FROM tbl_Participants"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0

'' DEBUG CODE HERE
Dim f
Dim w
Dim blnMatch

w = trim(lcase(Request.Form("whatever")))

Response.Write "<span style=""color:red"">whatever: " & w & "</span>" & vbCrLf

For Each f in Recordset1.Fields
Response.Write trim(lcase(f.name))
If f = w Then
Response.Write " <b>MATCH</b>"
blnMatch = True
End If
Response.Write "<br>" & vbCrLf
Next

If Not blnMatch Then
Response.Write "<h4>The value of whatever: <i>" & w & "</i> is not a valid field in tbl_Participants</h4>" & vbCrLf
End If

Second, change this

<p><%=(Recordset1.Fields.Item(Request.Form("whatever")).Value)%></p>
to this:

Dim whatever

whatever = trim(Request.Form("whatever"))

If len(whatever) Then
Response.Write Recordset1(whatever)
Else
Response.Write "Form field empty"
End If
what do u see now?

russell

08-15-2006, 10:37 PM

if it crashes on the line in red, then the text entered into the form does not match a field name in your table

Dim whatever

whatever = trim(Request.Form("whatever"))

If len(whatever) Then
Response.Write Recordset1(whatever)
Else
Response.Write "Form field empty"
End If

dlacloche

08-16-2006, 10:18 AM

Wow. Now it's kinda working.
When I run that, I get no crashes, but I get text at the top of the page:

whatever: last id
category
organization
title
prefix
first
last
city
state
zip
phone 1
email
address 1
phone 2
full_bio
photo
short_bio
address 2

The value of whatever: last is not a valid field in tbl_Participants

But then--it displays the info the way that I wanted on the page.

So, the text says that it's not valid, but then it works?!?

russell

08-16-2006, 10:39 AM

cool. ok i had a small omission in the test code, which is why it says it didn't match, but the second code sample did the trick, so thats good. note that u will need to wrap fields with spaces in 'em ( phone 1, phone 2) with square brackets [phone 1]

this will fix the mistake i made in the debug code ( if u still need it )

'' DEBUG CODE HERE
Dim f
Dim w
Dim blnMatch

w = trim(lcase(Request.Form("whatever")))

Response.Write "<span style=""color:red"">whatever: " & w & "</span>" & vbCrLf

For Each f in Recordset1.Fields
Response.Write trim(lcase(f.name))
If trim(lcase(f.name)) = w Then
Response.Write " <b>MATCH</b>"
blnMatch = True
End If
Response.Write "<br>" & vbCrLf
Next

If Not blnMatch Then
Response.Write "<h4>The value of whatever: <i>" & w & "</i> is not a valid field in tbl_Participants</h4>" & vbCrLf
End If

dlacloche

08-16-2006, 02:53 PM

Thanks again. I will use the code for good and not evil.