Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How do you specify a DSN name for an ODBC.OdbcConnection object?

Posted on 2005-05-04
16
Medium Priority
?
330 Views
Last Modified: 2012-08-14
I am trying to open a connection to an MSSQL database using a DSN with the ODBC.OdbcConnection object and am using the following string:
Public strCnn = (""PROVIDER=MSDASQL;DSN=MyDSNName;"")

Here is the Code to attempt the data call:
Dim cnn As OdbcConnection = New Odbc.OdbcConnection(strCnn)
Dim cmd As OdbcCommand = New OdbcCommand("{CALL spCustAuthentication(?,?,?)}", cnn)
Dim retVal As String

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@EmailUserName", OdbcType.VarChar, 50).Value = mstrEmailUserName
cmd.Parameters.Add("@UserPssword", OdbcType.VarChar, 50).Value = mstrUserPssword
cmd.Parameters.Add("@UserID", OdbcType.VarChar, 100).Direction = ParameterDirection.Output

cmd.Connection.Open()  'Failure happens at this point with

This is the error I get:
ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute

Any suggestions?
0
Comment
Question by:dhquest
16 Comments
 
LVL 29

Expert Comment

by:rdivilbiss
ID: 13932081
>> access denied.

Where is your user id and password?  (Not that I would put them in code nor post them here, but I would indicate something so it was obvious to the experts I didn't forget.

"PROVIDER=MSDASQL;DSN=MyDSNName;DATABASE=pubs;UID=<username>;PWD=<strong password>;"




0
 
LVL 12

Expert Comment

by:fruhj
ID: 13932852
www.connectionstrings.com has a huge inventory of connection strings for all occasions and systems.

If you're using a hosted server that you don't have direct access to - then the DSN route might not be possible for you.

Please note that it's possible to setup a "DSN-Less" connection string either in code or in your web.config file and use it in your code - in that scenario you wouldn't need the DSN at all.

that might be better or worse - it all depends on how you feel about setting up a DSN on the server.

- Jack
0
 

Author Comment

by:dhquest
ID: 13936396
rdivilbiss

"UID=<username>;PWD=<strong password>;"
I was not sure if that was necessary, since the DSN is already configured with that information.  Is it necessary?  If so, do I not specify the UID and PW in the DSN?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:dhquest
ID: 13937247
I also went to www.connectionstrings.com (very helpful by the way, thanks) and copied the example:

"DSN=myDsn;Uid=<myusername>;Pwd=<mypassword>;"

No Luck, still says that:
System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
   at System.Data.Odbc.OdbcConnection.Open()

But when I set up the DSN and test it, no problems...
0
 
LVL 12

Expert Comment

by:fruhj
ID: 13937499
can you use one of the other connection strings at connectionstrings.com? I would try one of the SQL ones at the top of the page.

and I assume you replaced the <my...> values with the real thing right?

Make sure you set it up as a system DSN, and not a user DSN - the user DSN will only work for the user you set it up as, not for the I_User account that runs most anonymous IIS stuff.
0
 

Author Comment

by:dhquest
ID: 13938202
I will try one of the other connection strings.  Do I need to change the object type from Odbc?  I have experimented with some of the strings, but I will try all.

Just a note, I am not even able to get the localhost web to connect to the server (all on the same network).  Would there be something else that I should check?

As for the replacing of the values, yes.

Yes, it is a system DSN, not user.
0
 
LVL 12

Expert Comment

by:fruhj
ID: 13938352
It looks like you're using .net for development, and I assume your using SQL server 2000 right (you said MSSQL, not MYSQL right?)

I'll also assume you're not using visual studio? (if you are, there's a few drag and drop operations that make this easy)

For that I would use the sql specific elements
You'll need to import system.data.sqlclient
you can then use the sqlconnection (.net) examples on Connectionstrings.com
(actually they have sample code for declaring the connection too - have a look at that - only a few lines)





0
 

Author Comment

by:dhquest
ID: 13938552
Yes, .Net.
Yes, SQL Server 2000.
Yes, VS.Net (although I am brand new to it, background is in VB6)
Maybe you could shed some light on the easy drag and drop stuff?
0
 
LVL 12

Accepted Solution

by:
fruhj earned 400 total points
ID: 13938798
on the left side, you probably have a tab for "server explorer" (if not it's under the view menu)
click the little plus computer icon - this will help you create a connection to the SQL server.
now below you'll have that server listed under servers

you can expand that until you see tables
for now, just drag a table down to your aspx page (stored procedures are alittle more complex so lets get the table workign first)

A sql connection and sqlDataadapter object will be created automagically

now right click the sqldataadapter1 and choose "generate dataset" (choose new)

now lets link to a table so we can see if it worked.open the toolbox
tab on the left side (or from the view menu)

choose the webforms toolbox list
drag a datagrid to the aspx page
bring up properties for the DataGrid
Set datasource using the drop down list

now you're almost there
you need to add 2 lines of code still
sqlDataAdapter1.Fill(dataSet11)
DataGrid1.DataBind

(adjust to the names of your objects)

now hit play see the table

-----
(I'll have to look up a Stored procedure example tonight- cant do that one off the top of my head)

hit play


0
 

Author Comment

by:dhquest
ID: 13939239
Error:

System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

I have access to the server, and I double checked all the security rights.
0
 
LVL 12

Expert Comment

by:fruhj
ID: 13939390
is this a build error or a run time error?

Are you able to drill down using the server explorer and see the list of tables in your database?

0
 
LVL 12

Expert Comment

by:fruhj
ID: 13939413
can you try a simple select * from table query using the microsoft query analyzer (QA has you enter your credentials - just one more validation)
0
 

Author Comment

by:dhquest
ID: 13939980
Run time error, I think.
Yes, I can drill down and see the tables, views, and stored procs
Yes, I do a simple select and even run an SP in QA under the user configured to access the data via the DSN connection
0
 
LVL 12

Expert Comment

by:fruhj
ID: 13940271
that seems wierd.

drill down into the generated code
look for a line like this:
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=MYPC;packet size=4096;integrated security=SSPI;data source=MYPC;" +
"persist security info=True;initial catalog=Northwind";

this is what was generated when I did up the example I gave you.
the default is to use integrated security - which doesn't require your password to be entered - I think it uses the aspnet account or the current user account (can't remember which)

anyhow, you might copy and paste that line, comment one out, and modify the other using some examples from connectionstrings.com and see if you have better luck


0
 

Author Comment

by:dhquest
ID: 13973489
Following is the string it created

"workstation id=""PGRM-1"";packet size=4096;user id=MyUserName;data source=MyServerName;persist security info=False"

Does this look right?
0
 
LVL 22

Expert Comment

by:WMIF
ID: 15446376
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.  I will make a recommendation to the moderators on its resolution in a week or two.  I appreciate any comments that would help me to make a recommendation.

Unless it is clear to me that the question has been answered I will recommend delete without refund of points.  It is possible that a Grade less than A will be given if no expert makes a case for an A grade.  It is assumed that any participant not responding to this request is no longer interested in its final disposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help.jsp#hs5

WMIF
EE Cleanup Volunteer
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Integration Management Part 2
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

577 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