i have create a simple aplication to insesrt some details to a database.
and now i need to update the details.
it means (PK IS ITEM NUMBER)
WHEN I fill the update form with an existing ITEM NUMBER the table is successfully updating,
but when i enter a NOT EXISTING ITEM NUMBER the table is creating a new record with entered ITEM NUMBER.
so i wont to do is when i insert an NOT EXISTING ITEM NUMBER to the text field and press enter i must get an message cold "no itemnuber found".

i am using SP.
this is my update procedure.

USE [ICS]
GO
/****** Object:  StoredProcedure [dbo].[Amend]    Script Date: 12/10/2009 08:35:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Amend]

@itemCode varchar(50) ,
@Description varchar(50) ,
@StockBalance varchar(50) ,
@ReLevel varchar(50) ,
@EconomicOrQuality varchar(50) ,
@Price varchar(50) ,
@Unit varchar(50) ,
@lasDateOfIss datetime ,
@Location varchar(50)

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here

UPDATE    AddItem
SET              Description =@Description, StockBalance =@StockBalance, ReLevel =@ReLevel, EconomicOrQuality =@EconomicOrQuality,
				 Price =@Price, Unit =@Unit, lasDateOfIss =@lasDateOfIss, Location =@Location
WHERE itemCode = @itemCode


END

my update button click event

private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=ICS;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("dbo.Amend", con);

            cmd.CommandType = CommandType.StoredProcedure;

           
            cmd.Parameters.AddWithValue("@itemCode", textBox1.Text);
            cmd.Parameters.AddWithValue("@Description", textBox2.Text);
            cmd.Parameters.AddWithValue("@StockBalance", textBox3.Text);
            cmd.Parameters.AddWithValue("@ReLevel", textBox4.Text);
            cmd.Parameters.AddWithValue("@EconomicOrQuality", textBox5.Text);
            cmd.Parameters.AddWithValue("@Price", textBox6.Text);
            cmd.Parameters.AddWithValue("@Unit", textBox7.Text);
            cmd.Parameters.AddWithValue("@lasDateOfIss", dateTimePicker1.Value.Date);
            cmd.Parameters.AddWithValue("@Location", textBox9.Text);

            try
            {
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString());
                con.Close();
                 
            }

Hi,

I have checked the given code. It is working as expected . I have tried with valid item code and it has been updated properly. No row has been inserted while giving invalid item code.

To ensure the number of rows affected i have commented the

SET NOCOUNT ON;

line in SP. The records affected count is 1 while updating existing record and it is 0 while giving non-existing record.

Is there any code available in the following lines in SP ?

-- Insert statements for procedure here

may missed while posting?

This article has been dead for over six months. Start a new discussion instead.