MSHFlexgrid sort issue on remote conn to SQL

03-15-2016, 12:09 AM

pizzaguy

Regular

Join Date: Apr 2005

Posts: 54

MSHFlexgrid sort issue on remote conn to SQL

Weird, probably simple question to answer:

I have a flexgrid sort that works when app is connected to local sql server, but when conn remotely the sort no longer works?

TIA

03-17-2016, 01:55 AM

pizzaguy

Regular

Join Date: Apr 2005

Posts: 54

anyone?

03-17-2016, 04:35 AM

Flyguy

MSHFlexgrid sort issue on remote conn to SQL

Lost Soul

Retired Moderator
* Guru *

Join Date: May 2001

Location: Vorlon

Posts: 19,174

can you share some code?

03-17-2016, 10:29 AM

pizzaguy

Regular

Join Date: Apr 2005

Posts: 54

Yes, I'm sorry. Here you go:

Code:

Private Sub RefreshInvoiceMaster()

    Call FormatInvoiceMasterGrid
    
    Dim CN As ADODB.Connection, RS As ADODB.Recordset, sSQL As String
    Set CN = New ADODB.Connection: Set RS = New ADODB.Recordset
    CN.CursorLocation = adUseClient
    RS.CursorLocation = adUseClient
    
    CN.Open ConnStr
    
    sSQL = "Select * FROM InvoiceMaster;"
    
    Set RS = CN.Execute(sSQL)
    
    fgInvoiceMaster.Redraw = False: fgInvoiceMaster.Rows = 2: fgROW = 0
    
    Do While Not RS.EOF()
        fgROW = fgROW + 1
        fgInvoiceMaster.Rows = fgInvoiceMaster.Rows + 1
    
        For fgCOL = 0 To RS.Fields.Count - 1
            fgInvoiceMaster.TextMatrix(fgROW, fgCOL) = RS(fgCOL).Value
            If fgCOL = 1 Then fgInvoiceMaster.TextMatrix(fgROW, fgCOL) = "$" & Format(fgInvoiceMaster.TextMatrix(fgROW, fgCOL), "###,###.00")
        Next fgCOL
        
        RS.MoveNext
    Loop
    
    fgInvoiceMaster.ColSel = 0
    fgInvoiceMaster.Sort = flexSortStringDescending
    fgInvoiceMaster.Redraw = True
    
End Sub

Like I said, the sort works locally, but not when I connect remotely. I have tried with/without adUseClient.

Thanks.

03-17-2016, 03:42 PM

pizzaguy

Regular

Join Date: Apr 2005

Posts: 54

I fixed it by changing to the following for dates (which is what was not working):

Code:

                If RS(fgCOL).Type = adDBDate Then
                    .TextMatrix(fgROW, fgCOL) = Format(RS(fgCOL).Value, "yyyy-mm-dd")
                Else
                    .TextMatrix(fgROW, fgCOL) = RS(fgCOL).Value
                End If