Hello rrocket,
I have been seeing a lot of people placing their connection strings to their databases on their ASP pages (Local String variables, directly within the open statement, and even in functions). I think newer developers and us that have a ‘few’ years under our belts start pushing the correct way again on how to connect to ones database within the ASP interface/framework.
Connection string should be stored inside of an application variable that is set within the Global.asa file of our websites. The Global.asa is a file/page that get loaded/refreshed every time the website gets started or the file itself is saved. This is “the” place to place application variables. Here is an example of a Global.asa file that has a connection to a Microsoft SQL Server, Microsoft Access Database, and Oracle Server:
-
<!-- METADATA Type="TypeLib" UUID="00000201-0000-0010-8000-00AA006D2EA4" -->
-
<script language=VBScript runat=Server>
-
Sub Application_OnStart
-
Application("My_SQL") = "Provider=SQLOLEDB;Data Source=000.000.000.000;Initial Catalog=DatabaseName;User ID=DatabaseUser;Password=Password;"
-
Application("My_Access") = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("Relative path to your Access database")
-
Application("My_Oracle") = "dsn=SystemDsnName;uid=DatabaseUser;pwd=Password;"
-
End Sub
-
</script>
-
Now any page can make reference to the application variables and there is only one place that the developer has to go to for updating a connection string if need be.
Hey rrocket,
You might be scratching your head trying to understand what this has to do with your question. Well, I don’t know what type of database your connection to or how your function that calls your connection string looks. Give your Global.asa file a try in place of a function to seed your connection string; it is just one less thing you will need to debug from now on.
Also, it looks like you should be using a “sub” instead of a “function”. Functions are to be used to return values.
Try one of the below snips and let us know if it worked:
If you’re using Microsoft SQL Server:
-
<%
-
Sub GetProfitPercent()
-
If Not LEN(Session("UserLocationId"))>0 Then
-
Response.Write("Your Session has ended!")
-
Response.End
-
End IF
-
-
Set Conn = Server.CreateObject("ADODB.Connection")
-
Conn.Open Application("My_SQL")
-
SQL = "SELECT PROFIT_PERCENT FROM t_DIRECTORY WHERE directory_id = '" & Session("UserLocationId") & "'"
-
Set myRS=Server.CreateObject("ADODB.Recordset")
-
myRS.Open SQL, Conn
-
-
If (myRS.EOF) Then
-
Session("PPercent") = ".15"
-
Else
-
Session("PPercent") = myRS("PROFIT_PERCENT")
-
End If
-
End Function
-
%>
-
If you’re using Microsoft Access Database:
-
<%
-
Sub GetProfitPercent()
-
If Not LEN(Session("UserLocationId"))>0 Then
-
Response.Write("Your Session has ended!")
-
Response.End
-
End IF
-
-
Set Conn = Server.CreateObject("ADODB.Connection")
-
Conn.Open Application("My_Access")
-
SQL = "SELECT PROFIT_PERCENT FROM t_DIRECTORY WHERE directory_id = '" & Session("UserLocationId") & "'"
-
Set myRS=Server.CreateObject("ADODB.Recordset")
-
myRS.CursorType = 1
-
myRS.LockType = 3
-
myRS.Open SQL, Conn
-
-
If (myRS.EOF) Then
-
Session("PPercent") = ".15"
-
Else
-
Session("PPercent") = myRS("PROFIT_PERCENT")
-
End If
-
End Function
-
%>
-
If you’re using Oracle Orical Server:
-
If Not LEN(Session("UserLocationId"))>0 Then
-
Response.Write("Your Session has ended!")
-
Response.End
-
End IF
-
-
Set Conn = Server.CreateObject("ADODB.Connection")
-
Conn.Open Application("My_Oracle")
-
SQL = "SELECT PROFIT_PERCENT FROM t_DIRECTORY WHERE directory_id = '" & Session("UserLocationId") & "'"
-
Set myRS=Server.CreateObject("ADODB.Recordset")
-
myRS.Open SQL, Conn
-
-
If (myRS.EOF) Then
-
Session("PPercent") = ".15"
-
Else
-
Session("PPercent") = myRS("PROFIT_PERCENT")
-
End If
-
Hope I was of some help~