forEach Loop Help

The code below is what I added below my original code per your post.  I guessed that the column name I need is the one I'm trying to update, templateID in this case, but when I tested it I got an error saying it cannot find table 0.  I can't change the 0 to a table name without a compiler error.

Where did I mess this up?

            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            da.SelectCommand = cmdUpdateManManDetails;
            da.Fill(ds);
 
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                dr["templateID"].ToString();
            }

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:

the sp does not return any dataset so,

            DataSet ds = new DataSet();
            da.SelectCommand = cmdUpdateManManDetails;
            da.Fill(ds);

is wrong... and dr["templateID"].ToString() is doing nothing in the loop...

 
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                dr["templateID"].ToString();
            }

the original code you post is updating all records on database... which looks fine... what do you want to do after updating records?

Hello HainKurt.  My original code only works on the first record in the mDetails table that matches a corresponding row in the aTemplates table.  It's not catching any additional matches.

The catch to all of this is the parameters being pass, with the exception of the requestID, will be different on each row in the mDetails table, so the query has to compare the mDetails table to the aTemplates table for each row in the mDetails table for the given requestID.

Dear scross1276,
Try to execute your update command in query analyzer or sql management studio using specific requestID and check how may rows has been affected.
In your application add label (Label1) control to display how many rows affected using bellow code:

Label1.Text=cmdUpdateManManDetails.ExecuteNonQuery();

If the affected rows count are same, so there is no problem with your code.

Good Luck

I get an error saying "subquery returns more than 1 value" when running it in Management Studio.

So, you have to enhance your subquery by adding more conditions to return only one templateID:

select templateID from aTemplates " +
                                    "where manmanSystem = @system " +
                                    "and manmanType = @type " +
                                    "and manmanCommand = @command " +
                                    "and manmanDb = @db

Because in the Where Clause the condition should be one value.

Good Luck

It has to update all of the records in the mDetails table for the given requestID.  That could be one or it could be 10, all of which with different parameters that it must compare to the aTemplates table first to see what templateID it needs.

I don't see how to get this done without a loop of some kind.

Dear scross1276,
The error occured in the sub query, not in the main query.
Main query is clean but you are about to update templateID in mDetails table and set templateID to given value and this value must be one value for one or more rows in mDetails,it don't matter.
Your query just like this:
Update mDetails Set templateID = value1,value2,.... (Which is returned from your sub query) Where requestID=@requestID
If you tried to run above query, No doubt it will make an error
the right one must be like this:
Update mDetails Set templateID = value Where requestID=@requestID

Good Luck

Example records for both tables are shown in the screenshot below.

The parameters match the columns by the same name.

My expectations are that for each row in the mDetails table where it has the same requestID (second column in that table), that the query compare the rows in mDetails to the rows in aTemplates, then return the matching templateID from aTemplates to mDetails where the system, type, command, and database columns match.  Only one templateID will ever match a particular set of system, type, command, and database parameters.

As you can see from the screenshot, it's not saving the templateID to the mDetails table right now, but if I run the code I orginally posted, it will update only the first mDetails record with the correct templateID and let the rest through like they weren't there.

Dear scross1276,
Till now you didn't get the point. Your update query will never run if the sub query returns more than one row.

That's why I posted this thread, to get help to figure out how to do what I described above.

Can you guys help me get it working like it needs to?

I had it working a different way ALL DAY and didn't realize it!!!  UGH!!!!  It's a long story, but it does work.  I gave you guys the points since you helped although I didn't actually do the work the way we discussed in this post.

Thanks for all of your help though!

looks like your templateID's are all null...