Question Problems with ADODB Recordset being ReadOnly

[Mudplugger]

Hi Guys,

I am new here, but have over 10 years coding experience with VB and VBA. I decided it was probably about time I branched out and as VB.net was the closest to my skill set I am starting there.

I have an Access 2000 mdb app to port into VB.net & SQL Server Compact as a training excercise and I am already haveing trouble. I have decided to try to walk before running so rather than trying to learn VB.net and ADO.net at the same time I wanted to stick with ADO 2.7 as I am familiar with that.

So to the problem - whenever I open an ADO recordset regardless of the datasource (.mdb or .sdf) the recordset only opens as ForwardOnly and ReadOnly. I know this is the default state for an ADO recordset, but changing the .LockType, .CursorType or .CursorLocation properties makes no diference to the recordset. I have confirmed that both connections (.mdb & .sdf) are "Mode=ReadWrite" but I still can't get a writable recordset.

Can anyone help me please, I'm getting desperate.

Module Module1
    Dim oMDBConn As ADODB.Connection
    Dim oSDFConn As ADODB.Connection

    Public Sub OpenMDB()
        Dim strDBPath As String

        If oMDBConn Is Nothing Then
            strDBPath = "C:\Users\Dave\Documents\Visual Studio 2010\Databases\vesta.mdb"
            oMDBConn = New ADODB.Connection

            With oMDBConn
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .Mode = ADODB.ConnectModeEnum.adModeReadWrite
                .Open("Data Source=" & strDBPath)
            End With
            MsgBox("MDB Open = " & CStr(oMDBConn.State = 1))
        Else
            MsgBox("Connection is already open")
        End If
    End Sub

    Public Sub OpenSDF()
        Dim strDBPath As String

        If oSDFConn Is Nothing Then
            strDBPath = "C:\Users\Dave\Documents\Visual Studio 2010\Databases\VestaCerts.sdf"
            oSDFConn = New ADODB.Connection

            With oSDFConn
                .ConnectionString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Users\Dave\Documents\Visual " & _
                                    "Studio 2010\Databases\VestaCerts.sdf;SSCE:Database Password=Vesta;"
                .Mode = ADODB.ConnectModeEnum.adModeReadWrite
                .Open()
            End With
            MsgBox("SDF Open = " & CStr(oSDFConn.State = 1))
        Else
            MsgBox("Connection is already open")
        End If
    End Sub


    Public Function GetRecordSet(ByVal sSource As String, ByVal oDBConn As ADODB.Connection) As ADODB.Recordset
        Dim oRs As ADODB.Recordset

        oRs = New ADODB.Recordset
        With oRs
            .ActiveConnection = oDBConn
            .Source = oDBConn.Execute(sSource)
            .LockType = ADODB.LockTypeEnum.adLockOptimistic
            .CursorType = ADODB.CursorTypeEnum.adOpenKeyset
            .CursorLocation = ADODB.CursorLocationEnum.adUseClient
            .Open(CursorType:=.CursorType, LockType:=.LockType)
        End With
        GetRecordSet = oRs
        oRs = Nothing
    End Function

    Public Sub CloseDatabase(ByVal oDBConn As ADODB.Connection)
        If Not oDBConn Is Nothing Then
            If oDBConn.State = ADODB.ObjectStateEnum.adStateOpen Then
                oDBConn.Close()
            End If
        End If
    End Sub

    Public Sub MigrateSkills()
        Dim oRSsdf, oRSmdb As ADODB.Recordset
        Dim strSQLsdf, strSQLmdb As String

        strSQLmdb = "SELECT * FROM Attachment"
        strSQLsdf = "SELECT * FROM Attachment"

        oRSmdb = GetRecordSet(strSQLmdb, oMDBConn)
        If Not oRSmdb.EOF Then
            MsgBox("A Recordset!!")
            oRSmdb.Close()
        End If

        oRSsdf = GetRecordSet(strSQLsdf, oSDFConn)
        If Not oRSsdf.EOF Then
            MsgBox("A Recordset!!")
            oRSsdf.Close()
        End If

    End Sub

End Module

Thanks in advance.

 

[Mudplugger]

64 views and not 1 reply, have I got everyone stumped

