C# sql IF NOT EXIST statement not working?

Question

Not sure if this is written correctly but it looks correct. I am wanting to update a record if the id already exists and insert if not.

        DataSet ds = new DataSet();

        ds.ReadXml(XDocument.Load(Application.StartupPath + @"\xml1.xml").CreateReader());


        using (var conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Application.StartupPath + "\\Database3.mdb"))
        {
            conn.Open();

            // make two commands here
            var commInsert = new OleDbCommand("Insert INTO Table1 (description, active) VALUES (@iq_question,@active);", conn);
            var commUpdate = new OleDbCommand("UPDATE Table1 SET description=@iq_question,active=@active WHERE ID=@question_id;", conn);
            // here add your parameters with no value

            //string question_id = row[0].ToString();
            //string iq_question = row[1].ToString();
            //string active = row[4].ToString();
            commInsert.Parameters.Add(new OleDbParameter("@iq_question", OleDbType.VarChar));
            commInsert.Parameters.Add(new OleDbParameter("@active", OleDbType.VarChar));

            commUpdate.Parameters.Add(new OleDbParameter("@question_id", OleDbType.AutoNumber));
            commUpdate.Parameters.Add(new OleDbParameter("@iq_question", OleDbType.Text));
            commUpdate.Parameters.Add(new OleDbParameter("@active", OleDbType.Text));

            foreach (DataTable table in ds.Tables)
            {
                foreach (DataRow row in table.Rows)
                {
                    // here only reset the values
                    commUpdate.Parameters["@question_id"].Value = row[0].ToString();
                    commUpdate.Parameters["@iq_question"].Value = row[1].ToString();
                    commUpdate.Parameters["@active"].Value = row[4].ToString();

                    int recs = commUpdate.ExecuteNonQuery();
                    if (recs < 1) // when no records updated do insert
                    {
                        commInsert.Parameters["@iq_question"].Value = row[1].ToString();
                        commInsert.Parameters["@active"].Value = row[4].ToString();

                        commInsert.ExecuteNonQuery();
                    }
                }
            }
            commInsert.Dispose();
            commUpdate.Dispose();
            conn.Close();
        }
        System.Windows.Forms.MessageBox.Show("Updated Latest Data Was Succesfull");

I either get an error on the insert saying it will create duplicate content, or it creates more rows with different data. So say I should be getting 10 rows from the xml file, the first time I run it I get the 10 rows with the correct data. If I run it again, I end up with 10 more so being 20 but the last 10 rows show different data. I don't think I am identifying the rows in the xml file correctly and I need to do some research on that part.

Answer 1

There is no Exists for MS Access. The engine is much more primitive than Sql Server. See here: Microsoft Access SQL. I think, what you can do is:

myCommand.CommandText = "UPDATE Table1 SET description=@iq_question,active=@active WHERE ID=@currentRow";
......
int recs = myCommand.ExecuteNonQuery();

if (recs < 1) // when no records updated do insert
{
    myCommand.Parameters.Clear();
    myCommand.CommandText = "Insert INTO Table1 VALUES(@iq_question,@active)";
    .....
}

This is still 2 statements but you can save some coding by not doing Select first. Because ExecuteNonQuery will tell you if you updated anything

Another thing is that your code is a bit inefficient. You have nested loop where you can reuse same command and connection. Yuu can do this

using (var conn = new OleDbConnection(.......))
{
    conn.Open();
    // make two commands here
    var commInsert = new OleDbCommand(.....);
    var commUpdate = new OleDbCommand(.....);
    // here add your parameters with no value
    commInsert.Parameters.Add(new OleDbParameter(....));
    .......

    Foreach (....)
    {
        Foreach (....)
        {
            // here only reset the values
            commUpdate.Parameters[0].Value = ...
            ...
            int recs = commUpdate.ExecuteNonQuery();
            if (recs < 1) // when no records updated do insert
            {
                commInsert.Parameters[0].Value = iq_question;
                .....
            }
        }
    }
    commInsert.Dispose();
    commUpdate.Dispose();
}

You can also use nested using for commands. Only setting values will be more efficient to what you do right now.