CODEWITHSUNDEEP
asp.netstored-procedures
user1084319
user1084319

Reputation: 299

Executing stored procedure with asp.net

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?

Upvotes: 0

Views: 742

Answers (3)

user1084319
user1084319

Reputation: 299

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

Upvotes: 0

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

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();
    }
}

Upvotes: 1

StrubT
StrubT

Reputation: 1018

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)

Upvotes: 1

Related Questions