It's probably not that code - it's likely to be the way you save it into the database in the first place. See here:
Why do I get a "Parameter is not valid." exception when I read an image from my database?[
^]
"what is parameterized query, i dont tnk i hav got an answer. By your comment it means that my image is not actually saving in the database....so what can i do to save it into the database and later retrieve it into a picture box....."
When you build an SQL query by concatenation:
Dim strsql As String = "insert into PersonalPro(SurNames,Gender,DOB,pictu)values('" + SurNamesTextBox.Text + "','" + GenderComboBox.Text + "','" + DOBDateTimePicker.Text + "','"+ PictuPictureBox.ImageLocation + "')"
You are assembling a string but combining other strings, obviously. But this has some effects which aren't immediately apparent. For example:
Dim userName As String = "Mike"
Dim sql As String = "SELECT * FROM myTable WHERE User='" + userName + "'"
Generate a string which SQL can understand:
SELECT * FROM myTable WHERE User='Mike'
But when you use an array of bytes:
Dim data As Byte() = File.ReadAllBytes("D:\Temp\MyPic.jpg")
Dim sql As String = "INSERT INTO myTable (Picture) VALUES ('" & data & "')"
What you get is not the same, because
data
is not a string, so the default ToString method is implied in order to complete the statement:
Dim data As Byte() = File.ReadAllBytes("D:\Temp\MyPic.jpg")
Dim sql As String = "INSERT INTO myTable (Picture) VALUES ('" & data.ToString & "')"
Since arrays do not implement a ToString override, the default
object
version is called, wihich returns the name of the class, rather than the data content:
INSERT INTO myTable (Picture) VALUES ('System.Byte[]')
So the value inserted into your database is just that: the text "System.Byte" rather than the file content.
This is what you saw when you wrote the DB content to a file and looked at it.
To get round this, you use what is called a Parametrized query: you use a "place marker" in your SQL statement, and supply a parameter with a value that matches it:
Using con As New SqlConnection(strConnect)
con.Open()
Using com As New SqlCommand("INSERT INTO myTable (Picture) VALUES (@PIC)", con)
Dim data As Byte() = File.ReadAllBytes("D:\Temp\MyPic.jpg")
com.Parameters.AddWithValue("@PIC", data)
com.ExecuteNonQuery()
End Using
End Using
The "place marker" in the SQL statement is
@PIC
which is just a named SQL variable. You then create a Parameter with the same name, and give it the data. Because you aren't playing with string conversions at any time, it all works seamlessly.
This is important!
There is another advantage of using Parameterized queries: which is that it prevent your users from damaging or destroying your database by typing into your text boxes.
No, I'm not joking. If you concatenate strings:
Dim sql As String = "INSERT INTO myTable (Picture) VALUES ('" & myTextBox.Text & "')"
Then
exactly what your user types will be sent to SQL server.
So if your user types
Mike');DROP TABLE myTable;--
and presses the "Enter" or "Login" button, then the string sent to SQL is:
INSERT INTO myTable (Picture) VALUES ('Mike');DROP TABLES myTable;
Which SQL sees as two separate commands and a comment. It does the select, and will return the data to your application, but it then deletes the table from your database...and you can't get it back.
This is called an SQL Injection attack, and it is the simplest, most stupid, most dangerous mistake any database coder can make. Potentially, it bypasses any security you use, and puts your DB at the complete control of someone on the other side of the world...
If you do the same thing as a parameterized query, nothing nasty happens - so always use them, even for trivial stuff!