ADO VBNET update / addnew question

Question

0.00/5 (No votes)

See more:

I am migrating a VBNet programme from DAO to ADO. The database is an *.mdb database.
In DAO I performed the following: Opened the database, created a recordset by using an SQL select command. I then could update using a recordset.edit/.update or a .addnew command.
In ADO I do the folloing:

VB

Public conn3 As New ADODB.Connection
Dim rstGyles As ADODB.Recordset
gsCurrentFilePath = OpenFileDialog1.FileName
conn3.Provider = "Microsoft.Jet.OLEDB.4.0"
conn3.Open(gsCurrentFilePath)
rstGyles = conn3.Execute("SELECT * FROM bzGyle ORDER BY gNo DESC")

This works perfectly for reading the file and displaying data but doesn't allow me to update using:

VB

rstGyles.Edit, .Update or .Addnew

The recordset seems to be being created in read only mode.
I know the answer may be to move to SQL insert and update commands but I'd prefer to rule out any obvious mistake first.

What I have tried:

I have tried using

VB

'rstGyles.Open("SELECT * FROM bzGyle ORDER BY gNo DESC", conn3,, ADODB.LockTypeEnum.adLockOptimistic)

This gives me error 91 'object reference not set to an instance of an object'.

I've also tried running first because microsoft documents say the lock must be set before opening a recordset:

VB

rstGyles.LockType = ADODB.LockTypeEnum.adLockOptimistic 'tried in v3.0.4

but this comes up with the same message.

Posted 5-Mar-19 5:45am

eramm01

Updated 6-Mar-19 3:12am

Maciej Los

v2

Add a Solution

Comments

[no name] 5-Mar-19 13:39pm

Your showing "code fragments". Your asking for a "complete solution" instead of an answer to a question. Nobody is going to write this for you.

eramm01 5-Mar-19 17:22pm

I'm not looking for a complete solution (I cannot see those words in my question) I'm asking for a suggestion or a hint.

MadMyche 5-Mar-19 13:40pm

Is there any particular reason you are staying with the VB6ish code instead of going to a NET provider such as System.Data.OleDb?

eramm01 5-Mar-19 17:22pm

Yes - because I don't want to spend ages rewriting it. I'd prefer just to get it working.

Richard Deeming 5-Mar-19 16:43pm

I agree with Myche: if you're migrating the code anyway, better to use ADO.NET instead of the long-dead ADODB.

ADO.NET Overview | Microsoft Docs[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-03-06T03:13:00

I'm with the others who are suggesting biting that bullet and updating to ADO.NET - especially as you are already spending ages trying to overcome the problems caused by using out-of-date technology.

However, try the following to see if it gets you over your hump..

1. I'm pretty sure it's the Lock type that is generating the error so, instead of ADODB.LockTypeEnum.adLockOptimistic, try

rstGyles.LockType = adLockOptimistic

Alternatively try putting ADODB.LockType. and see what comes up with intellisense OR using

rstGyles.Open("SELECT * FROM bzGyle ORDER BY gNo DESC", conn3)

and confirm that the error goes away.

2. Try explicitly quoting the cursor type as adOpenDynamic rather than the default