Simple Recordset AddNew giving error !! plase help

[sergiog]

Hi

I have the following code for adding a new record to a table:

(the recordset is an ADO recordset)

rstVacTypes.AddNew
rstVacTypes.Fields("Ref&quot = txtRef.text
rstVacTypes.Fields("Description&quot = txtDesc.text
rstVacTypes.update

on the first line, it's giving me an error saying "Cannot update 'Ref':field not updateable

any ideas pls?
thanks

 

[CassandraR]

Hi

Suggest checking Bob Rodes' FAQ, faq222-3670, on record types and locking.

Cassie

 

[CCLINT]

1. What is the field type?

?rstVacTypes.Fields("Ref&quot.Type

2. Post the SELECT Statement, or Recordsource used to open the recordset.

3. What does these return?
?rstVacTypes.Supports(adUpdate)
?rstVacTypes.CursorType
?rstVacTypes.CursorLocation

 

[sergiog]

Hi

Regarding the field type, does it make any difference ? because the error was returned on the first line and even when I removed rstVacTypes.Fields("Ref&quot = txtRef.text it still gave me the error. Anyway, the field type is 3 (autonumber in the access database)

The problem is that I have the following function to do all the retrieval of recordsets. conGlobal is a global connection I open before.

Public Function Retrieve(ByVal strSql As String, Optional ByVal CursorType As CursorTypeEnum = adOpenDynamic, Optional ByVal LockType As LockTypeEnum = adLockPessimistic) As ADODB.Recordset

Dim ADORecordset As ADODB.Recordset

Set ADORecordset = New ADODB.Recordset

'the location must be set to client so that the datagrid can be bound
'at runtime

ADORecordset.CursorLocation = adUseClient

ADORecordset.Open strSql, conGlobal, adOpenKeyset

Set Retrieve = ADORecordset

End Function

When I use this function to return the recordset:

set rst = Retrieve("SELECT * FROM VacTypes&quot

I get the error and the cursortype becomes "static" when the recordset is passed even though I am setting it to dynamic in the function.

The rstvactypes.supports(adupdate) returns false.

When I open the recordset there and then and replace

set rst = Retrieve("SELECT * FROM Vactypes&quot

with :

sSQLstring = "SELECT * FROM VacTypes WHERE Ref = 5
rstVacTypes.Open sSQLstring, conGlobal, adOpenDynamic, adLockPessimistic

it works out fine.

Any ideas what the problem might be.

Thanks for the interest.

 

[CCLINT]

Did you not even read the FAQ that Cassie2002 pointed you to?

A Client side cursor is always Static, because all of the data is retreived into the client.

So, you do not even need to pass the CursorType. It will change to Static.

And, concerning a dynamic server side cursor, I would use an adOpenDynamic cursor in only about 1% of the cases. A Dynamic cursor has a lot of overhead and increases network traffic considerably.
If you use a ServerSide cursor, then use a adOpenKeyset cursor type.

But, back to your problem. Even though the cursor is static, it is still Updatable. Either on the client (use adOpenOptimistic and just start editing), or force a batch update back to the db (you can do this after each record change, such as when moving to the next record, or after updating several records):

ADORecordset.Open strSql, conn, adOpenKeyset, adLockBatchOptimistic

You might want to do this as a default:
If rs.CursorLocation = adUseClient Then CursorType = adLockBatchOptimistic


?rstVacTypes.Supports(adUpdate)
should now return True.

Edit and Update.
ADORecordset.Fields(1).Value= "TEST"

ADORecordset.UpdateBatch

You may run into conflicts with assigning the AutoNumber field.
You may run into conflicts when adding a record and then updating it with-out doing a Resync/Requery first.
You may run into conflicts when you edit and Update a certain record, which has also been update by another user since you last opened/resync'ed the Recordset.

You will need an error handler for this.
You may instead want to do the database updating via action queries.

And lastly, the cursor doesn't always need to be a client side cursor in order to bind a datagrid to the rs....

 

[sergiog]

thanks.

your help solved it. I did read that article but I just couldn't understand what was wrong. maybe coz it was so early in the morning (or late at night) that i wasn't seeing well.

thanks again.

by the way, have you ever had the error "field not updatable" ? I have a bindingcollection of textboxes bound to a recordset, and on movenext it gives me that error. However, the value in the textbox changes ok.

thanks