timberbt
asked on
Displaying data differently in a DBList
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
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
ASKER
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. ;)
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. ;)
ASKER
Exactly what I asked for. I couldn't have gotten a better answer. Awesome.
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
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
ASKER
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. ;)
ASKER
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