[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 939
  • Last Modified:

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

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
theclassic
Asked:
theclassic
  • 5
  • 4
1 Solution
 
CCongdonCommented:
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
 
CCongdonCommented:
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
 
theclassicAuthor Commented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
CCongdonCommented:
Yes, that script was designed to work in ASP. It is written in VBScript. JScript wouldn't be a hole lot different...
0
 
theclassicAuthor Commented:
What is the "Encrypt Key" Tag? Is that for the asp or for asp.net also?  sorry, begginner....
0
 
CCongdonCommented:
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
 
theclassicAuthor Commented:
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
 
theclassicAuthor Commented:
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
 
CCongdonCommented:
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now