Solved

programmatically changing column text in DataGrid_BeforeColUpdate

Posted on 2001-08-22
27
402 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 52

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 52

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 52

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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 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…
Suggested Courses
Course of the Month2 days, 18 hours left to enroll

621 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