Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 9th, 2009, 11:34 AM
Authorized User
 
Join Date: Apr 2009
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default Problem with ADO connection to SQL database attached to project

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:
  #2 (permalink)  
Old May 10th, 2009, 03:42 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
The Following User Says Thank You to Imar For This Useful Post:
StevenF (May 18th, 2009)
  #3 (permalink)  
Old May 18th, 2009, 05:36 AM
Authorized User
 
Join Date: Apr 2009
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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
  #4 (permalink)  
Old May 24th, 2009, 11:16 AM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
  #5 (permalink)  
Old June 8th, 2009, 10:54 AM
Authorized User
 
Join Date: Apr 2009
Posts: 11
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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).
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL Server Connection and Database Problem workib C# 1 November 13th, 2008 03:26 PM
ADO Connection problem when not logged in as an Ad Aaron Edwards Access VBA 1 January 3rd, 2008 08:43 AM
Global ADO Connection to SQL ? debbiecoates VB Databases Basics 2 October 29th, 2007 03:22 AM
ADO Connection between 2 MS Access Database Scripts82 Access VBA 2 February 8th, 2006 02:35 AM
Convert ADO connection to DAO database dr_morose Access VBA 0 November 17th, 2004 05:44 PM



All times are GMT -4. The time now is 04:10 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.