Stored Procedure Not working from c# code

Question

I'm trying to call a stored procedure within C#.

using (SqlConnection connection = new SqlConnection(connectionString))
   using (SqlCommand command = connection.CreateCommand())
    {
      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = "updateData";
      command.Parameters.Add(new SqlParameter("@inrego", rego));
      command.Parameters.Add(new SqlParameter("@inOprt", oprt));
      command.Parameters.Add(new SqlParameter("@inService", service));

      connection.Open();

      int update = command.ExecuteNonQuery();

      Console.WriteLine(update);

      connection.Close();
    }

update shows 1 on console, but the database still isn't updated.

This is the stored procedure

CREATE PROCEDURE [dbo].updateData
@inrego varchar(5),
@inOprt char(3),
@inService as varchar(50)

AS
delete from buses where rego = @inrego;
insert into buses (rego, operator,service) values(@inrego, @inOprt, @inService);
RETURN 0

Running the stored procedure manually works, aka

USE [C:\USERS\---\DOCUMENTS\VISUAL STUDIO 2013\PROJECTS\---\TEST.DB.MDF]
GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[updateData]
        @inrego = N'1',
        @inOprt = N'2',
        @inService = N'3'

SELECT  @return_value as 'Return Value'

GO

works, and successfully updates the database, but the code form C# doesn't.

I suspect you might just need "dbo.updateData" for the call. — Gordon Linoff
– Gordon Linoff, Commented Aug 31, 2014 at 17:16
@GordonLinoff Modified it to add dbo at the start, but still not working — matthew5025
– matthew5025, Commented Aug 31, 2014 at 17:23

Eugene Podskal · Accepted Answer · 2014-09-01 07:43:53Z

4

I have been unable to reproduce the issue on clean local database(MS SQL EXPRESS 2013, Win 8.1, .NET 4.5):

CREATE TABLE [dbo].buses
(
    [rego] varchar(5) NOT NULL PRIMARY KEY,
    [operator] char(3),
    [service] varchar(50)
)

static void UpdateOrInsertBuses(String rego, String oprt, String service)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "updateData";
            command.Parameters.Add(new SqlParameter("@inrego", rego));
            command.Parameters.Add(new SqlParameter("@inOprt", oprt));
            command.Parameters.Add(new SqlParameter("@inService", service));

            connection.Open();
            try
            {
                int update = command.ExecuteNonQuery();

                Console.WriteLine(update);
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

// ...
// Add data
UpdateOrInsertBuses("11", "12", "13");
UpdateOrInsertBuses("21", "22", "23");

// Update added
UpdateOrInsertBuses("21", "22", "Changed for sure");

So, it is some issue that does not relate to your current code. As it has been suggested by @ Gordon Linoff it is either a permission issue, or some trigger that meddles into the updates, or the database for some reasons reverts or ignores any changes.

edited Sep 1, 2014 at 7:43

answered Aug 31, 2014 at 18:42

Eugene Podskal

10.4k5 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

matthew5025 Over a year ago

I'm actually "Microsoft SQL Server Database File (SqlClient)" as the data source, I'll try using a SQL server.

Eugene Podskal Over a year ago

So, it is the issue pertinent only to database files, not for hosted one? Hmm, I'll try later with database file DB, just to check.

Collectives™ on Stack Overflow

Stored Procedure Not working from c# code

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related