Storing Binary File in SQLClient using VB.NET

[Rob Panosh]

Hello,

I am trying to store Image Files( JPG, BMP, ..), Excel Files, Word Docs,
... into a SQL Server Table with no success. My code below keeps throwing
an exception on the following line of code "oCommand.ExecuteNonQuery()".
Any help would be greatly appreciated.

----------------------------------------------------------------------------
---

Here is a my my test Table Definition:

create table robtest2 ( testfile text null )
go

Now here is my test code:

Dim fs As New System.IO.FileStream("C:\Test.JPG", System.IO.FileMode.Open,
System.IO.FileAccess.Read)
Dim MyData(fs.Length) As Byte

fs.Read(MyData, 0, fs.Length)
fs.Close()

Dim oCommand As System.Data.SqlClient.SqlCommand
Dim oConnection As New System.Data.SqlClient.SqlConnection

oConnection.ConnectionString = "Network Library=DBMSSOCN;Application
Name=ProductVision;Data Source=dualasd;Initial Catalog=provis40;User
Id=sa;Password=asd;"
oConnection.Open()

oCommand = New System.Data.SqlClient.SqlCommand
oCommand.CommandType = CommandType.Text
oCommand.CommandText = "INSERT INTO ROBTEST2 VALUES ( @TestImage )"
oCommand.Connection = oConnection

Dim oParameter As New System.Data.SqlClient.SqlParameter

oParameter.SqlDbType = SqlDbType.Text ' I have tried SqlDBType.Image
oParameter.Value = MyData
oParameter = oCommand.Parameters.Add("@TestImage ",
System.Data.SqlDbType.Text)

oCommand.ExecuteNonQuery()
oCommand.Dispose()
oCommand = Nothing

Thanks,
Rob Panosh
Advanced Software Designs

[Jeremy]

Your error doesn't have anything to do with VB, look at your SQL statement:

> oCommand.CommandText = "INSERT INTO ROBTEST2 VALUES ( @TestImage )"

INSERT INTO ROBTEST2 VALUE ( @TestImage )

According to this query, what field will you be inserting data into? It
doesn't say. Also, VALUE is not the right keyword, it should be VALUES:

"INSERT INTO ROBTEST2 ( [FIELD_NAME] ) VALUES ( @TestImage )"

HTH,
Jeremy

"Rob Panosh" <rob_p...@asdsoftware.com> wrote in message
news:egGDWypL...@TK2MSFTNGP11.phx.gbl...

[Rob Panosh]

Jeremy,

> doesn't say. Also, VALUE is not the right keyword, it should be VALUES:

Don't know what message you were reading ... but this is the insert
statement from my original post.

oCommand.CommandText = "INSERT INTO ROBTEST2 VALUES ( @TestImage )"

Not true. This is a valid INSERT statment, you don't have to explicitly put
the names of the columns that you are inserting. This is a test case for
me. My SQL Table only has one column in it. If I change the insert to
"INSERT INTO robtest2 (testfile) VALUES ( @TestImage)" I get the same
results.

Rob

"Jeremy" <thevis...@hotmail.com> wrote in message
news:mS1Fa.42794$cm4.7...@twister.tampabay.rr.com...

[Jeremy]

Sorry, my bad. What is the error message you're getting? I use an Image
field for binary data now in our app, and we have no problems at all.

Jeremy

"Rob Panosh" <rob_p...@asdsoftware.com> wrote in message

news:uBbozBq...@TK2MSFTNGP11.phx.gbl...

[David Archuleta]

Rob,

You should change the image column's data type in the
database from text to image since text is for encoded
(text) data whereas image stores binary data.

Additionally, the parameter code is incorrect. You will
need to change it to:

Dim oParameter As New System.Data.SqlClient.SqlParameter

oParameter.ParameterName = "@TestImage"
oParameter.SqlDbType = SqlDBType.Image
oParameter.Value = MyData
oCommand.Parameters.Add(oParameter)

Regards,

David Archuleta

>.
>

[Rob Panosh]

Jeremy,

Found my problem with a little more digging, my column was defined
incorrectly in my table.

SQL Client:
Needs to be Image ( not Text )

Oracle Client:
Need to be BLOB

Regards,
Rob Panosh

"Jeremy" <thevis...@hotmail.com> wrote in message

news:jL2Fa.26103$4y6.5...@twister.tampabay.rr.com...

[Rob Panosh]

David,

Thanks for the reply. I figured this out with a little more digging.

Thanks,
Rob Panosh

"David Archuleta" <dav...@jetsonsystems.com> wrote in message
news:011101c32ea9$f44be0b0$a401...@phx.gbl...