Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

programmatically changing column text in DataGrid_BeforeColUpdate

Is there a way to programmatically change the value of a cell in a specific row and column of a DataGrid control?
0
cheffery7
Asked:
cheffery7
  • 16
  • 4
  • 3
  • +2
1 Solution
 
Ryan ChongCommented:
Hi cheffery7,

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

?

Tell me if i'm wrong..
0
 
cheffery7Author Commented:
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
 
Ryan ChongCommented:
Hi,

Have you check the AllowUpdate Property on DataGrid control?

Are the DataGrid control bound to a data source?

regards.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
cheffery7Author Commented:
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
 
Ryan ChongCommented:
Hi,

any error captured?

Put the Cancel = True on if statement too..
0
 
cheffery7Author Commented:
the allowupdate is set to true, and the grid is populated via a SQL select statement from an Oracle DB table.
0
 
cheffery7Author Commented:
the allowupdate is set to true, and the grid is populated via a SQL select statement from an Oracle DB table.
0
 
cheffery7Author Commented:
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
 
wsh2Commented:
Post your connection string please
0
 
Valliappan ANCommented:
Try Cancel=1 instead of setting it to oldvalue, and refresh or whatever you do with F5 key.

Cheers
0
 
cheffery7Author Commented:
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
 
ArvindtnCommented:
grd_control.TextMatrix(Row,Col) = Value

Hope this works for u.

0
 
cheffery7Author Commented:
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
 
cheffery7Author Commented:
tried this: grd_control.TextMatrix(Row,Col) = Value

got this: compile error Method or data member not found

any thoughts?
0
 
wsh2Commented:
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
 
cheffery7Author Commented:
tried this: grd_control.TextMatrix(Row,Col) = Value

got this: compile error Method or data member not found

any thoughts?
0
 
cheffery7Author Commented:
wsh2,

The result was the following error:

An Error Was Encountered

7004: The rowset is not bookmarkable.

Any thoughts?

Thanks
0
 
wsh2Commented:
Change the .CursorLocation back to adUseClient

  .CursorLocation = adUseClient  
0
 
cheffery7Author Commented:
wsh2,

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

thanks
0
 
Valliappan ANCommented:
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
 
cheffery7Author Commented:
wsh2,

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

thanks
0
 
cheffery7Author Commented:
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
 
Valliappan ANCommented:
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
 
cheffery7Author Commented:
valli an,

already tried that and it didn't work either.

Thanks
0
 
cheffery7Author Commented:
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
 
Valliappan ANCommented:
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
 
cheffery7Author Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 16
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now