Solved

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

Posted on 2007-04-01
10
263 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 

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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Web Form VB.Net  import CSV 4 47
Get Client IP on RDS - VB.NET 15 70
VB.NET 2008 Winforms Signing 13 33
Passing data between Forms 3 20
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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