Solved

ExecuteScalar returns false even when condition is true

Posted on 2007-04-02
7
245 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
Comment Utility
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
Comment Utility
kinda of tried something similar, but had more problems. can you provide code samples


thanks
0
 
LVL 21

Expert Comment

by:theGhost_k8
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:SirReadAlot
Comment Utility
i will look at this
0
 
LVL 3

Accepted Solution

by:
r1937 earned 500 total points
Comment Utility
...........
<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
Comment Utility
sure, let me try
0
 

Author Comment

by:SirReadAlot
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Zoom web page in asp.net 2 34
Sort Datagrdview programmatically 19 34
VB.Net - Find String in Array 3 25
DataGridView 2 28
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

9 Experts available now in Live!

Get 1:1 Help Now