c# and oledb provider for msaccess

[Eric Kiernan]

First, I don't know if this is the appropriate forum for asking oledb
SQL questions with C#. If someone knows a more appropriate group, let
me know. I have a table ( in an access mdb file )called Accounts, with
one field, Account. I have an List AccountList filled with unique
account names. Before I add them to the table, I want to make sure they
are not already there. After several failures, I bracketed the field
Account with [Account] which was successful, executed the ExecuteReader,
and did the INSERT ( with parameters), because the reader had no rows.
From every iteration after, when I do the ExecuteReader, the reader
claims it has rows, and never does another insert. The line retrived
from the select to the reader is always the same account, the only one
in the database. It's almost as if the reader doesn't clear out the
previous result. The connection is already open prior to executing this
code.

private void checkAccount()
---------------------------
OleDbDataReader reader;
int j;
for (int i = 0; i < AccountList.Count; i++) {
objCommand.Parameters.Clear();
objCommand.Parameters.AddWithValue("@Account", AccountList[i]);
objCommand.CommandText = "select * from Accounts where [Acount] = "
+ "'"+ AccountList[i] + "'";

reader = objCommand.ExecuteReader();
if (!reader.HasRows) {
reader.Close();
objCommand.CommandText = "INSERT INTO Accounts ( Account )
VALUES (@Account )";
j = objCommand.ExecuteNonQuery();
}
else {
reader.Close();
}
sleep(20);
}

[Erland Sommarskog]

Eric Kiernan (elki...@mail.com) writes:
> First, I don't know if this is the appropriate forum for asking oledb
> SQL questions with C#. If someone knows a more appropriate group, let
> me know.

This group is about using the native OLE DB interface. I think
microsoft.public.dotnet.framework.adonet is a better option. Or a newsgroup
for Access.

I have a table ( in an access mdb file )called Accounts, with
> one field, Account. I have an List AccountList filled with unique
> account names. Before I add them to the table, I want to make sure they
> are not already there. After several failures, I bracketed the field
> Account with [Account] which was successful, executed the ExecuteReader,
> and did the INSERT ( with parameters), because the reader had no rows.

Do you really need to make it that complex? In SQL Server you could
have used IF EXISTS, or even

INSERT Accounts(Account)
SELECT @account
WHERE NOT EXISTS (SELECT *
FROM Accounts
WHERE Account = @account)

But I don't know if you can do this in Access.

> objCommand.CommandText = "select * from Accounts where [Acount] = "
> + "'"+ AccountList[i] + "'";

Shouldn't the command text be

select * from Accounts where [Account] = @account

(Note that you have "Acount" in your query, looks like a typo.

Also, I was under the impression that you cannot use @variables with
OleDb client, but you had to use ? as parameter holder. But since your
INSERT works, I assume you can.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Eric Kiernan]

You are a life-saver. It was indeed my misspelling of Account, and
using Acount that was the problem. The great mystery is why it didn't
blow up since Acount doesn't exist as a database field. This problem was
really driving me nuts, but i'm surprised at how simple the solution
was. Thanks for your other advice, I'll play with it. As far as
parameters, they work great with an INSERT/UPDATE, I'll play with the
SELECT as well.