Getting Values out of a recordset

[theracer]

Hi,

I need to get values out of a recordset in order to filter a second sqlstring

I constructed a filter query and get the records out of it in the first SQLstring in order to use the values to filter the second SQLstring. Unfortunately, when I set CountryCurveSelect = recordset.Fields(C).Values, there are no items to be found...I also tried
with recordset.getrows but that also gives problems probably due to the
multidimensionality of the variant.

Here is what I set up

' Loop through the countries until predefined Number of Countries (NC) is
reached
For C = 1 To NC
   sqlstr = "Select [_Country Filter]![ID COUNTRY] from [_Country Filter]"
   Set rsread = db.OpenRecordset(sqlstr)
   rsread.MoveFirst
   CountryCurveSelect = rsread.Fields(C).Values
   rsread.Close

' Original curvesmoothing procedure   
   sqlstr = "Select * from [myTable] where [ID Country] = " &
CountryCurveSelect & ";"
   Set rsread = db.OpenRecordset(sqlstr)
   If rsread.BOF Then Resume Next   ' No records retrieved
   rsread.MoveLast
   ValueCount = rsread.RecordCount

etc...

Next C

What am I doing wrong?

 

[SydneyGeek]

I don't use ADO as much so I can't say categorically, but in this example I found no need to evaluate the parameters first.

Denis

 

[Norie]

Denis

I don't think I've had to do the parameter thing with DAO or ADO.

Always seemed to find it was just some SQL syntax error.

That's one reason I tend to 'build' the SQL like this.

Dim strSQL As String
 
     strSQL = strSQL & "SELECT Field1, Field2, Field2 "
     strSQL = strSQL & "FROM tblTheTable "
     strSQL = strSQL & "WHERE Field1 = '" & strCrit & "'"

Kind of helps when debugging, eg you can just comment out lines, replace bits quite easily.

 

[xenou]

    sSQL = "SELECT * FROM [My Table] WHERE [CountryID] In(SELECT TOP " & NC & " [CountryID] FROM [My Table])"
    Set qdf = DBEngine(0)(0).CreateQueryDef("", sSQL)
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm

Does this qdf have actual parameter objects in it? It looks like straight sql to me after the string value is assigned to sSQL. Interesting. What's being evaluated with Eval() here?

 

[SydneyGeek]

Norie

I tend to build longer SQL strings in rows like you said. Maybe I get carried away with it sometimes; today I had a sample that had too may line breaks, and the compiler wouldn't let me continue...
As for the syntax errors, yep, I get them too. But I have fixed run-time errors with that loop too many times to count now.

Xenou

No parameters, just criteria that are fed through as variables. I have had so many of these sorts of queries generate run-time errors that I tend to automatically evaluate them before opening the recordset.
It may be overkill, but it doesn't cause any slowdown that I can detect so I just do it.

Denis

 

[Norie]

Denis

I suppose if it works then that's the important thing.

I think I'd probably try and find what was causing the errors, then a few hours later give up as usual.

I'll file it away under the heading 'need too sort that out' - top priority of course.

 

[xenou]

I'll file it away under the heading 'need too sort that out' - top priority of course.

This is where I have this too, i think
It's good to know, though Thanks Denis - good tips.

ξ