Cheaky I know, but could I ask someone to paste my code into a project of their own, and test if they can get a readwrite recordset. At least that way I would know if the problem is machine specific. As I am using two databases and two differenct Drivers/Providers I find it unlikely that it would be a provider error. So I'm guessing it's either a code problem (most likely, as I'm new to .net) or a machine/framework problem.

If someone else could test the code for me or at least tell me the code is right then maybe I could rule a few things out.

Thanks again.

 

[Mudplugger]

Resolved!

OK, I figured out a solution.

It appears that no matter what properties of the Recordset object you set prior to calling the .Open method, if you don't pass the Source argument into the .Open method everything else gets ignored and set to its default value.

This also applies it you pass the CursorType and LockType arguments in without passing in the source argument.

So the easiest fix appears to be, don't bother setting properties just pass all the arguements into the .Open method.

 

[JohnH]

64 views and not 1 reply, have I got everyone stumped

Nobody should be interested in using Adodb and recordsets in VB.Net. The ADO technology in .Net is called ADO.Net.
ADO.NET Overview
Benefits of ADO.NET

 

[Mudplugger]

Nobody should be interested in using Adodb and recordsets in VB.Net. The ADO technology in .Net is called ADO.Net.
ADO.NET Overview
Benefits of ADO.NET

As I said in my original post, I am trying to learn one thing at a time, but thanks for not offering anything constructive.

 

[JohnH]

As I said in my original post, I am trying to learn one thing at a time, but thanks for not offering anything constructive.

You wondered why you didn't get any replies, and that is the reason. I hope the links offer you a good start when you get time to learn ADO.Net. Here's also much good stuff: Data Walkthroughs

 

[Mudplugger]

The question was rhetorical, hence no question mark, but no matter.

Thanks for the links they will come in handy. There is also a MS Press Step by Step ADO.NET due out later this year that I have my eye on.

 

[cjard]

64 views and not 1 reply, have I got everyone stumped

No.. I just hadn't seen the thread, and I usually do have something to say about data access

I can totally appreciate that you want to cling to at least a few familiar aspects while you venture into the new territory of VB.NET etc, but trust me, using ADODB is NOT the way to go. It's still in the language to provide legacy compatibility with old programs, because Microsoft wanted VB6 developers to be able to paste their old code into a new project and sort of have it work - i.e. VB.NET is an "upgrade" to VB6..

Thing is, it isnt and they must have had so many problems making it a warm and fuzzy place for upgrading VB6ers not to be scared off, because VB6 is just plain horrible

John has succinctly told you to ditch ADODB, I'll tell you why:

If I had an access database that I wanted to read and write I would do this:

Make a new project
"Add an existing file"
Choose my access DB
Say yes I want it copied to the project directory
A dataset wizard would appear
I would choose the tables I want to use and finish the wizard
A dataset designer would appear where I can see a rendition of my database tables (important: this is NOT the database, it is a local only data container LIKE A RECORDSET, that holds data while we push it back and forth between the db and the app using tableadapters as the push/pull devices)
I would go back to my form, then show the Data Sources window in visual studio
I would drag the node I wanted to see on my form out of DataSources, onto the form
Familiar devices of grids, navigator buttons etc would appear
I would press play to run my app

The app would read the data in the db, and save it back again and I didnt write a single line of code - vs wrote it all. All that code up there that took minutes to write, I just had VS do in seconds: it works, it's more secure than yours and it's following best Object Oriented programming practice. I can get a database-driven app up and running in less time than it takes you to write a post about how your ADODB recordset code doesnt work

There is no magic; you can see all the code the wizard writes, if you want. You can still manage your updates and inserts precisely if you want. It uses more high performance locking techniques so you dont get deadlocks.. etc etc

Truly, let the old world go and work through the "Creating a Simple Data Application" tutorial in the DW3 link of my signature - written by Microsoft, for people in your situation

 

[Mudplugger]

Thanks for the advice and links. As you said I am venturing into new teritory and want to lessen my learning curve at this point. For the first pass through this project I already have several targets I want to hit, schema redesign, form redesign, port from mdb to sdf plus some new funtionality that will improve usability.

I wrote the access app quite a few years ago now for a client and as his business has grown so the requirements for the app have grown, so it's been tweaked and prodded and pulled in directions the orginal design really wasn't intended for.

The ADODB stuff was to aid in the migration of data from the old mdb schema to the new redesigned sdf schema and I didn't want to have to manually enter 40,000 records in order to get the data to fit the new schema. So I needed it to be quick, dirty and not require me to have to start with ADO.NET yet. But that part is done now.

The rest of this prototype will continue to use ADODB just so I don't have to worry about that side of it and can concentrate on the VB.net stuff. Once I am happy with that the second pass, if you like, will be ADO.NET and I can start to get to grips with things like DataBinding, TableAdapters, DataAdapters, DataRows, DataSets, etc.etc.etc.

I did look at ADO.net when I started this, but after a couple of introdutory tutorials I had so many questions I decided I needed to get on with the vb.net and come back to the ADO.net after.

Thanks again, though.

 

[cjard]

I did look at ADO.net when I started this, but after a couple of introdutory tutorials I had so many questions I decided I needed to get on with the vb.net and come back to the ADO.net after

You'd be better off asking many questions about a widely used, modern, understood technology than asking a few about an archaic one that (a person like me) hasn't touched since ditching VB6 nearly 7 years ago. I reckon I could answer most of them in a few minutes whereas it will take hours of googling to find antique solutions..

It's the old "if I had a week to cut down a tree, I'd spend 5 days sharpening an axe" thing.. When I started ADO.NET it was new, and noone knew the answers to my questions so it was hours of googling and stabbing in the dark, disassembling the .NET framework. I wouldnt wish that on anyone; a few of us here have since become fairly solid on features like ADO.NET so that frustration is gone. Lot to take in for sure, but mixing the old and the new, when they don't work together as conducively as the new and the new is ultimately counterproductive and limits your performance