Convert DSN to DSNless... 1

[sonicsnail]

Hi all,

I've inherited an ASP app that uses a DSN, and I only have FTP access to my server, so need to convert the connections to DSN-less strings.

My ASP is really bad, and I need help changing this:

<%
Set MyConn = Server.CreateObject(&quot;ADODB.Connection&quot
MyConn.Open &quot;FILEDSN=c:\dsn\MyTable_dsn.dsn&quot;
%>

This appears on various pages in various places, so I'm hoping I can brutally find/replace this with a connection string??

Pete

 

[mwolf00]

hmm - you need more info about where the DSN is pointing to do a DSNless. What type of DB are you pointing at?


here's an OLEDB connection to a SQL Server DB

application(&quot;connStr&quot = &quot;Provider=SQLOLEDB.1;User Id=someID;Password=somePassword;Data Source=somePCname;Initial Catalog=dbName&quot;

You will find that different DBs have slight differences in the connection string...


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[sonicsnail]

Its an MS Access database...

I'm a little familiar with connection strings - I've used one before... I'm more confused by the &quot;MyConn&quot; part in my snippet above

 

[sonicsnail]

Here is some of the code in action:

Once MyConn is declared it is used for the authentication below..

I'm guessing I just need to use a connection string to create MyConn


---------snip---------

<%
Set MyConn = Server.CreateObject(&quot;ADODB.Connection&quot
MyConn.Open &quot;FILEDSN=c:\dsn\MyTable_dsn.dsn&quot;
%>
<%
SQL_query = &quot;select * from User where UserName='&quot;&Request.QueryString(&quot;txtUser&quot&&quot;'&quot;
Set RS = MyConn.Execute(SQL_query)
dim validate
if RS.EOF then
Validate = &quot;Invalid Username !!!&quot;
else
if RS(&quot;password&quot = Request.QueryString(&quot;txtPassword&quot then
session(&quot;UserName&quot = RS(&quot;UserName&quot
response.clear
Response.Redirect &quot;mainMenu.asp&quot;
else
session(&quot;UserName&quot = RS(&quot;UserName&quot
Validate = &quot;Invalid Password!!!&quot;
end if
end if
%>

---------snip---------

 

[ChrisHirst]

MyConn is just the variable name.

It could be called YourConn or SQLConn or Fred if you like!



Chris.

Indifference will be the downfall of mankind, but who cares?

 

[mwolf00]

Your choice...

accessDB = &quot;../myAccessDB.mdb&quot;

OLEDB Connection String strconn=&quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=&quot;
strconn=strconn & server.mappath(accessDB) & &quot;;&quot;
'strconn=strconn & &quot;Password=whatever;&quot; (if needed)

ODBC Connection String
strconn=&quot;DRIVER={Microsoft Access Driver (*.mdb)};&quot;
strconn=sourceDSN & &quot;DBQ=&quot; & server.mappath(accessdb)
strconn=sourceDSN & &quot;Password=whatever;&quot;


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[sonicsnail]

Thanks for the string choices, but how do I get this to be &quot;MyConn&quot;?

 

[mwolf00]

strconn=&quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=&quot;
strconn=strconn & server.mappath(accessDB) & &quot;;&quot;

set myConn = server.createObject(&quot;adodb.connection&quot
myConn.Open strConn


Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

 

[sonicsnail]

Thanks!...

My page is now giving errors in the SQL query syntax, which I'm told ISN'T the case when using DSNs.

I'm not convinced though - could the version of MS Access on my server matter?

Its saying there's an error in here:

SQL_query = &quot;select * from User where UserName='&quot;&Request.QueryString(&quot;txtUser&quot&&quot;'&quot;

 

[mwolf00]

version shouldn't matter for such a simple query. &quot;User&quot; may be a reserved word though - use brackets

SQL_query = &quot;select * from [User] where UserName='&quot;&Request.QueryString(&quot;txtUser&quot&&quot;'&quot;
response.write SQL_query
response.flush
Set RS = MyConn.Execute(SQL_query)

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook