error "cannot insert the value null into image column msg 515 level 16 state 2 line 1",

Question

1.50/5 (2 votes)

See more:

Hi I am having a problem storing image to mssql server I tried both Memory stream and BLOB as well and in both scinarios I get the same error "cannot insert the value null into image column msg 515 level 16 state 2 line 1", any suggestion is more than welcome Thanks.

VB

 Dim sqlstring = " begin tran;"
 sqlstring &= " INSERT INTO tbl_customers (stnname, cardnum,
 family_name,  city, fam_mem_nu, id_num, mrkz_num) VALUES
 ('" & stn & "','" & cd & "', '" & fmnm & "','" & ct & "', '"
 & fnum & "', '" & idn & "', '" & cntr & "')"
 sqlstring &= "INSERT INTO tbl_customers(imag) SELECT * FROM
 OPENROWSET(BULK N'c:\temp\tempimg', SINGLE_BLOB) imag ;  "
 sqlstring &= "commit tran;"
 sql.CommandText = sqlstring
 sql.Connection = conn
 conn.Open()
 Dim ms As New MemoryStream

 img = CameraControl1.SnapshotSourceImage


 img.Save("c:\temp\tempimg", Imaging.ImageFormat.Png)

'CameraControl1.SnapshotSourceImage.Save
' PictureBox3.Image.Save(ms, PictureBox3.Image.RawFormat)
 ' ms.ToArray()
' Dim data As Byte() = ms.GetBuffer()
'  Dim p As New SqlClient.SqlParameter("@img", SqlDbType.VarBinary)
' p.Value = data
' sql.Parameters.Add(p)
Dim x As Integer = sql.ExecuteNonQuery

Posted 13-Jan-15 8:32am

Member 11277237

Updated 14-Jan-15 23:05pm

Herman<T>.Instance

v2

Add a Solution

Comments

ZurdoDev 13-Jan-15 14:48pm

If you allow nulls on the imag column, what you have should work. Your first insert statement does not specify imag so that is why you get the error.

Member 11277237 13-Jan-15 15:10pm

Thanks Ryan some times things silly there but we can not see them I finally did pass it as sql parameter with one line. thanks again.

Richard Deeming 13-Jan-15 15:05pm

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Herman<T>.Instance 15-Jan-15 5:05am

Why you show your VB code if your SQL Server gives the error?

2 solutions

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)

ZurdoDev · Answer 1 · 2015-01-15T01:49:00

Solution 1

As mentioned in the comments, you need to either allow nulls on image column or do the insert in one line.

Posted 15-Jan-15 1:49am

ZurdoDev

Afzaal Ahmad Zeeshan · Answer 2 · 2015-02-02T08:50:00

Apart from the SQL Injection problem that others have also mentioned in your query; which makes it a lot vulnerable to any hacker you attempts to break you query to have a look at your under-the-hood stuff. The problem is that the image (object) that you're trying to insert into the table is a null object, that is why the error is being displayed to you, which is trying to give you two options to either Allow null values in the field (column) which you simply just can by opening the SSMS (SQL Server Management Studio) or by either running an SQL command, to allow the null values on the field. Otherwise you can create a conditional block (block of if-else statements) to ensure that if the image is null object, then you do not insert it into the table which would prevent any occurance of this problem at all.