dhquest
asked on
How do you specify a DSN name for an ODBC.OdbcConnection object?
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=My
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(?,?,?
Dim retVal As String
cmd.CommandType = CommandType.StoredProcedur
cmd.Parameters.Add("@Email
cmd.Parameters.Add("@UserP
cmd.Parameters.Add("@UserI
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]Connectio
Any suggestions?
Public strCnn = (""PROVIDER=MSDASQL;DSN=My
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(?,?,?
Dim retVal As String
cmd.CommandType = CommandType.StoredProcedur
cmd.Parameters.Add("@Email
cmd.Parameters.Add("@UserP
cmd.Parameters.Add("@UserI
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]Connectio
Any suggestions?
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
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
ASKER
rdivilbiss
"UID=<username>;PWD=<stron
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?
"UID=<username>;PWD=<stron
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?
ASKER
I also went to www.connectionstrings.com (very helpful by the way, thanks) and copied the example:
"DSN=myDsn;Uid=<myusername
No Luck, still says that:
System.Web.HttpUnhandledEx
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]Connectio
at System.Data.Odbc.OdbcConne
But when I set up the DSN and test it, no problems...
"DSN=myDsn;Uid=<myusername
No Luck, still says that:
System.Web.HttpUnhandledEx
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]Connectio
at System.Data.Odbc.OdbcConne
But when I set up the DSN and test it, no problems...
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.
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.
ASKER
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.
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.
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)
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)
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Error:
System.Web.HttpUnhandledEx
I have access to the server, and I double checked all the security rights.
System.Web.HttpUnhandledEx
I have access to the server, and I double checked all the security rights.
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?
Are you able to drill down using the server explorer and see the list of tables in your database?
can you try a simple select * from table query using the microsoft query analyzer (QA has you enter your credentials - just one more validation)
ASKER
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
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
that seems wierd.
drill down into the generated code
look for a line like this:
//
// sqlConnection1
//
this.sqlConnection1.Connec
"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
drill down into the generated code
look for a line like this:
//
// sqlConnection1
//
this.sqlConnection1.Connec
"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
ASKER
Following is the string it created
"workstation id=""PGRM-1"";packet size=4096;user id=MyUserName;data source=MyServerName;persis
Does this look right?
"workstation id=""PGRM-1"";packet size=4096;user id=MyUserName;data source=MyServerName;persis
Does this look right?
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:
https://www.experts-exchange.com/help.jsp#hs5
WMIF
EE Cleanup Volunteer
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:
https://www.experts-exchange.com/help.jsp#hs5
WMIF
EE Cleanup Volunteer
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=MyDS