help with connection string

[Jennifer.Berube]

well I'm not sure how to go about making my SQL connection string...

The code below is what I need to replace with my SQL connection...I
just don't know if that code is for DSN or access...

I don't want to use DSN just a connection string...help?

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=" & DSN & ";uid=" & UID & ";password=" & PASSWORD
p_oConn.open sDSN
Set GetConnection = p_oConn
end function

 

[Bob Barrows [MVP]]

well I'm not sure how to go about making my SQL connection string...

The code below is what I need to replace with my SQL connection...I
just don't know if that code is for DSN or access...

I don't want to use DSN just a connection string...help?

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=" & DSN & ";uid=" & UID & ";password=" & PASSWORD
p_oConn.open sDSN
Set GetConnection = p_oConn
end function

http://www.aspfaq.com/show.asp?id=2126

 

[Jennifer.Berube]

http://www.aspfaq.com/show.asp?id=2126
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

To tell you the truth...I can't really figure out where to edit the
information.....>< sorry! I get lost on what needs to be edited and
what doesn't...I'm new to asp.

 

[Bob Barrows [MVP]]

To tell you the truth...I can't really figure out where to edit the
information.....>< sorry! I get lost on what needs to be edited and
what doesn't...I'm new to asp.

An ADO Connection's Open method takes a single argument: a string
containing the information needed to connect to a data source. Your
ycode snip provides an example of this. The code assigns a string
containing connection information to a variable called sDSN:

sDSN = "DSN=" ...

It then calls the Open method of the Connection object, passing the
variable containing the connection string in the argument:
p_oConn.open sDSN

So, what you need to edit is the line of code that assigns the
connection string to the sDSN variable (an unfortunate choice of names
for the variable, but it doesn't really matter). Given that you did not
tell us the type or name of the database to which you are trying to
connect, I cannot be specific. In general:

This information in a connection string usually includes the name of an
OLE DB provider. If the provider name is not supplied, ADO assumes you
mean it to use the default OLE DB Provider for ODBC (called "MSDASQL").
So, given that you don't wish to use a DSN, you must supply the name of
the provider.
Provider=...;

The next bit of information the string needs to contain is the name of
the data source. For a server-based database such as SQL Server, this
will be the name or IP address (you may need to specify the network
library if using the IP address) of the server:
Data Source=ServerName;
or
Data Source=xxx.xx.xx.xxx
For a file-based database such as Jet (Access), this will be the path
and file name: it must be a file-system path, not a url, so if you wish
to use a virtual path to the file, you must use Server.MapPath to
generate the file-system path (these example assume Jet):
Data Source=p:\ath\to\databasefile.mdb;
or
Data Source=" & Server.MapPath("/db/databasefile.mdb") & ";"

For server-based databases, you can optionally supply the name of the
default database:
Initial Catalog=NameOfDatabase;

For server-based databases, you will need to supply the security info
needed for the connection. With SQL Server, the option exists to use
Windows or Integrated Authentication. This is rarely used in ASP, but it
is specified like this:
Integrated Security=SSPI;
More often, a user name and password will be supplied, like so:
UserID=SomeUser;Password=*******;

For Jet, unless a database is password protected or uses workgroup
security, no security information should be passed. See the FAQ article
or Carl Prothman's page (see the link below) for the syntax needed to
handle the other two cases.

Another optional piece of information I usually provide for server-based
databases is the name of the application. This aids in troubleshooting:
ApplicationName=Myapplication;

So, the idea is to put each piece of information together in a single
string, and pass that string to the Open method. This link might make it
clearer:
http://www.carlprothman.net/Default.aspx?tabid=81
Concentrate on the sections for OLE DB providers. Avoid ODBC unless
whatever database you are using has no OLE DB providers.

HTH,
Bob Barrows

 

[Bob Barrows [MVP]]

UserID=SomeUser;Password=*******;

Proof-reading failure. This should have been:
User ID=SomeUser;Password=*******;

"User ID" needs to be two words

 

[Jennifer.Berube]

Proof-reading failure. This should have been:
User ID=SomeUser;Password=*******;

"User ID" needs to be two words
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Wow....thanks a lot! I appreciate your assistance!

 

[Jennifer.Berube]

Proof-reading failure. This should have been:
User ID=SomeUser;Password=*******;

"User ID" needs to be two words
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

I guess what I'm really going at is how can I alter this code to fit
my SQL connection. OLEDB connection.

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=" & DSN & ";uid=" & UID & ";password=" & PASSWORD
p_oConn.open sDSN
Set GetConnection = p_oConn
end function

When I try to replace it all with my connection string it doesn't
work....

 

[Bob Barrows [MVP]]

I guess what I'm really going at is how can I alter this code to fit
my SQL connection. OLEDB connection.

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=" & DSN & ";uid=" & UID & ";password=" & PASSWORD
p_oConn.open sDSN
Set GetConnection = p_oConn
end function

When I try to replace it all with my connection string it doesn't
work....

You're still showing us the original code. We've already seen this and it
does not help us help you.
Show us what you tried and tell us what error you got. That's the only way
we are going to be able to help you.

Also, tell us the type and version of database you are using.

 

[Jennifer.Berube]

You're still showing us the original code. We've already seen this and it
does not help us help you.
Show us what you tried and tell us what error you got. That's the only way
we are going to be able to help you.

Also, tell us the type and version of database you are using.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Sorry...I'm rushing things and well this week has been like a week
from hell. I usually do a lot better at describing my situation.
Anyway, here is the error I am getting:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
/inc/utility.asp, line 81

And here is the code I thought I could get away with doing. I already
know there are obvious mistakes I just don't know how to fix
them....I've experimented with no luck.

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=xxxxxx" & DSN & ";uid=xxxxx" & UID & ";password=xxxxx3" &
PASSWORD
p_oConn.open sDSN
Set GetConnection = p_oConn
end function

 

[Jennifer.Berube]

You're still showing us the original code. We've already seen this and it
does not help us help you.
Show us what you tried and tell us what error you got. That's the only way
we are going to be able to help you.

Also, tell us the type and version of database you are using.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

not sure if that posted...

I get this error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no
default driver specified
/inc/utility.asp, line 81

With this code:

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=xxxxx" & DSN & ";uid=xxxxx" & UID & ";password=xxxxx" &
PASSWORD
p_oConn.open sDSN
Set GetConnection = p_oConn
end function

I know there are major problems with the code but I just don't know
how to go about repairing it...SQL Server 2000 database

 

[Bob Barrows [MVP]]

And here is the code I thought I could get away with doing. I already
know there are obvious mistakes I just don't know how to fix
them....I've experimented with no luck.

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=xxxxxx" & DSN & ";uid=xxxxx" & UID & ";password=xxxxx3" &
PASSWORD
p_oConn.open sDSN
Set GetConnection = p_oConn
end function

But you are ignoring the information in the links I provided! None of
the suggested OLE DB links use "DSN" as one of the attributes! Your
connection string should follow the form:

sDSN="Provider=??????;Data Source=?????;"

'if this is an Access database with no password protection or workgroup
security, you are done.

'If you are connecting to SQL Server, add this:
sDSN = sDSN & "User ID=?????;Password=?????;"
'and optionally, this:
sDSN = sDSN & "Initial Catalog=DatabaseName;"

Look at the links I provided and substitute your information for the
question marks.

If you want me to be more specific you need to tell me
1. what database type and version you are trying to connect to!
2.a) if SQL Server, what is the name of the server?
b) if Access, the path and name of the database file

 

