using one button to insert and update different db tables

Question

I am using the same button "Save" to update a table called AnalysisExperiments and also to insert data into a table called "Analysis". however the update is not working. Here is the code:

 #region Insert Data into Analysis Table
        if (checkIfRepeatedJobNumber(tbJobNumber.Text.Trim()))
        {
            MessageBox.Show("Job Number is already exist.", "Repeated Data");
            return;
        }

        string query = "insert into Analysis (ID, WellName, EstimatedStartDate, SOWComments, ProgressComments, Field, FocalPoint, StudyCompleted, Company, ReservoirPressure, ReservoirTemp, SelectedSamples) " +
            "values (@ID, @WellName, @SamplingDate, @SOWComments, @ProgressComments, @Field, @FocalPoint, @StudyCompleted, @Company, @ReservoirPressure, @ReservoirTemp, @SelectedSamples)";
        OleDbCommand cmd = new OleDbCommand(query, conn);
        cmd.Parameters.AddWithValue("@ID", tbJobNumber.Text);
        cmd.Parameters.AddWithValue("@WellName", tbWellName.Text);
        cmd.Parameters.AddWithValue("@SamplingDate", dtpSamplingDate.Text);
        cmd.Parameters.AddWithValue("@SOWComments", tbSOW_Comments.Text);
        cmd.Parameters.AddWithValue("@ProgressComments", tbProgressComments.Text);
        cmd.Parameters.AddWithValue("@Field", tbFieldName.Text);
        cmd.Parameters.AddWithValue("@FocalPoint", tbFocalName.Text);
        if (radYes.Checked)
            cmd.Parameters.AddWithValue("@StudyCompleted", "Yes");
        else
            cmd.Parameters.AddWithValue("@StudyCompleted", "No");

        cmd.Parameters.AddWithValue("@Company", tbCompany.Text);
        cmd.Parameters.AddWithValue("@ReservoirPressure", tbReservoirPressure.Text);
        cmd.Parameters.AddWithValue("@ReservoirTemp", tbReservoirTemp.Text);
        cmd.Parameters.AddWithValue("@@SelectedSamples", tbSelectedSamples.Text);


        try
        {
            conn.Open();
            int j = cmd.ExecuteNonQuery();
            if (j == 1)
            {
                MessageBox.Show("Done");
                this.Close();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            conn.Close();
        }
        #endregion

        #region update analysis Exp but still no working

        #region Update database
        try
        { int k = 0;
            OleDbDataAdapter da;
            da = new OleDbDataAdapter("select* from [AnalysisExperiments]", conn);
            string ExpQuery = "update AnalysisExperiments set SampleNumber = @SampleNumber, Status = @Status where ID = '" + tbJobNumber.Text + "' and Experiment = '";

            foreach (DataGridViewRow row in dgvExperiments.Rows)
            {
                ExpQuery += row.Cells["Experiment"].Value.ToString() + "'";
                OleDbCommand updateCommand = new OleDbCommand(ExpQuery, conn);
                updateCommand.Parameters.Add("@SampleNumber", OleDbType.VarWChar);
                MessageBox.Show(row.Cells["SampleNumber"].Value.ToString() + " | " + row.Cells["Status"].Value.ToString() + " | " + row.Cells["Experiment"].Value.ToString());
                updateCommand.Parameters["@SampleNumber"].Value = row.Cells["SampleNumber"].Value.ToString();
                updateCommand.Parameters.Add("@Status", OleDbType.Boolean);
                updateCommand.Parameters["@Status"].Value = row.Cells["Status"].Value;
                da.UpdateCommand = updateCommand;

                conn.Open();
                k = da.UpdateCommand.ExecuteNonQuery();
                conn.Close();
            }

            if (k == 1)
                MessageBox.Show("Done");
            else
            {
                MessageBox.Show("Nothing Updated!");
            }
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        #endregion

        #endregion

knowing that the compiler skips the loop in the update region.

There is a space missing in your select query, between the select keyword and the *, although I am not sure if that is the problem. — full-hyperion
– full-hyperion, Commented Feb 28, 2017 at 7:55
At the second loop the query syntax becomes invalid cause the += that concatenates the previous text with the new one — Steve
– Steve, Commented Feb 28, 2017 at 7:56

Steve · Accepted Answer · 2017-02-28 08:01:35Z

1

At the second loop the query syntax becomes invalid cause the += that concatenates the previous text with the new one

Dim newQuery = ExQuery + row.Cells["Experiment"].Value.ToString() + "'";

And then use the new string for the ExecuteNonQuery.

Still this approach has many problems. You should separate this code in different methods and use parameters also for the last value

answered Feb 28, 2017 at 8:01

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mohammed Al-A'mri Over a year ago

I tried that. But the compiler does not enter the loop body at all. @Steve

Aboc · Accepted Answer · 2017-02-28 08:28:02Z

0

Have you tried to debug the foreach line to see if there are any rows in your dgvExperiments and if they are of type (or subtype of) DataGridViewRow ?

answered Feb 28, 2017 at 8:28

Aboc

2371 silver badge5 bronze badges

2 Comments

Mohammed Al-A'mri Over a year ago

Good hint. I can see a row when the form is active but I trace the code I found that there is no rows.

Aboc Over a year ago

I think the error comes from antoher part of your code, then.

Collectives™ on Stack Overflow

using one button to insert and update different db tables

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related