retuning a recordset ? record count = 0? 1

[BOBOBOB1]

Having a little trouble returning a record count? ? - I need to return the following SQL statement and when I step through the code I get > BOF or EOF is true....

and the record count property = 0

things seem to be pointing towards the SQL statement ?

can anyone see a funny in the SQL?

By the way the variable MainFCode is a string


Any ideas would be appreciated

Set rst3 = New ADODB.Recordset 'create a new instance of the rst3 recordset

'Define the SQL statement that will get the "Sub-catogory's"

strSQL_3 = "SELECT tblSubCodes.Codes,tblSubCodes.Desc " _
& "From tblSubCodes " _
& "WHERE (((tblSubCodes.Codes) " _
& "Like" & " " & "'" & MainFCode & "*" & "'" & "));"

'Get the "Sub-catogory's"

If Not rst3.State = adStateOpen Then

rst3.Open strSQL_3, GetCon, adLockOptimistic, adCmdText

With rst3
.MoveLast
.MoveFirst
CRecords3 = rst3.RecordCount
End With

End If


Cheers

 

[HarleyQuinn]

Hi,

I can't see anything wrong with the actual SQL.

I know this sounds like a silly question but are you sure the MainFCode parameter that you are passing in actually returns any records??

Cheers

Harleyquinn

 

[zemp]

Some cursor types do not return a recordcount. Your cursor type is not specified so the default is being used. Try,

rst3.Open strSQL_3, GetCon, adOpenStatic, adLockOptimistic, adCmdText

zemp

 

[Golom]

You are using ADO for you data access method but you are using DAO mask characters. In DAO the mask characters for LIKE are "*" (any number of characters) and "?" (single character). The ADO equivalents are "%" and "_". You need SQL like

strSQL_3 = "SELECT tblSubCodes.Codes,tblSubCodes.Desc " _
& "From tblSubCodes " _
& "WHERE (((tblSubCodes.Codes) " _
& "Like '" & MainFCode & "[b][COLOR=red]%[/color][/b]'));"

 

[dilettante]

Golom, I notice you did a little optimization there too.

People don't always seem aware, but sadly VB 6 does not optimize out the concatenation of adjacent string literals within a string expression. Just compile a small example to native code and examine the EXE to prove it to yourself.

Even your cleaned-up, corrected version still does 3 completely useless string concat operations that eat time and lead to string-pool garbage collection penalties. That said, I'm no fan of 300-character long source lines either. What's a mother to do?

The use of stuff like [tt]"abc" & Chr$(34) & "def"[/tt] instead of the more correct [tt]"abc""def"[/tt] tends to drive me nuts. This is something else the compiler could optimize and then I'd have less heartburn over it - though it stills stinks from a readability standpoint. If obscurity makes one feel empowered why not write in C? ;-)

I just wish the compiler was smart enough to clean this up for us as it should.

 

[Golom]

dilettante

Yeah ... you're right. There's a definite penalty for doing "unnecessary" concatenations in code because string operations are notoriously slow and subject to the various sins that you mentioned. That said ... I'm probably a prime offender!

Given that I am the one that has to come back later (months or years later) and try to figure out what

(Select CL_Code as [Code], FirstName as [First], LastName  as [Last], PhoneString as [Phone], Cl_Postal as [Postal], LO_NUM as [Loyalty] From Client Where Cl_ARCust = True AND Cl_Code LIKE '" & SS & "*';) UNION ALL (Select CL_Code as [Code], FirstName as [First], LastName  as [Last], PhoneString as [Phone], Cl_Postal as [Postal], LO_NUM as [Loyalty] From Client Where Cl_ARCust = True AND  AND Cl_Name LIKE '*" & SS & "*';)
[b][COLOR=red]*** and many more clauses ***[/color][/b]

Really does

I prefer to see it as

WhereClause = " TRUE = TRUE "

    If mvarAROnly Then
        WhereClause = WhereClause & " AND Cl_ARCust = True "
    End If

    If mvarBusinessOnly Then
        WhereClause = WhereClause & " AND BusinessCustomer = True "
    End If

    If mvarExempt Then
        WhereClause = WhereClause & " AND TaxExempt = True "
    End If

    SS = Replace(txtSearch.Text, "'", "''")

    SelectClause = _
        "(Select CL_Code as [Code], " & _
        "       FirstName as [First], " & _
        "       LastName  as [Last], " & _
        "       PhoneString as [Phone], " & _
        "       Cl_Postal as [Postal], " & _
        "       LO_NUM as [Loyalty] "
    
    If Len(SS) > 0 Then

        SQL1 = SelectClause & _
        "From Client " & _
        "Where " & WhereClause & " AND Cl_Code LIKE '" & SS & "*';) "

        SQL2 = SelectClause & _
        "From Client " & _
        "Where " & WhereClause & " AND Cl_Name LIKE '*" & SS & "*';) "

        SQL3 = SelectClause & _
        "From Client  " & _
        "Where " & WhereClause & " AND PhoneString LIKE '*" & SS & "*' ;)"

        SQL4 = SelectClause & _
        "From Client  " & _
        "Where " & WhereClause & " AND Lo_Num LIKE '" & SS & "*' ;)"

        SQL = SQL1 & " UNION ALL " & SQL2 & " UNION ALL " & _             SQL3 & " UNION ALL " & SQL4

I just hope the compiler and OS cats are getting more adept at making up for my inefficiencies.