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.
- 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"
Partial Class ADO_Practice
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
.ConnectionString = "Provider=SQLNCLI10;Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;Integrated Security=True;User Instance=True"
.CursorLocation = CursorLocationEnum.adUseClient
strSQL1 = "SELECT [CategoryID], [CategoryName], [Description] FROM [Categories]"
Rset1.Open(strSQL1, Connection, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly)
'Do activities with data in the ADO Recordset Rset1
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
Message="Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
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.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)