Recordset update method failed

[carolyn]

I'm using VB5 SP3, connecting to database via ODBC.

Set WS = DBEngine.CreateWorkspace("MainWS", "admin", vbNullString, dbUseODBC)
Set CN = WS.OpenConnection("", dbDriverNoPrompt, False, strConnect)
Set RS = CN.OpenRecordset("select * from table", dbOpenDynaset, 0, dbPessimistic)

Private Sub Command1_Click()
    With RS
        .Edit
        ![cc] = Text1(2).Text
        ![ii] = Text1(0).Text
        .Update
        .Bookmark = .LastModified
    End With
End Sub

The table consists of 2 columns, ii of type int4 and cc of type varchar(100).

The code fails at '.Update', get the error message 3146 ODBC -- Call failed.

Any idea?

[KenX]

i am not sure but i think you have
to convert Text(0).Text to integer
to equate this to ii, thus

   !ii = CInt(Text(0).Text))

...
or correct me if i am wrong but the
table to which you set the recordset
is missing, in other words, to which
table are you connecting to?
(re: "select * from table")

i am not sure of this response though...

[carolyn]

The code didn't work (the integer conversion). Even if i'm updating the "cc" field of the table.

I've checked the updatable property for the connection, record set, and the DataUpdatable property of all fields. They are all TRUE!

Anymore ideas?

[Gen-X]

I have a few suggestions :

I think you may have a problem here...

Set RS = CN.OpenRecordset("select * from table", dbOpenDynaset, 0, dbPessimistic)

What you have asked with this statement is to have the ENTIRE take made available in a cursor and you have stated "0" for what you want it to do... This means no options.

I would consider using either dbRunAsync or dbExecDirect instead of 0 and change the dbPessimistic to dbOptimistic.

If you are only updating a single record I would also consider only loading that one into the recordset instead of the whole table (VB is allocating memory to hold the cursor index when you do "SELECT *")

Set RS = CN.OpenRecordset("select * from table where primarykey = '" & pkey "'", dbOpenDynaset, dbRunAsync, dbOptimistic)

But another thing I would suggest is having a look at the DBEngine.Errors(0).Description when the error occurs. This collection gives you all the errors that went into making the problem and not just the "ODBC-- Call Failed" which is too mysterious. Hopefully this will tell you what the problem was, if a primary key was violated, if a foreign key doesn't match or if you have filled in a field incorrectly.

Hope it helps

[carolyn]

I checked the error where the method fails, the error with DBEngine.Errors(0), Number is 10 and Description is:

S1C00: Only SQL_POSITION/REFRESH is supported for SQLSetPos

What does this mean? I can't find any documentation with regarding to this error.

[Gen-X]

The error message you got is part of the ODBC back end to your database....

I am thinking that perhaps you should try my suggestion of changing the OpenRecordset statement. I am wondering if the way in which the recordset is open means you are unable to do an .update to this record.

Also... Something I just noticed...

You have put

.Bookmark = .LastModified

Setting the .Bookmark field causes the recordset to try and reposition to a record that has that bookmark (ie SQLSetPos maybe?). Try commenting out this line and see how it goes again also.

[carolyn]

Thanks for your reply.

I tried OpenRecordset with your suggestion (tried both dbRunAsyn and dbExecDirect as options), and removed the .Bookmark = .lastmodified. statement, I'm still getting the same error.

[Gen-X]

Ok... this is where a process of methodical elimination becomes useful. Once exhausting all probable avenues you have to go back to basic and start from the beginning.

I would try tacking the following things :

1. Is the ODBC DSN created properly?
2. Have you run the "Test" on the ODBC and it said Successful?
3. Can you at least READ records from the database?
4. Test to see what the RecordCount is for your clause first
5. Reduce your update to only a single field

Somewhere along that line you should encounter a problem which shows that the error you are getting is a side-effect of the real problem.

[AlanTodd]

But do you have a current record? Try putting a .MoveFirst in your With block to ensure you are pointing to a valid record.

[Gen-X]

You dont need to use a .MoveFirst alan.

If the recordset contains records (See my point on checking the RecordCount) then it automatically positions the cursor at the first record for you.

Hence you can easily do one of a few options :

Option 1 :

Set rstData = Open.Recordset("table",...,..,..)
If rstData.RecordCount > 0 Then
  Do While Not rstData.EOF
    <Do your stuff here>
    rstData.MoveNext
  Loop
End If
rstData.Close
Set rstData = Nothing

Option 2 :

Set rstData = Open.Recordset("table",...,..,..)
Do While Not rstData.EOF
  <Do your stuff here>
  rstData.MoveNext
Loop
rstData.Close
Set rstData = Nothing

If no records are returned it automatically sets both BOF and EOF to TRUE

[carolyn]

I'm testing database manipulation with VB application, so the table is small, only 2 columns with 2 rows of data. A simple form with 2 text boxes to display the data, 4 navgation command buttons (first, prev, next, last), and "Add", "Delete" & "Save" button.

So far I have successfully get the data into the RecordSet, displaying the data on the form, and data navigation (.Move* methods). I'm displaying a record at a time, so I'm only updating 1 row at a time. If .Update fails such simple test, what'd happen to a real application?

[Gen-X]

Update *isn't* failing such a simple test.

The configuration of your system or the opening of the recordset is causing update to fail. Go through the check list I gave you... resolving that problem will resolve it for the real thing as well.

Sometimes VB needs things in JUST the right order to work and if you have one tiny thing out (ie your DSN for ODBC doesn't use Mixed Security when connecting to SQL Server) then it simply wont work.

[carolyn]

Gen-X,

Yes, I went through your list:

1. Is the ODBC DSN created properly?
--> must have, as i'm able to get data from the database.

2. Have you run the "Test" on the ODBC and it said successful?
--> don't see this option.

3. Can you at least READ records from the database?
--> Yes.

4. Test to see what the RecordCount is for your clause first
--> = 2 (my test table has only 2 rows of data)

5. Reduce your update to only a single field
--> I'm updating the data one row at a time. It still fails when I'm only updating one column.

I'm using PostgreSQL as database (the free database that comes with Linux). I have grant all permission on the table to public.

If I run a "insert", "delete" or"update" SQL statement from VB application, the query executed successfully. I only have problem when I try to update the record set.

[Edited by carolyn on 03-22-2000 at 01:24 AM]

[Gen-X]

Carolyne,

Thank you for the feedback...

Seeing as you have done all that I would be looking at your OpenRecordset statement.

Why?

Because you said Insert/Update/Delete statements work in SQL... this means that the database.Execute works fine which means your permissions are correct.

This means specifically that when you open the recordset it is unable to UPDATE because something is wrong in how it was set up.

Have a look through the rest of the DBEngine.Errors to see if there are more messages than the one you gave me. The errors you are experiencing *could* be a result of the ODBC drivers that you have installed because doing an "Execute" sends the query DIRECTLY to the database to process... while using an .Update actually sends all the messages back and forth between ODBC and the database to perform the query (Ie SQLSETPOS and the like).

PostGreSQL isn't the best and if it comes with Linix then you are obviously crossing the PC/Unix boundary which could cause problems itself.

Sorry I can't help you further...

[karunakaran]

Hi,
After checking all the comments you try this also.
instead of putting rs.edit, you need to put rs.addnew.

then it should work.

thanks
karun

[carolyn]

To anyone who's interested,

The workspace's DefaultCursorDriver has to be set as follows:

WS.DefaultCursorDriver = dbUseODBCCursor