Re-Posting on SQL Query from C#-It's driving me crazy

  • Thread starter Thread starter Eric Kiernan
  • Start date Start date
E

Eric Kiernan

First, I don't know if this is the appropriate forum for asking SQL
questions with C#. If someone knows a more appropriate SQL group, let
me know ( they all seem geared to one vendor). 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. I did a
select for the first account,executed the ExecuteReader, and it
correctly said HasRows = false, did the INSERT. From every iteration
after, when I do the ExecuteReader, the reader claims it has rows, and
never does another insert. The ExecuteReader always returns the first
record added, and i've verified the command string is correct on each
iteration, changing the account name i'm searching for. The connection
is already open prior to executing this code. Sorry for reposting but
it really is driving me nuts.

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

reader = objCommand.ExecuteReader();
if (!reader.HasRows) {
reader.Close();
objCommand.CommandText = "INSERT INTO Accounts ( Account )
VALUES (@Account )";
j = objCommand.ExecuteNonQuery();
}
else {
reader.Close();
}
sleep (20);
}
 
[...] Sorry for reposting but it really is driving me nuts.
Click to expand...

Just because it's driving you nuts, that doesn't mean there's any use at
all in reposting your question. Contrary to what you may think, we do try
our best to answer questions. If you don't get a reply, it's not because
you need to post your question again. It's because no one had an answer
(which is never all that surprising for off-topic posts).

There probably is in fact a forum somewhere around where questions about
SQL support in .NET are on-topic. Posting here, you're just hoping to
luck out that someone following a C# newsgroup also happens to know enough
about SQL to answer your question.

Pete
 
sorry

Peter said:
[...] Sorry for reposting but it really is driving me nuts.
Click to expand...

Just because it's driving you nuts, that doesn't mean there's any use at
all in reposting your question. Contrary to what you may think, we do
try our best to answer questions. If you don't get a reply, it's not
because you need to post your question again. It's because no one had
an answer (which is never all that surprising for off-topic posts).

There probably is in fact a forum somewhere around where questions about
SQL support in .NET are on-topic. Posting here, you're just hoping to
luck out that someone following a C# newsgroup also happens to know
enough about SQL to answer your question.

Pete
Click to expand...
 
Eric said:
First, I don't know if this is the appropriate forum for asking SQL
questions with C#. If someone knows a more appropriate SQL group, let
me know ( they all seem geared to one vendor). 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. I did a
select for the first account,executed the ExecuteReader, and it
correctly said HasRows = false, did the INSERT. From every iteration
after, when I do the ExecuteReader, the reader claims it has rows, and
never does another insert. The ExecuteReader always returns the first
record added, and i've verified the command string is correct on each
iteration, changing the account name i'm searching for. The connection
is already open prior to executing this code. Sorry for reposting but
it really is driving me nuts.

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

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


I don't do a lot of db coding, but these jump out at me:

1. Why do you add Parameters in the first command (Select * from
Accounts where [Acount] = 'some account'"), when there are no
parameters? The parameters are in your second call. I'm not sure if
that will affect the first call.

2. The indexes look fine in spite of David's response, but why use them
at all? Just use:

foreach (string s in AccountList) // and use s as the value to
test/store, not AccountList[idx].

3. Use Using statements, or at least dispose of things that can be
disposed. Personally, I don't like reusing the objCommand object the
way you are here. You haven't really closed it properly, when you go
onto using it in the if (!reader.HasRows) section.
 
I must be missing something, I'm don't believe i'm counting too many
times because my cntr is zero based but it is < count, which is one
based, that's why i aid < instead <=.
 
1. I used parameters originally in the first select and discarded them
because i wasn't getting them to work properly. But I kept them since
they worked in the insert statement.
2.Re-using the command object. I thought of the same thing and started
disposing of them, and creating new ones. It didn't make any difference.
Eric said:
First, I don't know if this is the appropriate forum for asking SQL
questions with C#. If someone knows a more appropriate SQL group, let
me know ( they all seem geared to one vendor). 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. I did a
select for the first account,executed the ExecuteReader, and it
correctly said HasRows = false, did the INSERT. From every iteration
after, when I do the ExecuteReader, the reader claims it has rows, and
never does another insert. The ExecuteReader always returns the first
record added, and i've verified the command string is correct on each
iteration, changing the account name i'm searching for. The
connection is already open prior to executing this code. Sorry for
reposting but it really is driving me nuts.

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

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


I don't do a lot of db coding, but these jump out at me:

1. Why do you add Parameters in the first command (Select * from
Accounts where [Acount] = 'some account'"), when there are no
parameters? The parameters are in your second call. I'm not sure if
that will affect the first call.

2. The indexes look fine in spite of David's response, but why use them
at all? Just use:

foreach (string s in AccountList) // and use s as the value to
test/store, not AccountList[idx].

3. Use Using statements, or at least dispose of things that can be
disposed. Personally, I don't like reusing the objCommand object the
way you are here. You haven't really closed it properly, when you go
onto using it in the if (!reader.HasRows) section.
Click to expand...
 
Boy was it dumb. I was spelling the field Account with one c in the
select statement. Why it didn't blow up is beyond me since there is no
field called Acount.

Eric said:
1. I used parameters originally in the first select and discarded them
because i wasn't getting them to work properly. But I kept them since
they worked in the insert statement.
2.Re-using the command object. I thought of the same thing and started
disposing of them, and creating new ones. It didn't make any difference.
Eric said:
First, I don't know if this is the appropriate forum for asking SQL
questions with C#. If someone knows a more appropriate SQL group,
let me know ( they all seem geared to one vendor). 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. I did a select for the first account,executed the
ExecuteReader, and it correctly said HasRows = false, did the
INSERT. From every iteration after, when I do the ExecuteReader, the
reader claims it has rows, and never does another insert. The
ExecuteReader always returns the first record added, and i've
verified the command string is correct on each iteration, changing
the account name i'm searching for. The connection is already open
prior to executing this code. Sorry for reposting but it really is
driving me nuts.

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

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


I don't do a lot of db coding, but these jump out at me:

1. Why do you add Parameters in the first command (Select * from
Accounts where [Acount] = 'some account'"), when there are no
parameters? The parameters are in your second call. I'm not sure if
that will affect the first call.

2. The indexes look fine in spite of David's response, but why use
them at all? Just use:

foreach (string s in AccountList) // and use s as the value to
test/store, not AccountList[idx].

3. Use Using statements, or at least dispose of things that can be
disposed. Personally, I don't like reusing the objCommand object the
way you are here. You haven't really closed it properly, when you go
onto using it in the if (!reader.HasRows) section.
Click to expand...
Click to expand...
 
Boy was it dumb. I was spelling the field Account with one c in the
select statement. Why it didn't blow up is beyond me since there is no
field called Acount.
Click to expand...

Just goes to show why a concise-but-complete code example that reliably
demonstrates the problem needs to be included.

The first time I read your question, I noticed that you had typed the
column name as "Acount". But you stated in your original post that the
first row was added, and that subsequent checks always turned up one row.
Having to rely on your problem description, I was able to "rule out" the
column name as a potential problem, because (it seemed to me) if that was
really the problem, your reader shouldn't have returned any rows (since
you added the new row with the correct spelling).

Had you posted a concise-but-complete code example, even those of us
SQL-newbies could have easily tested your code and seen exactly what was
going on rather than relying on your problem report.

Of course, in the end you did discover the spelling error on your own. So
all's well that ends well. But please take the lesson to heart: a "plain
English" description of a problem isn't nearly as precise as actual
working code. You can get much better help if you provide the latter.

Pete
 
Back
Top