Solved

ExecuteScalar returns false even when condition is true

Posted on 2007-04-02
7
256 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: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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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