Click here to Skip to main content
15,887,596 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
<pre>I have this error during update of the customer table:

Procedure or function 'Update_Customer' expects parameter '@CustomerPhoto', which was not supplied.

Stored procedure code :<pre>


SQL
ALTER procedure [dbo].[Update_Customer]
    @CustomerID int output,
    @CustomerName nvarchar (50),
    @CustomerPhoto image,
    @CustomerEmail nvarchar(Max),
    @CustomerPhone1 nvarchar(12),
    @CustomerPhone2 nvarchar(12),
    @CustomerAddress nvarchar(Max),
    @CustomerFax nvarchar(12),
    @CustomerStatus bit,
    @CountryID int,
    @CityID int,
    @Notes nvarchar (Max),
    @ModifiedBy nvarchar (30)
AS
BEGIN
    UPDATE CustomersTbl 
    SET CustomerID = @CustomerID,
        CustomerName = @CustomerName,
        CustomerPhoto = @CustomerPhoto,
        CustomerEmail = @CustomerEmail,
        CustomerPhone1 = @CustomerPhone1,
        CustomerPhone2 = @CustomerPhone2,
        CustomerAddress = @CustomerAddress,
        CustomerFax = @CustomerFax,
        CustomerStatus = @CustomerStatus,
        CountryID = @CountryID,
        CityID = @CityID,
        Notes = @Notes,
        ModifiedDate = GETDATE(),
        ModifiedBy = @ModifiedBy
    WHERE
        CustomerID = @CustomerID
END 


Data layer class code :


VB
Friend Function Update_Customer(ByVal CustomerID As String, ByVal CustomerName As String, ByVal CustomerEmail As String, ByVal CustomerPhone1 As String, ByVal CustomerPhone2 As String, ByVal CustomerAddress As String, ByVal CustomerFax As String, ByVal CustomerStatus As Boolean, ByVal CountryID As Integer, ByVal CityID As Integer, ByVal Notes As String, ByVal ModifiedBy As String) As String
    Dim retval As String

    Dim cmd As New SqlCommand("Update_Customer")

    cmd.Parameters.AddWithValue("@CustomerID", CustomerID)
    cmd.Parameters.AddWithValue("@CustomerName", CustomerName)
    cmd.Parameters.AddWithValue("@CustomerPhoto", SqlDbType.Image).Value = photo
    cmd.Parameters.AddWithValue("@CustomerEmail", CustomerEmail)
    cmd.Parameters.AddWithValue("@CustomerPhone1", CustomerPhone1)
    cmd.Parameters.AddWithValue("@CustomerPhone2", CustomerPhone2)
    cmd.Parameters.AddWithValue("@CustomerAddress", CustomerAddress)
    cmd.Parameters.AddWithValue("@CustomerFax", CustomerFax)
    cmd.Parameters.AddWithValue("@CustomerStatus", CustomerStatus)
    cmd.Parameters.AddWithValue("@CountryID", CountryID)
    cmd.Parameters.AddWithValue("@CityID", CityID)
    cmd.Parameters.AddWithValue("@Notes", Notes)
    cmd.Parameters.AddWithValue("@ModifiedBy", ModifiedBy)

    retval = dm.executeNonQuery(cmd)

    Return retval
End Function



Business layer class code :

VB
Public Function Update_Customer_WithOutPic(ByVal CustomerID As String, ByVal CustomerName As String, ByVal CustomerEmail As String, ByVal CustomerPhone1 As String, ByVal CustomerPhone2 As String, ByVal CustomerAddress As String, ByVal CustomerFax As String, ByVal CustomerStatus As Boolean, ByVal CountryID As Integer, ByVal CityID As Integer, ByVal Notes As String, ByVal ModifiedBy As String) As String
    Dim retval As String
    retval = p.Update_Customer_WithOutPic(CustomerID, CustomerName, CustomerEmail, CustomerPhone1, CustomerPhone2, CustomerAddress, CustomerFax, CustomerStatus, CountryID, CityID, Notes, ModifiedBy)
    Return retval
End Function


Update button code :

VB
Dim retval As String = p.Update_Customer(txtCustomerCode.Text, txtCustomerName.Text, txtCustomerEmail.Text, txtCustomerPhone1.Text, txtCustomerPhone2.Text, txtCustomerAddress.Text, txtCustomerFax.Text, CheckBox2.Checked, ComboCustomerCountry.SelectedValue, ComboCustomerCity.SelectedValue, txtCustomernote.Text, FrmMain.LblUserID.Text)


Module :
VB
Public Function GetPhoto(ByVal filePath As String) As Byte()
    Dim stream As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
    Dim reader As BinaryReader = New BinaryReader(stream)
    Dim photo() As Byte = reader.ReadBytes(stream.Length)
    reader.Close()
    stream.Close()
    Return photo
End Function


