"Object reference not set to an instance of an object."

Hi experts
the stored procedure works fine, its just that when the same product has been entered it displays

"product has been added"  instead of "product already exists",    but nothing actually is inserted in the table
how can i fix,
thanks

==============================UI CODE===
  Private Sub btnUpdateProd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateProd.Click
        'Initialing the controls
        Dim productProfile As New ProductProfile
        productProfile.prodID = txtProdID.Text
        productProfile.prodCode = txtProdCode.Text
        productProfile.prodName = txtProdName.Text
        productProfile.prodDesc = txtProdDesc.Text
        productProfile.pfID = ddlProdFamily.SelectedValue
        productProfile.prodLicName = txtProdLicName.Text
        productProfile.prodLicEncKey = txtprodLicEncKey.Text
        productProfile.supported = ddlSupported.SelectedValue
        productProfile.licVersion = txtLicVer.Text
        productProfile.prodAltCode = txtProdAltCode.Text
        'update product
        Select Case btnUpdateProd.Text
            Case "Add Product"
                If productDB.AddProduct(productProfile) Then
                    Me.ClearFields()
                lblmsg.Text = "The Product has been added."==========> gets called even if the same product has been entered
                Else
                    lblmsg.Text = "That Product already exists."
                End If
            Case "Update Product"
                If productDB.UpdateProduct(productProfile) Then
                    Me.ClearFields()
                    lblmsg.Text = "The Product has been updated."
                Else
                    lblmsg.Text = "That Product already exists."
                End If
        End Select
       Me.SetControlState(ControlState.NoSelection)
        LoadData()
    End Sub
=============================DAL CODE===========
 'Inserts user Product
    Public Shared Function AddProduct(ByVal productProfile As ProductProfile) As Boolean
        Dim DBConnection As SqlConnection = Connection()
        Dim ProdID As SqlParameter
        DBConnection.Open()
        Try
            Dim cmdproduct As SqlCommand = New SqlCommand("SPR_INSERT_PRODUCTS", DBConnection)
            cmdproduct.CommandType = CommandType.StoredProcedure
            'cmdproduct.Parameters.Add("@ProdID", productProfile.prodID)
            cmdproduct.Parameters.Add("@prodCode", productProfile.prodCode)
            cmdproduct.Parameters.Add("@prodName", productProfile.prodName)
            cmdproduct.Parameters.Add("@prodDesc", productProfile.prodDesc)
            cmdproduct.Parameters.Add("@pfID", productProfile.pfID)
            cmdproduct.Parameters.Add("@prodLicName", productProfile.prodLicName)
            cmdproduct.Parameters.Add("@prodLicEncKey", productProfile.prodLicEncKey)
            cmdproduct.Parameters.Add("@supported", productProfile.supported)
            cmdproduct.Parameters.Add("@licVersion", productProfile.licVersion)
            cmdproduct.Parameters.Add("@prodAltCode", productProfile.prodAltCode)
            AddProduct = True
            cmdproduct.ExecuteNonQuery()
            '   productProfile.prodID = ProdID.Value
        Catch ex As Exception
            System.Diagnostics.Debug.WriteLine(ex.ToString)
            AddProduct = False
        Finally
            DBConnection.Close()
        End Try
    End Function
========================================================SP
ALTER     PROCEDURE SPR_INSERT_PRODUCTS
      (
     -- @prodID         INT,
      @prodCode       varchar(10),
      @prodName       varchar(30),
      @prodDesc       varchar(200),
      @pfID           INT,
      @prodLicName    varchar(50),
      @prodLicEncKey  varchar(20),
      @supported      smallint,
      @licVersion     smallint,
      @prodAltCode    varchar(10)
      )
AS
BEGIN
Declare @Inserted int
IF EXISTS(Select * from product where prodName = @prodName)
BEGIN
  select @Inserted = 0
END
ELSE
    BEGIN
        SELECT @Inserted = 1

INSERT INTO [ALKLicense].[dbo].[product]([prodCode],[prodName],[prodDesc],[pfID],[prodLicName],
 [prodLicEncKey],[supported],[licVersion],[prodAltCode] )
VALUES(
          --  @prodID,
            @prodCode,
            @prodName,
            @prodDesc,
            @pfID,
            @prodLicName,
            @prodLicEncKey,
            @supported,
            @licVersion,
            @prodAltCode
)

END
return @inserted
END
SirReadAlotAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
your problem is that this call:

 If productDB.AddProduct(productProfile) Then

does not test anything, since your function AddProduct does test the valu of the StoredProcedure (to determine if the INSERT was actually accomplished) so will ALWAYS evaluate as True.  The code of the AddProduct function is written, using Excute.NonQuery(), there is no way to determine if the Insert actually occurred, since the Stored Procedure's returned value (select @Inserted = 0 or SELECT @Inserted = 1) is not actually 'returned' since a NoQuery does not return anything.  Since you are expecting to get the value returnd (as a SCALAR), change the call


        AddProduct = True
            cmdproduct.ExecuteNonQuery()

to:

    If cmdProduct.ExecuteScalar() = 0 then
          AddProduct = False
    Else
          AddProduct = True
    End If

