question

JESUSEDUARDOCHAVARINROCHA-8224 avatar image
0 Votes"
JESUSEDUARDOCHAVARINROCHA-8224 asked JackJJun-MSFT commented

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

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);


dotnet-csharpsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

are you trying to compare ID against text? you should check the ID as shown in this code.


 SELECT IMAGEN from Empleados3 WHERE ID = '{lblIDEMPLEADO.ID}'

you are getting this error because there is no record in your database which fulfilling your where clause. Additionally you can add a validation like

 if(ds.Tables["Empleados3"].count > 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);
 )

'








0 Votes 0 ·

@JESUSEDUARDOCHAVARINROCHA-8224, Welcome to Microsoft Q&A, as others metioned that, the error means that your returned datatable is null. I suggest that you could execute the command text in the sql server to check if it could return the data. Also, It will be good for you to provide the code about you upload the image to database.

0 Votes 0 ·
karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered karenpayneoregon commented

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

228633-schema.png

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;
     }
 }

228675-figure2.png

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);
     }
            
 }




schema.png (3.9 KiB)
figure2.png (21.1 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If im using a digital fingerprint reader, and I want to use it like the one who search in which row I would take the image, how it would be, can you give me an example or a explanation please:(?

0 Votes 0 ·
karenpayneoregon avatar image karenpayneoregon JESUSEDUARDOCHAVARINROCHA-8224 ·

If the value for the where condition is a string then

 public static Fruit GetImage(string 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.NChar).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);
                 }
    
             }
             else
             {
                 fruit.Picture = null;
             }
         }
     }
    
     return fruit;
 }
0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Empleados ID in my datatable is INT, And I do in that way because I have a digital fingerprint reader, and I dont want to use a box or something like that, so I was trying to compare the ID that I have in my database as Varchar to the one that I have in my main in a label as .text, I have saw some videos on youtube and they always have the same procedure, Im new on this, so thats what I found:(

0 Votes 0 ·
ErlandSommarskog avatar image ErlandSommarskog JESUSEDUARDOCHAVARINROCHA-8224 ·

Yeah, when you say it... the name starts the lbl, as if it was a label. But the label often reads "ID" or some other text. But you need the actual value. From where you get it, well, I don't know your application. And nor do I understand what the digital fingerprint reader have to do with it. You need to give us more details about your application.

0 Votes 0 ·

With my reader I am verifying when people enter and leave an area, so in my main form I have a code where I verify the fingerprint, and it compares it with the one in the database, and when the fingerprint is verified, it enters the name, id, date and time in a different db, first it shows it to me in a label and grabs the .text of that label and enters it into the database, thats how I get the name and id, well, I wanted that with the same id that the label.text gives me, it will compare it to the one in the db, and from there it will take the Image stored in the db which I have defined as IMAGE. and show it to me in a picturebox, with the code I showed above it showed me the image, but without the WHERE ID = '{lblIDEMPLEADO.Text}' and only from the row 0, again, I'm a bit new to programming, so sorry if what I say doesn't make sense :(

0 Votes 0 ·
Show more comments
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered JESUSEDUARDOCHAVARINROCHA-8224 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

With the same code, but without the WHERE ID = '{lblIDEMPLEADO.Text}, it took me the image of the database, but it always took me the one that is in the row 0, so with the where Id =, I was tryning to compare it with the Id that I have in my database, and it seems to be right, and in a few videos of youtube I have saw the same procedure, im new on this, so im really struggling with this:(

0 Votes 0 ·