CODEWITHSUNDEEP
c#sqloledb
Aximili
Aximili

Reputation: 29464

UPSERT query/syntax in OLE DB (MS Access)

For SQL Server, I could do this

UPDATE [Table] SET b=2, c=3 WHERE a=1;
IF @@ROWCOUNT=0 
INSERT INTO [Table] (a,b,c) VALUES (1,2,3)

How do you do a similar thing on MS Access (using OleDbConnection)?

Doing that I got

Characters found after end of SQL statement.

Upvotes: 0

Views: 813

Answers (1)

Steve
Steve

Reputation: 216293

I don't think the Jet/ACE OleDB engine has an equivalent of T-SQL syntax for this kind of problem.

You should go for the long route of checking if record exist, then decide for INSERT or UPDATE.
However, being Access mainly a single/local user database system you should not have many problems doing something like this pseudocode:

using(OleDbConnection cn = new OleDbConnection(constring))
{
     cn.Open();
     using(OleDbCommand cmd = new OleDbCommand("select count(*) from table where pkID = ?", cn);
     {
        cmd.Parameters.AddWithValue("pk", myID);
        int result = Convert.ToInt32(cmd.ExecuteScalar());
        if(result == 0)
           // do your insert command here
        else
           // do your update command here
     }
}

Of course, as I have said, this doesn't take into account concurrency problems.

Upvotes: 1

Related Questions