|
| |
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
 |
Database Error in IF THEN statement - 1/9/2007 14:14:41
I have a database result created by the database result wizard, and I am trying to place it in an IF THEN statement. Unfortunately, the code errors whenever it is in the IF THEN statement. Here is the code:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>SuiteNo</title>
<meta name="Microsoft Border" content="tb, default">
</head>
<body background="Images/new%20homepage%20copy.jpg">
<%arrAccess = Split(Session("Accesslevel")&"",",")
If isArray(arrAccess) then
For i=0 to uBound(arrAccess)
If trim(arrAccess(i)) = "1" then
%>
<font color=white>ACCESS 1</font>
<table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>SuiteNo</b></th>
<th ALIGN="LEFT"><b>Tenant</b></th>
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart" s-columnnames="SuiteNo,Tenant" s-columntypes="202,202" s-dataconnection="Database2" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="OCSCurrentTenantsQuery" s-displaycolumns="SuiteNo,Tenant" s-criteria s-order s-sql="SELECT * FROM OCSCurrentTenantsQuery" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" 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 OCSCurrentTenantsQuery"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&SuiteNo=202&Tenant=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="2422" --><tr>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="SuiteNo,Tenant" s-column="SuiteNo" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>SuiteNo<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"SuiteNo")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="15080" --></td>
<td>
<!--webbot bot="DatabaseResultColumn" s-columnnames="SuiteNo,Tenant" s-column="Tenant" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1"><<</font>Tenant<font size="-1">>></font>" startspan --><%=FP_FieldVal(fp_rs,"Tenant")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="15277" --></td>
</tr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr>" startspan --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
</table>
<p>
<% End if
If trim(arrAccess(i)) = "2" then
%>
</p>
<p><font color=red>ACCESS 2</font>
<% End if
If trim(arrAccess(i)) = "3" then
%>
</p>
<p><font color=blue>ACCESS 3</font>
<% End if
Next
End if
%>
</p>
</body></html>
I'm not sure if I only need to move the includes out of the IF THEN or if I can even put the results in that kind of statement. Any suggestions are greatly appreciated.
Ossus
|
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Database Error in IF THEN statement - 1/9/2007 14:22:50
quote:
If isArray(arrAccess) then
For i=0 to uBound(arrAccess)
If trim(arrAccess(i)) = "1" then
Which IF..THEN? ...and what is the error??
AFAIK you can nest DRW's inside an IF..THEN.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/9/2007 14:28:01
This is the statement:
If trim(arrAccess(i)) = "1" then
After that comes all the trash from the database result wizard.
The error only hits me when the code is inside the IF THEN statement.
Ossus
** I also tried the same code, but on the spooky Diet. That did not fix the problem, however it is easier to look at:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>SuiteNo</title>
<meta name="Microsoft Border" content="tb, default">
</head>
<body background="Images/new%20homepage%20copy.jpg">
<%arrAccess = Split(Session("Accesslevel")&"",",")
If isArray(arrAccess) then
For i=0 to uBound(arrAccess)
If trim(arrAccess(i)) = "1" then
%>
<font color=white>ACCESS 1</font>
<table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT">
<font color="#FFFFFF" face="Perpetua" style="font-size: 14pt"><b>SuiteNo</b></font></th>
<th ALIGN="LEFT">
<font color="#FFFFFF" face="Perpetua" style="font-size: 14pt"><b>Tenant</b></font></th>
</tr>
</thead>
<tbody>
<!--#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 OCSCurrentTenantsQuery"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&SuiteNo=202&Tenant=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<font color="#FFFFFF" face="Perpetua" style="font-size: 14pt">
<%=FP_FieldVal(fp_rs,"SuiteNo")%></font></td>
<td>
<font color="#FFFFFF" face="Perpetua" style="font-size: 14pt">
<%=FP_FieldVal(fp_rs,"Tenant")%></font></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<% End if
If trim(arrAccess(i)) = "2" then
%>
</p>
<p><font color=red>ACCESS 2</font>
<% End if
If trim(arrAccess(i)) = "3" then
%>
</p>
<p><font color=blue>ACCESS 3</font>
<% End if
Next
End if
%>
</p>
</body></html>
Again, this is the problem statement:
If trim(arrAccess(i)) = "1" then
%>
< Message edited by Ossus -- 1/9/2007 14:36:00 >
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/9/2007 14:41:50
Microsoft VBScript compilation error '800a03ea'
Syntax error
/_fpclass/fpdblib.inc, line 3
Sub FP_SetLocaleForPage
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/9/2007 14:55:48
quote:
<!--#include file="_fpclass/fpdblib.inc"-->
No, sorry it doesn't.
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/9/2007 15:09:23
No, it does not.
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/11/2007 12:14:20
That was actually the first thing I did. I made a new page and used the DRW and it worked fine.
In fact, when I was initially troubleshooting this, I took the code in that page, and simply moved the DRW code outside the IF THEN statement and it worked perfectly on that page.
Ossus
**
I also tried copying the <!--#include file="_fpclass/fpdblib.inc"--> include at the beginning of the document as well as where it shows up in the DRW code. I was afraid the include wasn't being called correctly because of the IF statement, but that didn't fix it either.
< Message edited by Ossus -- 1/11/2007 12:20:47 >
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/16/2007 12:53:34
I still haven't been able to figure this out. Anyone who has any ideas, I would appreciate it greatly. I'm absolutely stuck.
Ossus
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/16/2007 14:07:57
Yes, it does work.
The page displays correctly and does not show the previous error.
Ossus
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/16/2007 17:00:17
Nope, as soon as the IF THEN statement gets inserted, I get this:
Microsoft VBScript compilation error '800a03ea'
Syntax error
/_fpclass/fpdblib.inc, line 3
Sub FP_SetLocaleForPage
^
It's maddening that a simple IF THEN would break the Data Results.
Ossus
| |
|
|
|
Spooky
Posts: 26617
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: Database Error in IF THEN statement - 1/16/2007 22:18:01
Does this fail too?
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>SuiteNo</title>
<meta name="Microsoft Border" content="tb, default">
</head>
<body background="Images/new%20homepage%20copy.jpg">
<%arrAccess = "," &replace(Session("Accesslevel")," ","")&","
If instr(arrAccess,",1,") > 0 then %>
<font color=white>ACCESS 1</font>
<table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>SuiteNo</b></th>
<th ALIGN="LEFT"><b>Tenant</b></th>
</tr>
</thead>
<tbody>
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT * FROM OCSCurrentTenantsQuery"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&SuiteNo=202&Tenant=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<%=FP_FieldVal(fp_rs,"SuiteNo")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Tenant")%></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<p>
</p>
<%else%>
Not access level 1
<%end if%>
</p>
</body></html>
_____________________________
If you arent part of the solution, then there is good money to be made prolonging the problem
§þ k¥
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/18/2007 14:11:04
Actually, that last one works. But what does it all mean? Was it the following IF statements? Was it the syntax of having multiple IF statements? Or was it populating the Array?
Now we just need to figure out how to actually populate the array.
Ossus
P.S. You are a genius. Thanks so much for figuring this out.
< Message edited by Ossus -- 1/18/2007 14:50:27 >
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 10:41:29
Sorry to butt in but....
I've long ago abandoned FP DRW's. I had a lot of trouble and bad experiences with them. I've gone to a classic asp code approach when selecting information from SQL (or Access for that matter) databases. In essence, I have an include file that defines my connection and does the data collecting. Then, on the page where I want the information, I call the function and populate an Array. Once I get that array, I do all my looping and presenting, etc.
I mention this not to dangle an abstract point in front of you, but to let you know this approach has been very successful for me. Ultimately, it takes A LOT of code OUT of the middle of my page and puts it rightly separate from my HTML.
If you are at all interested, I'd be happy to send you a small working sample of how I do all this.
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 11:41:14
Ah, what the heck...let's see if it will all go up here. The code below is in a file called dataconnection_sql.asp
<%
option explicit
dim errMsg 'when executing SQL, if there is a problem, I write the problem to this variable called errMsg
'much of the items at the top here can actually be housed in include files that you simply stick
'at the top of every page. For ease of packaging I have included everything on one page.
'_________________________________________________________________________________________________________________________
'------------I have this in a file called: inclue/dbconnection.asp-----------------
'This WILL NOT WORK UNIT YOU give values the variables below.
' [yourmachinename] the name of your computer (right click My Computer > Properties > Computer Name
' [yourproductionSQLdatabasename] the name of the Production SQL database **SERVER** you are connecting to
' [yourdatabasename] the name of the SQL database you are connecting to
' [youruserid] the User Name with read/write rights to your database
' [yourpassword] the password for that User Name.
dim yourmachinename, yourproductionSQLDatabase, yourDatabaseName, YourUserID, YourPassword
yourmachinename = ""
yourproductionSQLDatabase = ""
yourdatabasename = ""
youruserid = ""
yourpassword = ""
Dim gstrConnectString
dim serverName
dim dbServerName
'for reasons of testing, it is often helpful to connect to either a live database
'or a local database. The script below is really just examining the URL of the page
'and from that URL determining whether to connect to a test server (your own)
'or the live production server.
serverName = lcase (request.ServerVariables ("SERVER_NAME"))
if ((serverName = "localhost") or (serverName = yourmachinename)) then
dbServerName = "(local)"
else
dbServerName = yourproductionSQLDatabase
end if
gstrConnectString = "Provider=SQLOLEDB; Driver=SQL Server; Server=" & dbServerName & "; Database="&yourdatabasename&"; User Id="&youruserid&"; Password="&yourpassword
'_______________________________________________________________________________________________________________________________
'the function below could be put in another include if you like.
'This function does the GETTING of data and places the result in an array.
'If there are errors with the SQL command, a variable called errMsg (which is a page-level, not function-level variable) is populated.
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")
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> " & SQLstr & "<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
'now it is time to actually collect your data from the database. In essence what you will be doing
'is quickly opening a connection, grabbing your data and sticking it in a two-dimensional array
'then closing your connection. Then, when you have your array, you can do whatever you want,
'similar to looping through a recordset but without all the overhead of having an open db connection.
dim strSQL 'the string you will pass to your getrows function
'Of course, you will want to tailor this string to whatever you want to collect and your table names, etc.
strSQL = "Select * from Employee Where LastName LIKE 'Jones';"
If request("doit") then
dim EmployeeArray
EmployeeArray = dbGetRows(strSQL)
'that is it. If your query returns records, EmployeeArray will be an array, otherwise it will not be. That simple.
end if
dim i, z
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Database connection sample</title>
<style type="text/css">
body {font-family: Arial;}
</style>
</head>
<body>
<div>!! Before you can make this page run, you will need to view the script
at the top of the page and set up your server connection information !!
<br /><br />
<a href="dataconnection_sql.asp?doit=true">Test the Connection</a>
<br />
Once you set up your connection information, and your strSQL query has gathered your data, you will see it in a table below.
<br /><br />
</div>
<%If isArray(EmployeeArray) then %>
<table style="border-collapse:collapse;">
<%for i = 0 to ubound(EmployeeArray,2) %>
<tr>
<%for z= 0 to ubound(EmployeeArray,1) %>
<td style="border-top: solid 1px blue;border-left: solid 1px blue;border-right: solid 1px blue;border-bottom: solid 1px blue;"><%=EmployeeArray(z,i) %></td>
<%next %>
</tr>
<%next %>
</table>
<%else %>
No data to display. EmployeeArray is not an array.
<%end if %>
</body>
</html>
< Message edited by skrile -- 1/23/2007 11:48:27 >
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 13:08:05
Skrile,
I appreciate the help with the code. I know the FP DRW is terrible, however I am not proficient in asp. Its fairly rough for me to get through complex chunks of code.
The most important part is that I get that array above populated so I can set up access levels for my users.
Again, I really appreciate the help. If you have any other suggestions, I'm going to look at your code and see if i can't make sense of it and somehow incorporate it into a fix.
Ossus
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 13:08:38
Yeah, and taking this one step further, I've gone strictly with parameterized queries to avoid SQL injection attacks when building my SQL string in-line. My getRows_param function does this for me:
function dbGetRows_param(procName)
dim blnSuccess
blnSuccess = true
sConnect = gstrConnectString
Set objCommand = Server.CreateObject("ADODB.Command")
Set objRS = Server.CreateObject("ADODB.Recordset")
With objCommand
.CommandText = procName
.CommandType = adCmdStoredProc
.ActiveConnection = sConnect
on error resume next
set objRS = .Execute
If err<>0 then
errmsg = "error number " & err.number & ": " & err.Description
blnSuccess = false
end if
on error goto 0
End With
if (blnSuccess) then
if ((NOT objRS.bof) AND (NOT objRS.eof)) then
grArray = objRS.GetRows()
end if
objRS.Close
Set objCommand.ActiveConnection = Nothing
Set objCommand = Nothing
end if
dbGetRows_param = grArray
A call to this usually looks something like:
dim strSQL
strSQL = "usp_GetInfo('someparameter1', somenumberparameter)"
recordArray = dbGetRows_param(strSQL)
Of course, this will only work with Stored Procedures, but alas, I've seen the light with them as well and have since made them a habit I cannot do without.
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 13:09:35
What database are you using?
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 13:14:44
As per my clients request, I am using access.
A lot of this is hindered by:
1) My lack of knowledge of ASP
2) My client's (sometimes reasonable) desires
I'm kind of stuck trying to please him while doing things well.
Ossus
| |
|
|
|
Spooky
Posts: 26617
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 13:17:43
Back to the last working code - did you carry on with that?
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 13:24:50
I messed around with the last bit of code you gave me that worked. It is this code
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>SuiteNo</title>
<meta name="Microsoft Border" content="tb, default">
</head>
<body background="Images/new%20homepage%20copy.jpg">
<%arrAccess = "," &replace(Session("Accesslevel")," ","")&","
If instr(arrAccess,",1,") > 0 then %>
<font color=white>ACCESS 1</font>
<table width="100%" border="1">
<thead>
<tr>
<th ALIGN="LEFT"><b>SuiteNo</b></th>
<th ALIGN="LEFT"><b>Tenant</b></th>
</tr>
</thead>
<tbody>
<!--#include file="_fpclass/fpdblib.inc"-->
<%
fp_sQry="SELECT * FROM OCSCurrentTenantsQuery"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=2 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Database2"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&SuiteNo=202&Tenant=202&"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<tr>
<td>
<%=FP_FieldVal(fp_rs,"SuiteNo")%></td>
<td>
<%=FP_FieldVal(fp_rs,"Tenant")%></td>
</tr>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</tbody>
</table>
<p>
</p>
<%else%>
Not access level 1
<%end if%>
</p>
</body></html>
It does not error, and it shows me the first access level, but does not display any others. I think what is happening (you would know better than I) that the array is automatically created to check the first access level.
e.g. quote:
arrAccess = "," &replace(Session("Accesslevel")," ","")&","
If instr(arrAccess,",1,") > 0 then %>
I have tried adding so that it would work with the other levels to no avail.
I did something like this:
quote:
<%if instr(arrAccess,",2,") = 2 then %>
to try and have it check for access level 2.
Again, my paltry attempts at writing ASP are laughable.
Ossus
| |
|
|
|
skrile
Posts: 59
Joined: 1/28/2002
From: Novi MI USA
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 13:28:31
I completely understand.
When you catch your breath, you can play with this. It will hook you up to an Access database.
If you copy and paste this code into a new file, with a very simple revision, you can be hitting your database directly and getting an array back. Specifically, if you enter the relative path to your Access database around line 22 (change to something like yourproductionAccessDatabase = "database/mydatabase.mdb"and then change the actual SQL select string around line 112 (strSQL = "select * from something")
you will get an array.
<%
option explicit
dim errMsg 'when executing SQL, if there is a problem, I write the problem to this variable called errMsg
'much of the items at the top here can actually be housed in include files that you simply stick
'at the top of every page. For ease of packaging I have included everything on one page.
'_________________________________________________________________________________________________________________________
'------------I have this in a file called: inclue/dbconnection.asp-----------------
'This WILL NOT WORK UNIT YOU give values the variables below.
' [yourmachinename] the name of your computer (right click My Computer > Properties > Computer Name
' [yourproductionAccessdatabasename] the file location of your Access Database
' [yourdatabasename] the name of the SQL database you are connecting to
' [youruserid] the User Name with read/write rights to your database
' [yourpassword] the password for that User Name.
dim yourmachinename, yourproductionAccessDatabase, yourDatabaseName, YourUserID, YourPassword
yourmachinename = ""
yourproductionAccessDatabase = ""
yourdatabasename = ""
youruserid = ""
yourpassword = ""
Dim gstrConnectString
dim serverName
dim dbServerName
'for reasons of testing, it is often helpful to connect to either a live database
'or a local database. The script below is really just examining the URL of the page
'and from that URL determining whether to connect to a test server (your own)
'or the live production server.
serverName = lcase (request.ServerVariables ("SERVER_NAME"))
if ((serverName = "localhost") or (serverName = yourmachinename)) then
dbServerName = "(local)"
else
dbServerName = yourproductionAccessDatabase
end if
gstrConnectString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath(yourproductionAccessDatabase)'
'_______________________________________________________________________________________________________________________________
'the function below could be put in another include if you like.
'This function does the GETTING of data and places the result in an array.
'If there are errors with the SQL command, a variable called errMsg (which is a page-level, not function-level variable) is populated.
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")
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> " & SQLstr & "<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
'now it is time to actually collect your data from the database. In essence what you will be doing
'is quickly opening a connection, grabbing your data and sticking it in a two-dimensional array
'then closing your connection. Then, when you have your array, you can do whatever you want,
'similar to looping through a recordset but without all the overhead of having an open db connection.
dim strSQL 'the string you will pass to your getrows function
'Of course, you will want to tailor this string to whatever you want to collect and your table names, etc.
strSQL = "Select * from Family;"
If request("doit") then
dim EmployeeArray
EmployeeArray = dbGetRows(strSQL)
'that is it. If your query returns records, EmployeeArray will be an array, otherwise it will not be. That simple.
end if
dim i, z
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Database connection sample</title>
<style type="text/css">
body {font-family: Arial;}
</style>
</head>
<body>
<div>!! Before you can make this page run, you will need to view the script
at the top of the page and set up your server connection information !!
<br /><br />
<a href="test.asp?doit=true">Test the Connection</a>
<br />
Once you set up your connection information, and your strSQL query has gathered your data, you will see it in a table below.
<br /><br />
</div>
<%If isArray(EmployeeArray) then %>
<table style="border-collapse:collapse;">
<%for i = 0 to ubound(EmployeeArray,2) %>
<tr>
<%for z= 0 to ubound(EmployeeArray,1) %>
<td style="border-top: solid 1px blue;border-left: solid 1px blue;border-right: solid 1px blue;border-bottom: solid 1px blue;"><%=EmployeeArray(z,i) %></td>
<%next %>
</tr>
<%next %>
</table>
<%else %>
No data to display. EmployeeArray is not an array.
<%end if %>
</body>
</html>
_____________________________
Steve Krile
Ergonomist posing as web developer - or visa versa
| |
|
|
|
Ossus
Posts: 56
Joined: 1/4/2007
From: Chicago, IL
Status: offline
|
RE: Database Error in IF THEN statement - 1/23/2007 13:39:09
That did it Spooky,
It was my = sign that threw everything off.
I really appreciate all the help that you and the rest of the members have provided.
Skrile, I still plan to pound on your code so I can learn how to do this better.
Thanks again Spooky, everything works. You really saved me on this one.
Landon DePasquale
A.K.A. Ossus
| |
|
 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 
I've long ago abandoned FP DRW's 
