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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.