[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

How to convert image file to bytes and save it to SQL Sever table?

I have a SQL Server table.
ID-INT(This is primary key and it is unique and generated when a row is inserted.
I eliminated the code for this)
IMAGE-VARBINARY
LABEL-VARCHAR
.
I am using FileUpload control in ASP.Net page to select a image file.
I have to convert this image file into bytes and save it to my SQL Server table.

Below is my ASPX.VB code:
---------------------------------------------
Dim mySqlCommand As New SqlCommand()
mySqlCommand.CommandText = "[dbo].[INSERT_IMAGE]"
mySqlCommand.Parameters.Add("@LABEL", SqlDbType.VarChar).Value = label

Try

Dim _fileInfo As New IO.FileInfo(fileUpload1)
Dim _NumBytes As Long = _fileInfo.Length
Dim _FStream As New IO.FileStream((fileUpload1).ToString.Trim, IO.FileMode.Open, IO.FileAccess.Read)
Dim _BinaryReader As New IO.BinaryReader(_FStream)
_tempByte = _BinaryReader.ReadBytes(Convert.ToInt32(_NumBytes))
_fileInfo = Nothing
_NumBytes = 0
_FStream.Close()
_FStream.Dispose()
_BinaryReader.Close()
Catch ex As Exception

End Try
 
mySqlCommand.Parameters.AddWithValue("@IMAGE", _tempByte)
mySqlCommand.Parameters.Add("@New_ID", SqlDbType.Int)
Dim myReturnParamNum As Integer = mySqlCommand.Parameters.Count - 1
mySqlCommand.Parameters(myReturnParamNum).Direction = ParameterDirection.Output
mySQLCommand.Connection = myConn
mySQLCommand.CommandTimeout = 1500
mySQLCommand.CommandType = CommandType.StoredProcedure
mySQLCommand.ExecuteNonQuery()
Try
If ReturnParam >= 0 Then
myNewID = Convert.ToInt32(mySQLCommand.Parameters(ReturnParam).Value)
Return myNewID
Else
Return 0
End If
Catch ex As Exception
Return 0
End Try
Catch e As Exception

Finally
 
myConn.Close()
End Try

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

SQL PROCEDURE
------------

DECLARE
@Path_File_And_Name VARCHAR(250),
@var_exec_string VARCHAR(1000)
BEGIN
 
-- SET NOCOUNT ON added to prevent extra result sets from
 
-- interfering with SELECT statements.
 
SET NOCOUNT ON;
 
BEGIN TRY
 
 
SET @Path_File_And_Name = @IMAGE
 
SET @var_exec_string ='INSERT INTO #myTable (myDocument)
     
 SELECT * FROM
     
 OPENROWSET(BULK N''' +
     
 @Path_File_And_Name + ''', SINGLE_BLOB) AS TMP_IMG'
   
   
 
BEGIN  
 
 
CREATE TABLE #myTable(myDocument varbinary(max))
 
 
EXEC(@var_exec_string)
 
 
 
INSERT INTO dbo.INSERT_IMAGE (IMAGE,LABEL)
   
VALUES(CAST((SELECT myDocument FROM #myTable) AS varbinary ),
   
@LABEL)
 
-------------------------

It is not throwing any error in store procedure or code but it is not inserting row into the table.
Can someone please help me.
Thanks

0
ASPNet_Developer
Asked:
ASPNet_Developer
1 Solution
 
KrtyknmCommented:
Always execute the dynamic SQL using the system procedure EXEC sp_executesql since it will accept the unicode characters. Also make the SQLString to NVARCHAR(MAX).

I hope the elow query will helps.
DECLARE
@Path_File_And_Name VARCHAR(250),
@var_exec_string NVARCHAR(MAX),
@IMAGE NVARCHAR(MAX),
@LABEL VARCHAR(1000)
BEGIN
 
	-- SET NOCOUNT ON added to prevent extra result sets from
 
	-- interfering with SELECT statements.
 
	SET NOCOUNT ON;
	 

	SET @IMAGE ='C:\Documents and Settings\manik\Desktop\Thai - Prash Courtney Laida.Jpg'
	 
	SET @LABEL ='Image'  
	  
	SET @Path_File_And_Name = @IMAGE 
	  
	SET @var_exec_string ='INSERT INTO #myTable (myDocument) 
	     
	 SELECT * FROM
	     
	 OPENROWSET(BULK N''' +
	     
	 @Path_File_And_Name + ''', SINGLE_BLOB) AS TMP_IMG' 
	    
	  
	CREATE TABLE #myTable(myDocument varbinary(max))
	  
	  
	EXEC sp_executesql @var_exec_string
	  
	  
	  
	INSERT INTO dbo.INSERT_IMAGE ([IMAGE],Label	)
	    
	SELECT CAST(myDocument AS VARBINARY),@LABEL FROM #myTable
	SELECT * FROM  dbo.INSERT_IMAGE

END

Open in new window

0
 
ASPNet_DeveloperAuthor Commented:
Krtyknm:
I have my table already designed and I do not want to change it, what should I do now?
0
 
ASPNet_DeveloperAuthor Commented:
Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now