I am trying to run the SQL Select query in my C# code. But I always get the -1 output on
int result = command.ExecuteNonQuery();
However, the same table if I use for delete or insert works…
ConnectString is also fine.
Please check below code
SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password=");
conn.Open();
SqlCommand command = new SqlCommand("Select id from [table1] where <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bad4dbd7df87fac0d3ca">[email protected]</a>", conn);
//command.Parameters.AddWithValue("@zip","india");
int result = command.ExecuteNonQuery();
// result gives the -1 output.. but on insert its 1
using (SqlDataReader reader = command.ExecuteReader())
{
// iterate your results here
Console.WriteLine(String.Format("{0}",reader["id"]));
}
conn.Close();
The query works fine on SQL Server, but I am not getting why only select query is not working.
All other queries are working.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
SqlCommand.ExecuteNonQuery Method
You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.
Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
SqlCommand.ExecuteScalar Method
Executes a Transact-SQL statement against the connection and returns the number of rows affected.
So to get no. of statements returned by SELECT statement you have to use ExecuteScalar method.
So try below code:
SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password=");
conn.Open();
SqlCommand command = new SqlCommand("Select id from [table1] where <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="b9d7d8d4dc84f9c3d0c9">[email protected]</a>", conn);
command.Parameters.AddWithValue("@zip","india");
// int result = command.ExecuteNonQuery();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
Console.WriteLine(String.Format("{0}",reader["id"]));
}
}
conn.Close();
Method 2
According to MSDN
result is the number of lines affected, and since your query is select no lines are affected (i.e. inserted, deleted or updated) anyhow.
If you want to return a single row of the query, use ExecuteScalar() instead of ExecuteNonQuery():
int result = (int) (command.ExecuteScalar());
However, if you expect many rows to be returned, ExecuteReader() is the only option:
using (SqlDataReader reader = command.ExecuteReader()) {
while (reader.Read()) {
int result = reader.GetInt32(0);
...
}
}
Method 3
you can use ExecuteScalar() in place of ExecuteNonQuery() to get a single result
use it like this
Int32 result= (Int32) command.ExecuteScalar();
Console.WriteLine(String.Format("{0}", result));
It will execute the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
As you want only one row in return, remove this use of SqlDataReader from your code
using (SqlDataReader reader = command.ExecuteReader())
{
// iterate your results here
Console.WriteLine(String.Format("{0}",reader["id"]));
}
because it will again execute your command and effect your page performance.
Method 4
That is by design.
For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
Method 5
you have to add parameter also @zip
SqlConnection conn = new SqlConnection("Data Source=;Initial Catalog=;Persist Security Info=True;User ID=;Password=");
conn.Open();
SqlCommand command = new SqlCommand("Select id from [table1] where <a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="2b454a464e166b51425b">[email protected]</a>", conn);
//
// Add new SqlParameter to the command.
//
command.Parameters.AddWithValue("@zip","india");
int result = (Int32) (command.ExecuteScalar());
using (SqlDataReader reader = command.ExecuteReader())
{
// iterate your results here
Console.WriteLine(String.Format("{0}",reader["id"]));
}
conn.Close();
Method 6
You should use ExecuteScalar() (which returns the first row first column) instead of ExecuteNonQuery() (which returns the no. of rows affected).
You should refer differences between executescalar and executenonquery for more details.
Hope it helps!
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0