Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Connection string Convert to DSN

Posted on 2004-09-02
12
Medium Priority
?
534 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 80 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 120 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 200 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
The viewer will learn how to count occurrences of each item in an array.
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses

610 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