Fetch a binary data from database

As Ak

I've a list of hexadecimal value with a data type varbinary in the database. But when I run a query, nothing shown as a result.

The example of the hexadecimal value in the database:

0x10160000, 0x1016FFFF

0x10170000, 0x1017FFFF

This is my current code

//this string SNE will get the data from the text file string
SNE = data["End_Rg"];
query = "SELECT Start_Range, End_Range, Status FROM Range WHERE End_Range = '" + SNE + "' ";
sqlcmd = new SqlCommand(query, cnn);
readData = sqlcmd.ExecuteReader();
readData.Read();
Console.WriteLine("Start Range: {0}", readData["Start_Range"]);
Console.WriteLine("End Range: {0}", readData["End_Range"]);
Console.WriteLine("Status: {0}", readData["Status"]);

Currently, I'm getting an error:

> System.InvalidOperationException: 'Invalid attempt to read when no data is present.'

Can I know what is the best way to fetch the binary data from the database?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

As Ak5y ago
@Nisha Regil, I've tried
SELECT Start_Range, End_Range, Status, Status FROM Range WHERE CONVERT(varchar(10), End_SerialNum,2) LIKE '%" + SNE + "%'

The data displayed for Start_Range and End_Range is System.Byte[].

How can i solve this issue to get the correct value from the database?
Nisha Regil5y ago
What is the value of data["End_Rg"] at line #2?
Can you try using 'like' instead of '=' at line #3 . May be it will show some data.

Try to use if not exists before reading the data

IF NOT EXISTS(SELECT * FROM ...)
BEGIN
PRINT 'No matching row exists'
END

OR Use

if(readData.HasRows)
{
// ....
}
else
{
MessageBox.Show("Reservation Number Does Not Exist","Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
Jignesh Kumar5y ago
Hi A k, Please check null before access, https://stackoverflow.com/questions/762861/how-to-check-if-a-datareader-is-null-or-empty
Sachin Singh5y ago
use conversion while selecting
SELECT CAST(ColumnName as varbinary(max) );
As Ak5y ago
@sachin singh I've tried your suggestion, but the error still the same. actually, i've tried to run the select query in the sql server, but it shows nothing as a result.
Mageshwaran R5y ago
Hi AK,
please try this code:
C#:
using (var conn = new SqlConnection("Your ConnectionString"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText =
    @"SELECT
          ImageFile
      FROM
          Students
      WHERE
          Students_ID = @Students_ID
    ";
    cmd.Parameters.AddWithValue("@Students_ID", Txt_Name.Text);
    using (var reader = cmd.ExecuteReader())
    {
        if (reader.Read())
        {
            byte[] raw = (byte[])reader.Items["ImageFile"];

        }
    }
}
Sachin Singh5y ago
use like below
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    // read data for first record here
}
your error is related to datareader not database