AW
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arthur_WoodCommented:
ExecuteNonQuery() is used to execute SQL or Stored Procedures that DO NOT return a Value (or a recordset), such as a direct INSERT, UPDATE, ALTER TABLE, or DELETE.  

Your SP inirectly 'might' execute an INSERT, but is DIRECTLY going to return a Value (the value of the paramater @update), so you cannot use EexcuteNonQuery - since the value is not being returned, no matter what occurs in the SP.

AW
0
SirReadAlotAuthor Commented:
Hi, thanks for everything.

I hsve used your code, but i still have the same problem. This time even if the product does not exist it still displays==='product already exist'

   Public Shared Function AddProduct(ByVal productProfile As ProductProfile) As Boolean
        Dim DBConnection As SqlConnection = Connection()
        Dim ProdID As SqlParameter
        DBConnection.Open()
        Try
            Dim cmdproduct As SqlCommand = New SqlCommand("SPR_INSERT_PRODUCTS", DBConnection)
            cmdproduct.CommandType = CommandType.StoredProcedure
            'cmdproduct.Parameters.Add("@ProdID", productProfile.prodID)
            cmdproduct.Parameters.Add("@prodCode", productProfile.prodCode)
            cmdproduct.Parameters.Add("@prodName", productProfile.prodName)
            cmdproduct.Parameters.Add("@prodDesc", productProfile.prodDesc)
            cmdproduct.Parameters.Add("@pfID", productProfile.pfID)
            cmdproduct.Parameters.Add("@prodLicName", productProfile.prodLicName)
            cmdproduct.Parameters.Add("@prodLicEncKey", productProfile.prodLicEncKey)
            cmdproduct.Parameters.Add("@supported", productProfile.supported)
            cmdproduct.Parameters.Add("@licVersion", productProfile.licVersion)
            cmdproduct.Parameters.Add("@prodAltCode", productProfile.prodAltCode)
            If cmdproduct.ExecuteScalar() = 0 Then
                AddProduct = False
            Else
                AddProduct = True
            End If
            'AddProduct = True
            'cmdproduct.ExecuteNonQuery()

        Catch ex As Exception
            System.Diagnostics.Debug.WriteLine(ex.ToString)
            ' AddProduct = False
        Finally
            DBConnection.Close()
        End Try
    End Function
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

r1937Commented:
Q: At what point does a "Object reference not set to an instance of an object." is displayed?

You say that Product has been added."==========> gets called even if the same product has been entered - which means the query/SP executed without errors.

I see something in your SP:
Your SP returns @Inserted = 0 if data is already present
else @Inserted = 1 if inserted.
So I assume the ExecuteNonQuery() executes, but no error will generate even if the record already exists. (please confirm!)

Try this instead:

cmdproduct.ExecuteNonQuery()
If CInt(cmdproduct.Parameters("@inserted").Value) = 1 Then
    AddProduct = True
Else
    AddProduct = False
End If

I am not very familiar with stored procedures, so if this does not work or anyone sees any mistake pl notify.
0
r1937Commented:
Hey Lots of posts while im writing :)
0
SirReadAlotAuthor Commented:
i will try this
0
SirReadAlotAuthor Commented:
with r1937, i got
      Message      "An SqlParameter with ParameterName '@inserted' is not contained by this SqlParameterCollection."      String
0
SirReadAlotAuthor Commented:
this code always evaluate to "false" regardless of whether data already exsits or not.

thanks

  Public Shared Function AddProduct(ByVal productProfile As ProductProfile) As Boolean
        Dim DBConnection As SqlConnection = Connection()
        Dim ProdID As SqlParameter
        DBConnection.Open()
        Try
            Dim cmdproduct As SqlCommand = New SqlCommand("SPR_INSERT_PRODUCTS", DBConnection)
            cmdproduct.CommandType = CommandType.StoredProcedure
            'cmdproduct.Parameters.Add("@ProdID", productProfile.prodID)
            cmdproduct.Parameters.Add("@prodCode", productProfile.prodCode)
            cmdproduct.Parameters.Add("@prodName", productProfile.prodName)
            cmdproduct.Parameters.Add("@prodDesc", productProfile.prodDesc)
            cmdproduct.Parameters.Add("@pfID", productProfile.pfID)
            cmdproduct.Parameters.Add("@prodLicName", productProfile.prodLicName)
            cmdproduct.Parameters.Add("@prodLicEncKey", productProfile.prodLicEncKey)
            cmdproduct.Parameters.Add("@supported", productProfile.supported)
            cmdproduct.Parameters.Add("@licVersion", productProfile.licVersion)
            cmdproduct.Parameters.Add("@prodAltCode", productProfile.prodAltCode)
            If cmdproduct.ExecuteScalar() = 0 Then
                AddProduct = False
            Else
                AddProduct = True
            End If
        Catch ex As Exception
            System.Diagnostics.Debug.WriteLine(ex.ToString)
            ' AddProduct = False
        Finally
            DBConnection.Close()
        End Try
    End Function
0
Arthur_WoodCommented:
set a breakpoint on this line in you code:

 If cmdproduct.ExecuteScalar() = 0 Then

then single step your code, and you will see what value is being returned by the ExeuteScalar call.

AW
0
Arthur_WoodCommented:
glad to be of assistance.

AW
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.