Solved

Referencing a web.config SQL connection in classic asp...Can it be done?

Posted on 2008-10-16
9
908 Views
Last Modified: 2008-11-05
I want to get rid of all the Connection.asp files on the server, and was wondering if I can use the setting in the web.config file on my server used by the aspx pages for this purpose....

web.config...

<connectionStrings>
    <add name="Connect" connectionString="Provider=SQLNCLI; Server=servername ; Database=dbname;Uid=auser;Pwd=pass;"/>


to replace this in the asp files...
<!--#include file="Connection.asp"-->

connection.asp

<%

'This is the link to the client's database, change accordingly
Set db=Server.CreateObject("ADODB.Connection")
db.Open "DSN=dbname;UID=auser;PWD=pass"

'replace ??? with the client's subdomain below
subdomain="changethis"
'replace ??? with the client's folder , the register folder should be placed in the client's folder
filepath="changethis"
%>
0
Comment
Question by:theclassic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 9

Accepted Solution

by:
CCongdon earned 500 total points
ID: 22731908
Try this. The connection string is output in a variable named connStr. You can change this of course.
The XPath statement //add[@name='Connect' is formatted to the web config fragment you showed above. If you need to change it, the query is basically like this:
Find the first xml tag named add that is anywhere in the document with an attribute named 'name' which has a value of 'Connect'
The method selectSingleNode pulls the first occurence out of the document. Here's another example based on a web config for one of my sites:
connStr = xmlDoc.selectSingleNode("//add[@key='DBConn']").Attributes.getNamedItem("value").Text
This is for a web config that looks like this:
<configuration>
  <appSettings file="">
    <clear />
    <add key="EncryptKey" value="BJ123BJ123FF" />
    <add key="DBConn" value="data source=........." />
  </appSettings>
</configuration>

Dim xmlDoc
Dim connStr
Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.Async = False
xmlDoc.Load(Server.Mappath("web.config"))
If (xmlDoc.parseError.errorCode = 0) Then
  xmlLoad = True
  xmlDoc.setProperty "SelectionLanguage", "XPath"
  connStr = xmlDoc.selectSingleNode("//add[@name='Connect']").Attributes.getNamedItem("connectionString").Text
Else
  Response.Write("UNABLE TO FIND CONNECTION")
  Response.End()
End If

Open in new window

0
 
LVL 9

Expert Comment

by:CCongdon
ID: 22731941
Oh yeah, and in case the web.config is somewhere else besides in the same directory as the ASP page looking for the connection string change this line:
xmlDoc.Load(Server.Mappath("web.config"))

 Change "web.config" to a reference relative to the page that is calling the file.
For instance "..\web.config" if the web.config is in a parent directory.
0
 

Author Comment

by:theclassic
ID: 22732231
The web config is at the root - and this will work with classic asp?  The connection file is found in many subfolders below the root level, so that is helpful.  basically there is a connection.asp file, and then it is referenced by the tag in my post....I will try this and get back to you...Thanks!!!
0
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 9

Expert Comment

by:CCongdon
ID: 22732289
Yes, that script was designed to work in ASP. It is written in VBScript. JScript wouldn't be a hole lot different...
0
 

Author Comment

by:theclassic
ID: 22732300
What is the "Encrypt Key" Tag? Is that for the asp or for asp.net also?  sorry, begginner....
0
 
LVL 9

Expert Comment

by:CCongdon
ID: 22732350
You can ignore that piece, I was just showing an example of a web.config that one of my sites uses and how differences in the web.config would change the script if you needed to use the script on another site.
0
 

Author Comment

by:theclassic
ID: 22881293
CCongdon - I need this back in there somewhere - Server.CreateObject("ADODB.Connection") - when it was removed, it is causing application errors, particularly
ADODB.Recordset error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
0
 

Author Comment

by:theclassic
ID: 22881458
Nevermind, i got it -

<%
Dim xmlDoc
Dim connStr
Set xmlDoc = Server.CreateObject("Microsoft.XMLDOM")
xmlDoc.Async = False
xmlDoc.Load(Server.Mappath("web.config"))
If (xmlDoc.parseError.errorCode = 0) Then
  xmlLoad = True
  xmlDoc.setProperty "SelectionLanguage", "XPath"
  connStr = xmlDoc.selectSingleNode("//add[@name='ClientConnect']").Attributes.getNamedItem("connectionString").Text
  Set db=Server.CreateObject("ADODB.Connection")
  db.Open connStr
  Else
  Response.Write("UNABLE TO FIND CONNECTION")
  Response.End()
End If
%>
 
0
 
LVL 9

Expert Comment

by:CCongdon
ID: 22885589
Yeah, the code sample I gave you just grabbed the connection string from the web.config, but didn't do anything else.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Running classic asp applications under Windows Server 2008 R2 (x64) and IIS 7 is not as easy as one may think. It took me a while to figure it out while getting error 8002801d a few times. After you install the OS you will need to install the fol…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question