Solved

Problem Getting A Connection In ADO .Net

Posted on 2002-06-07
15
200 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
Comment Utility
Ooops, forget the first Dim objConn statement.  That was a typo.
0
 
LVL 4

Author Comment

by:RichW
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
0
 
LVL 69

Expert Comment

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

Author Comment

by:RichW
Comment Utility
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
Comment Utility
You're right.  I did do it correctly.  I made another typo online.  Thanks though!
RichW
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Author Comment

by:RichW
Comment Utility
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 69

Expert Comment

by:Éric Moreau
Comment Utility
Have you tried using "integrated security"?
0
 
LVL 69

Expert Comment

by:Éric Moreau
Comment Utility
where have you set your password?
0
 
LVL 69

Expert Comment

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

Author Comment

by:RichW
Comment Utility
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 69

Accepted Solution

by:
Éric Moreau earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now