Click here to Skip to main content
15,039,319 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi all!!!

I am storing a document in database. i want to enter null value to database if there is no document attached for the transaction. this is my code

If (fuAttachedDoc.HasFile) Then

            Dim file As HttpPostedFile = fuAttachedDoc.PostedFile
            If file.ContentLength > 0 Then
                Dim size As Integer = file.ContentLength
                obj.Size = size
                Dim name As String = file.FileName
                Dim position As Integer = name.LastIndexOf("\")
                name = name.Substring(position + 1)
                obj.Aname = name

                Dim contentType As String = file.ContentType
                obj.ContentType = contentType
                Dim fileData As Byte() = New Byte(size - 1) {}
                obj.Data = fileData
                file.InputStream.Read(fileData, 0, size)
            End If
            obj.Size = 0
            obj.Aname = ""
            obj.ContentType = ""
            obj.Data = 0

        End If

now it shows error that value of type int cannot me converted to 1-d array. i set obj.Data= Nothing then it shows that stored procedure expects a value. please tell me how can i store null to db if there is no file attached. Data is of type Byte(). I tried using obj.Data =DBNull.Value instead of zero but the same error exists after changing the code
here is the code for stored procedure

ALTER procedure [dbo].[sp_insert_details_employeeWithAttachment] 

@unqId bigint,  
@empId bigint,  
 @joining_dt datetime,   
 @deptID bigint,  
 @subDeptID bigint, 
 @sectionId bigint, 
@postId bigint,  
@scaleId bigint,  
@postType nvarchar(1),  
@BasicPay decimal(10,2),  
@GradePay decimal(10,2),  
@PayBand decimal(10,2),
@Aname nvarchar(100),
@ContentType varchar(50),
@Size bigint,
@Data varbinary(max)
--,@attachedDoc image  
 if @unqId=0  
   SET @unqId=(Select ISNULL(MAX(unqid),'0')+1 from details_employee)  
  INSERT INTO [details_employee]  
           ([unqId],[empId],[joining_dt],[deptID],[subDeptID],[sectionId] ,[postId],[scaleId],[postType],[BasicPay],[GradePay],[PayBand], [AName] ,[ContentType] ,[Size] ,[Data] 
  VALUES(@unqId,@empId,@joining_dt,@deptID,@subDeptID,@sectionId ,@postId,@scaleId,@postType,@BasicPay,@GradePay,@PayBand
  , @Aname ,@ContentType,@Size,@Data 
  UPDATE [details_employee] SET   
  empId=@empId,postId=@postId,joining_dt=@joining_dt,deptID=@deptID,subDeptID=@subDeptID,scaleId=@scaleId,postType=@postType, sectionId=@sectionId , 
  BasicPay=@BasicPay,GradePay=@GradePay,PayBand=@PayBand ,AName=@Aname,ContentType=@ContentType,Size=@Size ,Data=@Data 
  --, attachedDoc=@attachedDoc  
  WHERE unqId=@unqId  
Updated 5-Jul-12 2:44am
AmitGajjar 5-Jul-12 7:50am
on which line you are getting this error ?
ujjwal uniyal 5-Jul-12 8:21am

I tried using but still there is error..
Sanjay Kunjam 5-Jul-12 7:52am
You code is working fine, check your Object obj.
tiggerc 5-Jul-12 9:20am
does your database column allow you to insert nulls?
ujjwal uniyal 6-Jul-12 0:36am
yeah.. it has been set to allow null values


            obj.Size = 0
            obj.Aname = ""
            obj.ContentType = ""
            obj.Data = 0
End If

see above code. you set obj.Data =0 that cause the problem. you need to set it as null instead of zero. i am c# guy so no idea about vb but you need DBNull kind of assignment with the obj.Data.

hope this information helps you

ujjwal uniyal 5-Jul-12 8:23am
hmm. I tried setting it to DBNull.value but still the same error. value of type System.DBNUll cannot be converted to i-d array.

Data is of type byte()
AmitGajjar 5-Jul-12 8:28am
your database field should allow null.
ujjwal uniyal 5-Jul-12 8:33am
it allows. The problem is that stored procedure expects some data from this field. When it is set to nothing stored procedure shows error and when it is set to dbnull.value it says dbnull cannot be converted to 1-d array of Byte
AmitGajjar 5-Jul-12 8:39am
can you post your SP code by improving question ?
ujjwal uniyal 5-Jul-12 8:43am
AmitGajjar 5-Jul-12 8:51am
use SqlBinary.Null instead of DBNull.Value
AmitGajjar 5-Jul-12 9:21am
have you used System.Data.SqlTypes.SqlBinary.Null for inserting null value ?
ujjwal uniyal 5-Jul-12 8:57am
it's too not working. it says Data is Null. This method or property cannot be called on Null values.
Sanjay Kunjam 5-Jul-12 9:10am
At which point you are getting this error.

obj.Data = fileData
file.InputStream.Read(fileData, 0, size)


obj.Data = 0

Try after this.

file.InputStream.Read(fileData, 0, size)
obj.Data = fileData

ujjwal uniyal 6-Jul-12 1:29am
at obj.Data=0
AmitGajjar 6-Jul-12 2:43am
try with obj.Data = new byte[0];
AmitGajjar 5-Jul-12 9:22am
can you verify that your database field is allowing null. try to insert using manual query instead of SP. and check if it is working ?
codiemorgan 5-Jul-12 16:05pm
I expect that obj.Data is an array when you initialize it with file.InputStream.Read() ?

Maybe try creating an empty array of the type it is expecting? (1dimensional array) - maybe it accepts it. If it is expecting a 1Dimentional Array of Integer then try the following:

Dim Empty(0) as Integer; '<--- see (0) array size = Empty

Or maybe try:
Array.Resize(,0) ' this will empty the array and set its size to 0. An empty array.
Try to set obj.Data=DbNull.Value instead of obj.Data= Nothing

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