Solved

Connection string Convert to DSN

Posted on 2004-09-02
12
506 Views
Last Modified: 2011-10-03
Hello My server says i have to use this connection string.

<%Dim Conn, RS

Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")

DSNName = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
DSNName = DSNName & Server.MapPath("/USERNAME/database/mydatabase.mdb")
Conn.Open DSNName

sql = "SELECT * FROM [TableName] WHERE (((TableName.FieldName)='Value'))"
RS.Open sql, Conn, 3, 3%>

My Web page Script as follows:
************************************************Below is What script uses
'* Global variables.                                                       *
   '***************************************************************************

   dim DbConn

   '***************************************************************************
   '* Functions and subroutines.                                              *
   '***************************************************************************

   '---------------------------------------------------------------------------
   ' OpenDB(): Opens the database connection (global variable 'DbConn').
   '---------------------------------------------------------------------------

   sub OpenDB()

     dim dbDir

     set DbConn = Server.CreateObject("ADODB.Connection")
     dbDir = Server.MapPath("./data/NFL.mdb")
     DbConn.Open "DBQ="& dbDir _
       &";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"

   end sub


Im Lost on how to convert Mine to what my host wants me to use

and the syntax is killing me %<--  and ETC

I dont want to re write the whole project, as can see my Sub is : Sub OpenDb

with no sql or Table def for this part.

Database name : Woef.mdb
stored in directory Pool/Data/Woef.mdb
Site Name www.myHost/HostUser/Pool/

Thanks
Woef

Will Give  points for a copy paste solution with Explanation



0
Comment
Question by:Woefman
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 1

Expert Comment

by:Tom Cullen
ID: 11965330
Woef, have you setup a DSN before?
0
 

Author Comment

by:Woefman
ID: 11965860
No Sir , thanks for the reply .

I changed Hosts now im screwed I think till learn this method.

Im Just a Dumb IT College kid trying to learn this stuff , and it gets overwhelming at times .
0
 
LVL 1

Expert Comment

by:Tom Cullen
ID: 11966681
Hi Woefman, first off go into your ODBC Data Source Connection, found in your control panel, or with XP, your Administrative Tools folder. Click your Data Sources (ODBC) and then click the Tab that says System DSN. Once you are here, click Add and select Microsoft Access Driver (*.mdb) and then Finish.

Now you will be taken to another form that Requests the;
1. Data Source Name (Enter all one word here)
2. Description (Enter your Description here)
3. Click Select (Locate and select your Database)

Then click Ok.

You will have your DSN, once you do that, let me know, and I will provide the Include Connection String you will need.

SM64
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 11

Expert Comment

by:sciber_dude
ID: 11966796
@ Starmanager64  -

I guess Woefman cant create a DSN by himself as he is hosting his website on a remote server.

@ Woefman  - Try changing this part of your code.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   sub OpenDB()

     dim dbDir

     set DbConn = Server.CreateObject("ADODB.Connection")
     dbDir = Server.MapPath("data/Woef.mdb")
     DbConn.Open "DBQ="& dbDir &";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"

   end sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Make sure this Sub is in the root directory "pool"
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11966839
This is a better sub....

<%
sub OpenDB()
       dbDir = server.MapPath("data/Woef.mdb")

       strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
               & " Data Source= " & dbDir & ";"_
               & " Mode=Share Deny None;User Id=admin;PASSWORD=;"

       if not IsObject("DbConn") then
            set DbConn=Server.CreateObject("ADODB.Connection")
            DbConn.ConnectionTimeout = 15
            DbConn.CommandTimeout =  10
            DbConn.Mode = 3 'adModeReadWrite
            if DbConn.state = 0 then
                 DbConn.Open strConnectString
            end if
       end if
end sub
%>
0
 
LVL 1

Assisted Solution

by:Tom Cullen
Tom Cullen earned 20 total points
ID: 11967087
good call scriber dude :)

However, he should set his system up locally anyway for his own testing and then tell his hosting co. to create the DSN for him on thier server that replicates his own.

