Recordsets in VB.NET

Question

All,

Trying to implement a recordset approach in VB.NET and (with the wonderful help of this community) have gotten to the point where things almost run. :)

Code:

Dim strSQL As String
Dim rsMaster As New ADODB.Recordset
strSQL = "select * " & "from tblDQ " & "order by xid, xcode, xDOS"
rsMaster.Open(strSQL, objConn, adOpenForwardOnly, adLockOptimistic)

The last line throws an exception while attempting to execute the rsMaster.Open line:

COM Exception was unhandled

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Any ideas? Changing the cursor type or lock type doesn't seem to do any good, and the types specified above are available as options when coding the line. I've (on a WAG) added the following lines to the project:

Imports ADODB.LockTypeEnum Imports ADODB.CursorTypeEnum

with no luck.

TIA!

Dave

Answer 1

Some ideas:

• Does the SQL statement work in a "pure" database tool? (The &s looks a bit strange, but I assume that's a formatting issue in SO.)
• The select * should work, but try a specific simple column (a short string or an integer), maybe there is an issue with a "tricky" column data type.
• Do you have the objConn declared and a proper connection opened?

---

Some more ideas:

• As you get the error when you execute the statement it seems that there is a problem with your statement. Is there a really simple SQL statement that will work in your scenario? (no order by, just a column, ..)
• Try the statement from within MSAccess using the same connection parameters you are using in your VB.NET program. Maybe you are using a different user from within your program, maybe you have to qualify the table name etc.
• If you say the connection is working fine, what did you do using the connection? Was there anything requiring access to the DB?

Answer 2

Ok, if removing the adOpenForwardOnly and the adLockOptimistic parameters doesn't change anything, how about changing the code slightly to use the Execute Method of the Connection object?

rsMaster.Open(strSQL, objConn, adOpenForwardOnly, adLockOptimistic)

Becomes

rsMaster = objConn.Execute(strSQL)

Answer 3

I'm not familiar with VB.Net, but in Access 2007 using parentheses with ADO recordset open is interpreted as a syntax error. Try with no parentheses:

rsMaster.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic

Update: Sorry, my Access experience doesn't apply to ADO.NET.

I looked in the MS Knowledge Base, and wonder whether this version of your code would work.

Dim strSQL As String
Dim rsMaster As New ADODB.Recordset()
strSQL = "select * from tblDQ order by xid, xcode, xDOS"
rsMaster.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rsMaster.CursorType = ADODB.CursorTypeEnum.adOpenForwardOnly
rsMaster.LockType = ADODB.LockTypeEnum.adLockOptimistic
rsMaster.Open(strSQL, objConn)

Answer 4

Not sure about your connection. You have an Access tag, but mentioned you tested in SQL Server. If you are using an access .mdb file

Imports adodb

Public Class Form1

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim strSQL, strConn As String
        Dim rsMaster As New ADODB.Recordset
        Dim objConn As New ADODB.Connection

        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DQ\DQ.mdb"
        objConn.Open(strConn)

        strSQL = "select * " & "from tblDQ " & "order by xid, xcode, xDOS"
        rsMaster.Open(strSQL, objConn, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockOptimistic)
        rsMaster.MoveFirst()
        Me.Text = rsMaster("xcode").Value


        rsMaster.Close()
        rsMaster = Nothing

    End Sub
End Class