|
| |
Inserting a Database-Populated Dropdown in a FP Database Results Region
View related threads:
(in this forum
| in all forums)
|
Logged in as: Guest
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
 |
Inserting a Database-Populated Dropdown in a FP Databas... - 9/18/2004 16:46:43
Shortly after one discovers the wonder of creating database driven pages, one starts to become brave and before long you decide to create forms that gather at least part of their information from your database. These forms can be used to either submit new records or update existing ones. Of course a good developer should always control, where possible, the input received from the user. One good way to do this is via dropdowns or select boxes, whichever you prefer to call them.
When creating a form to insert a new record, adding a dropdown that is populated with choices from a table in your database is fairly straight forward. You place your cursor in the form at the spot you want to insert the dropdown. You then choose, Insert - Database - Results. In step 1, choose your connection. Step 2, choose the table you are pulling from. Step 3, choose the field or fields to be used in the dropdown.
Now, some may say, "Hold the phone." Why would I choose more than one field for the dropdown? Well, if your data is coming from a lookup table wherein you store a list of items and corresponding IDs for those items, it is good practice to insert the ID into a new record as opposed to the name of the item. So, if that is the case, in Step 3, you want to choose the item ID and its name. In step 4, choose the formatting option of "Drop-Down List - One record per item". Then, under "Display items from this field:", select the field you want the user to see. If you are using an ID field as the value, choose that field in the option labeled, "Submit values from this field". Finally, it step 5, you only need to click the "Finish" button.
In a form that you create from scratch, you can repeat the above process as many times as you want, creating several database-populated dropdowns.
Now, if you try the above process in an edit form created by the DRW, the only result you will get is an error message. In my case it looked like this:
Syntax error
/PPS/Team304/_fpclass/fpdblib.inc, line 3
Function FP_FieldVal(rs, fldname)
The reason? You can't put a database results region within another database results region. However, before you step out onto the 20th story balcony, hope is not lost. With a little pure ASP, you can add as many dropdowns to your edit form as you added to your submit form. In fact, you can save each of your dropdowns as its own file and reuse them throughout your application.
NOTE: This example assumes an Access database stored in the fpdb folder. However, the code provided would work with many setups if modified accordingly.
Here are two examples of using ASP to create a dropdown:
This first example pulls data from one database field. I have assumed for sake of this example that the data in the field can repeat, so have used DISTINCT in my SQL statement. First, the code to get the data needed for the dropdown. The code you may need to change for your application is in green.
<%
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("fpdb/DatabaseName.mdb")
conntemp.open myDSN
mySQL = "SELECT DISTINCT FieldName FROM TableName"
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
arrFieldName=myRS.GetRows()
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%>
The magic here is performed by this line:
arrFieldName=myRS.GetRows()
The GetRows method takes the recordset and dumps it into an array. You ask, "So what?" Well, the value of putting a recordset into an array is that 1) it makes it much easier to display the data and 2) it allows you to close the connection before writing out the data.
So, now that you have your data, how do you make your dropdown? Here is the code:
<select size = "1" name= "MySelect">
<option>Select One</option>
<%
For i = 0 to Ubound(arrFieldName, 2)
Response.write("<option value="""& arrFieldName(0,i) &"""")
If arrFieldName(0,i)=fp_rs("FieldName") Then Response.Write(" Selected") End If
Response.write(">"&arrFieldName(0,i)&"</option>")
Next
%>
</select>
What the above code does is loop through each item of the array and write it between <option> tags so that the items appear as choices in your dropdown.
Inevitably, you are going to ask, "How do I have the dropdown load with the current database value selected." Well, I beat you to the punch. That task is performed by this line:
If arrFieldName(0,i)=fp_rs("FieldName") Then Response.Write(" Selected") End If
That line tells the code as it loops through the array to mark as "selected" the value in the array that matches the value in the database for the record being edited.
So here is the complete code:
<%
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("fpdb/DatabaseName.mdb")
conntemp.open myDSN
mySQL = "SELECT DISTINCT FieldName FROM TableName"
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
arrFieldName=myRS.GetRows()
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%>
<select size = "1" name= "MySelect">
<option>Select One</option>
<%
For i = 0 to Ubound(arrFieldName,2)
Response.write("<option value="""&arrFieldName(0,i)&"""")
'If arrFieldName(0,i)=fp_rs("FieldName") Then Response.Write(" Selected")End If
Response.write(">"&arrFieldName(0,i)&"</option>")
Next
%>
</select>
You can save the code as a separate .ASP page and then call it as an include in any form you may need it. For example, suppose you save the page as DropDown1.asp. In Normal View of the page that holds your Edit form, place the cursor where you want the dropdown. Then switch to HTML view and insert this:
<!--#include file="DropDown1.asp"-->
That's all there is to it.
Now, if you are going to pull data form a lookup column and you want to display one value, but insert another, we must make a few changes to the code. First, we must include both fields in our SQL Statement. So this:
mySQL = "SELECT DISTINCT FieldName FROM TableName"
becomes:
mySQL = "SELECT FieldName1, FieldName2 FROM TableName"
Note, I have removed DISTINCT as there should be no duplicate records in a lookup column. Also, I am assuming that FieldName1 is your ID field and FieldName2 is the field you want to display in the dropdown.
The next change is in the way we display the information. It may help to know a little about what an array is and how it works. An array is similar to a database table. In our first example, we pulled the values from only one field of the table. Imagine those values in a one-column table. In arrays, columns and rows are numbered beginning with 0. So, in this line, Response.write("<option value="&arrFieldName(0, 8)&""), we are telling the code to write the value that is in the first column, 9th row of our one-column table.
Now we are pulling values from two fields, so our values will be stored in a two-column, multi-row table. So, since we are displaying the value from one field, but inserting the value from another, we need to make a small change in the code that renders the dropdown.
We change "& FieldName (0,i)&" to "& FieldName (1,i)&".
So here is the complete code for displaying a dropdown that shows one value, but inserts another:
<%
Set conntemp=Server.CreateObject("ADODB.Connection")
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("fpdb/Review304.mdb")
conntemp.open myDSN
mySQL = "SELECT FieldName1, FieldName2 FROM Employees"
conntemp.execute mySQL
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, conntemp, 0, 1
arrFieldName=myRS.GetRows()
myRS.Close
Set myRS = nothing
conntemp.Close
Set conntemp = nothing
%>
<select size = "1" name= "MySelect">
<option>Select One</option>
<%
For i = 0 to Ubound(arrFieldName,2)
Response.write("<option value="""&arrFieldName(0,i)&"""")
'If arrFieldName(0,i)=fp_rs("FieldName1") Then Response.Write(" Selected")End If
Response.write(">"&arrFieldName(1,i)&"</option>")
Next
%>
</select>
That is the end of the story. Happy coding!
< Message edited by BeTheBall -- 4/25/2006 20:38:08 >
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
|
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 9/22/2004 11:24:00
Couple "watch-outs" about this.
I've had many a long debugging session before figuring out this little ditty. When you do the comparison of values to decide whether a Select Value should be selected
quote:
If arrFieldName(0,i)=fp_rs("FieldName") Then Response.Write(" Selected") End If
you may want to trim the values like this:
If trim(arrFieldName(0,i))=trim(fp_rs("FieldName")) Then Response.Write(" Selected") End If
I can't tell you how many times I got burned by that before I figured it out.
Secondly, I'm not a big fan of using #includes in the middle of a page - like to display a drop down. However, the .asp page you've created would make a great function, and could be genericized fairly simply as to be re-usable anywhere, on any site.
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
fredecd
Posts: 312
Joined: 12/3/2003
From: Louisiana, USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 4/19/2005 10:11:48
Duane,
Thanks for this tip. I got this working for displaying rows or records from the array.
Question: Do you know if there is a way to do a record count within a record set using an array?
I have posted this problem as another message in the forum. I am trying to count database records on specific days in a calendar, and I was hoping to use this tip as part of the solution, but I am having trouble getting it working.
Any ideas?
_____________________________
Chris
http://www.sightbysite.net
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 4/19/2005 10:24:05
Really, you have two choices here:
1. Loop through the array and count using a variable
2. Do another database query using the COUNT() syntax and HAVING, or WHERE
SELECT COUNT(recordID) WHERE DateOfThing < "&now()&" AND DateOfThing > "&now()-180&";"
For my money, the second database is probably my preference, only because I don't have to embed any logic in a looping function (something I like to avoid), but depending on the size of your records, looping may be less costly in system resources.
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
JohnH
Posts: 43
Joined: 12/1/2004
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 6/24/2005 5:09:29
This is exactly what I am looking for. The only problem I have is using this code to connect to a SQL 2000 database. Can anyone help.
Many thanks,
John
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 6/24/2005 7:17:34
These folks have never steared me wrong!
w3schools
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
JohnH
Posts: 43
Joined: 12/1/2004
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 6/24/2005 9:07:38
Thanks for the reply Skrile. I have taken a look at the w3schools web site and it gives examples that relate to Access MDB files. I am a bit of an ASP thicky so any help with configuring the above code to work with SQL Server 2000 would be gratefully appreciated.
Thanks,
John
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 6/24/2005 9:25:18
Well, there are tons of examples out on the web how to connect to a database with well-commented lines etc...but here are the basics(all in <%%> of course):
Create your database object and Record set:
Set grConn = Server.CreateObject("ADODB.Connection")
set grRS = Server.CreateObject("ADODB.Recordset")
This will tell your ADODB object where your database is. Keep it relative using the Server.MapPath. That way, your application is "portable".
grConn.ConnectionString ="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("somedatabase.mdb")
Then set up your SQL string:
strSQL = "SELECT COUNT(recordID) AS RecordCount WHERE DateOfThing <"&date()&" AND DateOfThing > "&date() - 180&";"
Finally, put it all together and open up your connection with your sql string:
grRS.Open strSQL, grConn
IF NOT grRS.BOF then
myCount = grRS("RecordCount")
END IF
grRS.close
grConn.close
This will give you a variable called myCount holding the count you are looking for. There are lots of other ways to do this, and I left out a bunch of DIM statements and such, but in essences this is all you need. Here it is all put together:
Set grConn = Server.CreateObject("ADODB.Connection")
grConn.connectionString ="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("somedatabase.mdb")
set grRS = Server.CreateObject("ADODB.Recordset")
grRS.Open strSQL, grConn
IF NOT grRS.BOF then
myCount = grRS("RecordCount")
END IF
grRS.close
grConn.close
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 6/24/2005 9:28:34
This is a nice summary too:
Database connection tutorial
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
JohnH
Posts: 43
Joined: 12/1/2004
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 6/24/2005 9:32:17
Thanks again Steve. Only thing is that the example you have given me is also a MDB file, I am trying to connect to a SQL 2000 database called IntranetDB. Could you give me an example for a SQL database as opposed to a MDF file.
I would be very grateful.
Regards,
John
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 6/24/2005 9:36:07
Whoops...sorry...here is a SQL connection string:
grConn.ConnectionString = "Provider=SQLOLEDB; Server=your server name; Database=IntranetDB; User Id=someusername; Password=somepassword"
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 6/24/2005 9:40:42
Here are some prime examples and a little builder for you:
connection strings
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
dezeptus
Posts: 11
Joined: 9/8/2005
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 9/9/2005 12:05:33
With this example (dropdown asp page), on the editor its giving me a data type mismatch when I submit it. What am I doing wrong?
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 9/9/2005 12:17:10
Can you post your code?
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
dezeptus
Posts: 11
Joined: 9/8/2005
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 9/9/2005 12:46:44
Actually.. I got passed that.. that was my mistake. Can I explain to you what I'm trying to do?
I have Table A and Table B. Table B has a field which looks up a value in Table A. So, I did the dropdown asp using Table A. Offcourse, thats why I was getting the datamismatch cuz I was trying to update Table B field B with that info. So now, I changed it using Table B Field B info. Now it works, however it only brings up values in Table B.Field B that have been used before... example...
Table A Field A (this is where Table B.Field B looksup)
Item 1
Item 2
Item 3
Item 4
Table B Field B has records that contain Item 1, Item 2, and Item 3.
So on my dropdown only those Items appear. How can I make it show all the items in Table A Field A?
Also, table A had a fieldID and then the field name... but in the results and editor it kept coming up with the fieldid instead of actually showing the name. Nothing worked until I deleted fieldID and made field the primamy key. Work around?
Thanks for all your help in advance.
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 9/9/2005 12:48:14
Oh boy. This is a little too abstract. Can you post your code for the page?
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
dezeptus
Posts: 11
Joined: 9/8/2005
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 9/9/2005 13:07:06
This is the editor code...
<!--#include File='Login_Check.asp'-->
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Results -- Edit</title>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
</head>
<body bgcolor="#FFFFFF">
<!--webbot bot="PurpleText" PREVIEW="-Important- If you modify this Database Results region using the Database Results Wizard, then your Database Editor will no longer work. If you accidentally open the Database Results Wizard, simply click Cancel to exit without regenerating the Database Results region." -->
<!--webbot bot="DatabaseRegionStart" s-columnnames="Key,Type,CSR,Branch,ClientCode,CaseNumber,Quarter,Year,Run,Billable,Amount,CombinedRuns,RunsNeeded,Delivery,ManagerApproval" s-columntypes="3,202,202,202,202,202,202,202,202,202,202,202,202,202,202" s-dataconnection="controlrequests" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="FALSE" i-ListFormat="5" b-makeform="FALSE" s-RecordSource="Results" s-displaycolumns="Key,Type,CSR,Branch,ClientCode,CaseNumber,Quarter,Year,Run,Billable,Amount,CombinedRuns,RunsNeeded,Delivery,ManagerApproval" s-criteria="{Key} EQ {Key} +" s-order s-sql="SELECT * FROM Results WHERE Key = ::Key::" b-procedure="FALSE" clientside SuggestedExt="asp" s-DefaultFields="Key=0" s-NoRecordsFound="No records returned." i-MaxRecords="1" i-GroupSize="0" u-dblib="../../../_fpclass/fpdblib.inc" u-dbrgn1="../../../_fpclass/fpdbrgn1.inc" u-dbrgn2="../../../_fpclass/fpdbrgn2.inc" Tag="BODY" BOTID="0" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00" align="left"><font color="#000000">This is the start of a Database Results region.</font></td></tr></table>" startspan --><!--#include file="../../../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM Results WHERE Key = ::Key::"
fp_sDefault="Key=0"
fp_sNoRecords="No records returned."
fp_sDataConn="controlrequests"
fp_iMaxRecords=1
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_iDisplayCols=15
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-CheckSum="46872" --> <form METHOD="POST" action="update.asp" onsubmit="return FrontPage_Form1_Validator(this)" name="FrontPage_Form1">
<table BORDER=0>
<tr>
<td><b><font size="2">Type:</font></b></td>
<td><select size="1" name="Type"> <option <% if "Option 1" = FP_Field(fp_rs,"Type") then %> selected <% end if %> value="Option 1">Option 1</option>
<option <% if "Option 2" = FP_Field(fp_rs,"Type") then %> selected <% end if %> value="Option 2">Option 2</option>
<option <% if "Option 3" = FP_Field(fp_rs,"Type") then %> selected <% end if %> value="Option 3">Option 3</option>
<option <% if "Option 4" = FP_Field(fp_rs,"Type") then %> selected <% end if %> value="Option 4">Option 4</option>
<option <% if "Option 5" = FP_Field(fp_rs,"Type") then %> selected <% end if %> value="Option 5">Option 5</option>
<option <% if "Option 6" = FP_Field(fp_rs,"Type") then %> selected <% end if %> value="Option 6">Option 6</option>
</select>
</td>
</tr>
<tr>
<td><b><font size="2">CSR:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="CSR" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input name="CSR" size="64" value="<%=FP_FieldHTML(fp_rs,"CSR")%>" maxlength="255"></td>
</tr>
<tr>
<td><b><font size="2">Branch:</font></b></td>
<td><!--#include file="DropDown1.asp"-->
</td>
</tr>
<tr>
<td><b><font size="2">ClientCode:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="ClientCode" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input type="TEXT" name="ClientCode" size="64" value="<%=FP_FieldHTML(fp_rs,"ClientCode")%>" maxlength="255"></td>
</tr>
<tr>
<td><b><font size="2">CaseNumber:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="CaseNumber" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input type="TEXT" name="CaseNumber" size="64" value="<%=FP_FieldHTML(fp_rs,"CaseNumber")%>" maxlength="255"></td>
</tr>
<tr>
<td><b><font size="2">Quarter:</font></b></td>
<td><select size="1" name="Quarter"> <option <% if "Option 1" = FP_Field(fp_rs,"Quarter") then %> selected <% end if %> value="Option 1">Option 1</option>
<option <% if "Option 2" = FP_Field(fp_rs,"Quarter") then %> selected <% end if %> value="Option 2">Option 2</option>
<option <% if "Option 3" = FP_Field(fp_rs,"Quarter") then %> selected <% end if %> value="Option 3">Option 3</option>
<option <% if "Option 4" = FP_Field(fp_rs,"Quarter") then %> selected <% end if %> value="Option 4">Option 4</option>
</select>
</td>
</tr>
<tr>
<td><b><font size="2">Year:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="Year" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input type="TEXT" name="Year" size="64" value="<%=FP_FieldHTML(fp_rs,"Year")%>" maxlength="255"></td>
</tr>
<tr>
<td><b><font size="2">Run:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="Run" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input name="Run" size="64" value="<%=FP_FieldHTML(fp_rs,"Run")%>" maxlength="255"></td>
</tr>
<tr>
<td><b><font size="2">Billable:</font></b></td>
<td><select size="1" name="Billable"> <option <% if "Option 1" = FP_Field(fp_rs,"Billable") then %> selected <% end if %> value="Option 1">Option 1</option>
<option <% if "Option 2" = FP_Field(fp_rs,"Billable") then %> selected <% end if %> value="Option 2">Option 2</option>
</select>
</td>
</tr>
<tr>
<td><b><font size="2">Amount:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="Amount" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input type="TEXT" name="Amount" size="64" value="<%=FP_FieldHTML(fp_rs,"Amount")%>" maxlength="255"></td>
</tr>
<tr>
<td><b><font size="2">CombinedRuns:</font></b></td>
<td><select size="1" name="CombinedRuns"> <option <% if "Option 1" = FP_Field(fp_rs,"CombinedRuns") then %> selected <% end if %> value="Option 1">Option 1</option>
<option <% if "Option 2" = FP_Field(fp_rs,"CombinedRuns") then %> selected <% end if %> value="Option 2">Option 2</option>
</select>
</td>
</tr>
<tr>
<td><b><font size="2">RunsNeeded:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="RunsNeeded" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input type="TEXT" name="RunsNeeded" size="64" value="<%=FP_FieldHTML(fp_rs,"RunsNeeded")%>" maxlength="255"></td>
</tr>
<tr>
<td><b><font size="2">Delivery:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="Delivery" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input type="TEXT" name="Delivery" size="64" value="<%=FP_FieldHTML(fp_rs,"Delivery")%>" maxlength="255"></td>
</tr>
<tr>
<td><b><font size="2">ManagerApproval:</font></b></td>
<td> <!--webbot bot="Validation" s-display-name="ManagerApproval" s-data-type="String" b-value-required="False" i-maximum-length="255" --><input type="TEXT" name="ManagerApproval" size="64" value="<%=FP_FieldHTML(fp_rs,"ManagerApproval")%>" maxlength="255"></td>
</tr>
<tr>
<td colspan="2"><input type="submit" value=" OK "><input type="reset" value=" Reset "></td>
</tr>
</table>
<input type="hidden" name="Key" value="<%=FP_FieldHTML(fp_rs,"Key")%>">
</form>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="../../../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside Tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00" align="left"><font color="#000000">This is the end of a Database Results region.</font></td></tr></table>" startspan --><!--#include file="../../../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-CheckSum="9297" -->
</body>
</html>
This is the dropdown asp code...
<%
Set fp_conn = Server.CreateObject("ADODB.Connection")
fp_conn.Open Application("controlrequests_ConnectionString")
mySQL = "SELECT DISTINCT Branch FROM Results"
fp_conn.execute mySQL
Set myRS = Server.CreateObject("ADODB.Recordset")
myRS.Open mySQL, fp_conn, 0, 1
arrBranch=myRS.GetRows()
myRS.Close
Set myRS = nothing
fp_conn.Close
Set fp_conn = nothing
%>
<select size = "1" name= "Branch">
<%
For i = 0 to Ubound(arrBranch,2)
Response.write("<option value="""&arrBranch(0,i)&"""")
If trim(arrBranch(0,i))=trim(fp_rs("Branch")) Then Response.Write("Selected") End If
Response.write(">"&arrBranch(0,i)&"</option>")
Next
%>
</select>
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 9/9/2005 13:44:20
OK, well first things first. Looks like your form is using a FrontPage database Result Wizard. While this is good for basic forms, when you want multiple drop downs populated by database calls, this will be a problem for you. Might I suggest you get rid of the DRW all together and rely solely on the dbGetRows function to get all your data.
I'm a big fan of creating Functions and calling them rather than stacking Include files. So, if I were doing this I would handle it something like this:
First, put my DbGetRows() function at the top of your page, or better still, save it to a file called something like dbConnection.asp and include it on your form page. There are many benefits of the code below, but I'll save that discussion for another time.
Function dbGetRows(ByVal SQLStr)
'variable declarations:
dim grConn, grRS, grArray
If SQLStr&""<>"" then
'open the database
Set grConn = Server.CreateObject("ADODB.Connection")
grConn.ConnectionString = gstrConnectString
grConn.Open
set grRS = Server.CreateObject("ADODB.Recordset")
'#####this was commented out for performance reasons. Because we are using GetRows, we don't need to move around the record
'#####grRS.CursorType = 3 'Static
on error resume next
grRS.Open SQLStr, grConn
if grConn.Errors.Count > 0 then
response.clear
response.write "<b>Error in GetRows</b><br/>"
response.write "<b>SQL statement:</b> " & strSQL & "<br>"
'display the common error messages
For Each unerror In grConn.Errors
response.write "<li>" & unerror.description & "</li>"
Next
response.end
end if
'get the rows and assign them to the return array
if ((NOT grRS.bof) AND (NOT grRS.eof)) then grArray = grRS.GetRows() else grArray = ""
'close the database
If (grRS.state <> 0) Then grRS.close
If (grConn.state <> 0) Then grConn.close
If (isObject(grRS)) Then Set grRS = Nothing
If (isObject(grConn)) Then Set grConn = Nothing
'return the array
dbGetRows = grArray
End If
End Function
It's also helpful to have an include the defines your connection string. Notice that the above function relies on an external variable called gstrConnectString. You may want to define your connection line in that dbConnection.asp file as well. I do, and it is generally a good idea to do that in one place in your application. So, you will need another line like this:
gstrConnectString = ""Provider=SQLOLEDB; Server=" & dbServerName & "; Database=YourDatabaseName; User Id=YourUserName; Password=YourPasspword"
Now you have done the hardest part. You can now go into the code of your form page and create a two-dimensional array holding the records you are looking for with a pretty simple line of code:
DIM formArray()
DIM strSQL
DIM KeyVariable
KeyVariable = Request("KeyVariable")
strSQL = "SELECT * FROM Results WHERE Key = "&KeyVariable
formArray = dbGetRows(strSQL)
set strSQL = nothing
Form array is now going to be a two dimensional array that holds the record associated with the matching row of the Results table.
The beauty of using this approach is you have effectively separated your business logic from your HTML - a generally accepted good idea. Of course, it's not practical to separate all .asp out of the middle of the page, but things like records and database activity really should never be done in the middle of a page.
Now, when referencing each value for your form, you can work with the array variable rather than the fp database object.
I don't know what is in your Results table, but let's say that table has the following columns that are in this order in your db design:
Type
Quarter
CSR
ClientCode
The first number in a two dimensional array refers to the "column" of your database
Column 0 = Type
Column 1 = Quarter
etc.
The second number in a two dimensional array referes to the "row" of your database. Since you are calling for only one record (matching the KeyID), your array will only have one row.
Row 0 = record associated with KeyID
So here's what you would expect if you wrote the following lines of code:
response.write formArray(0,0) = the Type would be displayed
response.write formArray(1,0) = the Quarter would be displayed
response.write formArray(2,0) = the CSR would be displayed
response.write formArray(3,0) = the ClientCode would be displayed
Big watchout. You may not pass a KeyID, or find a matching record. You have to watch out for these two situations, or your page will bomb on you. To catch the first situation (no keyID is passed) I use the following little function.
function ProcessVal(val,defaultval)
'this is used to grab request variables
DIM tempVal
tempVal = request(val)
If tempVal&""="" then
tempVal = defaultval
End If
ProcessVal = tempVal
end function
What this does is take a variable name and look for it in either a submitted form, querystring or cookie. If it doesn't find it, it will set the variable to some default value you choose. So, instead of:
KeyVariable = Request("KeyVariable")
you would use:
KeyVariable = ProcessVal("KeyVariable",0)
Now your formArray getRows will not crash for lack of an ID value.
Next little trick is to make sure that even if you did pass an ID value that there was a matching row, thus an array was created. The easiest logic to trap this condition would be:
If isArray(formArray) then
(draw your form, input fields, etc here)
Else
response.write "I can't write the form. There is no matching record."
End if
Finally, your form. An example of a couple fields would be:
<tr>
<td><b><font size="2">Type:</font></b></td>
<td>
<select name=Type>
<option value="Option 1" <%If trim(formArray(0,0)) = "Option 1" then response.write "selected"%>>Option 1
<option value="Option 2" <%If trim(formArray(0,0)) = "Option 2" then response.write "selected"%>>Option 2
<option value="Option 3" <%If trim(formArray(0,0)) = "Option 3" then response.write "selected"%>>Option 3
<option value="Option 4" <%If trim(formArray(0,0)) = "Option 4" then response.write "selected"%>>Option 4
</select>
</td>
</tr>
<tr>
<td><b><font size="2">CSR:</font></b></td>
<td><input type=text><%=formArray(2,0)%></td>
</tr>
I know there is a lot here...but in my experience using FP DRW, I've learned less is best. In fact, I NEVER use them anymore.
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
dezeptus
Posts: 11
Joined: 9/8/2005
Status: offline
|
RE: Inserting a Database-Populated Dropdown in a FP Dat... - 9/9/2005 13:58:43
Cool.. Thanks for the heads up in regards to the DRW... I will try this and let you know how it goes for me.. Again, thanks for all your help! It is truly appreciated.
Kenny
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
