Click here to Skip to main content
15,920,708 members
Please Sign up or sign in to vote.
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.

 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
 Dim ms As New MemoryStream

 img = CameraControl1.SnapshotSourceImage

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

' 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
Updated 14-Jan-15 23:05pm
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?

As mentioned in the comments, you need to either allow nulls on image column or do the insert in one line.
Share this answer
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.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900