Apologies - a bit off topic - Ole data updates

Elements Oxygene
1

I still find using OleDB stuff much more difficult than the ADOQuery in Delphi. Ignoring whether the way I’m trying to do it is the best, I’m stuck.

I have a valid OleDB Connection opened successfully into an Access mdb.
I want to update (edit) a field value in the table tblUsers in one row. The table in Access actually has just one row with UserID = Admin My code:

var TheSQL: String;
var OleDbAdapt2 := new System.Data.OleDb.OleDbDataAdapter;
OleDbAdapt2.UpdateCommand := clsVariables.TheDBOleDbConn.CreateCommand; // clsVariables.TheDBOleDbConn is the valid open connection
TheSQL := ‘update tblUsers set UserCurrentStatus = ’ + clsCommon.DoubleQuoteString(‘In’); // + ’ where UserID = “Admin”’; this works, but not with the where clause included in TheSQL
OleDbAdapt2.UpdateCommand.CommandText := TheSQL;
OleDbAdapt2.UpdateCommand.ExecuteNonQuery;

The OleDbAdapt2.UpdateCommand.ExecuteNonQuery fails if the where clause is in TheSQL string, but works ok if the where clause is not.

Error:

No value given for one or more required parameters.

Any thoughts, comments, raucous laughter welcomed

2

Update - it works with the where clause if the field is the primary key!

Is this expected?

Bob Russell

3

Can you post the working and non working SQL (not code)?

4

Working SQL for a successful table row update (TheGuid is the primary key field in the Access table tblUsers :

‘update tblUsers set UserCurrentStatus = “In” where TheGuid = “{DBDC78F8-1D95-41D2-A9CE-92245DDFFA97}”’

Non Working SQL (TheID is not the primary key, but is defined as a non duplicate index field in the Access table)

'update tblUsers set UserCurrentStatus = “In” where TheID = “Admin”

Thanks for your help.’

5

What is the type of the “TheID” field?

6

TheID is a shorttext field in Access. TheGuid is also a shorttext field in Access.

Regards Bob Russell

7

I think here is your answer (I think the second one):

8

Thanks for this - useful. I’ve just tested and to my surprise, using [TheID] didn’t work. So I re-checked my Access table, and found that the field is actually named ID, not TheID. Aaargh! Having spent hours on this it looks as if I totally confused myself. So I tried using [ID] and that was OK, I then tried just using ID as the field name and it still worked - so ID is not confused with a Keyword.

Anyway, I appreciate your efforts on my behalf and have learnt something about the quirks of Access!

Regards Bob Russell

1 Like