Solved

ExecuteScalar returns false even when condition is true

Posted on 2007-04-02
7
255 Views
Last Modified: 2012-08-13
Hi experts,

I am trying to insert prevent multiple products from being inserted into hte data table. This is what happens

1) When a data already exsits message displayed is "That Product already exists" which is fine
2) When a data does not exsit it still displays "That Product already exists" it should be "The Product has been added"

How do i recitify this?

Thanks
=======================1============================
 '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)
            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
=====================================step2=============================
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

        'If btnUpdateProd.Text = "Add Product" Then
        '    If productDB.AddProduct(productProfile) Then
        '        Me.ClearFields()
        '    Else
        '        lblmsg.Text = "That Product details  already exists."
        '    End If
        'Else
        '    If productDB.UpdateProduct(productProfile) Then
        '        Me.ClearFields()
        '    End If
        'End If
        'lblmsg.Text = "Action Successfully."
        Me.SetControlState(ControlState.NoSelection)
        LoadData()
    End Sub
#End Region

==============================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
  • 2
7 Comments
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 18835186
how about this approach?
 cmd.CommandText = "SELECT COUNT(*) FROM TABLE where PMTR=VALUE";
 Int32 count = (Int32) cmd.ExecuteScalar();
if count is >0 then data alreadyt exists.
0
 

Author Comment

by:SirReadAlot
ID: 18835193
kinda of tried something similar, but had more problems. can you provide code samples


thanks
0
 
LVL 21

Expert Comment

by:theGhost_k8
ID: 18835207
well i don' t have IDE on my machine so cant create but got this from EE only:
have an idea.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER  procedure [dbo].[Employee_Check]
@employee nvarchar
as
    select Employee from GOVWIN.dbo.EMPL where employee = @employee

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Next in your app or web form but the following:

            Dim EmployeeFound As String
            EmployeeFound = "execute employee_check " + CStr(Employee)
            Dim sqlcomm As New SqlCommand(EmployeeFound, Myconn)
            If sqlcomm.ExecuteScalar = Nothing Then
                NonQueryCommand.ExecuteNonQuery()
            Else
                Response.Write("Employee Already In Database!!")
            End If
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SirReadAlot
ID: 18835359
i will look at this
0
 
LVL 3

Accepted Solution

by:
r1937 earned 500 total points
ID: 18836527
...........
<your code>
....................
.....................
......................
            cmdproduct.Parameters.Add("@prodLicEncKey", productProfile.prodLicEncKey)
            cmdproduct.Parameters.Add("@supported", productProfile.supported)
            cmdproduct.Parameters.Add("@licVersion", productProfile.licVersion)
            cmdproduct.Parameters.Add("@prodAltCode", productProfile.prodAltCode)

        Dim RetValue As New SqlParameter()
        RetValue.ParameterName = "@Inserted"
        RetValue.SqlDbType = SqlDbType.Int
        RetValue.Direction = ParameterDirection.ReturnValue

        cmdproduct.Parameters.Add(RetValue)
        cmdproduct.ExecuteNonQuery()

        If cmdproduct.Parameters("@Inserted").Value = 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
 

Author Comment

by:SirReadAlot
ID: 18836535
sure, let me try
0
 

Author Comment

by:SirReadAlot
ID: 18837366
thank you very much, i have been trying for days!!


    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("@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)

            Dim RetValue As New SqlParameter
            RetValue.ParameterName = "@Inserted"
            RetValue.SqlDbType = SqlDbType.Int
            RetValue.Direction = ParameterDirection.ReturnValue

            cmdproduct.Parameters.Add(RetValue)
            cmdproduct.ExecuteNonQuery()

            If cmdproduct.Parameters("@Inserted").Value = 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

    'Search for users
    Public Shared Function SearchProduct(ByVal productProfile As ProductProfile, ByVal search As String) As DataTable
        Dim DBConnection As SqlConnection = Connection()
        Dim dt As New DataTable
        DBConnection.Open()
        Try
            Dim cmdproduct As SqlCommand = New SqlCommand("SPR_SEARCH_PRODUCTS", DBConnection)
            cmdproduct.CommandType = CommandType.StoredProcedure
            cmdproduct.Parameters.Add("@search", search)
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(cmdproduct)
            adapter.Fill(dt)
        Catch e As SqlException
            System.Diagnostics.Debug.WriteLine(e.ToString)
        End Try
        DBConnection.Close()
        Return dt
    End Function
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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