C# Sql Data not saving

Question

I have a few tables in a c# application I'm currently working on and for 4/5 of the tables everything saves perfectly fine no issues. For the 5th table everything seems good until I reload the program again (without modifying the code or working with a seperate install so that the data doesn't go away) The 4/5 tables are fine but the 5th doesn't have any records in it after it has been restarted (but it did the last time it was running). Below is some code excerpts. I have tried a few different solutions online including creating a string to run the sql commands on the database manually and creating the row directly as opposed to the below implementation which uses a generic data row.

//From main window
        private void newInvoice_Click(object sender, EventArgs e)
        {
            PosDatabaseDataSet.InvoicesRow newInvoice = posDatabaseDataSet1.Invoices.NewInvoicesRow();
            Invoices iForm = new Invoices(newInvoice, posDatabaseDataSet1, true);
        }

//Invoices Table save [Works] (from Invoices.cs)
 private void saveInvoice_Click(object sender, EventArgs e)
        {
            iRecord.Date = Convert.ToDateTime(this.dateField.Text);
            iRecord.InvoiceNo = Convert.ToInt32(this.invoiceNumField.Text);
            iRecord.Subtotal = (float) Convert.ToDouble(this.subtotalField.Text);
            iRecord.Tax1 = (float)Convert.ToDouble(this.hstField.Text);
            iRecord.Total = (float)Convert.ToDouble(this.totalField.Text);
            iRecord.BillTo = this.billToField.Text;
            invoicesBindingSource.EndEdit();
            if (newRecord)
            {
                dSet.Invoices.Rows.Add(iRecord);
                invoicesTableAdapter.Adapter.Update(dSet.Invoices);
            }
            else
            {
                string connString = Properties.Settings.Default.PosDatabaseConnectionString;
                string queryString = "UPDATE dbo.Invoices set ";
                queryString += "Date='" + iRecord.Date+"'";
                queryString += ", Subtotal=" + iRecord.Subtotal;
                queryString += ", Tax1=" + iRecord.Tax1.ToString("N2");
                queryString += ", Total=" + iRecord.Total;
                queryString += " WHERE InvoiceNo=" + iRecord.InvoiceNo;
                using (SqlConnection dbConn = new SqlConnection(connString))
                {
                    SqlCommand command = new SqlCommand(queryString, dbConn);
                    dbConn.Open();
                    SqlDataReader r = command.ExecuteReader();
                    dbConn.Close();
                }
            }
            dSet.Invoices.AcceptChanges();
        }

//Invoice Items save [works until restart] (also from Invoices.cs)
  private void addLine_Click(object sender, EventArgs e)
        {
                DataRow iRow = dSet.Tables["InvoiceItems"].NewRow();
                iRow["Cost"] = (float)Convert.ToDouble(this.costField.Text);
                iRow["Description"] = this.descriptionField.Text;
                iRow["InvoiceNo"] = Convert.ToInt32(this.invoiceNumField.Text);
                iRow["JobId"] = Convert.ToInt32(this.jobIdField.Text);
                iRow["Qty"] = Convert.ToInt32(this.quantityField.Text);
                iRow["SalesPerson"] = Convert.ToInt32(this.salesPersonField.Text);
                iRow["SKU"] = Convert.ToInt32(this.skuField.Text);

                dSet.Tables["InvoiceItems"].Rows.Add(iRow);
                invoiceItemsTableAdapter.Adapter.Update(dSet,"InvoiceItems");
                PosDatabaseDataSet.InvoiceItemsDataTable dTable = (PosDatabaseDataSet.InvoiceItemsDataTable)dSet.InvoiceItems.Copy();
                DataRow[] d = dTable.Select("InvoiceNo=" + invNo.ToString());
                invoiceItemsView.DataSource = d;

        }

Thanks in advance for any insight.

UPDATE: October 17, 2011. I am still unable to get this working is there any more ideas out there?

Answer 1

you must execute your Sql Command in order to persis the changes you made.

using (SqlConnection dbConn = new SqlConnection(connString))
{
  dbConn.Open();
  SqlCommand command = new SqlCommand(queryString, dbConn);
  command.ExecuteNonQuery();
  dbConn.Close();
}

The ExecuteReader method is intended (as the name says) to read the data from a SQL table. You need to use a different method as you can see above.

Answer 2

We need some more info first, you haven't shown the case where your code fails.

Common mistakes on this kind of code is calling DataSet.AcceptChanges() before actually committing the changes to the database.

Second is a conflict between databound data through the binding source vs edits to the dataset directly.

Lets see the appropriate code and we can try and help.

Set a breakpoint after teh call to invoiceItemsTableAdapter and check the InvoiceItems table for the row you have added. Release the breakpoint and then close your app. Check the database again. I would say that another table may be forcibly overwriting the invoice item table.