SM64
0
 
LVL 11

Assisted Solution

by:sciber_dude
sciber_dude earned 30 total points
ID: 11967141
@ Starmanager64 -

Thats a good point @ local web. The code i gave Woefman is actually a DSN-less connection code. So he doesnt have to create a DSN connection.

:) SD

0
 

Author Comment

by:Woefman
ID: 11971032
Ok you all are working hard Im uping the Points , Im new here not sure how this points thing works . if  we come to a working solution ..and i happen to be to squemish on the points  let me know Ill Up it more to be fair.

To answer your questions or doubts,  I can make a DSN Connection on my computer I have done it in MMC Console Before, i have a basic understanding of Databases but im no pro.

I lack in the Web Database stuff.

My Server is at : http://www.1asphost.com/learnasp.asp

they have the code Im forced to use ,it can be viewed on there help site.

They specifically need me to use a DSN less  if im using the terminology correct.

Basically since it is a free host , I have to use my user name as part of the connection.


The Script or web page for me ..is a football pool site . Im merly testing it  on this free server  before i go nuts and spend huge bucks . I use  a linux server for my real or normal website.

I just wanted to play with ASP a bit , I only tried it a short time  from what ive read in my books.

so  ...as in previous posts ...

say my site is :http://www.1asphost.com/Username/Pool

Database
http://www.1asphost.com/Username/Pool/data/NFL.mdb

I Use a Common .asp page  forwhere the working Connection string is now but it wont work online at this host because I need a DSN LESS  Like stated In first Post.

Thx for time and Comments

Woef


0
 

Author Comment

by:Woefman
ID: 11971044
Follow up

Where i am confused ...So Im Clear , is No where in my Webpage (Common.asp) does it directly use the SQL when making the connection.  If look at My Web host  Example above  they show me calling up a table at same time as the connection string.

Im trying to meet their requirments and just get  the thing to work .

I know im a pain , if read this topic from beginning it might be clear.


Do i need to send Example code  ?

Woef
0
 
LVL 3

Accepted Solution

by:
Mike_V earned 50 total points
ID: 11972521
Hello guys, I just joined EE yesterday and WOW what a great resource.  I'm going to take a stab at this.  I'm fairly new to ASP and VB and am not 100% what the prob is here so I am going to ask a couple q's and quote alot here in an attempt to answer this for you woef and in the process learn a bit myself.

As I understand you, your ISP gave you this code example below?

<%Dim Conn, RS
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
DSNName = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
DSNName = DSNName & Server.MapPath("/USERNAME/database/mydatabase.mdb")
Conn.Open DSNName
sql = "SELECT * FROM [TableName] WHERE (((TableName.FieldName)='Value'))"
RS.Open sql, Conn, 3, 3%>

This example sets, opens, and retrieves.  But I do not see actual DSN usage?  

If this was actually calling a DSN entry I believe these 2 lines
DSNName = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
DSNName = DSNName & Server.MapPath("/USERNAME/database/mydatabase.mdb")
Would be replaced by something to the effect of
DSNName = "THE ACTUAL DSN IN THE ODBC OF THE WEB SERVER"
Dont quote me on that, but I think I am at least very close there.

Your ASP page at current is? (UNcommented to save space here)
   '***************************************************************************
'* Global variables
    dim DbConn
   ' OpenDB(): Opens the database connection (global variable 'DbConn').
   sub OpenDB()
     dim dbDir
     set DbConn = Server.CreateObject("ADODB.Connection")
     dbDir = Server.MapPath("./data/NFL.mdb")
     DbConn.Open "DBQ="& dbDir _
       &";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
   end sub
   '***************************************************************************

This does much less than the example your ISP gave you.  It simply sets and opens a connection to the DB.
The RS.OPEN and SQLString I would assume are in other parts of your ASP, so we shoul dbe able to ignore them from your ISP's example and jsut concentrate on egtting the connection.

