Solved

programmatically changing column text in DataGrid_BeforeColUpdate

Posted on 2001-08-22
27
395 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
  • 16
  • 4
  • 3
  • +2
27 Comments
 
LVL 50

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 50

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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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 50

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 50 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

785 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