Update records in foreach loop

Hi I want to loop through a datatable and for each row pass values from the row fields to a scalar function that takes long and lat coordinates and updates a field in the row with the result of the function.
I'm not even sure if this is the right way to go about this, but this is what I've come up with so far and am getting an error saying too 'Procedure or function usp_update_riMileageDemo has too many arguments specified.'  Any ideas on what I can do to fix this?

//code behind function
public void upDistance()
        {
            using (SqlConnection upconn = new SqlConnection(connection))
            {  
                SqlCommand upcmd = new SqlCommand("usp_update_riMileageDemo", upconn);
                upcmd.CommandType = CommandType.StoredProcedure;

                DataTable upTable = getriRecords();
                foreach (DataRow row in upTable.Rows)
                {
                    Double slat; Double slong; Double dlat; Double dlong; Int32 riID;
                    riID = Convert.ToInt32(row["riRateDetailID"]);
                    slat = Convert.ToDouble(row["SrceLat"]);
                    slong = Convert.ToDouble(row["SrceLong"]);
                    dlat = Convert.ToDouble(row["DestLat"]);
                    dlong = Convert.ToDouble(row["DestLong"]);

                    Double distance;
                    distance = pcMiler(slat, slong, dlat, dlong);

                    upconn.Open();
                    upcmd.Parameters.AddWithValue("@riID", riID);
                    upcmd.Parameters.AddWithValue("@distance", distance);

                    upcmd.ExecuteNonQuery();
                    upconn.Close();
                }
            }
              }

Open in new window


sql sproc

ALTER PROCEDURE [dbo].[usp_update_riMileageDemo]
      -- Add the parameters for the stored procedure here
      @riID int,
      @distance as decimal
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
update dbo.RIRateDetail set Distance = @distance where RIRateDetailID = @riID
END
Scarlett72Asked:
Who is Participating?
 
Snarf0001Commented:
        public void upDistance()
        {
            using (SqlConnection upconn = new SqlConnection(connection))
            {
                SqlCommand upcmd = new SqlCommand("usp_update_riMileageDemo", upconn);
                upcmd.CommandType = CommandType.StoredProcedure;

                var pRID = upcmd.Parameters.Add("@riID", SqlDbType.Int);
                var pDistance = upcmd.Parameters.Add("@distance", SqlDbType.Decimal);

                DataTable upTable = getriRecords();
                foreach (DataRow row in upTable.Rows)
                {
                    Double slat; Double slong; Double dlat; Double dlong; Int32 riID;
                    riID = Convert.ToInt32(row["riRateDetailID"]);
                    slat = Convert.ToDouble(row["SrceLat"]);
                    slong = Convert.ToDouble(row["SrceLong"]);
                    dlat = Convert.ToDouble(row["DestLat"]);
                    dlong = Convert.ToDouble(row["DestLong"]);

                    Double distance;
                    distance = pcMiler(slat, slong, dlat, dlong);

                    upconn.Open();
                    pRID.Value = riID;
                    pDistance.Value = distance;

                    upcmd.ExecuteNonQuery();
                    upconn.Close();
                }
            }
        }

Open in new window

0
 
Snarf0001Commented:
You're adding new instances of the parameters on every pass of the for loop.
So by the time you're on row 10, you have 10 parameters called @riID and 10 called @distance.

Declare the parameters outside the for loop, and then just reset the values on each pass.
0
 
Scarlett72Author Commented:
Thanks Snarf, I'm away from the office over the weekend, will test and reply on Monday.
0
 
Scarlett72Author Commented:
Hi Snarf, you put me on the right track to resolve this I added upcmd.Parameters.Clear(); after upcmd.ExecuteNonQuery();

~Thank you,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.