SQL Select Query Not Selecting

Question

I'm trying to query data from an SQL table using C# using System.Data.SqlClient;. I'm able to SELECT * FROM table without problem. I suspect my problem may be due to data types, and perhaps unintentionally casting some variables incorrectly. The error I get is: Invalid attempt to read data when no data is present.

Further details:

In my SQL Table, Column1 and Column3 are of type int. Column2 and Column4 are of type char(1). Column5 is of type decimal(5,4).

Could you please help identify the problem in following code?

public void Score(List<Tuple<int, char>> myList)
    {
        int myColumn1 = myList[0].Item1;
        char myColumn2 = myList[0].Item2;
        int myColumn3 = myList[1].Item1;
        char myColumn4 = myList[1].Item2;

        myConnection.Open();

        SqlCommand myCommand = new SqlCommand("SELECT * FROM table" +
        " WHERE Column1 = " + myColumn1 + 
        " AND Column2 = '" + myColumn2 +
        "' AND Column3 = " + myColumn3 +
        " AND Column4 = '" + myColumn4 + "'", myConnection);

        SqlDataReader myReader = null;
        myReader = myCommand.ExecuteReader();

        Console.Out.WriteLine(myReader[5].ToString());         
    }

Thank you!

You need to look into using parameters for your sql. You're opening yourself up to SQL injection attacks doing what you have written so far. — Robbert, May 14 '14 at 04:00
duplicate of http://stackoverflow.com/questions/1147615/invalid-attempt-to-read-when-no-data-is-present, http://stackoverflow.com/questions/18744949/invalid-attempt-to-read-data-when-no-data-is-present, http://stackoverflow.com/questions/19864837/invalid-attempt-to-read-when-no-data-is-present-in-asp-net, http://stackoverflow.com/questions/4628698/invalid-attempt-to-read-when-no-data-is-present-in-dr, http://stackoverflow.com/questions/11549515/datareader-invalid-attempt-to-read-when-no-data-is-present, and others. — Nathan, May 14 '14 at 04:06
Are your char columns padding with white space, preventing a match? — Tsarin, May 14 '14 at 04:12

Grant Winney · Accepted Answer · 2014-05-14T04:12:55.710

4

After retrieving the data from the database, you have to Read it:

while (myReader.Read())
{
    Console.Out.WriteLine(myReader[5].ToString());
}

You can see an example on MSDN.

A couple other notes:

If myReader[5] might be null, then consider using Convert.ToString(myReader[5]). That'll return an empty string instead of throwing an exception.
If you're using .NET 4.5, there's an alternate syntax you could try: myReader.GetFieldValue<string>(5)

edited May 14 '14 at 04:12

answered May 14 '14 at 04:01

Grant Winney

65,241
13
115
165

2

Also, you can condense that line into `var myReader = myCommand.ExecuteReader();`. – Simon Whitehead May 14 '14 at 04:05

Nagaraj S · Answer 2 · 2014-05-14T04:13:46.417

Use paremeterized query to avoid sql injectioin

myCommand = new SqlCommand("SELECT * FROM table
        WHERE Column1=@column1
         AND Column2 =@myColumn2 
         AND Column3 = @myColumn3 
         AND Column4 =@myColumn4", myConnection);

  myCommand.Parameters.AddWithValue("@column1", value1);
  myCommand.Parameters.AddWithValue("@myColumn2", value2);
  myCommand.Parameters.AddWithValue("@myColumn3", value3);
  myCommand.Parameters.AddWithValue("@myColumn3", value4);

  using(SqlDataReader reader = myCommand.ExecuteReader())
  {
    while(reader.Read())
    {
      //code here
    }
  }

SQL Select Query Not Selecting

2 Answers2