ExecuteNonQuery

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nbohana - 29 Sep 2005 21:17 GMT

I am trying to get the number of records selected using the ExecuteNonQuery
(MARKED BELOW BY ***). It does not crash but returns a "-1". The select
returns one record. What am I doing wrong? please help.

strSQL = "SELECT [med-id], [animal-id], [animal-name], medication, date,
dosage, frequency, [vet-id], [veterinarian-name], notes FROM [medication]
WHERE [animal-id] = " + anum1;

cmSQL = new SqlCommand(strSQL, cnKennel);

if (cnKennel.State != ConnectionState.Open)
cnKennel.Open();

*** intRowsAffected = cmSQL.ExecuteNonQuery(); ***

drSQL = cmSQL.ExecuteReader();

if(drSQL.Read())
{
txtMedID.Text = drSQL["med-id"].ToString();
txtAnimalID.Text = drSQL["animal-id"].ToString();
txtAnimalName.Text = drSQL["animal-name"].ToString();
txtMedication.Text = drSQL["medication"].ToString();
txtDate.Text = drSQL["date"].ToString();
txtDosage.Text = drSQL["dosage"].ToString();
txtFreq.Text = drSQL["frequency"].ToString();
txtVetID.Text = drSQL["vet-id"].ToString();
gbVetName.Text = drSQL["veterinarian-name"].ToString();
txtMedNotes.Text = drSQL["notes"].ToString();
}

Signature

Norm Bohana

Reply to this Message

Marina - 29 Sep 2005 21:31 GMT

This is all in the documentation, if you had looked.

From the ExecuteNonQuery method:

For UPDATE, INSERT, and DELETE statements, the return value is the number of
rows affected by the command. For all other types of statements, the return
value is -1. If a rollback occurs, the return value is also -1.

Meaning, you should not be using this method if you are running some
returning a result set.

So, you need to either:
1) Run a count(*) type query and get the result via ExecuteScalar
2) Use a DataTable instead of walking through a SqlDataReader
3) Have a variable that you increment by one each time you call .Read that
returns True on your data reader, so that you don't have to run 2 queries
(as you would using method #1).

>I am trying to get the number of records selected using the ExecuteNonQuery
> (MARKED BELOW BY ***). It does not crash but returns a "-1". The select
[quoted text clipped - 26 lines]
> txtMedNotes.Text = drSQL["notes"].ToString();
> }

Reply to this Message

nbohana - 29 Sep 2005 21:42 GMT

Thank you very much, I did look, just didn't see it. Sorry

Signature

Norm Bohana

> This is all in the documentation, if you had looked.
>
[quoted text clipped - 44 lines]
> > txtMedNotes.Text = drSQL["notes"].ToString();
> > }

Reply to this Message

Elton W - 29 Sep 2005 23:58 GMT

In addition to Marina’s comment, I notice that you used

if(drSQL.Read())
{
// …
}

Unless you only want to get first one record in the datareader, it’s better
to use
while(drSQL.Read())
{
//…
}

And if you need only first record, you can use
drSQL = cmSQL.ExecuteReader(CommandBehavior.SingleRow)

HTH

Elton Wang

> Thank you very much, I did look, just didn't see it. Sorry
>
[quoted text clipped - 46 lines]
> > > txtMedNotes.Text = drSQL["notes"].ToString();
> > > }

Reply to this Message

ExecuteNonQuery

Free Magazines