Problem with ADO connection to SQL database attached to project

StevenF · May 9th, 2009, 11:34 AM

Hi,
I am trying to use ADO to pull data from a database into an ADO recordset, but I am running into a problem that I think is related to the connection string. I have already implemented code that is pretty much identical in an Excel application, where I also connect to an SQL database with ADO. In Excel VBA, this code works perfectly. The main difference between the Excel VBA code and the ASP.Net code is the connection string, which is why I suspect this is a connection string problem.

To test the functionality I want to achieve, I have added the Northwind database to the App_Data folder in my project. I then want to use an SQL query to pull out all data from a table in this database.

The code runs fine until I try to open the connection – when I execute the “.Open()” code (see below). Then I get this error message: “Multiple-step OLE DB operation generated errors”.

It would be great if I could get some pointers about why I experience this problem, and how I can resolve it.

Below are:
- the database connection as it appears in my Web.config
- The code I use, which results in an error
- The detailed error message I receive

-------

Web.config Connection entry

<add name="NORTHWNDConnectionString1" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirector y|\NORTHWND.MDF;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />

Code

Code:

Imports System.Data
Imports System.Data.OleDb
Imports ADODB

Partial Class ADO_Practice
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim Connection As ADODB.Connection
        Dim Rset1 As ADODB.Recordset
        Dim strSQL1 As String
        Connection = New ADODB.Connection
        Rset1 = New ADODB.Recordset

        With Connection
            .ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;User Instance=True"
            .CursorLocation = CursorLocationEnum.adUseClient
            .Open()
        End With

        strSQL1 = "SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]"
        Rset1.Open(strSQL1, Connection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly)

        'Do activities with data in the ADO Recordset Rset1

    End Sub
End Class

Error:
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

System.Runtime.InteropServices.COMException was unhandled by user code
ErrorCode=-2147217887
Message="Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
Source="Provider"
StackTrace:
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
at ADO_Practice.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\stevenf\My Documents\IT & Technical\ASP.NET practice\Practice sites\MenuControlPractice\ADO_Practice.aspx.vb:lin e 19
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:

Imar · May 10th, 2009, 03:42 AM

Hi there,

There's probably something wrong with your connection string. I tried yours and got the same error. I don't think the SQLNCLI10 library supports all the features you are using, like DataDirectory. I could make it work by pointing to the physical address of the database, marking the connection as trusted and adding the Database attribute. I used this connection string:

Code:

 
.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;
          AttachDbFilename=D:\Databases\PlanetWrox.mdf;
          Trusted_Connection=Yes;User Instance=True"

You can take a look here: http://www.connectionstrings.com/ for more examples of connection strings.

But is there a reason why you are using ADO? ADO is a legacy technology from the COM world. You're using a .NET wrapper around this COM technology. but under the hood it's still old technology. .NET has much better options in the System.Data.SqlClient like the SqlConnection, SqlDataReader and so on.

Take a look at the ADO.NET quick starts to learn more about ADO.NET, in case your choice for classic ADO is by accident: http://www.dotnetjunkies.com/quickst...sOverview.aspx

Cheers,

Imar

StevenF · May 18th, 2009, 05:36 AM

Hi Imar,

The reason I was using ADO is that I was not aware that this was an outdated approach (!). My general .Net knowledge is still developing... I have now read up on ADO.Net, and it looks very promising. I have updated my Excel-based ADO code with some of the new tools such as the datareader, dataadapter, and commandbuilder, and now it works fine.

However, the experience of having used ADO, and being exposed to LINQ and ADO.Net made me wonder about another question: Is it safe to assume that technologies build on each other, so that the most recent is always the best? Clearly, ADO.Net is better than ADO, but is LINQ (which I assume is the most recent), also better than ADO.Net? Or, is it the case that different data access technologies are more or less appropriate in certain scenarios? If so, do you know about any resources that describe the relative strenghts of different approaches?

Finally, I just wanted to say thank you for writing such a great book on "Beginning ASP.Net"! I have been working my way through it (about 80% there), and I think it is the best "Introduction to IT topic XYZ" that I have come across. Great work!

Steven

Imar · May 24th, 2009, 11:16 AM

Hi Steven,

First of all, glad to hear you like the book so much!

With regards to technologies: sometimes they extend each other and sometimes they are a replacement. Newer isn't always better as some technologies can be used for different scenarios.

For example, LINQ to SQL is great to quickly build data driven applications, but it may not be suitabe in all situations. So, LINQ to SQL doesn't replace ADO.NET but it's "just another option". However, LINQ to SQL will eventually be replaced with the ADO.NET Entities Framework, its bigger (and newer) brother. It looks like Microsoft is not going to actively develop LINQ to SQL anymore but instead invest in the entities framework. So, in this case, newer *is* better as LINQ to SQL will not evolve, while the Entities Framework will (there's a lot of new stuff coming for .NET 4.0)

Hope this helps,

Imar

StevenF · June 8th, 2009, 10:54 AM

Thanks very much for your thoughts on data access technologies - very useful background info to be aware of as I navigate the options that are available now (and those we can expect to be available soon).