Solved

ExecuteScalar returns false even when condition is true

Posted on 2007-04-02
7
251 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
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.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

813 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now