CODEWITHSUNDEEP
c#
Ashfaque Baig
Ashfaque Baig

Reputation: 53

Edit existing data in SQL Server database

I have a table SupplierMaster in a SQL Server database with a column SUPPLIERNAME.

I want to edit saved supplier name using stored procedure with below query

ALTER PROCEDURE [dbo].[usp_SupplierMasterUpdateDetails]
(
    @SUPPLIERNAME NVARCHAR(50)
)
AS
BEGIN
    UPDATE [dbo].[SupplierMaster]
    SET [SUPPLIERNAME] = @SUPPLIERNAME
    WHERE [SUPPLIERNAME] = @SUPPLIERNAME
END

and I run the BELOW code through "UPDATE BUTTON" to update the data.

string connString = ConfigurationManager.ConnectionStrings["dbx"].ConnectionString;

using (SqlConnection conn = new SqlConnection(connString))
{
    using (SqlCommand cmd = new SqlCommand("usp_SupplierMasterUpdateDetails", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        // Parameter
        cmd.Parameters.AddWithValue("SUPPLIERNAME", AddSupplierTextBox.Text);

        // Open Connection
        conn.Open();

        // ExecuteReader (Select Statement)
        // ExecuteScalar (Select Statement)
        // ExecuteNonQuery (Insert, Update or Delete)
        cmd.ExecuteNonQuery();

        MessageBox.Show("SUCCESSFULLY UPDATED", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

But its not updating the selected data.

Please advice and assist me to correct the code for proper work.

Upvotes: 0

Views: 152

Answers (1)

Andrei Dragotoniu
Andrei Dragotoniu

Reputation: 6345

You have multiple issues there.

First you need to fix your update query just as Thomas Levesque suggested. a SQL Server table needs a primary key to be able to uniquely identify a record, for updates for example.

The easiest thing you could do is set that primary key to be identity of type int and make it self generating. Your supplier table could look like this :

SupplierID int, Primary Key, identity
SupplierName nvarchar(100)

Now, when you do an update, you would do it like this:

Update SupplierMaster
Set SupplierName = @supplierName
Where SupplierID = @suplierID

Such a SQL statement will return an int value. This return value will tell you how many SQL rows this update statement has changed. If it says 0 then it means that the SQL statement could not find that id you passed through and nothing changed. If it says 1, then the record was found and updated, if you get more than 1 you have an issue with the SQL statement and multiple rows were updated.

In your code check for this return value and that's how you determine if your update statement was successful or not.

Upvotes: 1

Related Questions