[Jennifer.Berube]

But you are ignoring the information in the links I provided! None of
the suggested OLE DB links use "DSN" as one of the attributes! Your
connection string should follow the form:

sDSN="Provider=??????;Data Source=?????;"

'if this is an Access database with no password protection or workgroup
security, you are done.

'If you are connecting to SQL Server, add this:
sDSN = sDSN & "User ID=?????;Password=?????;"
'and optionally, this:
sDSN = sDSN & "Initial Catalog=DatabaseName;"

Look at the links I provided and substitute your information for the
question marks.

If you want me to be more specific you need to tell me
1. what database type and version you are trying to connect to!
2.a) if SQL Server, what is the name of the server?
b) if Access, the path and name of the database file

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Okay it's an SQL 2000 Server and database. I don't know the
specifics..
Alentus...my hosting company suggest this:

' Define a connection string for the database
' The connect string is used to login to the database.
' DSN=DSNmydsn is the ODBC data source name and
' PWD=mypassword is the password for the database.

strODBC = "DSN=DSNmydsn; UID=myuserid; PWD=mypassword;"
' Define an SQL query
' The query is to retrieve the ID, Name and Description fields
' from a table named Test, and will return the records in
' order of ID.
strSQL = "SELECT ID,Name,Description FROM Test ORDER BY ID"
' Create a database connection object
set d1 = Server.CreateObject("ADODB.Connection")
....

But my question is do I just delete the connect functions? Do I keep
them...? What do I delete...what don't I delete? That's where i get
stuck...

 

[Bob Barrows [MVP]]

Okay it's an SQL 2000 Server and database. I don't know the
specifics..

