?
Solved

ExecuteScalar returns false even when condition is true

Posted on 2007-04-02
7
Medium Priority
?
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 21

Expert Comment

by:K V
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:K V
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
Industry Leaders: 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 2000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

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 …
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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