Home » Infrastructure » Windows » UPDATE does not work in VB6
UPDATE does not work in VB6 [message #197224] Tue, 10 October 2006 10:26 Go to next message
astrosmurf
Messages: 4
Registered: October 2006
Junior Member
I am connecting to an Oracle database from Visual Basic 6 using ADO. From what I can see in the ODBC settings and what I've been told by the DB admin I have write-rights to the database.

I can read from the db with no problems but when I try and write to it nothing happens. No error messages or anything, but nothing gets written either. Examples of what I've tried (Conn is a connection object, RecSet is a recordset object. Conn.Mode = adModeReadWrite.):

 
        Dim cmd As ADODB.Command
        Set cmd = New ADODB.Command
        Dim recordsAffected
        cmd.CommandType = adCmdText
        cmd.ActiveConnection = Conn
        cmd.CommandText = "update TABLE set OPERATOR = 'joe' where (ID = " & id & ")"
        cmd.Execute recordsAffected

        RecSet.CursorType = adOpenStatic
        RecSet.LockType = adLockOptimistic
        RecSet.CursorLocation = adUseClient ' or adUseServer
        RecSet.Open "Select OPERATOR from TABLE where (ID = " & id & ")"
        RecSet!OPERATOR = "joe"
        RecSet.Update
        RecSet.Close

        Conn.Execute "update TABLE " _
                    & "set OPERATOR = 'joe' " _
                    & "where ID = " & id & " "
        Conn.Execute "commit"


What do I do wrong?
Re: UPDATE does not work in VB6 [message #197255 is a reply to message #197224] Tue, 10 October 2006 14:49 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know anything about Visual Basic, so this doesn't have to mean much at all.

In order to make changes in Oracle database visible to other users, you have to COMMIT them. There IS a commit statement in the last part of the code in your post, but - what happens if you put the red statement as the last one in the FIRST part of your code:

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim recordsAffected
cmd.CommandType = adCmdText
cmd.ActiveConnection = Conn
cmd.CommandText = "update TABLE set OPERATOR = 'joe' where (ID = " & id & ")"
cmd.Execute recordsAffected
Conn.Execute "commit"
Re: UPDATE does not work in VB6 [message #197382 is a reply to message #197255] Wed, 11 October 2006 02:01 Go to previous messageGo to next message
astrosmurf
Messages: 4
Registered: October 2006
Junior Member
Nothing. (I.e. nothing changes in program behaviour. Still no writing to the database.)
Re: UPDATE does not work in VB6 [message #197398 is a reply to message #197382] Wed, 11 October 2006 02:36 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There are "SELECT ... FROM TABLE" and "UPDATE TABLE ..." - what is "TABLE"? Is it a real table name? Shouldn't be, as TABLE is a reserved word. There is, though, a possiblity to create a table whose name is "TABLE", but it must be enclosed into double quotation marks, like in this example:
SQL> create table "table" (id number);

Table created.

SQL> insert into "table" values (1);

1 row created.

SQL> update table set id = 2;
update table set id = 2
       *
ERROR at line 1:
ORA-00903: invalid table name

SQL> update "table" set id = 2;

1 row updated.

SQL> select * From "table";

        ID
----------
         2
But, Oracle refuses to work with invalid table name - that's strange, because you said there weren't any error messages.

I'm sorry, but I don't know what else might be wrong. Usually, it is lack of COMMIT statement that prevents users to see entered (or updated) data, but - as nothing happened although you did commit changes you've made - I have no idea.

Unless - are you SURE Conn.Execute "commit" is a proper way to do it? Is there some other way to do it?
Re: UPDATE does not work in VB6 [message #197432 is a reply to message #197398] Wed, 11 October 2006 04:40 Go to previous messageGo to next message
astrosmurf
Messages: 4
Registered: October 2006
Junior Member
Oh, pardon. TABLE is just my attempt at anonymizing the code. In reality it's called KORT_FABRIK. (The database is in Sweden.)

Quote:

Unless - are you SURE Conn.Execute "commit" is a proper way to do it?


Not at all. It was suggested by an acquaintance who is knowledgeable about VB and databases but who has never worked with an Oracle db.
Re: UPDATE does not work in VB6 [message #197455 is a reply to message #197432] Wed, 11 October 2006 05:26 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh! It seems that we are bunch of blind men trying to paint a nice picture.

Googling around brought

Connection.CommitTrans

- try to put it instead of

Conn.Execute "commit"

I'm almost 100% sure it won't work, but I have nothing left to offer.
Re: UPDATE does not work in VB6 [message #197949 is a reply to message #197455] Fri, 13 October 2006 07:05 Go to previous messageGo to next message
astrosmurf
Messages: 4
Registered: October 2006
Junior Member
Problem solved. I spoke to the DB admin and it turns out that TABLE (KORT_FABRIK) isn't a table as I thought, but a "view". And he has told me how to update it; I needed to first update specific fields to trigger updates in others, and such. Now it works.

Thanks for all the helpful suggestions.
Re: UPDATE does not work in VB6 [message #197951 is a reply to message #197949] Fri, 13 October 2006 07:37 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, a view! I'm glad you finally solved the problem http://www.orafaq.com/forum/fa/451/0/
Previous Topic: Complete Installation Guide in Text Format Required ?
Next Topic: Re: running 2 databases with the same name on on 1 host
Goto Forum:
  


Current Time: Fri Mar 29 00:56:27 CDT 2024