Vb6 odbc connection won't save my record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Vb6 odbc connection won't save my record

Author

Topic

deedee
Starting Member

3 Posts

Posted - 2005-07-05 : 06:48:20

Hi,

My application does communicate with the SQL database using ODBC. I can read from it and browse. When I make a connection using access, using the same user adding records is possible.

When I use my vb code it works fine until I try to add a new record and update. I get a message that the database does not allow this because of insufficient rights. This happens with the same user as mentioned above. Even the sa user has the same result.

Does anyone know the solution or does anyone have some sample code how to write into an sql database using odbc?

Deedee

deedee
Starting Member

3 Posts

Posted - 2005-07-05 : 07:41:01

And this was my code which failed

Private Sub Command1_Click()

Set wspace = CreateWorkspace("ws", "user", "pw", dbUseODBC)
Set conodbc = wspace.OpenConnection("user", dbDriverNoPrompt, False, "ODBC;database=ttx;UID=user;PWD=pw;dsn=ttx")
Set rstodbc = conodbc.OpenRecordset("select * from etiket", dbOpenDynamic, 0, dbOpenDynaset)
rstodbc.MoveFirst

MsgBox rstodbc.Fields(1).Value 'this works fine

rstodbc.AddNew 'seems to work as well

rstodbc.Fields(1).Value = "test" 'odbc call fails runtime error 3146

rstodbc.Update
End Sub

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-07-05 : 09:19:44

I think you're misunderstanding the contents of the recordset....
it's a READONLY recordset....based on the statement "SELECT * from etc,etc"

It's not the same as a direct-linked recordset to an access table....where the table is editable.

You'll need to issue an "Insert into etiket (cola, colb, colc) values (a,b,c)" style statement to get the new data into the sql database.

deedee
Starting Member

3 Posts

Posted - 2005-07-07 : 03:26:01

Thanks Andrew.

I just found out that using the ADO object does the trick. I was using the dao. The procedure is the same as I used in my original code. Making selections did not make any difference.

Deedee

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-07-08 : 05:07:13

the reason why DAO failed is because after adding the row, you need to move the cursor from the one you've added so it will be saved,
ADO automatically saves without moving the cursor

--------------------
keeping it simple...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-08 : 09:09:44

Try to get out of the habit of opening recordsets (in either ADO or DAO) and updating or adding rows directly.

It is much better practice (and in .NET the only way to do it) to use either stored procedures and/or parameterized INSERT statements and to call commands to add or change data in your tables.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources