We help IT Professionals succeed at work.

Displaying data differently in a DBList

timberbt
timberbt asked
on
Medium Priority
254 Views
Last Modified: 2013-12-25
I just whipped up a really simple little VB program to try something.  What I have is a Report table sitting on an SQL server.  People submit a request for a report, and I'm making a status piece so they can see what the queue looks like.  So it is currently just doing select * from Requests order by priority

No problems, works like a champ.  What I would like to do though, is somehow designate which requests in the queue belong to the person that is looking at the list.

i.e. the Requests table has a "ClientName" field and if their NT login matches this, then display that whole record with red text...or something like that.  Just so they can glance at it and know "Oh my first request is third in line, and my 2nd request is last in the queue"

Am I using the wrong control for this?  The DBList seems really sort of cut and dry simple without too much flexibility.  If I can do it with this, than great, but if not let me know how and what I should be using instead.  Thanks!

=Timberbt
Comment
Watch Question

Commented:
You won't be able to do this with the DBList.  This is the sacrifice you make when using bound controls that automate the filling of lists from the database.

I generally prefer to work with unbound grid controls, such as the Microsoft Flex Grid (actually, I prefer the retail version of the VideoSoft VSFlexgrid Pro).  You have to manually define the number of rows and columns, and fill the cells individually (you can do a row at a time if you wish).  However, it gives you the ability to set specific attributes for each cell or row individually.  For example, you could make each row where the ClientName field matches there NTUserID Bold or Red, or have a Yellow background.

