| UPDATE does not work in VB6 [message #197224] |
Tue, 10 October 2006 10:26  |
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 #197398 is a reply to message #197382] |
Wed, 11 October 2006 02:36  |
 |
Littlefoot
Messages: 21823
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
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 |
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 #197949 is a reply to message #197455] |
Fri, 13 October 2006 07:05 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
