Solved

programmatically changing column text in DataGrid_BeforeColUpdate

Posted on 2001-08-22
27
393 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 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi cheffery7,

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

?

Tell me if i'm wrong..
0
 

Author Comment

by:cheffery7
Comment Utility
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 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi,

Have you check the AllowUpdate Property on DataGrid control?

Are the DataGrid control bound to a data source?

regards.
0
 

Author Comment

by:cheffery7
Comment Utility
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 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi,

any error captured?

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

Author Comment

by:cheffery7
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Post your connection string please
0
 
LVL 9

Accepted Solution

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

Cheers
0
 

Author Comment

by:cheffery7
Comment Utility
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
Comment Utility
grd_control.TextMatrix(Row,Col) = Value

Hope this works for u.

0
 

Author Comment

by:cheffery7
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:cheffery7
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Change the .CursorLocation back to adUseClient

  .CursorLocation = adUseClient  
0
 

Author Comment

by:cheffery7
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
valli an,

already tried that and it didn't work either.

Thanks
0
 

Author Comment

by:cheffery7
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now