Click here to Skip to main content
15,867,771 members
Articles / Database Development / SQL Server
Tip/Trick

Why do I get a "Parameter is not valid." exception when I read an image from my database?

Rate me:
Please Sign up or sign in to vote.
4.98/5 (17 votes)
27 Sep 2012CPOL3 min read 96.5K   7   14
Why? Normally, it's because you stored it wrong...

Introduction

I find that it is quite common for people to have a problem loading an image from a database and recreating it as an Image for display - when they try to create the new Image, it throws an exception reporting "Parameter is not valid."

So, this explains why it happens, and (hopefully) will save me - and you - some typing in the future. 

Background

Storing images in databases is such a handy idea, that everyone picks up on it at some stage. Indeed, it can be a good idea, provided the images are small, or there aren't too many of them, and provided that you only load them when you actually need them. Loading images from a DB when you don't need them can waste a lot of bandwidth, and slow your application down a serious amount.

But the problem is that it is also far too easy to get wrong - particularly if you use string concatenation to assemble your SQL statements - and the error only shows up when you try to use the stored information. It then looks like a fault in your reader code: "but it can't be - it works! I  got it form here!" 

Loading images from the DB 

The code to read an image from a DB and convert it to a Image for display is pretty easy:

C#
using (SqlConnection con = DBAccess.DBCon)
{
    using (SqlCommand cmd = new SqlCommand("SELECT picture FROM Pictures WHERE Id=@ID", con))
    {
        cmd.Parameters.AddWithValue("@ID", Id);
        SqlDataReader reader = cmd.ExecuteReader();
        if (reader.Read())
        {
            byte[] data = (byte[])reader["Picture"];
            using (MemoryStream stream = new MemoryStream(bytes))
            {
                myImage = new Bitmap(stream);
            }
        }
    }
}

But - if the data is not a valid image for any reason, the line:

C#
myImage = new Bitmap(stream);

will throw an exception: "Parameter is not valid."

It is only if you actually look at the data returned from the database that you realise why - and it's not so obvious when you glance at it in the debugger:

{byte[21]}
[0] 83
[1] 121
[2] 115
[3] 116
[4] 101
[5] 109
[6] 46
[7] 68
[8] 114
[9] 97
[10] 119
[11] 105
[12] 110
[13] 103
[14] 46
...

It doesn't look like anything you recognise, so it could be your data - though the length of 21 bytes is a big clue: is your image likely to be only 21 bytes long? That's a pretty small image...

But it is readable, with a little practice. Each byte is an ASCII value, in decimal.

"83" is an uppercase 'S'
"121" is an lowercase 'y'
"115" is an lowercase 's'
"116" is an lowercase 't'
"101" is an lowercase 'e'
"109" is an lowercase 'm'
"46" is a '.'
"68" is an uppercase 'D'
"114" is an lowercase 'r'
"97" is an lowercase 'a'
"119" is an lowercase 'w'
"105" is an lowercase 'i'
"110" is an lowercase 'n'
"103" is an lowercase 'g'
"46" is an lowercase '.'
... 

In short, the data  you read from the database is a human readable string, that says

"System.Drawing.Bitmap" 

When the Image class tries to convert it to an actual image, it can't understand it at all, and throws the exception. The code works - it's the data that is the problem.

Saving an image to the database 

This is where you caused the problem!

Normally, this is as a result of concatenating strings to form an SQL command:

C#
string sql = "INSERT INTO myTable (imageData) VALUES ('" + myImage + "')";

Does not include the image content in the SQL command - it calls the default Image.ToString method, which returns the human readable Image type:

C#
"System.Drawing.Bitmap" 

So you end up with an SQL command: 

C#
INSERT INTO myTable (imageData) VALUES ('System.Drawing.Bitmap')

Which will not cause an error - but it will save the name of the image type as what you think is the Image data itself. 

This is yet another reason why you should never concatenate string to form SQL commands. If you want the others, use Google: "Bobby Tables" should do it. And please - don't think they are joking!

Doing it properly isn't difficult, or even that time consuming, and  it can help to improve readability as well as reliability. All you have to do is use a Parametrized Query.

First, convert the Image to an array of bytes: 

C#
Image myImage = Image.FromFile(@"D:\Temp\MyPic.jpg");
byte[] data;
using (MemoryStream ms = new MemoryStream())
{
    myImage.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
    data = ms.ToArray();
}

Then, use the bytes in your SqlCommand object: 

C#
using (SqlConnection con = new SqlConnection(strConnect))
{
    con.Open();
    using (SqlCommand com = new SqlCommand("INSERT INTO Pictures (Picture) VALUES (@IM)", con))
    {
        com.Parameters.AddWithValue("@IM", data);
        com.ExecuteNonQuery();
    }
}

Before I close...

No, you can't retrieve the image data for the images you already stored wrongly - you will have to delete them all, and save them to the DB correctly! 

Yes, yes - I know you knew that. But you would be surprised how many people it comes as a shock to! Laugh | :laugh:

History

Original version.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?

Comments and Discussions

 
Question5 star Pin
aaaaaaaaaaa22223-Sep-17 4:38
aaaaaaaaaaa22223-Sep-17 4:38 
Question[My vote of 1] My vote of 1 Pin
alfiep18-Jan-16 23:51
alfiep18-Jan-16 23:51 
AnswerRe: [My vote of 1] My vote of 1 Pin
Sascha Lefèvre7-Feb-16 4:43
professionalSascha Lefèvre7-Feb-16 4:43 
QuestionPlease i need more explanation Pin
Member 959922517-Apr-15 7:04
Member 959922517-Apr-15 7:04 
AnswerRe: Please i need more explanation Pin
PIEBALDconsult17-Apr-15 7:07
mvePIEBALDconsult17-Apr-15 7:07 
GeneralRe: Please i need more explanation Pin
OriginalGriff17-Apr-15 7:55
mveOriginalGriff17-Apr-15 7:55 
GeneralRe: Please i need more explanation Pin
aaaaaaaaaaa22223-Sep-17 5:48
aaaaaaaaaaa22223-Sep-17 5:48 
GeneralRe: Please i need more explanation Pin
OriginalGriff23-Sep-17 5:56
mveOriginalGriff23-Sep-17 5:56 
GeneralMy vote of 5 Pin
Richard MacCutchan15-Sep-13 1:28
mveRichard MacCutchan15-Sep-13 1:28 
GeneralMy vote of 5 Pin
NeonMika27-Sep-12 18:55
NeonMika27-Sep-12 18:55 
I think this can help many beginners who have never worked with convertings to byte[].

Thanks!
GeneralRe: My vote of 5 Pin
OriginalGriff27-Sep-12 21:13
mveOriginalGriff27-Sep-12 21:13 
GeneralMy vote of 5 Pin
Nick_F26-Sep-12 7:51
Nick_F26-Sep-12 7:51 
GeneralMy vote of 5 Pin
Simon_Whale26-Sep-12 3:26
Simon_Whale26-Sep-12 3:26 
GeneralRe: My vote of 5 Pin
OriginalGriff26-Sep-12 3:35
mveOriginalGriff26-Sep-12 3:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.