Solved

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

Posted on 2007-03-30
4
231 Views
Last Modified: 2008-02-01
Hi experts,

I am just trying to prevent the entry of multiple products into a data base. but keep getting the above error.

how do i fix?

thanks

====================sp=============
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



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



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
================================================================then
  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
================================

thanks  
 
0
Comment
Question by:SirReadAlot
  • 2
4 Comments
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 250 total points
ID: 18827056
I think this is the line that is failing:
     productProfile.prodID = ProdID.Value

It fails because ProdID has never been instantiated.
What do you expect to be in ProdID?  I would just comment out that line since it doesn't do anything useful, anyway.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 250 total points
ID: 18827064
BTW, I would put a unique constraint on prodName instead of trying to prevent it in your USP layer.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 18827468
Since it looks like you are attempting to return a value from your stored procedure, it you probably help if you created the Return parameter.  You can then query it successfully after executing the stored procedure.
0
 

Author Comment

by:SirReadAlot
ID: 18831474
the stored procedure works fine, its just that when the same product has been entered it displays

"product has been added" but nothing actually is inserted in the table

==============================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."
                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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

786 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