Connection string

mcramsey · March 22nd, 2010, 07:49 AM

Hi.

I'm using Windows XP SP3, IIS, SQL Server 2008 and ODBC.

Some say Classic ASP won't work with DNS (ODBC).

So I plan to use 'Connection string' instead.

Where should I put it?

Can you guys help me and show some example?

Kept getting this error;

http://img2.pict.com/cd/8e/ad/3153435/0/800/ptb.jpg

Below is the 'Main.asp' file.

Line 106 refers to the underline bold text below;

Quote:

<%@ Language=VBScript %>

<%
Response.Buffer = True
Response.Expires = -1000
%>

<HTML><HEAD><TITLE>Main Page</TITLE>

<base target="_self">

<BODY bgProperties=fixed bgColor=#FFFFFF leftMargin=0 topMargin=0 bottommargin10>

<DIV style="LEFT: 5px; POSITION: absolute; TOP: 80px"></DIV>

<style type="text/css">
body {font-size: 8pt; font-family: verdana; color:white;}
table, tr, td {font-size: 10pt; font-family: verdana; color: black;}
a:link {font-size: 8pt; font-family: verdana; color:black; text-decoration: none;}
a:visited {font-size: 8pt; font-family: verdana; color:black; text-decoration: none;}
a:active {font-size: 8pt; font-family: verdana; color:#CCCCFF; text-decoration: none;}
a:hover {font-size: 8pt; font-family: verdana; color: #db7093; text-decoration: none;}

font {font-size: 8pt; font-family: arial}
.para1 {position:absolute; top: 48; left: 106; font-family: "font2, Georgia"; font-size: 50px; font-weight:bold; line-height: 40px; text-align: left; color: #CC99FF; }
.para2 {position:absolute; top: 46; left: 105; font-family: "font1, Georgia"; font-size: 50px; font-weight:bold; line-height: 40px; text-align: left; color: #660066; }
.badan1 {font-family: "Verdana"; font-size: 10pt; font-weight:bold; text-align: left; color: #FFFFFF; }
.badan2 {font-family: "Verdana"; font-size: 10pt; font-weight:bold; text-align: left; color: #000000; }
.edit {font-size: 10pt; font-family: symbol; color: #ff1493;}
</style>

<! SCROLLBAR COLOR !>

<STYLE type=text/css>BODY {
SCROLLBAR-FACE-COLOR: #CC99FF; FONT-SIZE: 8pt;
SCROLLBAR-HIGHLIGHT-COLOR: #990099;
SCROLLBAR-SHADOW-COLOR: #CC66FF; COLOR: #000000;
SCROLLBAR-3DLIGHT-COLOR: #9900ff;
SCROLLBAR-ARROW-COLOR: #993366;
SCROLLBAR-TRACK-COLOR:#CC99FF; FONT-FAMILY: arial, trebuchet MS, verdana, arial, sans-serif;
SCROLLBAR-DARKSHADOW-COLOR: #000000
}
</STYLE>
</HEAD>

<div align="center"><div align="center" style="width: 661; height: 80">
<p style="text-align: center">News Flash</div>
<div align="center" style="width: 658; height: 80">
<p style="text-align: center">News Flash</div>
</div>
<p align="justify"><p align="justify"><p align="justify">
<%
Sub DrawPage()
%><center>
<div align="center">
<%
'Set the current record pointer to the first record of the recordset
rs.MoveFirst

'Loop through all the records in the recordset
while not rs.EOF

'Load recordset field values into local variables
Title = rs("News_title") & ""
Body = rs("News_body") & ""
DateTime = rs("News_Date") & ""
%>
<table width="790" border="0" height="48" style="border-collapse: collapse" bordercolor="#111111" cellpadding="0"
cellspacing="0" align=center>
<tr>
<td width="104" height="21" bgcolor="#660066" style="font-size: 10pt; font-family: verdana; color: white"></td>
<td width="1625" height="21" bgcolor="#660066"><div><%=Title %></div></td>
<td height="21" bgcolor="#660066" width="162"><div><%=DateTime %></div></td>
</tr>

<tr>
<td width="104" height="12" bgcolor="#CC99FF" style="font-size: 10pt; font-family: verdana; color: black"></td>
<td width="1787" height="12" bgcolor="#CC99FF" style="font-size: 10pt; font-family: verdana; color: black" colspan="2">
<div style="width: 705; height: 13"><%=Body %></div></div></td>
</tr>

<tr>
<td width="1891" bgcolor="#CC99FF" style="font-size: 4pt; font-family: verdana; color: black" colspan="3" height="9"></td>
</tr>

<tr>
<td width="2011" height="6" colspan="3" style="font-size: 10pt; font-family: verdana; color: black">
<p align="center">
</td>
</tr>
</table>

<%
rs.MoveNext
wend
%>
<%
End Sub

'Database coding commence
dim rs, sql, cn, Title, Body, DateTime

'Set connection details
set cn = server.CreateObject("ADODB.Connection")
cn.Open "DSN=Trains;UID=sa;PWD=;DATABASE=Trains"

'Set sql string
sql = "SELECT * FROM News where News_Expiry >"
sql = sql & "'" & TRIM(Date()) & "'"
sql = sql & " order by news_id desc"

'Create a recordset
set rs = cn.Execute(sql)

if rs.EOF then
%>
<center>
<IMG SRC=images/tanda.gif>

<%
Response.Write "<b>There's no news to publish.</b>"
%>
</center>
<%
else
Call DrawPage()
End if
%>

</center>

<p align="center">
<p align="center">
<p align="center"><font face="Webdings" color="#CC99FF" size="7">
<marquee scrollamount="3">CDBFGHKSPITJQ</marquee></font>

</BODY>
</HTML>

Old Pedant · March 22nd, 2010, 06:03 PM

You *can* put it most any place.

But you can certainly put it directly into the open:

Code:

cn.Open "Provider=sqloledb;" & _ 
           "Server=YOURSERVERNAME;" & _
           "Database=Trains;" & _
           "User Id=sa;" & _
           "Password="

But provide a real user id and password, if available.

mat41 · March 22nd, 2010, 06:04 PM

;;;Some say Classic ASP won't work with DNS (ODBC).

no that no true. But why use ODBC when you dont have to. Here is IMO a better method to connect:

First get you connection string details out of each file. What if the server, UN, PW etc chnages and you have 50 pages? A real bugger making so many chnages!!!

Step 1 place this into a file (alter the variables) EG scripts/global.asp:

<%
dim conn,sql,EoF
const IUSER = "yourUserName"
const pWord = "yourPassword"
const DBNAME = "databaseName"
const DBSERVER = "machineName"

GetConnectionString = "Provider=SQLOLEDB.1; Password=" & pWord & "; Persist Security Info=True; User ID=" & IUSER & "; Initial Catalog=" & DBNAME & "; Data Source=" & DBSERVER & ";"
%>

Step 2:

Place this (I place at the top) in every file you would like to make a DB connection:


<% Set Conn = server.createobject("ADODB.Connection")
conn.open(GetConnectionString)
'now write your SQL
SQL = "SELECT ....."
'now execute it
set somevariable = conn.execute(sql)

NOTE - You should not use the SA account this is considered a bit of a security risk

kushalkg · April 22nd, 2010, 12:47 PM

It clearly says the login failed. It means either password is incorrect or "sa" doesn't have login permissions to the database. Also, you may want to test the connection using ODBC administrator on web Server with "sa" credentials.