Again pasting info from you to ensure I understand you correctly.  This info is used in the below code.

Database name : Woef.mdb
stored in directory Pool/Data/Woef.mdb
Site Name www.myHost/HostUser/Pool/

I may be wrong but as far as I can see if you edit your ASP page to
   '***************************************************************************
'* Global variables
    dim DbConn
   ' OpenDB(): Opens the database connection (global variable 'DbConn').
   sub OpenDB()
     dim dbDir
     set DbConn = Server.CreateObject("ADODB.Connection")
     dbDir = Server.MapPath("/HostUser/Pool/data/NFL.mdb")
     DbConn.Open "DBQ="& dbDir _
       &";Driver={Microsoft Access Driver (*.mdb)};FIL=MS Access;"
   end sub
   '***************************************************************************
I cannot see why the above would not satisfy what your ISP gave as an example.

The ONLY thing I can think of is s possibly directory permissions problem, eg: Frontpage permissions.
They gave you
DSNName = DSNName & Server.MapPath("/USERNAME/database/mydatabase.mdb")
Your ISP MAY be telling you that ALL databases need to reside in a special directory called /database/
Frontpage uses a dir called fpdb

IF the above wont work and you do need to use the ISP's speical DB dir then there would be 2 steps to making this work.

1) MOVE the physical MDB file
from /HostUser/Pool/data/NFL.mdb
to /HostUser/database/NFL.mdb

2) Change you ASP to reflect this
dbDir = Server.MapPath("/HostUser/database/NFL.mdb")

Lemme know if this helps woef
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11976019
@ Woefman

These are the instructions on your website provider's website. (read my comments in between the lines)

> 1. If you are writing to a database, make sure it is in the /USERNAME/database/ directory.
> THIS IS IMPORTANT; it is the only directory where databases can be if they are to be written to.

- say my site is :http://www.1asphost.com/Username/Pool
- Database http://www.1asphost.com/Username/Pool/data/NFL.mdb

You need to move your database into a folder called DATABASE under your USERNAME directory. For e.g. if you username is Woefman, then you database path should be http://www.1asphost.com/Woefman/database/NFL.mdb. So create a folder called database and move the database into it.

> 2. Make sure you use the DSN-Less connection method.
So forget about creating a DSN connection. you will need to use the code i gave you.

> I Use a Common .asp page  forwhere the working Connection string is now but it wont work online at this host
> because I need a DSN LESS  Like stated In first Post.

Excellent! copy the following Sub into the common.asp file (which will be saved in the {USERNAME} directory).
<%
sub OpenDB()
       dbDir = server.MapPath("database/NFL.mdb")

       strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
               & " Data Source= " & dbDir & ";"_
               & " Mode=Share Deny None;User Id=admin;PASSWORD=;"

       if not IsObject("DbConn") then
            set DbConn=Server.CreateObject("ADODB.Connection")
            DbConn.ConnectionTimeout = 15
            DbConn.CommandTimeout =  10
            DbConn.Mode = 3 'adModeReadWrite
            if DbConn.state = 0 then
                 DbConn.Open strConnectString
            end if
       end if
end sub
%>

> Where i am confused ...So Im Clear , is No where in my Webpage (Common.asp) does it directly use the SQL when
> making the connection.  If look at My Web host  Example above  they show me calling up a table at same time as the
> connection string.

Now, what you do is this.... On every other page where you need to the ASP script to talk to the database, you add this line of code...  on top of the file.

<!--#include file="common.asp"-->

The SQL statements will be written onto those pages which need to interact with the database. Normally you would need database connection strings on each page that needs to interact with the database. What you have done here is ... moved the database connection strings to a different page and you are calling it on each subsequent page. (a cleaner way to program).

Hope this explains it all.
:) SD
0
 
LVL 11

Expert Comment

by:sciber_dude
ID: 11976030
oops! Mike_V explained the same stuff i did. Sorry for the double post!

and Mike_V, welcome to EE.

:) SD
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

813 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now