Data Grid - Allow editing on only one column 1

[isha]

I am developing a simple VB package with SQL Server database.
I want to see some data(pending complaints) on a datagrid. I have done this. Now I want to allow editing in only one of the Data Grid. But i do not know how to do this. Can someone give me some idea.
Thanks.

 

[Glasgow]

dgdselect.Columns(0).Locked = true
dgdselect.Columns(1).Locked = true

etc

should do the trick, I think

 

[Glasgow]

Er sorry, dgdselect is the data grid control, if that is not obvious.

 

[isha]

Glasgow
I am using the code
Set DataGrid1.DataSource = rs1 for displaying data in the datagrid. But when I want to enter some text in any of the columns of the grid it do not accept.
kindly help.

 

[Glasgow]

Are you saying that none of the columns will now accept any user input? Have you added code to set .Locked property as suggested?

I assume that what you want to achieve is that all columns in the grid are display only except one? How many columns does the grid have and which one should allow data entry - i.e. 1st, 2nd etc? It would be useful to see more of your code.

 

[Glasgow]

Also, is the grid's AllowUpdate property set to true?

 

[isha]

Glasgow

There are 10 columns in the datagrid1.
After the data is displayed on the datagrid through the following code. I want to add some data in the last two colums(column8&9) of the datagrid which are blank. But i cann't enter anything in any of the columns of the grid.

Private Sub Command1_Click()
DataGrid1.Columns(8).Locked = False
DataGrid1.Columns(9).Locked = False
Set rs = New ADODB.Recordset
rs.Open "select * from ccomplaints where ccomplaints.lodge_dt between '" & Format(DTPicker1.Value, "mm/dd/yyyy&quot & "' and '" & Format(DTPicker2.Value, "mm/dd/yyyy&quot & "'order by c_no,lodge_dt", con, adOpenStatic, adLockOptimistic

Set DataGrid1.DataSource = rs

End Sub

 

[Glasgow]

If AllowAddNew property is true, the .Locked property will need to be set to True for columns 0 to 7.

However, how many fields are there in the ccomplaints table - 8 or 10? - i.e. are columns 8 & 9 existing fields within the table that you want to enter/modify?

 

[CCLINT]

Change adOpenStatic to adOpenKeyset,
or change the cursor location to Client and the cursor type to adLockBatchOptimistic
rs.CursorLocation = adUseClient
For the latter you will need to use UpdateBatch in order to update the records back to the source

 

[isha]

Glasgow

There are ten columns in the table. The AllowAddNew property is true but I can't put any text in the DataGrid column(8) & column(9)

 

[Glasgow]

Sorry I meant AllowUpdate not AllowAddNew. Has Cclint's suggestion helped?

 

[CCLINT]

>Has Cclint's suggestion helped?

Probably not...I mis-understood the problem. It would more than likely be the AllowUpdate property.

 

[isha]

CCLINT

I added the following code:
rs.CursorLocation=adUserClient
and changed adOpenStatic to OpenKeyset

Now I can enter the text in the datagrid columns.

Kindly tell me now what is the simplest way of saving the data entered in the last two columns of the datagrid to the table?

Thanks

 

[Glasgow]

The data should surely be saved automatically as the columns are bound to the table?

 

[CCLINT]

No.
This is because a Client side cursor is being used now.
Only the recordset will be updated, but not the data source as the recordset in Not directly bound to a table in this case.

You will need to use rs.UpdateBatch.
There are several ways of doing this, but some of the ways are only avaiable to Changes, and not AddNew's.

Declare your recordset object variable in the form's code window declarations section using the KeyWord WithEvents:

Option Explicit
Private WithEvents rsADO As ADODB.Recordset

Then, use this event:

Private Sub rsADO_WillMove(ByVal adReason As ADODB.EventReasonEnum, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
    With pRecordset
        If Not (pRecordset.BOF Or pRecordset.EOF) Then
            Select Case .Status
                Case adRecModified
                    .UpdateBatch adAffectCurrent
                Case adRecNew
                    .UpdateBatch adAffectCurrent
                Case adRecDeleted
                    .UpdateBatch adAffectCurrent
            End Select
        End If
    End With
End Sub

 

[CCLINT]

>This is because a Client side cursor is being used now.
..should add: "...and a adLockBatchOptimistic lock"

Sorry, I was assuming a lock type of adLockBatchOptimistic was being used, because I had instructed to use this lock type a couple of posts back.

If you use a lock type of adLockOptimistic, then this will be different of course.

 

[scsii]

Dim rsFind as ADODB.Recordset
Dim sSql as string 'your Query String



sSql = "Select * From tbl....."
Set rsFind = New ADODB.Recordset

rsFind.CursorLocation = adUseClient
rsFind.Open sSql, adoConnect, adOpenKeyset,adLockOptimistic


'Use these codes but the sole problem is that all the columns will be unlocked