The code for this would look something like this:
(this code assumes that you've already opened the connection)

Public CN as ADODB.Connection
Public CMD as ADODB.Command
Public RS as ADODB.Recordset

Public Sub FillTable()
' This procedure is used to retrieve messages from the database
Dim sSQL As String
Dim i As Long
Dim lSaveRow As Long

    On Error GoTo ErrorRtn
   
' Temporarily freeze the form and timers while this procedure executes
    tblMessages.Redraw = False
   
' Retrieve all messages for this user
    Set CMD = Nothing
    Set CMD = New ADODB.Command
    CMD.CommandType = adCmdText
    CMD.ActiveConnection = CN
       
    sSQL = "SELECT * FROM REQUESTS"
    sSQL = sSQL & " ORDER BY PRIORITY"
       
    CMD.CommandText = sSQL
    Set RS = CMD.Execute
   
    If RS.State = adStateOpen Then
        If Not RS.EOF And Not RS.BOF Then
' This ensures that the recordcount is correct
            RS.MoveLast
            RS.MoveFirst
' Leave an extra row for the grid's column headers
' You should set the tables colums at design time, and also fill the column headers then too.
' At design time, the table should have 1 fixed row, and however many columns you'll need.            
            tblMessages.Rows = RS.RecordCount + 1
            i = 1
           
' Refill the message table
            While Not RS.EOF
                tblMessages.TextMatrix(i, 0)= RS("MESSAGE_ID").Value& ""
                tblMessages.TextMatrix(i, 1)= RS("LOGON_NAME").Value & ""
                tblMessages.TextMatrix(i, 2)= RS("SUBJECT").Value & ""
                tblMessages.TextMatrix(i, 3)=RS("RECEIVED_DATE").Value & ""
                tblMessages.TextMatrix(i, 4)= RS("READ_YET").Value & ""
                tblMessages.TextMatrix(i, 5)= RS("BODY").Value & ""
                tblMessages.TextMatrix(i, 6)= RS("CLIENTNAME").Value & ""
                If RS("CLIENTNAME").Value = gsNTUserName Then
                    SetRowBold i, True
                Else
                    SetRowBold i, False
                End If
                i = i + 1
                RS.MoveNext
            Wend

        Else
            tblMessages.Rows = 1
        End If
    Else
        tblMessages.Rows = 1
    End If
       
    RS.Close

ExitRtn:
   
    Set CMD = Nothing
    Set RS = Nothing
    tblMessages.Redraw = True
    Exit Sub

ErrorRtn:
    MsgBox Err.Description, vbExclamation, Err.Number & ""
    GoTo ExitRtn
End Sub

Private Sub SetRowBold(lRow As Long, bBold As Boolean)
' This procedure is used to set a table row bold or normal
Dim i As Long

    tblMessages.Row = lRow

    For i = 0 To tblMessages.Cols - 1
        tblMessages.Col = i
        tblMessages.CellFontBold = bBold
    Next i

End Sub

Author

Commented:
Awesome!  Thanks for the code and everything.... Just a small question and I'll have this badboy up and running...  on the Rs.movelast command I'm getting an error that Rowset does not support fetching backwards... I've always used the format of
RS.open method to populate the record set...  in the way you do it where you Set RS = Cmd.Execute
do I need to change the cursortype, and if so how?  I'm just assuming it is defaulting to a forward only cursor there.

Thanks again for the help.  Besides that one wrinkle (simply b/c of me being unfamiliar with that method) it looks like a great solution.

=Timberbt

Author

Commented:
Heh, well I just switched it to using the .open and set the cursortype there.  It seems to work perfectly.  Let me know if the other way is better, and what I may have been doing wrong.  Thanks again.

Commented:
I think that you were on the right track, I think that the default depends on the cursor location.  The default for that is adUseServer, and if so, I think that all(?) cursors are Forward Only.  If you use the Recordset Open method, I think that you can choose the cursor type that you want (provided that you have adUseClient as the cursor location).  Some of this may depend on the "Provider" that you're using as well.

You can set the cursor location on the connection object or on the recordset object.  If you set it on the connection object, then all recordsets will inherit that cursor location unless you override it by setting the value for the recordset.  

By the way, the reason I was using the Command object, is that I like to pass parameters to my queries and you need to use the command object for that.  Otherwise, you have to hassel formatting your SQL with the proper delimeters for string, number, date query values (select * from table where lastname = 'jones').  In this case, since you're not passing any criteria, using the Recordset Open method is probably better.  

A Forward Only cursor would be OK, the only reason I was using the MoveFirst/MoveLast was to get an accurate RecordCount, so I could set the correct number of rows for the grid without having to think about it.  However, you could easily modify the code like this:


   CMD.CommandText = sSQL
   Set RS = CMD.Execute
   
   If RS.State = adStateOpen Then
       If Not RS.EOF And Not RS.BOF Then
' Use this to clear out the table except for the headers  
           tblMessages.Rows = 1
           i = 1
' Refill the message table
           While Not RS.EOF
' Add one row at a time, i should always be equal to
' the last row of the grid
               tblMessages.Rows = tblMessages.Rows + 1
               .....
               i = i + 1
               RS.MoveNext
           Wend

Author

Commented:
Okay, gotcha...so you're just adding a row as you go then.  So what parameters would you pass along with a command object that you couldn't put in the SQL statement itself for the RS.open method?  I would assume that would items such as # of rows to return at a time or something of that sort?  

Thanks again and it works great.  Exactly what I asked for.  People's jaws are going to drop when they see how much better this client is than the old one.  ;)  


Author

Commented:
Exactly what I asked for.  I couldn't have gotten a better answer.  Awesome.

Commented:
Great!  Thanks!

No, what I meant about the parameters is those things that you might want to include as criteria in the Where clause.  You can do just about anything with the Recordset Open that you would with the command object, but you have to worry about formatting the SQL with delimiters.  With the command object, you can use adodb.parameter objects and simply fill the values.  ADO takes care of "delimiting" the values, so you never get errors about a missing quote mark around a string literal or invalid date format etc.

Also, if you ever call stored procedures instead of simply passing dynamic SQL, then you might have some Output parameters that are passed back from the query in addition to your result set, and the only way to do this is with parameters.  Here is an example of opening a recordset from a command object that uses parameters.

Dim CN as ADODB.Connection
Dim CMD as ADODB.Command
Dim RS as ADODB.Recordset
Dim Parm as ADODB.Parameter
Dim sSQL As String

    On Error GoTo ErrorRtn
       
    Set CMD = Nothing
    Set CMD = New ADODB.Command
    CMD.CommandType = adCmdText
    CMD.ActiveConnection = CN
   
    Set Parm = New ADODB.Parameter
    Parm.Type = adVarChar
    Parm.Size = 5
    Parm.Direction = adParamInput
   
    sSQL = "SELECT USERS.USER_ID,"
    sSQL = sSQL & " USERS.FIRST_NAME,"
    sSQL = sSQL & " USERS.MIDDLE_NAME,"
    sSQL = sSQL & " USERS.LAST_NAME"
    sSQL = sSQL & " FROM USERS"
    sSQL = sSQL & " WHERE USERS.LAST_NAME = ?"

    CMD.CommandText = sSQL

    CMD.Parameters.Append Parm

    Parm.Value = "Jones"

    Set RS = CMD.Execute

Author

Commented:
Okay I follow you now.  Thanks for the additional info.  Now it's time to analyze hordes of SQL statements and get some of this stuff working.  ;)  

Explore More ContentExplore courses, solutions, and other research materials related to this topic.