Why can I not add a record to a database using the .AddNew command?

[Stockton.S]

The .AddNew command as specified below doesn't appear to work and gives the following error:

Object or Providers not Capable of preforming requested operation

UserRs.AddNew
UserRs("userlogin") = getusr()
UserRs("viewpic").Value = True
UserRs("viewtip").Value = True
UserRs("viewupdate").Value = False
UserRs.Update

The error occurs ont he UserRs.AddNew line.

The UserRs is an open ADODB.RECORDSET database connection and as far as I am aware should work.

Any help or feedback would be appreciated.

Simon

[Ianpbaker]

Hi Stockton.S

What cursor type are you using to open the recordset?

Ian

[Stockton.S]

Hi Ian

UserRs.CursorType = adOpenForwardOnly

Although I am not sure what Cursor's do as this is an adaptation from loads of examples and postings I suppose that I need this to be let me see... what does VB prompt?

UserRs.CursorType = adOpenKeyset??????


Please let me know if that is what I was doing wrong!

Thanks

Simon

[Stockton.S]

all the different cursors that VB prompts give the same error

[Ianpbaker]

I've had this problem before and I think it its something to do with the way you connect to the database. Also try opening it as dynamic and batch optimistic for the locking

Ian

[Stockton.S]

I tried opening it as all the different type of cursor but still doesn't work

Set UserRs = New ADODB.Recordset
UserRs.CursorType = adOpenForwardOnly
UserRs.Open strSQLUser, UserCon, , , adCmdText

preceeds what was previously posted.

any other ideas?

Simon

[Ianpbaker]

you definatley can't use adOpenForwardOnly as that is a static recordset. you have to use adOpenKeyset or adOpenDynamic. but as you said it doesn't work with either of them. give the following a go

Set UserRs = New ADODB.Recordset
UserRs.Open strSQLUser, UserCon,adOpenDynamic,adLockOptimistic , adCmdText

If that doesn't work, please post your connection

Ian

[Stockton.S]

Thanks!

I can now execute the AddNew command but I am unsure how to assign values to the specific fields in the database.

userlogin, viewpic, viewtip, viewupdate are all field names in the table and they are y/n field types so how do I assign the various values to them?

UserRs.AddNew
UserRs("userlogin") = getusr()
UserRs("viewpic").Value = True
UserRs("viewtip").Value = True
UserRs("viewupdate").Value = False
UserRs.Update

Simon


P.S. What does:

UserRs.Open strSQLUser, UserCon,adOpenDynamic,adLockOptimistic , adCmdText

do?

The recordset is opened using the connection string as specified Dynamically meaining that it can change? adlockoptimistic (what does that do?)

[Ianpbaker]

Hi simon

when using yes no fields you can use 1 and 0 or true and false, it doesn't matter.

as for the opening of the recordset

UserRs.Open strSQLUser,UserCon,adOpenDynamic,adLockOptimistic , adCmdText

adOpenDynamic - opens the recordset so that you can scroll backwards, forwards, any way you want trough the returned records and let's you change them as you so wish. Be warned though that this is the most heaviest recordset and if you only want to read records you should use adopenstatic or adopenfowardonly.

adLockOptimistic - this is to do with the locking of the records when the system is changing them. obviously you don't want two users trying to change the same record, at the same time. basically, when the recordset.update is fired, the record becomes locked to all changes aprt from the machine that fired it. Understaning locking is quite complex abd if you want to know more go to the msdn site

adCmdText - Evaluates CommandText as a textual definition of a command or stored procedure call.

Hope this helps

Ian

[Stockton.S]

That helps loads thanks!

I have a number of connections opened and closed whilst my program does different things so I guess that I should lock the records when I open all of these connections as I am using this as a multi user program.

The AddNew but works fine apart from the

UserRs("login") = getusr()

bit it comes up with the error!

"ITEM CANNOT BE FOUND IN THE COLLECTION CORRISPONDING TO THE REQUESTED NAME OR ORDINAL"

The login is a valid field name (although different to previous posting I have changed it in the database) but it appears that because it is of type char it won't allow being updated I don't understand.

Simon