Write Image to sql-Database

Question

5.00/5 (1 vote)

See more:

VB

Dim img As Byte()
Dim fs As New FileStream("c:\temp\MyZipFile.zip", FileMode.Open, FileAccess.Read)
Dim br As New BinaryReader(fs)

img = br.ReadBytes((Int(fs.Length)))

sQry = "Insert into st_image " & vbCrLf
sQry = sQry & "(DOC_OBJECT_ID, DOC_IMAGE, DOC_COMPRESSED)" & vbCrLf
sQry = sQry & "Values" & vbCrLf
sQry = sQry & "(" & vbCrLf
sQry = sQry & cMain.NewSystemCounter & ", @IMG"
sQry = sQry & ", "
sQry = sQry & " -1" & vbCrLf
sQry = sQry & ")" & vbCrLf

VB

Try
   Dim cmdAdd As New OleDbComman
   cmdAdd.Connection = conn1
    
   cmdAdd.CommandText = sQry
   cdAdd.Parameters.AddWithValue("@IMG", img)
   cmdAdd.ExecuteNonQuery()
    
   Catch ex As Exception
      MsgBox(ex.Message)
   End Try

When start the program i always get the message "Must declare variabele '@IMG'"
What i doing wrong.

I use Visual Basic 2012 WPF and SQl-Server 2005.

sQry = "Insert into st_image (DOC_OBJECT_ID, DOC_IMAGE, DOC_COMPRESSED) Values ( 13080002756, @IMG, -1 )"

Thank you.

Posted 12-Sep-13 23:35pm

quator

Updated 17-Sep-13 10:15am

v2

Add a Solution

Comments

Pheonyx 17-Sep-13 7:11am

Can you step through your code and once you have finished populating sQry, copy its contents and add it to the question so I can see the whole Insert statement rather than trying to piece it together myself.

Use the Improve Question feature to add it to the bottom of your question then reply to this comment so that I know.

Also, you asked how you can check the parameter has been added, if you step through your code an after you add the parameter, check the Parameter collection ensuring what you added is there.

quator 17-Sep-13 16:07pm

The Parameter collection is empty. But I don't know why.

Pheonyx 17-Sep-13 16:12pm

Does seem a bit odd, but the fact your parameter is not being added to the command is clearly the issue. Try creating the parameter separately as an SqlParameter object then adding it to the Parameters collection on the command. See if you can successfully create the parameter on its own.

4 solutions

Solution 1

Try to change

C#

cdAdd.Parameters.AddWithValue("@IMG", img)

to

C#

cdAdd.Parameters.Add("@IMG",SQLDbType.Image).Value = img;

Posted 13-Sep-13 14:59pm

Midnight Ahri

Solution 2

Thank you for your answer,

But still i get the error "Must declare variabele '@IMG'"

Posted 15-Sep-13 23:09pm

quator

Updated 16-Sep-13 21:25pm

v2

Comments

Richard C Bishop 17-Sep-13 15:42pm

You need to stop posting garbage as solutions to your own questions. You are on the verge of abuse and risk having your account canceled.

quator 18-Sep-13 3:21am

Please can tell me want i doing wrong.
Because don't want that my account would be canceled.

I have responded to a user. And that have i put in solution.
If that is the reason then I am sorry.
I won't happen again

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Pheonyx · Accepted Answer · 2013-09-15T23:54:00

Solution 3

I don't know if this is a typo from when you created the question but look at the following (from your question):

VB

cmdAdd.CommandText = sQry
   cdAdd.Parameters.AddWithValue("@IMG", img)

on the first line you have typed cmdAdd but on the second line you have typed cdAdd, I suggest you correct that type if it exists in your code as it could well be the issue.

Posted 15-Sep-13 23:54pm

Pheonyx

Comments

quator 16-Sep-13 8:58am

Probably a stupid question.

An insert in a sql database with parameters works only with stored procedures ?

Pheonyx 16-Sep-13 9:35am

Nope, parameters work with both stored procedures and text commands.
Before you execute the procedure, have you checked to see that the parameter has been added successfully?

quator 17-Sep-13 3:52am

For a reason that I do not know want an insert with text commands do not work. I get the same error message each time.

"Must declare variable '@ IMG'

How can i check if parameter has been added successfully?

quator · Accepted Answer · 2013-09-16T02:46:00

Solution 4

Yes your are right this is type mistake.

cdadd must be cmdadd

Posted 16-Sep-13 2:46am

quator

Comments

quator 17-Sep-13 3:24am

But still I get the same error.