Solved

Connection string Convert to DSN

Posted on 2004-09-02
12
501 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
Comment Utility
Woef, have you setup a DSN before?
0
 

Author Comment

by:Woefman
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:sciber_dude
Comment Utility
@ 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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Assisted Solution

by:sciber_dude
sciber_dude earned 30 total points
Comment Utility
@ 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@ 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
Comment Utility
oops! Mike_V explained the same stuff i did. Sorry for the double post!

and Mike_V, welcome to EE.

:) SD
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Accessibility and Usability are two concepts that seem to be closely related.  But, too many people seem to have a distorted perception of them. During last five years, those two words have come to the day-to-day work of almost every web develope…
Read about why website design really matters in today's demanding market.
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…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

771 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

12 Experts available now in Live!

Get 1:1 Help Now