Solved

Problem Getting A Connection In ADO .Net

Posted on 2002-06-07
15
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month5 days, 5 hours left to enroll

635 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