Public Function byteArrayToImage(ByVal byt As Byte()) As Image
    Dim ms As New System.IO.MemoryStream()
    Dim drwimg As Image = Nothing
    Try
        ms.Write(byt, 0, byt.Length)
        drwimg = New Bitmap(ms)
    Finally
        ms.Close()
    End Try
    Return drwimg
End Function


What I have tried:

Iam sure that picture box is not empty
Posted
Updated 10-Apr-17 5:28am
Comments
Bryian Tan 10-Apr-17 11:20am    
where the code that set photo value?

cmd.Parameters.AddWithValue("@CustomerPhoto", SqlDbType.Image).Value = photo
khalid4775 10-Apr-17 11:25am    
Public Function GetPhoto(ByVal filePath As String) As Byte()
Dim stream As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
Dim reader As BinaryReader = New BinaryReader(stream)
Dim photo() As Byte = reader.ReadBytes(stream.Length)
reader.Close()
stream.Close()
Return photo
End Function


Public Function byteArrayToImage(ByVal byt As Byte()) As Image
Dim ms As New System.IO.MemoryStream()
Dim drwimg As Image = Nothing
Try
ms.Write(byt, 0, byt.Length)
drwimg = New Bitmap(ms)
Finally
ms.Close()
End Try
Return drwimg
End Function


Bryian Tan 10-Apr-17 11:32am    
Where is the GetPhoto function being called?
khalid4775 10-Apr-17 11:34am    
in Module
Richard Deeming 11-Apr-17 14:04pm    
cmd.Parameters.AddWithValue("@CustomerPhoto", SqlDbType.Image).Value = photo;

You're confusing AddWithValue and Add. Change that line to:
cmd.Parameters.AddWithValue("@CustomerPhoto", photo);

Use the debugger and check what is in photo when you you get into Update_Customer - because you don't pass the value as a method parameter.
If it's null - and I bet it is - you would get that error, and be best off passing it as a parameter to the function instead of relying on a class level variable.
 
Share this answer
 
Comments
khalid4775 10-Apr-17 11:26am    
http://store6.up-00.com/2017-04/149183753091961.jpg

But image is there


http://store6.up-00.com/2017-04/149183753103342.jpg
OriginalGriff 10-Apr-17 11:44am    
No, it isn't. That is what the debugger is telling you: "photo" is "Nothing"

Now it's up to you to find out why, and we can't do that for you because we can't run your code. Almost certainly, you are setting the "photo" field of a totally different instance of the class.
Seriously, that needs to be a parameter to you function, not a class level variable.
In your stored procedure all the fields are mandatory.

When this type of issue occurred then it is sure that some parameters which you are passing from application to DB through SP is null.
If you think @CustomerPhoto should not null then do the null check validation before calling the SP.

If you think some parameters may contains value or not then set this type of parameters as Null.
Ex: @CustomerPhoto image= null.
 
Share this answer
 
Comments
khalid4775 10-Apr-17 11:32am    
i have tried this


@CustomerID int output,
@CustomerName nvarchar (50),
@CustomerPhoto image = null,
@CustomerEmail nvarchar(Max),
@CustomerPhone1 nvarchar(12),
@CustomerPhone2 nvarchar(12),
@CustomerAddress nvarchar(Max),
@CustomerFax nvarchar(12),
@CustomerStatus bit,
@CountryID int,
@CityID int,
@Notes nvarchar (Max),
@ModifiedBy nvarchar (30)


as

BEGIN
update CustomersTbl set
CustomerID=@CustomerID,
CustomerName=@CustomerName,
CustomerPhoto=@CustomerPhoto,
CustomerEmail=@CustomerEmail,
CustomerPhone1=@CustomerPhone1,
CustomerPhone2=@CustomerPhone2,
CustomerAddress=@CustomerAddress,
CustomerFax=@CustomerFax,
CustomerStatus=@CustomerStatus,
CountryID=@CountryID,
CityID=@CityID,
Notes=@Notes,
ModifiedDate=getdate(),
ModifiedBy=@ModifiedBy
where CustomerID=@CustomerID

END


But the image has been set to Null
Ramesh Kumar Barik 10-Apr-17 11:40am    
It will work fine.
But you want to pass @CustomerPhoto value to the SP. So from the application side check whether the image file contains value or not.
khalid4775 10-Apr-17 11:44am    
http://store6.up-00.com/2017-04/149183753091961.jpg

But image is there


http://store6.up-00.com/2017-04/149183753103342.jpg
Ramesh Kumar Barik 10-Apr-17 11:57am    
It seems that in that, generated image is stored in the path.
Follow the steps that can help you.

Change the parameter datatype to Varchar(200);
Ex: @CustomerPhoto varchar(200)

from the application pass the generated file name to that parameter and it will store the newly generated file name in the DB.
When you retrieve the image from the DB then you can append the physical path with column value.
Ex:

Select 'http://store6.up-00.com/2017-04/'+ CustomerPhoto as CustomerPhoto from CustomersTbl where where CustomerID=@CustomerID.

Check this code it may help you :)

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