Solved

Problem Getting A Connection In ADO .Net

Posted on 2002-06-07
15
208 Views
Last Modified: 2013-11-23
I am trying to connect to an MSDE database using ADO .Net.

I am in a .vb file of a of an aspx project.

Here's the code I'm using:

Imports ADODB
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataAdapter

Dim objConn As SqlConnection
        objConn = New SqlConnection("data source=db_setup;initial catalog=db_setup;persist security info=False;user id=rwelsh;packet size=4096")

Public Sub Page_Load(ByVal Sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim objConn As SqlConnection
        objConn = New SqlConnection("data source=db_setup;initial catalog=db_setup;persist security info=False;user id=rwelsh;packet size=4096")

        Dim become As New SqlCommand()
        Dim JobsDA As SqlDataAdapter = New SqlDataAdapter()
        Dim JobsDS As DataSet = New DataSet()
        Dim objComm
        objComm = objConn.CreateCommand()
        objComm.CommandText = "SELECT * FROM tblMessaging"

        objConn.Open()

I keep getting an error: SQL Server does not exist or access is denied.

The database does exist, I can get into it though Access, WinSQL, etc.

I've also tried using the OleDB object, but I get the same error.

Also, I've also tried this code on a SQL Server 2000 database with the same error.

Has anyone had any luck with ADO .Net?  If you could provide any help I'd really appreciate it.

RichW
0
Comment
Question by:RichW
  • 8
  • 7
15 Comments
 
LVL 4

Author Comment

by:RichW
ID: 7063208
Ooops, forget the first Dim objConn statement.  That was a typo.
0
 
LVL 4

Author Comment

by:RichW
ID: 7063253
If I create an Access db, and link the SQL Server tables to that, I can get a connection using:

Dim oOleDbConnection As OleDb.OleDbConnection
        Dim sConnString As String = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=C:\billingProject\db_setupSQLServer.mdb;" & _
                 "User ID=Admin;" & _
                 "Password=;"
        oOleDbConnection = New OleDb.OleDbConnection(sConnString)
        oOleDbConnection.Open()

So, I'm just wondering, what is wrong with ADO .Net and SQL Server????
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7063327
0
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.

 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7063328
the "data source" argument must be filled with the name of your server
0
 
LVL 4

Author Comment

by:RichW
ID: 7063330
I did, emoreau.  That's exactly where I got the connection string for the Access db.  I saw your link in another question.

Thanks, but I have tried all of them and still get the error.

I went into VB 6 and used ADO 2.5, and I can connect to the SQL Server db no problem.

I've read that a bunch of people are complaining to Microsoft about ADO .Net's problems with SQL Server.

RichW
0
 
LVL 4

Author Comment

by:RichW
ID: 7063345
You're right.  I did do it correctly.  I made another typo online.  Thanks though!
RichW
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7063348
Have you tried using "integrated security"?

I have no problem connecting to SQL 2000 using this connection string:
"Data Source=(local);Integrated Security=SSPI;Initial Catalog=Portfolio_FMC"

And I use it this way:

    Public Function ExecSQL(ByVal pstrSQL As String, _
                            Optional ByRef pintRA As Integer = 0) As Boolean
        Dim objCN As New SqlConnection(ConnectionString)
        Dim objCmd As New SqlCommand(pstrSQL, objCN)

        Try
            objCmd.Connection.Open()
            pintRA = objCmd.ExecuteNonQuery()
            objCmd.Connection.Close()
            ExecSQL = True
        Catch ex As Exception
            ExecSQL = False
        Finally
            objCmd.Connection.Close()
        End Try
    End Function
0
 
LVL 4

Author Comment

by:RichW
ID: 7063383
emoreau,

I used your function and I still get the same error:


SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

Source Error:


Line 87:         Dim objCN As New SqlConnection(objConn.ConnectionString)
Line 88:         Dim objCmd As New SqlCommand(pstrSQL, objCN)
Line 89:         objCmd.Connection.Open()
Line 90:         Try
Line 91:             objCmd.Connection.Open()
 

Source File: c:\inetpub\wwwroot\BillingTest\frmBillingMessaging.aspx.vb    Line: 89

Stack Trace:


[SqlException: SQL Server does not exist or access denied.]
   System.Data.SqlClient.SqlConnection.Open()
   BillingTest.WebForm1.ExecSQL(String pstrSQL, Int32& pintRA) in c:\inetpub\wwwroot\BillingTest\frmBillingMessaging.aspx.vb:89
   BillingTest.WebForm1.Page_Load(Object Sender, EventArgs e) in c:\inetpub\wwwroot\BillingTest\frmBillingMessaging.aspx.vb:53
   System.Web.UI.Control.OnLoad(EventArgs e)
   System.Web.UI.Control.LoadRecursive()
   System.Web.UI.Page.ProcessRequestMain()

 
I can't figure it out!  I can get to it from Access and other tools, so I know the db is there with the data.

Thanks again.
RichW
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7063395
Have you tried using "integrated security"?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7063400
where have you set your password?
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 7063402
db_setup is the name of your server?
0
 
LVL 4

Author Comment

by:RichW
ID: 7063677
Hi emoreau,

Yeah, db_setup is the database name.

I'm VERY familiar with ADO, and ADO .Net isn't too far of a stretch from it, so I'm starting to think it's a security issue.

I also tried connecting to a SQL Server 2000 db and I get the same exact error.

I'm going to try it on my system at home and see if I get the same results.

I have tried integrated security.  I've tried just about everything I can find out there, but I still get the same error.

I can get a connection on Access and MySQL, but for some reason I get the error with SQL Server.

RichW
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 150 total points
ID: 7063687
I asked: db_setup is the name of your server?
You answer: Yeah, db_setup is the database name

database name and server name are 2 different things!

Your connection string should look like this:
objConn = New SqlConnection("data source=YourServerName;initial catalog=db_setup;persist security
info=False;user id=rwelsh;packet size=4096")
0
 
LVL 4

Author Comment

by:RichW
ID: 7064197
I'm sorry, emoreau, I thought I said earlier that I had made a typo.  The datbase name is db_setup, the server name is richardwelsh\VSdotNET.  I realize I typed it wrong earlier, and was just responding that db_setup is the db name.

I'm on MAJOR sinus medications that are making me loopy.  lol

There were three separate questions when I came back into the thread, and the "yeah" was in response to a previous posting question of security, and I then answered in the same sentence the other question of the db name.

I assure you it's not something as simple as entering the wrong data source.

As I said, I can get a connection wtih Access and MySQL, but not SQL Server.  I've just loaded MSDE on my XP box at home, and I'm going to try it here.  I will let you know how I make out.

Thanks for hanging in there with this one.
RichW



0
 
LVL 4

Author Comment

by:RichW
ID: 7069638
emoreau, thanks for your help.  I figured out that I had to configure the ASP.NET worker process to run under the SYSTEM account in the <processModel> section of the Machine.config file.  

Because of security concerns, the ASP.NET worker process runs under the default ASPNET account. If you do not enable impersonation for an application, all of the threads that run the requests for that application run under the process account.

This problem occurs because the ASPNET account does not have sufficient permissions to connect to or write to an Access database.

For security reasons, Microsoft recommends that you enable impersonation on your ASP.NET application. This method works if the impersonated user has necessary permissions to the computer and the database that you are accessing.

Since you are the only one who responded and you hung in there with me, I'm giving you an A and I upped the points.

Thanks again!
RichW


0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

828 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