Solved

Urgent data adapter not saving data

Posted on 2004-10-16
3
178 Views
Last Modified: 2010-04-15
Hello experts.  I am loading in a table with 300 stocks, then poulating the table with the latest data from another table.  

I have checked the dataGrid and the changes to the dataTable in C# code is flawless.

The problem here is the data adapter is not updating all the rows. In fact, it is only updating half of the rows, leaving half empty. I dont know how to solve this.

Any recommendations on how the get the dataset to save the sql server would be greatly appreciated!

Thanks


using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

namespace Stocktable
{

      public class Form1 : System.Windows.Forms.Form
      {
            public DataSet virtualDatabase;
            public SqlConnection cnDatabase;
            public string cnString;
            public System.Data.SqlClient.SqlDataAdapter da_tblStocks;
            public System.Data.SqlClient.SqlDataAdapter da_tblStockPrices;
            public SqlCommandBuilder cb_tblStocks;
            public SqlCommandBuilder cb_tblStockPrices;
            public DataTable tblStocks;
            public DataTable tblStockPrices;
            public DataRow drStock;
            public DataRow drStockPrice;
            private System.Windows.Forms.DataGrid dataGrid1;
            private System.Windows.Forms.Button button1;

            private System.ComponentModel.Container components = null;

            public Form1()
            {
                  InitializeComponent();
                  virtualDatabase = new DataSet();
                  cnString = "workstation id=HISTDB;packet size=4096;integrated security=SSPI;data source=\"HIST" +
                        "DB\\STOCKDATABASE\";persist security info=True;initial catalog=DB1";
                  cnDatabase = new SqlConnection(cnString);
                  cnDatabase.Open();
                  da_tblStocks = new SqlDataAdapter("SELECT * from tblStocks", cnDatabase);
                  da_tblStocks.Fill(virtualDatabase, "tblStocks");
                  cb_tblStocks = new SqlCommandBuilder(da_tblStocks);
                  tblStocks = virtualDatabase.Tables["tblStocks"];

                  da_tblStockPrices = new SqlDataAdapter("SELECT * from tblStockPrice", cnDatabase);
                  da_tblStockPrices.Fill(virtualDatabase, "tblStockPrices");
                  cb_tblStockPrices = new SqlCommandBuilder(da_tblStockPrices);
                  tblStockPrices = virtualDatabase.Tables["tblStockPrices"];
      
                  dataGrid1.DataSource = tblStocks.DefaultView;
            }

            protected override void Dispose( bool disposing )
            {
                  if( disposing )
                  {
                        if (components != null)
                        {
                              components.Dispose();
                        }
                  }
                  base.Dispose( disposing );
            }

            #region Windows Form Designer generated code

            private void InitializeComponent()
            {
                  this.dataGrid1 = new System.Windows.Forms.DataGrid();
                  this.button1 = new System.Windows.Forms.Button();
                  ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
                  this.SuspendLayout();
                  //
                  // dataGrid1
                  //
                  this.dataGrid1.DataMember = "";
                  this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
                  this.dataGrid1.Location = new System.Drawing.Point(8, 64);
                  this.dataGrid1.Name = "dataGrid1";
                  this.dataGrid1.Size = new System.Drawing.Size(600, 392);
                  this.dataGrid1.TabIndex = 0;
                  //
                  // button1
                  //
                  this.button1.Location = new System.Drawing.Point(8, 8);
                  this.button1.Name = "button1";
                  this.button1.Size = new System.Drawing.Size(128, 40);
                  this.button1.TabIndex = 1;
                  this.button1.Text = "Update";
                  this.button1.Click += new System.EventHandler(this.button1_Click);
                  //
                  // Form1
                  //
                  this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
                  this.ClientSize = new System.Drawing.Size(720, 461);
                  this.Controls.Add(this.button1);
                  this.Controls.Add(this.dataGrid1);
                  this.Name = "Form1";
                  this.Text = "Form1";
                  this.Load += new System.EventHandler(this.Form1_Load);
                  ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
                  this.ResumeLayout(false);

            }
            #endregion

            [STAThread]
            static void Main()
            {
                  Application.Run(new Form1());
            }

            private void Form1_Load(object sender, System.EventArgs e)
            {
                  DataRow[] draStocks = tblStocks.Select("stock_id > 0", "symbol");
                  DataRow[] draStockPrices;
                  DataRow[] draTemp;
                  DataRow drTemp;
                  foreach (DataRow drStock in draStocks)
                  {
                        draStockPrices = tblStockPrices.Select("stock_id = " + drStock["stock_id"] + " and s_date <= #10-5-04#", "s_date DESC");
                        drStockPrice = (DataRow)draStockPrices.GetValue(0);
                        drStock["s_open"] = convert(drStockPrice["s_open"]);
                        drStock["s_high"] = convert(drStockPrice["s_high"]);
                        drStock["s_low"] = convert(drStockPrice["s_low"]);
                        drStock["s_close"] = convert(drStockPrice["s_close"]);
                  }

            }
            private double convert(object column)
            {
                  if (column == System.DBNull.Value)
                  {
                        return 0;
                  }
                  else
                  {
                        return System.Convert.ToDouble(column);
                  }

            }

            private void button1_Click(object sender, System.EventArgs e)
            {
                        this.virtualDatabase.AcceptChanges();
                        this.da_tblStocks.Update(virtualDatabase, "tblStocks");      
            }


      }
}
0
Comment
Question by:OptionsMM
3 Comments
 
LVL 6

Accepted Solution

by:
ryerras earned 500 total points
ID: 12329608
May be this is the code block that is causing the weird behaviour

private void button1_Click(object sender, System.EventArgs e)
          {
                    this.virtualDatabase.AcceptChanges();
                    this.da_tblStocks.Update(virtualDatabase, "tblStocks");    
          }


put   --this.virtualDatabase.AcceptChanges();-- after the Update like below

private void button1_Click(object sender, System.EventArgs e)
          {
                  this.da_tblStocks.Update(virtualDatabase, "tblStocks");    
                  this.virtualDatabase.AcceptChanges();
          }
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now