CODEWITHSUNDEEP
c#sql.netsql-server
Eren Hatırnaz
Eren Hatırnaz

Reputation: 63

Returning an integer value from SQL Server

I have an SQL query as follows:

SqlConnection conn = new SqlConnection(connectionString);

conn.Open();

SqlCommand cmd = new SqlCommand("SELECT id FROM Pages WHERE pageName=about",conn);
//cmd.Parameters.Add("@pageName","hakkinda");

SqlDataReader reader = cmd.ExecuteReader();

flID = reader.GetInt16(0);

reader.Close();
conn.Close();

I get an error message:

Invalid attempt to read when no data is present.

What's wrong?

Upvotes: 3

Views: 3632

Answers (4)

Hedrack
Hedrack

Reputation: 774

If you expect only single value to be returned, you can use .ExecuteScalar()

flID = int.Parse(query.ExecuteScalar().ToString());

Also, use single quotation marks for pageName value.

SqlCommand cmd = new SqlCommand("SELECT id FROM Pages WHERE pageName='about';",conn);

Upvotes: 0

Jainendra
Jainendra

Reputation: 25143

You have to call DataReader.Read to fetch the result:

SqlDataReader reader = cmd.ExecuteReader();
reader.Read();

DataReader.Read returns a boolean, so if you have more than 1 result, you can do:

while (reader.Read()) {
  // read data here
}

Your select statement should be:

"SELECT id FROM Pages WHERE pageName='about'"

Upvotes: 0

Dylan Smith
Dylan Smith

Reputation: 22235

I notice a couple potential issues:

You need to call reader.Read(), before trying to read data from it. This is usually done in a loop when people expect multiple rows.

while (reader.Read()) {
    flID = reader.GetInt16(0);
}

also in your SQL if "about" is meant to be a literal and not another column name you probably need single quotes around it:

"SELECT id FROM Pages WHERE pageName='about'"

Upvotes: 3

Keith
Keith

Reputation: 21244

Your query is returning 0 results. This is because the parameter value in the SQL string is missing quotes. It should read as follows (notice the single quotes around the word 'about'):

SqlCommand cmd = new SqlCommand("SELECT id FROM Pages WHERE pageName='about'",conn);

Upvotes: 0

Related Questions