locked
How to insert rows if there are other such rows? RRS feed

  • Question

  • I have an app and need for deploying to customers. to check in the following way if they have subscribed to the app.

    they will have a table on their database. If there are aleady entries in this table, then I can add my modules to it. The record looks like this.

    MODULE_ID DESCRIPTION DISPLAY_TEXT ACTIVE
    12               Census           NULL             1

    All that I have to do is to add my modules to this in the code. I have 16 and 17 module ID and the names are 'Orders' and 'Missed Medicine'.

    Module_ID is len of 18 Numberic

    Description is varchar(50)

    Display_test varchar(25) allows nulls.

    Active is 1 numeric allows nulls.

     

    I need to code the INSERT but only if there are such rows already in the table. what is the best way for this?

    Monday, November 7, 2011 2:11 PM

Answers

  • Something like this maybe? Quickly written, so don't expect pretty code:

    SqlCommand select = new SqlCommand("select module_id from table where module_id in ('16', '17')", cx);
    SqlDataReader reader = select.ExecuteReader();
    bool exists16 = false, exists17 = false;
    while(reader.Read())
    {
            switch (reader[0] as string)
            {
                    case "16":
                            exists16 = true;
                            break;
                    case "17":
                            exists17 = true;
                            break;
            }
    }
    if (!exists16)
            new SqlCommand("insert into table (MODULE_ID, DESCRIPTION) values ('16', 'Orders')", cx).ExecuteNonQuery();
    if (!exists17)
            new SqlCommand("insert into table (MODULE_ID, DESCRIPTION) values ('17', 'Missed Medicine')", cx).ExecuteNonQuery();
    

     

    Monday, November 7, 2011 3:46 PM
  • I need to code the INSERT but only if there are such rows already in the table. what is the best way for this?

    Hi tusharr,

    Do you want to update the record? If the answer is yes, you can update the record like this:

    static void Main(string[] args)
    {
        using (SqlConnection con = new SqlConnection("Data Source=.\\bw;Integrated Security=SSPI;Initial Catalog=AdventureWorks"))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "UPDATE [TableName] SET [Description] = @Description WHERE [Module_ID] = @Module_ID";
                cmd.Parameters.Add("@Description", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Module_ID", SqlDbType.Int, 18);
                cmd.Parameters["@Description"].Value = "Orders";
                cmd.Parameters["@Module_ID"].Value = 16;
                cmd.ExecuteNonQuery();
            }
        }
    }
    


    If I misunderstand you, please give us more information about the requirement.

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 8, 2011 9:19 AM
    Moderator

All replies

  • What do you mean "if there are such rows already in the table"?

    I cant actually get this sentance, so please explain it Better.


    Mitja
    Monday, November 7, 2011 3:14 PM
  • Something like this maybe? Quickly written, so don't expect pretty code:

    SqlCommand select = new SqlCommand("select module_id from table where module_id in ('16', '17')", cx);
    SqlDataReader reader = select.ExecuteReader();
    bool exists16 = false, exists17 = false;
    while(reader.Read())
    {
            switch (reader[0] as string)
            {
                    case "16":
                            exists16 = true;
                            break;
                    case "17":
                            exists17 = true;
                            break;
            }
    }
    if (!exists16)
            new SqlCommand("insert into table (MODULE_ID, DESCRIPTION) values ('16', 'Orders')", cx).ExecuteNonQuery();
    if (!exists17)
            new SqlCommand("insert into table (MODULE_ID, DESCRIPTION) values ('17', 'Missed Medicine')", cx).ExecuteNonQuery();
    

     

    Monday, November 7, 2011 3:46 PM
  • I need to code the INSERT but only if there are such rows already in the table. what is the best way for this?

    Hi tusharr,

    Do you want to update the record? If the answer is yes, you can update the record like this:

    static void Main(string[] args)
    {
        using (SqlConnection con = new SqlConnection("Data Source=.\\bw;Integrated Security=SSPI;Initial Catalog=AdventureWorks"))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = con;
                cmd.CommandText = "UPDATE [TableName] SET [Description] = @Description WHERE [Module_ID] = @Module_ID";
                cmd.Parameters.Add("@Description", SqlDbType.VarChar, 50);
                cmd.Parameters.Add("@Module_ID", SqlDbType.Int, 18);
                cmd.Parameters["@Description"].Value = "Orders";
                cmd.Parameters["@Module_ID"].Value = 16;
                cmd.ExecuteNonQuery();
            }
        }
    }
    


    If I misunderstand you, please give us more information about the requirement.

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 8, 2011 9:19 AM
    Moderator