Perform an update on a SQL table based on passed parameter

Question

This program was already written and I need to add to it an update SQL statement to update another table based on @IN. After declaring the SQL command how do I get it to excute, can I use the same SQLConnection as other sql command? WHat this program does is it parses in a file to a list called individuals and from that I just want to update the fields in SQL. The first SQL was already written in an seems to execute correctly now I need to do the UpdtHasInv SQL and actually get the code to perform the update is where I need help.

Heres what I have so far:

 static void InsertCustomData(Collection<Individuals> individuals)
    {
        #region
        string insertsql = "";

        insertsql = @"Insert into IND (IND_ID, ITEM_NAME,   INDL_ITEM_VALUE, XPTIM)
                        select  IND_ID, '{0}' as Item, '{1}' as val, (current timestamp -      curtime)"
                        FROM    IND 
                        where   IN = @IN";

    // this is SQL I added for update
          **UpdtHasInv = @"UPDATE Ind
                       SET    HAS_I = -1
                       WHERE  IN = @IN";**

        #endregion

        using (DB2Connection conn = DB2Helper.getConnection())
        {
            DB2Command cmd = conn.CreateCommand();                
            cmd.CommandTimeout = 600;
            cmd.Parameters.Add("@IN", "");

            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }

            foreach (Individuals individual in individuals)
            {
                cmd.Parameters["@IN"].Value = individual.In;


                foreach (CustomData customData in individual.CustomData)
                {
                    cmd.CommandText = string.Format(insertIndsql,   customData.Data.Key,            customData.Data.Value);

Could I just write : 'cmd.CommandText = UpdtIsInv;' after these lines:

 foreach (Individuals individual in individuals)
            {
                cmd.Parameters["@IN"].Value = individual.in

Where does sql-server come in? – JeffO Oct 10 '12 at 14:56 — JeffO, Oct 10 '12 at 14:56

score 0 · Accepted Answer · answered Oct 10 '12 at 14:22

You could simply append your UPDATE after the INSERT using a semicolon.

insertIndsql = @"
INSERT INTO IND (IND_ID, ITEM_NAME,   INDL_ITEM_VALUE, XPTIMESTAMP)
SELECT  INDIVIDUAL_ID, '{0}' as Item, '{1}' as val, (current timestamp - curtime)
FROM    IND 
WHERE   TIN = @TIN;

UPDATE DB2INST1.Individual
SET    HAS_INVESTMENT = -1
WHERE  TIN = @TIN;";

When executed, both statements will be executed as part of the same command.

Perform an update on a SQL table based on passed parameter

1 Answers1