Executing stored procedure with asp.net

Question

I am trying to execute a stored procedure in asp.net. The stored procedure requires 3 parameters, all 3 are ID's(ints). The 3 parameters are : TaskID, ExhibitID, and InvestigatorID.

I have a hidden field that contains an array of ExhibitID's that came from a javascript function. My question is how do I get the query to execute as I am looping through the array?

Here is an example of my stored procedure:

var cnSaveTask = new SqlConnection(ConfigurationManager.ConnectionStrings["OSCIDConnectionString"].ToString());
        var comLinkExhibitToTask = new SqlCommand("p_CaseFileTasksExhibitLinkAdd", cnSaveTask) { CommandType = CommandType.StoredProcedure };
        foreach (string exhibit in hidExhibitsIDs.Value.Split(','))
        {
            comLinkExhibitToTask.Parameters.AddWithValue("@TaskID", taskID);
            comLinkExhibitToTask.Parameters.AddWithValue("@ExhibitID", Convert.ToInt32(exhibit));
            comLinkExhibitToTask.Parameters.AddWithValue("@InvestigatorID", int.Parse(Session["InvestigatorID"].ToString()));

        }

        try
        {
            cnSaveTask.Open();
            comLinkExhibitToTask.ExecuteNonQuery();
        }

It is not working in my DB though. Nothing gets added. My guess is that since it is iterating and not executing, it just keeps replacing the "exhibitID" everytime then eventually tries to execute it. But I don't think just adding "comLinkExhibitToTask.ExecuteNonQuery()" outside the try is a good idea. Any suggestions?

StrubT · Accepted Answer · 2012-09-27 19:22:28Z

1

you can either move the try block into the foreach loop or wrap the foreach loop with a try block. (depending on what error handling you wish - continue with the next exhibit on error or completely abort execution)

answered Sep 27, 2012 at 19:22

StrubT

1,0287 silver badges18 bronze badges

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

Comments

Nick Vaccaro · Accepted Answer · 2012-09-27 19:37:10Z

1

I've never used AddWithValue, so I can't speak to its functionality. Here's how I typically write a DB call like this.

using (SqlConnection cnSaveTask = new SqlConnection(ConfigurationManager.ConnectionStrings["OSCIDConnectionString"].ConnectionString))
{
    cnSaveTask.Open();

    using (SqlCommand comLinkExhibitToTask = new SqlCommand("p_CaseFileTasksExhibitLinkAdd", cnSaveTask))
    {
        comLinkExhibitToTask.CommandType = CommandType.StoredProcedure;

        comLinkExhibitToTask.Parameters.Add(new SqlParameter("@TaskID", SqlDbType.Int) {Value = taskID});
        // etc.

        comLinkExhibitToTask.ExecuteNonQuery();
    }
}

answered Sep 27, 2012 at 19:37

Nick Vaccaro

5,5126 gold badges42 silver badges60 bronze badges

2 Comments

user1084319 Over a year ago

I think "AddWithValue" is causing the problem, because I get a "storedProcedureName has too many arguments". I think it just keeps adding parameters.

user1084319 Over a year ago

Will let you know in a few mins. Gotta recompile

user1084319 · Accepted Answer · 2012-09-27 20:11:33Z

The solution:

var cnSaveTask = new SqlConnection(ConfigurationManager.ConnectionStrings["OSCIDConnectionString"].ToString());
        try
        {
            var comLinkExhibitToTask = new SqlCommand("p_CaseFileTasksExhibitLinkAdd", cnSaveTask) { CommandType = CommandType.StoredProcedure };
            cnSaveTask.Open();
            comLinkExhibitToTask.Parameters.Add(new SqlParameter("@TaskID", SqlDbType.Int));
            comLinkExhibitToTask.Parameters.Add(new SqlParameter("@ExhibitID", SqlDbType.Int));
            comLinkExhibitToTask.Parameters.Add(new SqlParameter("@InvestigatorID", SqlDbType.Int));

            foreach (string exhibit in hidExhibitsIDs.Value.Split(','))
            {
                comLinkExhibitToTask.Parameters["@TaskID"].Value = taskID;
                comLinkExhibitToTask.Parameters["@ExhibitID"].Value = Convert.ToInt32(exhibit);
                comLinkExhibitToTask.Parameters["@InvestigatorID"].Value = int.Parse(Session["InvestigatorID"].ToString());

                comLinkExhibitToTask.ExecuteNonQuery();
            }
        }
        catch (Exception ex)
        {
            ErrorLogger.Log(0, ex.Source, ex.Message);
        }
        finally
        {
            if (cnSaveTask.State == ConnectionState.Open)
            {
                cnSaveTask.Close();
            }
        }

Since I was in a loop it kept adding parameters. So just declare the parameters outside the loop, and only pass the values in the loop. That way there are only 3 parameters, and the values will be passed in accordingly

Consider the "using" statement as well. It will handle your finally block automatically.

Collectives™ on Stack Overflow

Executing stored procedure with asp.net

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related