[RESOLVED] Multiple ADO recordset objects

[shantanu@india]

Hi,

I have a situation where many procedures of a form are using a ADO connection declared under the General section of the form code:

Option Explicit

Dim db As New ADODB.Connection
Dim rst As New ADODB.Recordset
.................................
.................................

Now inside one of the procedures I need to define a separate recordset object,say "rst1" using the same connection object "db".

Do I need to use the 'NEW' keyword to create a new instance of the recordset object variable inside the procedure or is it not necessary to use 'NEW'?

Private Sub readdata()

Dim rst1 As New ADODB.Recordset

or,

Private Sub readdata()

Dim rst1 As  ADODB.Recordset

Which of the two should I use & why?

Thanks in adavnce.

[Ellis Dee]

You should never Dim As New, but instead always use two lines of code:

Dim Variable As Object

Set Variable = New Object

And don't forget the cleanup!

Set Variable = Nothing

[shantanu@india]

Thanks for the help Ellis Dee.

[si_the_geek]

For a longer explanation of that, see the article Why shouldn't I use "Dim .. As New .."? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

As the variable is an Object type (in this case an ADODB.Recordset), it needs to be Set before you use it - usually with the New keyword as Ellis showed.

Note also that the recordset(s) and connection(s) are separate objects, and are not actually related as such - you associate them later (usually via the recordset.Open method), but they are not bound to each other in any way (you can use a recordset with one connection then re-use it with another, and/or use multiple recordsets with the same connection).

[shantanu@india]

Note also that the recordset(s) and connection(s) are separate objects, and are not actually related as such - you associate them later (usually via the recordset.Open method), but they are not bound to each other in any way (you can use a recordset with one connection then re-use it with another, and/or use multiple recordsets with the same connection).

Thanks for the explanation...I was looking for that.

[shantanu@india]

I also had a look at your thread at:

http://www.vbforums.com/showthread.php?t=511763

Thanks for the elucidation si_the_geek.

[shantanu@india]

I am trying to learn using SQL statements but have got stuck with the following piece of code:

Option Explicit


Private Sub Form_Load()
Dim db As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String

Dim steptimestring As String


'get steptime data for this particular step

Set db = New ADODB.Connection
Set rst = New ADODB.Recordset
db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\VB_programs\Star_vb\db11.mdb;Persist Security Info=False"
db.Open


sSQL = "SELECT * FROM rec2  WHERE  rec2.stepno = 1"
'sSQL = FormatSelect(sSQL)
Debug.Print sSQL
'MsgBox sSQL
rst.Open sSQL, db, adOpenStatic, adLockOptimistic

steptimestring = rst.Fields("iftime")
db.Close
rst.Close
Set db = Nothing
Set rst = Nothing

End Sub

The recordset opening command generates the following error message:
Run time error '-2147217913(80040e07)':
Data type mismatch in criteria expression

I have checked the syntax of the SQL statement with si_the_geek's Formatselect() function but of no avail.

Can somebody help?

Thanks.

[si_the_geek]

Good work, not many people go to the effort of running that function!

Unfortunately due to the complexities and variety of SQL (and no interaction with the database itself) it is only capable of finding fairly basic errors, so this kind of thing is a bit beyond it.


An explanation of what that error (and several other common ones) means, along with general advice to fix it, is shown in the article What does this error mean, and how do I fix it? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

In this case, the issue is that you are passing a number to compare with stepno, but apparently one or more values in the field (or the data type of the field, so all values) cannot be converted to a number - and so this error occurs when they are compared.

If the data type of the field is Text/Char, put single quotes around the value, eg: sSQL = "SELECT * FROM rec2 WHERE rec2.stepno = '1'"


By the way, this question should really have been posted as a new thread (as it isn't directly related to your original question), and preferably in the Database Development forum (as it is related to databases, and specifically SQL).

[shantanu@india]

Thanks Si_the_geek.....you are bang on target.