Error: System.IndexOutOfRangeException: 'There is no row at position 0.'

Question

Hi. im triying to show an image saved in a sql server but im having this error, someone know why?

This is my code.
SqlCommand command = new SqlCommand($"SELECT IMAGEN from Empleados3 WHERE ID = '{lblIDEMPLEADO.Text}'", conn);
SqlDataAdapter dp = new SqlDataAdapter(command);
DataSet ds = new DataSet();
dp.Fill(ds, "Empleados3");
byte[] MisDatos = new byte[0];
DataRow myRow = ds.Tables["Empleados3"].Rows[0]; <------- HERE I HAVE THE ERROR
MisDatos = (byte[])myRow["IMAGEN"];
MemoryStream ms = new MemoryStream(MisDatos);
picturebox1.Image = Image.FromStream(ms);

Answer 1

I would go a slightly different route to get a single image. For this demo, I use a model called Fruit because all the images are fruits in this case.

Full source and data script and for the record this came from a VB.NET code sample

public class Fruit  
{  
    public int Id { get; set; }  
    public string Description { get; set; }  
    public Image Picture { get; set; }  
  
    public override string ToString()  
    {  
        return Description;  
    }  
}  

Code to get a record count which is used in a form to keep the image possible to be return in range and a method to get the image.

public class DataOperations  
{  
    public static string ConnectionString =   
        "Data Source=.\\sqlexpress;Initial Catalog=WorkingImages;Integrated Security=True";  
  
    /// <summary>  
    /// Get record count  
    /// </summary>  
    /// <returns>row count</returns>  
    public static int RowCount()  
    {  
        using (var cn = new SqlConnection(ConnectionString))  
        {  
            using (var cmd = new SqlCommand("SELECT COUNT(id)  FROM dbo.Fruits ", cn))  
            {  
                cn.Open();  
                return Convert.ToInt32(cmd.ExecuteScalar());  
            }  
              
        }  
    }  
    /// <summary>  
    /// Get image by primary key  
    /// </summary>  
    /// <param name="identifier">Existing key</param>  
    /// <returns>Fruit instance</returns>  
    public static Fruit GetImage(int identifier)  
    {  
        var fruit = new Fruit();  
        using (var cn = new SqlConnection(ConnectionString))  
        {  
            using (var cmd = new SqlCommand("SELECT id, Description, Picture FROM dbo.Fruits WHERE dbo.Fruits.id = @id;", cn))  
            {  
                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = identifier;  
                cn.Open();  
                var reader = cmd.ExecuteReader();  
                if (reader.HasRows)  
                {  
                    reader.Read();  
                    var imageData = (byte[])reader[2];  
                    using (var ms = new MemoryStream(imageData, 0, imageData.Length))  
                    {  
                        ms.Write(imageData, 0, imageData.Length);  
                        fruit.Picture = Image.FromStream(ms, true);  
                    }  
  
                }  
            }  
        }  
  
        return fruit;  
    }  
}  

Table schema

Form code

public partial class Form1 : Form  
{  
    public Form1()  
    {  
        InitializeComponent();  
        Shown += OnShown;  
    }  
  
    private void OnShown(object sender, EventArgs e)  
    {  
        numericUpDown1.Maximum = DataOperations.RowCount();  
    }  
  
    private void GetImageButton_Click(object sender, EventArgs e)  
    {  
        pictureBox1.Image = DataOperations.GetImage((int)numericUpDown1.Value).Picture;  
    }  
}  

I updated the code to show how to handle when a record is not found

private void OnShown(object sender, EventArgs e)  
{  
    numericUpDown1.Minimum = 1;  
    numericUpDown1.Maximum = DataOperations.RowCount();  
  
    /*  
    * There is no key pasted 3, so 4 is invalid  
    */  
    var fruit = DataOperations.GetImage(4);  
  
    if (fruit.Picture == null)  
    {  
        pictureBox1.Image = DataOperations.ConvertTextToImage(Environment.NewLine + "    Error", "Arial", 20, Color.Red, Color.White, 300, 200);  
    }  
          
}  

Answer 2

Never do this:

SqlCommand command = new SqlCommand($"SELECT IMAGEN from Empleados3 WHERE ID = '{lblIDEMPLEADO.Text}'"

That is, never interleave parameter values into the query string. And, yes, when I say never, I mean NEVER.

There are many reasons for this. The most important is that it opens for SQL injection, but also that leads to performance issues on the SQL Server side. Furthermore, it is more difficult - what if there is a single quote in the data.

Instead, you should do as in Karen's example:

   SqlCommand command = new SqlCommand($"SELECT IMAGEN from Empleados3 WHERE ID = @ID}'"  
   command.Parameters.Add("@ID", SqlDbType.Int).Value = lblIDEMPLEADO;  

Note: I'm here assuming that the data type of the column Empleados.ID is int.

Answer 3

The error is pretty clear. Your query did not return any rows and your code is not checking for an empty result set.