Image upload problem into database

Hi guys,

I'm currently trying to insert image into my SQL db. I have tried a number of methods that were posted online, and so far with no luck.

My current code reads:

Dim conn As New Data.SqlClient.SqlConnection()
conn.ConnectionString = ConfigurationManager.ConnectionStrings("MainDBConnection").ToString
conn.Open()

Dim cmd As New Data.SqlClient.SqlCommand("SP_SAVEImage", conn)

cmd.CommandType = Data.CommandType.StoredProcedure

Dim nUserID As New Data.SqlClient.SqlParameter("@.nUserID", Data.SqlDbType.Int)
nUserID.Value = "1"

Dim nAlbumID As New Data.SqlClient.SqlParameter("@.nAlbumID", Data.SqlDbType.Int)
nAlbumID.Value = "1"

Dim sDescription As New Data.SqlClient.SqlParameter("@.sDescription", Data.SqlDbType.VarChar, 50)
sDescription.Value = "image1"

Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName

Dim sImageType As New Data.SqlClient.SqlParameter("@.sImageType", Data.SqlDbType.VarChar, 50)
sImageType.Value = fileType

Dim sImageData As New Data.SqlClient.SqlParameter("@.sImageData", Data.SqlDbType.Image, uploadedFile.Length)
sImageData.Value = uploadedFile

cmd.Parameters.Add(nUserID)
cmd.Parameters.Add(nAlbumID)
cmd.Parameters.Add(sDescription)
cmd.Parameters.Add(sImageName)
cmd.Parameters.Add(sImageType)
cmd.Parameters.Add(sImageData)

Dim reader1 As Data.SqlClient.SqlDataReader

reader1 = cmd.ExecuteReader

Running through debug, everything runs up until the last line, where an error is caught saying : Failed to convert parameter value from a SqlParameter to a String

I reckon it's to do with the input sImageData being input as a byte array - but I can't seem to find a way around it.

Any help greatly appreciated!!

Dim sImageName As New Data.SqlClient.SqlParameter("@.sImageName", Data.SqlDbType.VarChar, 50)
sImageName.Value = sImageName

You are setting an SqlParameter equal to a string value. Must be a typeo. Fix and retry.

|||

THANK YOU!!!!!!

I feel such an idiot now!! I've been looking at this code for so long I couldn't see the obvious.

I had honestly just given up on the idea of saving my data this way; but decided to check the post before going to bed - I can sleep well now knowing at least some of my code works! lol.

Cheers mate

Image data type Size

I need to store images in MS SQL. I have the upload procedures and stuff but I'm missing the point about the image data type size.

It is supposed to be able to store up to 2Gb!!! but when I declare the data field image I can't specify the max size for the field and by default is 16 !!

16 bytes!! what can I do with that?
How can I insert a file?

Please helpThe pointer to the data is the only thing stored in this database row. The data is stored elsewhere and can be up to 2G. You don't get to specify the size.

Bill|||Thanks for the reply!

But that leads me to this question: How can I write the Insert Statement for the image field?

create table ImgTest (
imgID Int,
imgFName varchar(30),
imgFType varchar(30),
imgFile image
)

All I got so far is a procedure in vb net that tryes to insert a byte array asigned to parameter value:

Insert Into ImgTest Values (1, 'file.jpg', 'image file', @.bytearray)

That's where I'm having trouble because it returns the error: "Data will be truncated" or something like that. I guess it's because it tryes to store the file in the 16bytes that are reserved for the pointer...

Oh.. I'm so lost..

Thank you!sql

Image data type Size

I need to store images in MS SQL. I have the upload procedures and stuff but I'm missing the point about the image data type size.

It is supposed to be able to store up to 2Gb!!! but when I declare the data field image I can't specify the max size for the field and by default is 16 !!

16 bytes!! what can I do with that?
How can I insert a file?

Please helpThe pointer to the data is the only thing stored in this database row. The data is stored elsewhere and can be up to 2G. You don't get to specify the size.

Bill|||Thanks for the reply!

But that leads me to this question: How can I write the Insert Statement for the image field?

create table ImgTest (
imgID Int,
imgFName varchar(30),
imgFType varchar(30),
imgFile image
)

