-1

I'm trying to read QuaInMagazine (int value) from a SQL Server database. This is part of code:

sqlCon.Open();

using (SqlCommand sqlComm = new SqlCommand("Select QuaInMagazine from tbl_Parts2 where Name = '" + Name_txt.Text + "' and Number='" + Number_txt.Text + "'", sqlCon))
{
    using (SqlDataReader reader = sqlComm.ExecuteReader())
    {
        qua = (int)reader["QuaInMagazine"];
    }
}

qua = qua + Convert.ToInt32(Qua_txt.Text);

sqlCon.Close();

When I run it, I get an InvalidOperationException error at this instruction:

qua = (int)reader["QuaInMagazine"];

The error says that there wasn't any data. Any ideas what I'm doing wrong?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
WG97
  • 52
  • 1
  • 2
  • 6
  • Yo say you are trying to read, but ironically you are missing the `IDataReader.Read()` call. – Cleptus Aug 07 '18 at 10:25
  • 1
    pretty sure most ADO.NET tutorials would show you how to write this properly, as per the answers below. Perhaps you just missed a step by accident. – ADyson Aug 07 '18 at 10:25
  • 1
    also consider using parameterized query instead of passing parameters and building sql query. – Anupam Singh Aug 07 '18 at 10:28
  • 2
    Oh can I point out you have a SQL injection flaw in your code. You should not take input from a user field and pass it straight to SQL without sanitising it. What you are doing is very dangerous. https://xkcd.com/327/ – Ian Newson Aug 07 '18 at 10:29
  • I would suggest using an ORM like PetaPoco or Dapper to simplify dealing with null values. – mjwills Aug 07 '18 at 11:30
  • Possible duplicate of [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) – mjwills Aug 07 '18 at 11:31
  • Hi, can I remind you to please accept the answer you found most useful please?That's how others will get the most out of StackOverflow. :) – Ian Newson Aug 28 '18 at 00:31

3 Answers3

3

You're retrieving a set of data, not a single value. You need to use the Read method to iterate over the set and retrieve the values:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader

Here's an example from Microsoft:

using (connection)
{
    SqlCommand command = new SqlCommand(
      "SELECT CategoryID, CategoryName FROM Categories;",
      connection);
    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
        {
            Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
                reader.GetString(1));
        }
    }
    else
    {
        Console.WriteLine("No rows found.");
    }
    reader.Close();
}

EDIT:

Oh can I point out you have a SQL injection flaw in your code. You should not take input from a user field and pass it straight to SQL without sanitising it. What you're doing is very dangerous.

https://xkcd.com/327/

Ian Newson
  • 7,679
  • 2
  • 47
  • 80
  • 1
    +1 On the edit, btw there is also a full webpage about [bobby tables and avoiding SQLi in different languages](http://bobby-tables.com/) – Cleptus Aug 07 '18 at 10:41
0

You normally have a loop that starts with 

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

You should call that at least once, even if you don't actually want a loop. You could use an if instead of the while.

nvoigt
  • 75,013
  • 26
  • 93
  • 142
  • @WG97 thats weird, that code is pretty common. I would check if the `InvalidCastOperation` is returned at that particullar line. Do note that `IDataReader.Read() `method does return a bool, so whe while makes perfect sense. – Cleptus Aug 07 '18 at 10:31
0
while(reader.Read()) 
{
    if(reader["QuaInMagazine"] != DBNull.Value)
    {
        int quaInMagazineOrdinal = reader.GetOrdinal("QuaInMagazine"); 
        qua = reader.GetInt32(quaInMagazineOrdinal);
    }
}
Cleptus
  • 3,446
  • 4
  • 28
  • 34
Bayram Akbuz
  • 75
  • 1
  • 7
  • if the select statement changes later and retrieves more than one column, your code would be problematic. I would suggest `qua = (int)reader["QuaInMagazine"];` – Cleptus Aug 07 '18 at 10:28
  • it is more powerful to use – Bayram Akbuz Aug 07 '18 at 10:31
  • int quaInMagazineOrdinal = reader.GetOrdinal("QuaInMagazine"); qua = reader.GetInt32(quaInMagazineOrdinal); – Bayram Akbuz Aug 07 '18 at 10:34
  • 1
    More powerful? According to [the doc](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getint32(v=vs.110).aspx) that method performs no conversion, so its just the same and you avoid potential errors if the number of fields retrieved change, and avoid unnecesary instructions like `GetOrdinal` – Cleptus Aug 07 '18 at 10:36