Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

programmatically changing column text in DataGrid_BeforeColUpdate

Posted on 2001-08-22
27
Medium Priority
?
404 Views
Last Modified: 2008-02-01
Is there a way to programmatically change the value of a cell in a specific row and column of a DataGrid control?
0
Comment
Question by:cheffery7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 4
  • 3
  • +2
27 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6413500
Hi cheffery7,

DataGrid1.Row = i
DataGrid1.Col = j
DataGrid1.Text = "xyz"

?

Tell me if i'm wrong..
0
 

Author Comment

by:cheffery7
ID: 6413549
ryancys,

Here is the code I am using:

Private Sub dbgDataGrid_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
Dim iNumRows As Integer, iCounter As Integer
Dim rTempRS As New ADODB.Recordset
   
    On Error GoTo HandleErrors
    dbgDataGrid.Columns(ColIndex).Value = UCase(dbgDataGrid.Columns(ColIndex).Value)
    If Len(dbgDataGrid.Columns(ColIndex).Value) > 7 Then
            MsgBox "The Coil Number Value You Specified (" & dbgDataGrid.Columns(ColIndex).Value & ") Is Too Long." + Chr(13) + _
            "Resetting to Original Value: " & OldValue & " and Cancelling Edit.", vbOKOnly + vbExclamation, "Invalid..."
            dbgDataGrid.Columns(ColIndex).Value = OldValue
            SendKeys "{F5}"
    End If


However when I run the code in debug, the dbgDataGrid.Columns(ColIndex).Value = OldValue will not change the value of the dbgDataGrid.Columns(ColIndex).Value

Any ideas?
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6413576
Hi,

Have you check the AllowUpdate Property on DataGrid control?

Are the DataGrid control bound to a data source?

regards.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:cheffery7
ID: 6413587
ryancys,

Here is the code I am using:

Private Sub dbgDataGrid_BeforeColUpdate(ByVal ColIndex As Integer, OldValue As Variant, Cancel As Integer)
Dim iNumRows As Integer, iCounter As Integer
Dim rTempRS As New ADODB.Recordset
   
    On Error GoTo HandleErrors
    dbgDataGrid.Columns(ColIndex).Value = UCase(dbgDataGrid.Columns(ColIndex).Value)
    If Len(dbgDataGrid.Columns(ColIndex).Value) > 7 Then
            MsgBox "The Coil Number Value You Specified (" & dbgDataGrid.Columns(ColIndex).Value & ") Is Too Long." + Chr(13) + _
            "Resetting to Original Value: " & OldValue & " and Cancelling Edit.", vbOKOnly + vbExclamation, "Invalid..."
            dbgDataGrid.Columns(ColIndex).Value = OldValue
            SendKeys "{F5}"
    End If


However when I run the code in debug, the dbgDataGrid.Columns(ColIndex).Value = OldValue will not change the value of the dbgDataGrid.Columns(ColIndex).Value

Any ideas?
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 6413618
Hi,

any error captured?

Put the Cancel = True on if statement too..
0
 

Author Comment

by:cheffery7
ID: 6413622
the allowupdate is set to true, and the grid is populated via a SQL select statement from an Oracle DB table.
0
 

Author Comment

by:cheffery7
ID: 6413632
the allowupdate is set to true, and the grid is populated via a SQL select statement from an Oracle DB table.
0
 

Author Comment

by:cheffery7
ID: 6413656
not sure what you are saying here:

Put the Cancel = True on if statement too..

in regards to capturing an error, no error is captured while I step through the code.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6414124
Post your connection string please
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 150 total points
ID: 6414259
Try Cancel=1 instead of setting it to oldvalue, and refresh or whatever you do with F5 key.

Cheers
0
 

Author Comment

by:cheffery7
ID: 6414351
If chkShowEnded.Value = 0 And chkShowPending.Value = 0 Then
   sSQLStr = "SELECT c.status_flag, c.coil_seq_no,  
         c.al_manufacturer_name, c.al_manufacturer_lot_no,
         c.thickness, c.width, c.end_cut_flag, c.kind,
         c.al_fuji_lot_no, c.original_al_length,
         c.prescribed_al_length, p.pallet_seq_no,
         c.production_order_no FROM    
         mpc_coil_parameters_tbl c,
         mpc_pallet_parameters_tbl p
         WHERE (c.production_order_no =        
         p.production_order_no AND c.status_flag > -1 AND  
         c.status_flag < 5 AND p.pallet_seq_no =
            (SELECT MIN(pallet_seq_no)
             FROM mpc_pallet_parameters_tbl WHERE
             mpc_pallet_parameters_tbl.al_fuji_lot_no =
             c.al_fuji_lot_no)) OR (c.al_fuji_lot_no  
             LIKE '%LDR%' AND c.status_flag > -1 AND
             c.status_flag < 5 AND p.pallet_seq_no =
            (SELECT MIN(pallet_seq_no) FROM
             mpc_pallet_parameters_tbl)) ORDER BY
             c.coil_seq_no"
        chkShowPending.Enabled = True
        chkShowEnded.Enabled = True
        cmdAdd.Enabled = True
        bShowAllEnabled = True
   

 ' Reopen the Recordset (using the appropriate SQL string)
On Error GoTo HandleErrors
If rADORecordset.State <> adStateClosed Then
               rADORecordset.Close
