ExecuteNonQuery

[Guest]

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();
}

 

[Marina]

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).

 

[Guest]

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

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
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();
}

 

[Guest]

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
--
Norm Bohana

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
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();
}