DataSet Table giving "there is no row in position 0" error

Question

I am trying to retrieve an image from my database to appear as the background of my windows form. But the error "There are no rows at position 0" keeps appearing. I have tested it on other forms with the same code and it works fine. May I know what's wrong with the code below?

cmd = new SqlCommand("select Background from Employee where EmployeeName='" + label10.Text + "'", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);

            if (ds.Tables[0].Rows[0]["Background"] != System.DBNull.Value)
            { 
            MemoryStream ms = new MemoryStream((Byte[])ds.Tables[0].Rows[0]["Background"]);
            this.BackgroundImage = new Bitmap(ms);

Avoid string concatenation for SQL query, use parameterized query instead. Also you can create a `DataTable` and fill it from `SqlCommand`, then check with `DataTableName.Rows[0]["ColumnName"]`. — Tetsuya Yamamoto, Dec 05 '17 at 07:18

score 1 · Answer 1 · answered Dec 05 '17 at 07:04

1

you must need to check ds.Tables[0].Rows.Count > 0 before trying code like this.

There is no need of dataset you can use DataTable directly.

One more change is make use of SQLParameter to pass value , rather than writing query like this , to avoid sql injection.

Make use of using to dispose objects.

answered Dec 05 '17 at 07:04

Pranay Rana

175,020
35
237
263

Do I implement a if else statement using `ds.Tables[0].Rows.Count > 0` and insert `if (ds.Tables[0].Rows[0]["Background"] != System.DBNull.Value)` into the if else statement? – EGS Dec 05 '17 at 07:25

Tetsuya Yamamoto · Answer 2 · 2017-12-05T09:57:56.563

0

The exception message itself is pretty straightforward: DataTable inside DataSet contains no rows so that you can't pull any data from it. The && operator can check both row existence & DBNull.Value condition (row count checking must be on the left):

if (ds.Tables[0].Rows.Count > 0 && ds.Tables[0].Rows[0]["Background"] != DBNull.Value)
{
    // do another stuff
}

NB: You can simplify usage of DataAdapter and DataSet by using standard SqlDataReader and DataTable.Load method like this example:

using (SqlConnection con = new SqlConnection(...))
{
    using (SqlCommand cmd = new SqlCommand("select Background from Employee where EmployeeName = @Name", con))
    {
        cmd.Parameters.AddWithValue("@Name", label10.Text);

        var dt = new DataTable();
        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            dt.Load(dr); // fill DataTable from reader
        }

        if (dt.Rows.Count > 0 && dt.Rows[0]["Background"] != DBNull.Value)
        {
            using (MemoryStream ms = new MemoryStream((byte[])dt.Rows[0]["Background"]))
            {
                this.BackgroundImage = new Bitmap(ms);
            }
            // do another stuff
        }
        else
        {
            // do another stuff
        }
    }
}

edited Dec 05 '17 at 09:57

answered Dec 05 '17 at 07:50

Tetsuya Yamamoto

24,297
8
39
61

Hi thanks for the reply. But how do I imply this section of my codes into yours `MemoryStream ms = new MemoryStream((Byte[])dt.Rows[0]["Background"]);` `this.BackgroundImage = new Bitmap(ms);` – EGS Dec 05 '17 at 09:18
I suggest putting `using` statement for `MemoryStream` initialization so that the byte array immediately disposed after converting it to bitmap, I had edited by adding example of that. – Tetsuya Yamamoto Dec 05 '17 at 09:59
I tried the codes given but now i am getting the "Out of Memory" error. – EGS Dec 06 '17 at 00:58
@EGS: Can you provide size of byte array from `dt.Rows[0]["Background"]`? You can consider this issue as reference if you have large amount of RAM but experiencing 'out of memory': https://stackoverflow.com/questions/15595061/outofmemoryexception-while-populating-memorystream-256mb-allocation-on-16gb-sys. – Tetsuya Yamamoto Dec 06 '17 at 02:28
Sorry I am really new to this but where do i provide the size and expand the memory in the program? – EGS Dec 06 '17 at 04:54

DataSet Table giving "there is no row in position 0" error

2 Answers2