rADORecordset.CursorLocation = adUseClient
rADORecordset.Open sSQLStr, cADOConnection,        
               adOpenStatic, adLockOptimistic, adCmdUnknown
Set dbgDataGrid.DataSource = rADORecordset
0
 

Expert Comment

by:Arvindtn
ID: 6414384
grd_control.TextMatrix(Row,Col) = Value

Hope this works for u.

0
 

Author Comment

by:cheffery7
ID: 6414406
If chkShowEnded.Value = 0 And chkShowPending.Value = 0 Then
   sSQLStr = "SELECT c.status_flag, c.coil_seq_no,  
         c.al_manufacturer_name, c.al_manufacturer_lot_no,
         c.thickness, c.width, c.end_cut_flag, c.kind,
         c.al_fuji_lot_no, c.original_al_length,
         c.prescribed_al_length, p.pallet_seq_no,
         c.production_order_no FROM    
         mpc_coil_parameters_tbl c,
         mpc_pallet_parameters_tbl p
         WHERE (c.production_order_no =        
         p.production_order_no AND c.status_flag > -1 AND  
         c.status_flag < 5 AND p.pallet_seq_no =
            (SELECT MIN(pallet_seq_no)
             FROM mpc_pallet_parameters_tbl WHERE
             mpc_pallet_parameters_tbl.al_fuji_lot_no =
             c.al_fuji_lot_no)) OR (c.al_fuji_lot_no  
             LIKE '%LDR%' AND c.status_flag > -1 AND
             c.status_flag < 5 AND p.pallet_seq_no =
            (SELECT MIN(pallet_seq_no) FROM
             mpc_pallet_parameters_tbl)) ORDER BY
             c.coil_seq_no"
        chkShowPending.Enabled = True
        chkShowEnded.Enabled = True
        cmdAdd.Enabled = True
        bShowAllEnabled = True
   

 ' Reopen the Recordset (using the appropriate SQL string)
On Error GoTo HandleErrors
If rADORecordset.State <> adStateClosed Then
               rADORecordset.Close
rADORecordset.CursorLocation = adUseClient
rADORecordset.Open sSQLStr, cADOConnection,        
               adOpenStatic, adLockOptimistic, adCmdUnknown
Set dbgDataGrid.DataSource = rADORecordset
0
 

Author Comment

by:cheffery7
ID: 6414445
tried this: grd_control.TextMatrix(Row,Col) = Value

got this: compile error Method or data member not found

any thoughts?
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6414544
Try this:

'   Reopen the Recordset (using the appropriate SQL string)
On Error GoTo HandleErrors
With rADORecordset
   If .State <> adStateClosed Then .Close
   .CursorLocation = adUseSERVER
   .Open sSQLStr, cADOConnection, adOPENDYNAMIC, adLockOptimistic
End With
Set dbgDataGrid.DataSource = rADORecordset
0
 

Author Comment

by:cheffery7
ID: 6414904
tried this: grd_control.TextMatrix(Row,Col) = Value

got this: compile error Method or data member not found

any thoughts?
0
 

Author Comment

by:cheffery7
ID: 6415032
wsh2,

The result was the following error:

An Error Was Encountered

7004: The rowset is not bookmarkable.

Any thoughts?

Thanks
0
 
LVL 14

Expert Comment

by:wsh2
ID: 6415239
Change the .CursorLocation back to adUseClient

  .CursorLocation = adUseClient  
0
 

Author Comment

by:cheffery7
ID: 6417374
wsh2,

that took care of the rowset error, but the code still is not changing the value back to the old value.

thanks
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6417872
Just check the values of,

dbgDataGrid.Columns(ColIndex).Value
and
OldValue

in debug mode. Are they equal, already or different. If they are equal, then you will need to have the SP3 for VB.

Anyhow, it is better to have SP5, for VB6.

Try this, and let me know.

0
 

Author Comment

by:cheffery7
ID: 6418021
wsh2,

that took care of the rowset error, but the code still is not changing the value back to the old value.

thanks
0
 

Author Comment

by:cheffery7
ID: 6418591
wsh2,

in regards to

in debug mode. Are they equal, already or different. If they are equal, then you will need to have the
SP3 for VB.

Anyhow, it is better to have SP5, for VB6.

they are different in debug mode.

and I already have SP5 installed.

0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6420814
Hi, look at this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/DBGrid98/html/daevtbeforecolupdatex.asp

So, you need to put Cancel=True as already suggested by ryancys. Thats it. You need not set the Value property in that case, if I am right.

Cheers
0
 

Author Comment

by:cheffery7
ID: 6422943
valli an,

already tried that and it didn't work either.

Thanks
0
 

Author Comment

by:cheffery7
ID: 6423046
turns out that when I actually included the code to refresh the datagrid it actually worked without me having to reset the column value to the old value.

thanks for everyone's help
0
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6424573
You could do dbgDataGrid.Refresh, for refreshing the data, instead of sending the F5 key.

The MSDN example worked or not? I hope that is the right way to do this. Since, if you do refresh, then entire data, might be retrieved or repainted, but doing a Cancel in BeforeColUpdate, would cancel the operations to the current cell alone.

I have not tried this in Datagrid, but in other grids, this method have worked for me. Anyhow, goodluck.

Cheers.
0
 

Author Comment

by:cheffery7
ID: 6428470
valli an,

The cancel=true did not work.  It was only when I did a refresh on the sub routine that populates the data grid that I was able to get the results I was looking for.  Let me know if you want any more detail.

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question