|
skrile -> 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>
|
|
|
|
