Editing in Grid with flexibility 1

[Mukesh2375]

Hi everybody
can anybody suggest me what should i use and how for my following purpose i want some data in grid layout where data in some columns (that i will specify will be coming from specific fields of a particular recordset and some will be ..say in grid. i want the non bound colum values (the columns whose values are NOT coming from a recordset field) editable. The purpose is such that i will update the database at the end. also i may update the list of data that is coming in the grid (I mean i will update the recordset say by activating a form and accepting values; so this may change the number of rows coming in the grid. also if the data in the grid is sorted, the newly addes values should appear in sorted order in the grid.

regards
Mukesh

 

[CCLINT]

You could use a FlexGrid for this...Or A DataGrid bound to a Recordset...A Recordset not connected to a database which you create at run time, adding the fields, fieldtypes, records as needed. Or, it is based off of a recordset which has retreived it's data from the DB but is now disaconnected from the db so that any changes remain on the client and do not affect data in the db.
If you want, however, the ability to set individual cell colors and individual cell formats (DataGrid only does column formating) you will need to use the FlexGrid.

 

[zemp]

I agree with CCLINT that the MSFlexgrid will be your best option. You can then add a floating text box (or combo box, etc.) to make the grid 'editable'. I also like to place the data for the grid into a UDT array and load the grid manually. This way the data is separated from the database and I can do any datamanipulation or updating that I like and update the database when I am complete. Thus controling the number of calls to the database.

See faq222-3262, by Tom Kane, for examples on how to code floating controls. You can also search for examples.

Thanks and Good Luck!

zemp

 

[CCLINT]

>Thus controling the number of calls to the database

Zemp: I just want to point out that you can do this with a DataGrid as well...As a matter of fact, you can change/add/delete all you want to in a data grid with-out the DB being affected at all, or only when and with what peices of data you want affected.
1. Connect to db
2. Create RS, using a Client side cursor, and retreive data
3. Disconnect RS
4. Bind DataControl to RS
5. On update, using the Grid's AfterUpdate event, or the RS RecordChanged event, send an action query back to the server to update the record in the db.
This makes it just as stateless as the FlexGrid.

 

[Mukesh2375]

Thanx Zemp and CCLINT. i have been able to fill data in the FlexGrid. One more thing - can anybody give me some idea to UDT array and some examples on how to use it in FlexGrid

regards

 

[CCLINT]

Would a fabricated recordset be an option instead? You could then load the FlexGrid in one line of code.

 

[zemp]

CCLINT - To be honest I have never worked with the datagrid at length. Thanks for the useful info. What do you mean by a fabricated recordset? It sounds like a viable option.

Mukesh2375 - I use the UDT mainly to avoid using a double subscripted array. The components of the UDT correspond to the columns of the grid and the elements correspond to the row of the grid. I often include components that are not displayed such as ID numbers.

For example,

public type myUDT
col1 as string
col2 as string
col3 as string
end type

Declared as: dim array() as myUDT

The number of elements will depend on the number of records in the recordset.

Thanks and Good Luck!

zemp

 

[CCLINT]

Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset

'Start appending the desired fields
rs.Fields Append "Field1", adVarWChar, 20, adFldUpdatable
rs.Fields Append "Field2", , adVarWChar, 10, adFldUpdatable

rs.Open

'Add the date!
'You can also pass an array of fieldnames and an array of field values when adding the records!
rs.AddNew

Set DataGrid1.DataSource = rs
'(or a MSHFlexGrid)

You can now easily sort, filter, find, etc., the data.

 

[zemp]

That opens up all the recordset properties. Similar in functionality to a UDT but with more capabilities.

Nice, a star for you.

Thanks and Good Luck!

zemp

 

[CCLINT]

Thank you Zemp!

When using this method you might want to consider using ADOR instead, as it is lightweight compared to a normal ADODB reordset.

 

[Mukesh2375]

Zemp - i cud get how to define an UDT array but nowhere you mentioned how 2 use it in conjugation with FlexGrid i.e. how do i attach it to FlexGrid - cud u give any example please.

CCLINT - i tried yr example but it gives Run-time error '91', 'Object variable or With block variable not set' in the line
Set FlexGrid1.DataSource = rs
By fabricated recordset do you mean the type you have given above. well cud u give me a solution for the error

Thanx everyone

 

[CCLINT]

rs.Fields.Append "Field1", adVarWChar, 20, adFldUpdatable
rs.Fields.Append "Field2", adVarWChar, 10, adFldUpdatable

There were typos in these lines

 

[zemp]

Mukesh2375 - as I mentioned in my original post, with the UDT you will have to add the data manually. You cannot attach it to the grid. You will need to loop through the elements of the array and fill each cell individually.

Here is an example of a procedure that fills a grid with the data from a UDT called 'm_udtCustomers'.

Public Sub Fill_Grid()
'// Fills the grid with the information in the UDT array.
Dim l_intRow As Integer

On Error GoTo Err_FillGrid

txtFloat.Visible = False
MSFlexGrid1.Rows = UBound(m_udtCustomers) + 2
For l_intRow = 1 To UBound(m_udtCustomers) + 1
MSFlexGrid1.Row = l_intRow
MSFlexGrid1.Col = 0
MSFlexGrid1.Text = m_udtCustomers(l_intRow - 1).Type
MSFlexGrid1.Col = 1
MSFlexGrid1.Text = m_udtCustomers(l_intRow - 1).FirstName
MSFlexGrid1.Col = 2
MSFlexGrid1.Text = m_udtCustomers(l_intRow - 1).LastName
MSFlexGrid1.Col = 3
MSFlexGrid1.Text = m_udtCustomers(l_intRow - 1).Address
MSFlexGrid1.Col = 4
MSFlexGrid1.Text = m_udtCustomers(l_intRow - 1).City
MSFlexGrid1.Col = 5
MSFlexGrid1.Text = m_udtCustomers(l_intRow - 1).State
MSFlexGrid1.Col = 6
MSFlexGrid1.Text = m_udtCustomers(l_intRow - 1).ZipCode
Next l_intRow
Exit Sub

Err_FillGrid:
MSFlexGrid1.Rows = 1
MsgBox Err.Number & "; " & Err.Description
End Sub

This uses the .text and .col properties, you can also use the .textmatrix property and reduce some lines of code.

Hope this helps.

Thanks and Good Luck!

zemp

 

[Mukesh2375]

Zemp : Thanx buddy. i knew the thing you mentioned. actually i thought there is some way to link the array with the grid so that we specify to the columns of the grid which array element to show and grid wud take care to show respective data. thanx anyway.

CCLINT : i used the code given by you after correcting even before and the error i m getting is after that. below is the code i tried..

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Fields.Append "Field1", adVarWChar, 20, adFldUpdatable
rs.Fields.Append "Field2", adVarWChar, 10, adFldUpdatable
rs.Open
rs.AddNew
Set fg.DataSource = rs

thanx everybody
Mukesh

 

[CCLINT]

You need to use the OLEDB Flex grid (Component reference to "Microsoft Hierarchical FlexGrid Control 6.0 (OLEDB" as the Datasource is compatible with ADO, whereas the MS FlexGrid is compatible with DAO, and needs to be bound to a DAO Data1 data control instead of a Adodc.