I'm sorry, but I cannot get specific without knowing the name of the server
(or its IP address) and the name of the database on the server. I am going
to make a final attempt at helping you below, but you are going to have to
substitute your information when you go to implement the code I suggest.

Alentus...my hosting company suggest this:

' Define a connection string for the database
' The connect string is used to login to the database.
' DSN=DSNmydsn is the ODBC data source name and
' PWD=mypassword is the password for the database.

Well, they are wrong. You do not need a DSN or ODBC.

strODBC = "DSN=DSNmydsn; UID=myuserid; PWD=mypassword;"

And this is not really the proper syntax to be using (although it might
work).

' Define an SQL query
' The query is to retrieve the ID, Name and Description fields
' from a table named Test, and will return the records in
' order of ID.
strSQL = "SELECT ID,Name,Description FROM Test ORDER BY ID"
' Create a database connection object
set d1 = Server.CreateObject("ADODB.Connection")
...

But my question is do I just delete the connect functions?

What "connect functions"?

Do I keep
them...? What do I delete...what don't I delete? That's where i get
stuck...

No deleting. Just replace what is there. I'm really failing to understand
your problem. I have repeatedly advised you to simply replace the connection
string that they advised you to use with the new connection string that does
not use ODBC or DSNs. Let's start with your original code:

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=xxxxx" & DSN & ";uid=xxxxx" & UID & ";password=xxxxx" &
PASSWORD

'I'm not sure why you left those x's in there ... you were
'supposed to replace them with your own information.

p_oConn.open sDSN
Set GetConnection = p_oConn
end function


All you need to do is change it to:

function GetConnection()
const Server= "SQLServerName"
const UID = "webuser"
const PASSWORD = "password"
const DB = "NameOfDatabase"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "Provider=SQLOLEDb; Data Source=" & Server & _
"; user id=" & UID & ";password=" & PASSWORD

'The following two lines are for debugging. If you run into
'problems, uncomment them (remove the apostrophes) and
'run the page. Look at the resulting connection string in the
'browser. If that does not reveal your problem, post the result
'here. Comment out these lines when finished debugging:
'Response.Write sDSN & "<br>"
'Response.End

p_oConn.open sDSN
Set GetConnection = p_oConn
end function

Replace SQLServerName with the name of the SQL Server, and replace
NameOfDatabase with the name of your database

 

[Jennifer.Berube]

I'm sorry, but I cannot get specific without knowing the name of the server
(or its IP address) and the name of the database on the server. I am going
to make a final attempt at helping you below, but you are going to have to
substitute your information when you go to implement the code I suggest.



Well, they are wrong. You do not need a DSN or ODBC.




And this is not really the proper syntax to be using (although it might
work).



What "connect functions"?


No deleting. Just replace what is there. I'm really failing to understand
your problem. I have repeatedly advised you to simply replace the connection
string that they advised you to use with the new connection string that does
not use ODBC or DSNs. Let's start with your original code:

function GetConnection()
const DSN = "membershipdb"
const UID = "webuser"
const PASSWORD = "password"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "DSN=xxxxx" & DSN & ";uid=xxxxx" & UID & ";password=xxxxx" &
PASSWORD

'I'm not sure why you left those x's in there ... you were
'supposed to replace them with your own information.

p_oConn.open sDSN
Set GetConnection = p_oConn
end function

All you need to do is change it to:

function GetConnection()
const Server= "SQLServerName"
const UID = "webuser"
const PASSWORD = "password"
const DB = "NameOfDatabase"

Dim p_oConn, sDSN
Set p_oConn = server.createObject("ADODB.Connection")
sDSN = "Provider=SQLOLEDb; Data Source=" & Server & _
"; user id=" & UID & ";password=" & PASSWORD

'The following two lines are for debugging. If you run into
'problems, uncomment them (remove the apostrophes) and
'run the page. Look at the resulting connection string in the
'browser. If that does not reveal your problem, post the result
'here. Comment out these lines when finished debugging:
'Response.Write sDSN & "<br>"
'Response.End

p_oConn.open sDSN
Set GetConnection = p_oConn
end function

Replace SQLServerName with the name of the SQL Server, and replace
NameOfDatabase with the name of your database

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"- Hide quoted text -

- Show quoted text -

Nevermind I get it....I kept forgetting the top information when
actually coding it.(const Password...etc..) I'm sorry to bother
you....blah. It's been a week of nothing but pure stress. Anyway, I
never provided information regarding my actual connections mainly for
security reasons. I prefer not to provide the password and
connections to my database to the internet. Sure I could trust you
but that doesn't mean that someone else who comes across this won't
find it to be something else.

Thanks for your assistance and patience.