0

I'm working on a VB6 program that connects to a SQL Server 2008 R2 database. In the past I have always used the MSFlexGrid control and populated it manually. Now, however, the guy who is paying me for this wants me to use data-bound grids instead, which forces me to use the MSHFlexGrid control because I'm using ADO and not DAO. So, I have two questions...

First, how would I move a column in a MSHFlexGrid? For example, if I wanted the third column to appear as the sixth column in the grid, is there a simple single line of code that would do that?

Second, believe it or not, I've never had to do anything in a grid other than display the data, as is, from a recordset. Now, however, I have a recordset with some fields that contain just ID numbers that refer to records in other files - for example, a field containing an ID number referring to a record in the Customers table, instead of the field containing the customer's name. What is the easiest way to, instead of having a column showing customer ID numbers from the recordset, having that column show customer names? I thought I read somewhere that there's a way to embed a sql command in a MSHFlexGrid column, but if there is I wouldn't know how to do it. Is this possible, or is there a simpler way to do it?

TIA,

Kevin

Kevin
  • 15
  • 2
  • 4

2 Answers2

1

Wow! VB6.... Back to the future! :-)

You can move Columns using the ColPosition Property.

This article shows how you could setup the grid to display hierarchical data.

If you just want to display the customer name on the same line as the main data then that is doable as well by just creating the proper SQL for your data source. For that matter you can control the column order the same way as well.

Now, how about considering upgrading to .Net? Just kidding..... No, I'm not. OK. I am, maybe. :-)

Kevin LaBranche
  • 20,908
  • 5
  • 52
  • 76
  • My mistake - I didn't specify the source of the data in the recordset. I'm creating a blank recordset, then filling it with data that the user has input into several controls (text boxes, combo boxes, etc.), then writing the recordset to the table, then displaying the recordset in a grid. To create the recordset I'm using something like: rs.Open "Select * from Projects where 1 = 2". So while I can, instead of using *, specify the fields in this statement and therefore set their order, I'll still be stuck with showing ID numbers in some of the grid fields. How should I fix that then? – Kevin Apr 06 '11 at 06:05
  • Do you want to hide the fields? You can do that also. Look at the ColIsVisible Property. Here's a link to the MSHFlexGrid on MSDN. They have links to all the properties, methods, etc.... http://msdn.microsoft.com/en-us/library/aa228851(VS.60).aspx – Kevin LaBranche Apr 06 '11 at 14:15
1

The column order would typically be handled by your SELECT statement.

Say you have a Pies table that has a FruitID foreign key related to the FruitID in a Fruits table:

SELECT PieID AS ID, Pie, Fruit FROM Pies LEFT OUTER JOIN Fruits
    ON Pies.FruitID = Fruits.FruitID

This returns 3 items: ID, Pie, and Fruit in that order.

Moving columns after the query/display operation is rarely used, but yes ColPosition can be used for that.

Bob77
  • 13,167
  • 1
  • 29
  • 37
  • I suggested LEFT OUTER JOIN here to retrieve *all* rows of Pies even if the FruitID lookup fails (which returns NULL for Fruit in such a case). People often use INNER JOIN for this instead, but you risk dropping records... though that might be your intent too I suppose. – Bob77 Apr 06 '11 at 01:21
  • My mistake - I didn't specify the source of the data in the recordset. I'm creating a blank recordset, then filling it with data that the user has input into several controls (text boxes, combo boxes, etc.), then writing the recordset to the table, then displaying the recordset in a grid. To create the recordset I'm using something like: rs.Open "Select * from Projects where 1 = 2. So while I can, instead of using *, specify the fields in this statement and therefore set their order, I'll still be stuck with showing ID numbers in some of the grid fields. How should I fix that then? – Kevin Apr 06 '11 at 03:20