All I got so far is a procedure in vb net that tryes to insert a byte array asigned to parameter value:

Insert Into ImgTest Values (1, 'file.jpg', 'image file', @.bytearray)

That's where I'm having trouble because it returns the error: "Data will be truncated" or something like that. I guess it's because it tryes to store the file in the 16bytes that are reserved for the pointer...

Oh.. I'm so lost..

Thank you!sql

image and text

After I upload a image file to a table with image field, how do I get the
file name? In other words if there 1000 records in one table (1000 images),
how do I know their file names?
Is there a way I can display those images in sql query analyzer using a
T-SQL command.
Using a textcopy, can I retrieve more than 1 record/1 image at a time?
ex: if there are 10 products under one category. Is there a way to retrieve
all 10 products with images using one T-sql statement, and display them on a
web page?
thanks
Vignesh
Uhway
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
It is very good practice to store only the pathes to files located on disk.
It consumes a lot of system resource to deal with images.
There are pretty good examples provided by Microsoft to display images on
the client.

> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
The data is stored in binary format so you will not be able to see the
image.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>
|||If you are storing the images in an image data type, there is no filename,
only the PK of the row where the image is stored. If you wish to keep the
original filename and store the image in an image field, you must also
create a column to store the filename in.
Reasonable people differ on whether or not it is better to store the data in
an image field or only the filename, leaving the data in a physical file.
There are pros and cons to both methods,
Test to see which is better for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>

image and text

After I upload a image file to a table with image field, how do I get the
file name? In other words if there 1000 records in one table (1000 images),
how do I know their file names?
Is there a way I can display those images in sql query analyzer using a
T-SQL command.
Using a textcopy, can I retrieve more than 1 record/1 image at a time?
ex: if there are 10 products under one category. Is there a way to retrieve
all 10 products with images using one T-sql statement, and display them on a
web page?
thanks
VigneshUhway
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
It is very good practice to store only the pathes to files located on disk.
It consumes a lot of system resource to deal with images.
There are pretty good examples provided by Microsoft to display images on
the client.

> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
The data is stored in binary format so you will not be able to see the
image.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>|||If you are storing the images in an image data type, there is no filename,
only the PK of the row where the image is stored. If you wish to keep the
original filename and store the image in an image field, you must also
create a column to store the filename in.
Reasonable people differ on whether or not it is better to store the data in
an image field or only the filename, leaving the data in a physical file.
There are pros and cons to both methods,
Test to see which is better for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>

image and text

After I upload a image file to a table with image field, how do I get the
file name? In other words if there 1000 records in one table (1000 images),
how do I know their file names?
Is there a way I can display those images in sql query analyzer using a
T-SQL command.
Using a textcopy, can I retrieve more than 1 record/1 image at a time?
ex: if there are 10 products under one category. Is there a way to retrieve
all 10 products with images using one T-sql statement, and display them on a
web page?
thanks
VigneshUhway
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
It is very good practice to store only the pathes to files located on disk.
It consumes a lot of system resource to deal with images.
There are pretty good examples provided by Microsoft to display images on
the client.
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
The data is stored in binary format so you will not be able to see the
image.
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>|||If you are storing the images in an image data type, there is no filename,
only the PK of the row where the image is stored. If you wish to keep the
original filename and store the image in an image field, you must also
create a column to store the filename in.
Reasonable people differ on whether or not it is better to store the data in
an image field or only the filename, leaving the data in a physical file.
There are pros and cons to both methods,
Test to see which is better for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uhway" <vbhadharla@.sbcglobal.net> wrote in message
news:%23rLo8Ct9EHA.3944@.TK2MSFTNGP12.phx.gbl...
> After I upload a image file to a table with image field, how do I get the
> file name? In other words if there 1000 records in one table (1000
images),
> how do I know their file names?
> Is there a way I can display those images in sql query analyzer using a
> T-SQL command.
> Using a textcopy, can I retrieve more than 1 record/1 image at a time?
> ex: if there are 10 products under one category. Is there a way to
retrieve
> all 10 products with images using one T-sql statement, and display them on
a
> web page?
>
> thanks
> Vignesh
>
>