Sql Server Stored Procedure not executing from MVC

Question

I am trying to execute a stored procedure in my Sql Server Database from an Asp.Net MVC project. I have set it up in a way that I can use it for testing purposes, which is why the variable "procedureTest" is a constant value (I know that "2044" is an existing record in my database). I will change this once I accomplish a successful run. Also, I know that my stored procedure works because I have executed it in Sql Server Management Studio successfully. The procedure has the task of adding ID from one table to another, but I have yet to see it appear in this table. I am not receiving an error in my catch block so I am kind of lost at the moment. I could definitely use your help.

try
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();

        int procedureTest = 2044;

        var command = new SqlCommand("SELECT ID FROM Images WHERE ID = @id", connection);
        var paramDate = new SqlParameter("@id", procedureTest);
        command.Parameters.Add(paramDate);
        var reader = command.ExecuteReader();

        while (reader.Read())
        {
            var storedProcCommand = new SqlCommand("EXEC addToNotificationTable @ID", connection);
            var paramId = new SqlParameter("@ID", reader.GetInt32(0));
            storedProcCommand.Parameters.Add(paramId);
            command.ExecuteNonQuery();
        }
    }
}
catch (Exception e)
{
    string exceptionCause = String.Format("An error occurred: '{0}'", e);
    System.IO.File.WriteAllText(@"C:\Users\Nathan\Documents\Visual Studio 2013\Projects\MVCImageUpload\uploads\exception.txt", exceptionCause);
}

Stored Procedure:

CREATE PROCEDURE addToNotificationTable @ID int
AS
Insert NotificationTable (ID)
SELECT ID 
FROM Images
Where ID = @ID

1. Post the stored procedure. 2. If you press F12 and go into the network tab, do you see expected data being passed into your controller as expected? How are you calling this? 3. Use SQL Profiler to monitor the database and ensure the stored proc is being called. There are many layers of issues that could occur in this code before it even gets to the database. — Nick.Mc
– Nick.Mc, Commented Jul 21, 2015 at 5:27
@Nick.McDermaid, just added the procedure. Initially I had it in a timer that was initialized in Global.asax.cs, but was unsure if it was the timer or this code that was causing it not to work so I decided to call it from an action result that receives data from a different application, and then adds the data to my database. I just added it to the bottom of my action result, because I know the actionresult works just fine. It was a quick attempt just to see if I could get it to work and then go from there. I will try #2, and #3 that you stated. — Vigs
– Vigs, Commented Jul 21, 2015 at 5:49
@Ben, do you mean I need a ; inside of the quotation marks of the query for the SELECT command? — Vigs
– Vigs, Commented Jul 21, 2015 at 5:53
It seems you are missing CommandType in 2nd command , try using : var storedProcCommand = new SqlCommand("EXEC addToNotificationTable @ID", connection) {CommandType= CommandType.StoredProcedure }; — Anupam Singh
– Anupam Singh, Commented Jul 21, 2015 at 6:11

Mukesh Kalgude · Accepted Answer · 2015-07-21 05:32:22Z

1

Change you code like this

while (reader.Read())
        {
            var storedProcCommand = new SqlCommand("EXEC addToNotificationTable @ID", connection);
            var paramId = new SqlParameter("@ID", reader.GetInt32(0));
            storedProcCommand.Parameters.Add(paramId);
            storedProcCommand.ExecuteNonQuery();
        }

answered Jul 21, 2015 at 5:32

Mukesh Kalgude

4,8542 gold badges20 silver badges32 bronze badges

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

Comments

Adersh M · Accepted Answer · 2015-07-21 06:05:19Z

First of all, you missed to specify the command type. Also using EXEC in SqlCommand is not a proper way.

Please try with the below code

while (reader.Read())
        {
            using(SqlCommand storedProcCommand = new SqlCommand("addToNotificationTable", connection)) //Specify only the SP name
            { 
                storedProcCommand.CommandType = CommandType.StoredProcedure; //Indicates that Command to be executed is a stored procedure no a query
                var paramId = new SqlParameter("@ID", reader.GetInt32(0));
                storedProcCommand.Parameters.Add(paramId);
                storedProcCommand.ExecuteNonQuery()
            }
        }

Since you are calling the sp inside a while loop, wrap the code in using() { } to automatically dispose the command object after each iteration

Collectives™ on Stack Overflow

Sql Server Stored Procedure not executing from MVC

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related