Unable to insert record using the ExecuteScalar or ExecuteNon-Query

Question

I have a database with a role sa.

When I execute the stored procedure which inserts a record to the table. However, when I run the same stored procedure via the ADO.NET code, the record is not getting inserted successfully. However, I can see that that the stored procedure has been called and runs successfully up to ExecuteQuery.

Tried adding a transaction and commit and still no luck.

using (SqlConnection conn = new SqlConnection(ConnectionString))
{
    conn.Open();

    using (SqlCommand command = conn.CreateCommand())
    {
        command.CommandText = "InsertData";
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@Id", Id);
        command.Parameters.AddWithValue("@name", Name);                       

        command.ExecuteNonQuery();              
    }   
}

Stored procedure:

CREATE PROCEDURE [dbo].[InsertData]
    @Id INT,
    @name VARCHAR(50)
AS
    SET NOCOUNT ON;

    INSERT INTO Employee (Id, Name) 
    VALUES (@Id, @name)
GO

var ConnectionString = "Data Source=xxxx;Initial Catalog=xxxx;Persist Security Info=True;User Id=xxxx;Password=xxxxx;MultipleActiveResultSets=True";

Show us the code. Both the Store Procedure and the `ExecuteQuery()` — Juan Carlos Oropeza, Oct 25 '17 at 13:07
Psychic powers suggest that you are inside a transaction you haven't shared with us, and you need to commit that transaction. — Chris Nielsen, Oct 25 '17 at 13:11
No exception..works successfully but records not inserted.. :( — Dev, Oct 25 '17 at 13:11
@ChrisNielsen no transactions.. I tried adding a transaction and committing it.. Still no luck — Dev, Oct 25 '17 at 13:12
Check and then double check your connection string. In almost every question like this that I have seen, it turns out that the code was working fine, just not inserting to the database/server that was being checked for the existence of a record. — GarethD, Oct 25 '17 at 13:17
@GarethD connection string looks alright to me... checked several times... — Dev, Oct 25 '17 at 13:19
side note: [Can we stop using AddWithValue() already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Zohar Peled, Oct 25 '17 at 13:21
When you say *"I can see that that the sproc has been called*" - how are you checking this? If you haven't already, you could run a trace with SQL Server Profiler to see what commands are being run on the database. — GarethD, Oct 25 '17 at 13:22
@GarethD "I can see that that the sproc has been called" = using sql trace — Dev, Oct 25 '17 at 13:27
Put a try catch block in but you said it is not throwing an error — paparazzo, Oct 25 '17 at 13:34
@Dev on which database? Typically when people have such questions they are either targeting the wrong database (dev vs test) or use a user-instance database that gets replaced every time they restart debugging. What does the connection string look like? Does it have an `AttachDbFileName` keyword? — Panagiotis Kanavos, Oct 25 '17 at 13:34
@Dev *post* the connection string here, don't say it looks OK. Clearly something isn't OK. It could be a user-instance DB, it could be a LocalDB that gets rebuild by some migration script — Panagiotis Kanavos, Oct 25 '17 at 13:36
@Paparazzi I have try catch block as well... Not hitting catch block as there are no errors — Dev, Oct 25 '17 at 13:41
@ChrisNielsen you were right. It was transaction scope in C# code in a different layer..... — Dev, Oct 25 '17 at 14:31

score 2 · Answer 1 · answered Oct 25 '17 at 14:30

Finally resolved after spending a day on it...I was using a legacy code. Someone who is very brilliant has got a transaction open using C# code . and we have these methods where you create a new sql connection string which inserts the data and then since we have not got that transaction complete- It was not inserting a record..

So best way to resolve this is find the word 'transaction' and you may find something like :

TransactionOptions options = new TransactionOptions();
options.IsolationLevel = IsolationLevel.ReadCommitted;
transaction = new TransactionScope(TransactionScopeOption.RequiresNew, options);

score 0 · Answer 2 · answered Oct 25 '17 at 13:40

Your code looks fine, can you try doing this?

using (SqlConnection conn = new SqlConnection(ConnectionString))
    {
        conn.Open();

        using (SqlCommand command = conn.CreateCommand())
        {
            string cmd = string.format("INSERT INTO PaymentDevices (Id, Name) VALUES ({0}, '{1}')",Id,Name);
            command.CommandText = cmd;
            command.CommandType = CommandType.Text;

            command.ExecuteNonQuery();              
        }   
  }

This will help you to identify where your problem is, if it works then the problem is your SP call itself, if it does not work the problem is in your connection string or a transaction open

Unable to insert record using the ExecuteScalar or ExecuteNon-Query

2 Answers2