Solved

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

Posted on 2007-04-01
10
257 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:SirReadAlot
  • 4
  • 4
  • 2
10 Comments
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 500 total points
ID: 18831822
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18831832
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
 

Author Comment

by:SirReadAlot
ID: 18832729
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
 
LVL 3

Expert Comment

by:r1937
ID: 18832736
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
 
LVL 3

Expert Comment

by:r1937
ID: 18832738
Hey Lots of posts while im writing :)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:SirReadAlot
ID: 18832755
i will try this
0
 

Author Comment

by:SirReadAlot
ID: 18832773
with r1937, i got
      Message      "An SqlParameter with ParameterName '@inserted' is not contained by this SqlParameterCollection."      String
0
 

Author Comment

by:SirReadAlot
ID: 18832875
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18833191
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
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 18835332
glad to be of assistance.

AW
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now