Click here to Skip to main content
14,242,951 members
Rate this:
Please Sign up or sign in to vote.
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
 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
Updated 14-Jan-15 23:05pm
v2
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.
digimanus 15-Jan-15 5:05am
   
Why you show your VB code if your SQL Server gives the error?
Rate this:
Please Sign up or sign in to vote.

Solution 1

As mentioned in the comments, you need to either allow nulls on image column or do the insert in one line.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

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.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100