|
The ASPSmith |
|
|
| D: | Domains | Authors.aspalliance.com | Stevesmith | Articles | ADO Code Example |
|
ADO Code Example [Example]
Many new ASP developers run into initial problems with getting their ASP pages to work with their local database. Since there are several different ways to access a database using ADO, and many small details that can go wrong along the way, I decided this would make a nice tool to share with other developers. This page was actually developed by a Microsoft support line technician to help analyze user errors. What he would do is have the user copy this file into their root web and attempt to connect to a DSN. If it worked, then their other problem had to be with their code, since obviously ADO was working. If it didn't work, then he knew something was truly wrong with the system's setup. Apart from just being a nice sanity check for page development, the code in this script can be hacked to create simple and easy web-based reports. Unfortunately I do not yet have a DSN set up for this web account. When I do, I will post a sample page here to demonstrate how this script works. The complete source code for the page is listed below: 1 <% OPTION EXPLICIT %>2 <!-- #INCLUDE VIRTUAL="/stevesmith/include/articleformat.asp" --> 3 <% 4 Call ArticleHeader("ADO Test Example","","") 5 '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 6 '% File: ADOselect.asp 7 '% Author: Aaron L. Barth (MS) 8 '% Purpose: For testing ADO connectivity to any ODBC Datasource 9 '% Disclaimer: This code is to be used for sample purposes only 10 '% Microsoft does not gaurantee its functionality 11 '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 12 Dim dsn 13 Dim dbuser 14 Dim dbpass 15 Dim dbtable 16 Dim dbfield 17 Dim dbwhere 18 Dim rs 19 Dim conn 20 Dim sql 21 Dim i 22 23 if Request("REQUESTTYPE") <> "POST" then 24 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 25 ' % If the request does not contain REQUESTTYPE = "POST 26 ' % then display Form Page 27 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 28 29 dsn = Session("dsn") 30 dbuser = Session("dbuser") 31 dbpass = Session("dbpass") 32 dbtable = Session("dbtable") 33 dbfield = Session("dbfield") 34 dbwhere = Session("dbwhere") 35 %> 36 <form ACTION="adoselect.asp" method="POST"> 37 <table> 38 <tr><td><b>You are authenticated as: </td> 39 <td><font COLOR="GREEN"><% = Request.ServerVariables("LOGON_USER")%></td></tr> 40 <tr><td><b>Your IP Address is: </td> 41 <td><font COLOR="GREEN"><% = Request.ServerVariables("REMOTE_ADDR")%></td></tr> 42 <tr><td><b>System DSN:</td> 43 <td><input TYPE="TEXT" NAME="datasource" VALUE="<% = dsn %>"></td></tr> 44 <tr><td><b>Username:</td> 45 <td><input TYPE="TEXT" NAME="username" VALUE="<% = dbuser %>"></td></tr> 46 <tr><td><b>Password:</td> 47 <td><input TYPE="Password" NAME="password" VALUE="<% = dbpass %>"></td></tr> 48 <tr><td><b>Table:</td> 49 <td><input TYPE="TEXT" NAME="table" VALUE="<% = dbtable %>"></td></tr> 50 <tr><td><b><font COLOR="RED">WHERE</td> 51 <td></td></tr> 52 <tr><td><b>Field to Query:</td> 53 <td><input TYPE="TEXT" NAME="field" VALUE="<% = dbfield %>"></td></tr> 54 <tr><td><b>Value to Query:</td> 55 <td><input TYPE="TEXT" NAME="where" VALUE="<% = dbwhere %>"></td></tr> 56 </table> 57 <input TYPE="HIDDEN" NAME="REQUESTTYPE" VALUE="POST"> 58 <input TYPE="Submit" VALUE="Query Database"> 59 <hr> 60 </form> 61 <% 62 63 else 64 '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 65 '% Perform Query to Database 66 '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 67 68 69 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 70 ' % Request the datsource from the Previous Form 71 ' % Set the Session variable so we can retrieve the 72 ' % value for the next query 73 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 74 75 dsn = Request("datasource") 76 Session("dsn") = dsn 77 78 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 79 ' % Request the username from the Previous Form 80 ' % Set the Session variable so we can retrieve the 81 ' % value for the next query 82 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 83 84 dbuser = Request("username") 85 Session("dbuser") = dbuser 86 87 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 88 ' % Request the password from the Previous Form 89 ' % Set the Session variable so we can retrieve the 90 ' % value for the next query 91 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 92 93 dbpass = Request("password") 94 Session("dbpass") = dbpass 95 96 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 97 ' % Request the table from the Previous Form 98 ' % Set the Session variable so we can retrieve the 99 ' % value for the next query 100 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 101 102 dbtable = Request("table") 103 Session("dbtable") = dbtable 104 105 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 106 ' % Request the table from the Previous Form 107 ' % Set the Session variable so we can retrieve the 108 ' % value for the next query 109 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 110 111 dbfield = Request("field") 112 Session("dbfield") = dbfield 113 114 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 115 ' % Request the table from the Previous Form 116 ' % Set the Session variable so we can retrieve the 117 ' % value for the next query 118 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 119 120 dbwhere = Request("where") 121 Session("dbwhere") = dbwhere 122 123 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 124 ' % Check to see if any of the requested values are blank, IF they 125 ' % are, then inform the user which variables are blank ELSE 126 ' % Continue with the query 127 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 128 if dsn = "" OR dbuser = "" OR dbtable = "" then 129 130 Response.write "Error in SQL Statement:<BR>" 131 if dsn = "" then 132 Response.write "<FONT COLOR=RED>Missing System DSN</FONT><P>" 133 end if 134 if dbuser = "" then 135 Response.write "<FONT COLOR=RED>Missing Username</FONT><P>" 136 end if 137 if dbtable = "" then 138 Response.write "<FONT COLOR=RED>Missing Tablename</FONT><P>" 139 end if 140 Response.write "<FORM ACTION=adoselect.asp><INPUT TYPE=SUBMIT VALUE=ReQuery></FORM>" 141 else 142 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 143 ' % Create the Conn Object and open it 144 ' % with the supplied parameters 145 ' % System DSN, UserID, Password 146 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 147 148 Set Conn = Server.CreateObject("ADODB.Connection") 149 Set rs = Server.CreateObject("ADODB.RecordSet") 150 Conn.Open dsn, dbuser, dbpass 151 152 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 153 ' % Build the SQL Statement and assign it 154 ' % to the variable sql. Concatinating the dbtable and the SELECT 155 ' % statement 156 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 157 if dbfield = "" OR dbwhere ="" then 158 sql="SELECT * FROM " & dbtable 159 else 160 161 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 162 ' % IF dbfield and dbwhere are specified, then 163 ' % change the SQL statement to use the WHERE clause 164 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 165 ' 166 sql="SELECT * FROM " & dbtable 167 sql = sql & " WHERE " & dbfield 168 sql = sql & " LIKE '%" & dbwhere & "%'" 169 end if 170 171 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 172 ' % For Debugging, Echo the SQL Statement 173 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 174 Response.Write "<B><FONT SIZE=2 COLOR=BLUE>SQL STATEMENT: </B>" & sql & "<HR>" 175 176 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 177 ' % Open the RecordSet (RS) and pass it 178 ' % the connection (conn) and the SQL Statement (sql) 179 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 180 RS.Open sql, Conn 181 %> 182 183 <p> 184 <table BORDER="1"> 185 <tr> 186 <% 187 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 188 ' % Loop through Fields Names and print out the Field Names 189 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 190 191 For i = 0 to RS.Fields.Count - 1 192 %> 193 <td><b><% = RS(i).Name %></b></td> 194 <% Next %> 195 </tr> 196 <% 197 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 198 ' % Loop through rows, displaying each field 199 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 200 Do While Not RS.EOF 201 %> 202 <tr> 203 <% For i = 0 to RS.Fields.Count - 1 %> 204 <td VALIGN="TOP"><% = RS(i) %></td> 205 <% Next %> 206 </tr> 207 <% 208 RS.MoveNext 209 Loop 210 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 211 ' % Make sure to close the Result Set and the Connection object 212 ' %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% 213 RS.Close 214 Conn.Close 215 %> 216 </table> 217 218 <% 219 end if 220 end if 221 %> 222 <% 223 Call ArticleFooter() 224 %> |
|
|
|
|
|
|