OptionsMM
asked on
Urgent data adapter not saving data
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.SqlD ataAdapter da_tblStocks;
public System.Data.SqlClient.SqlD ataAdapter 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.DataG rid dataGrid1;
private System.Windows.Forms.Butto n button1;
private System.ComponentModel.Cont ainer components = null;
public Form1()
{
InitializeComponent();
virtualDatabase = new DataSet();
cnString = "workstation id=HISTDB;packet size=4096;integrated security=SSPI;data source=\"HIST" +
"DB\\STOCKDATABASE\";persi st security info=True;initial catalog=DB1";
cnDatabase = new SqlConnection(cnString);
cnDatabase.Open();
da_tblStocks = new SqlDataAdapter("SELECT * from tblStocks", cnDatabase);
da_tblStocks.Fill(virtualD atabase, "tblStocks");
cb_tblStocks = new SqlCommandBuilder(da_tblSt ocks);
tblStocks = virtualDatabase.Tables["tb lStocks"];
da_tblStockPrices = new SqlDataAdapter("SELECT * from tblStockPrice", cnDatabase);
da_tblStockPrices.Fill(vir tualDataba se, "tblStockPrices");
cb_tblStockPrices = new SqlCommandBuilder(da_tblSt ockPrices) ;
tblStockPrices = virtualDatabase.Tables["tb lStockPric es"];
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.DataG rid();
this.button1 = new System.Windows.Forms.Butto n();
((System.ComponentModel.IS upportInit ialize)(th is.dataGri d1)).Begin Init();
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeC olor = System.Drawing.SystemColor s.ControlT ext;
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.b utton1_Cli ck);
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(720, 461);
this.Controls.Add(this.but ton1);
this.Controls.Add(this.dat aGrid1);
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.F orm1_Load) ;
((System.ComponentModel.IS upportInit ialize)(th is.dataGri d1)).EndIn it();
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("sto ck_id = " + drStock["stock_id"] + " and s_date <= #10-5-04#", "s_date DESC");
drStockPrice = (DataRow)draStockPrices.Ge tValue(0);
drStock["s_open"] = convert(drStockPrice["s_op en"]);
drStock["s_high"] = convert(drStockPrice["s_hi gh"]);
drStock["s_low"] = convert(drStockPrice["s_lo w"]);
drStock["s_close"] = convert(drStockPrice["s_cl ose"]);
}
}
private double convert(object column)
{
if (column == System.DBNull.Value)
{
return 0;
}
else
{
return System.Convert.ToDouble(co lumn);
}
}
private void button1_Click(object sender, System.EventArgs e)
{
this.virtualDatabase.Accep tChanges() ;
this.da_tblStocks.Update(v irtualData base, "tblStocks");
}
}
}
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.SqlD
public System.Data.SqlClient.SqlD
public SqlCommandBuilder cb_tblStocks;
public SqlCommandBuilder cb_tblStockPrices;
public DataTable tblStocks;
public DataTable tblStockPrices;
public DataRow drStock;
public DataRow drStockPrice;
private System.Windows.Forms.DataG
private System.Windows.Forms.Butto
private System.ComponentModel.Cont
public Form1()
{
InitializeComponent();
virtualDatabase = new DataSet();
cnString = "workstation id=HISTDB;packet size=4096;integrated security=SSPI;data source=\"HIST" +
"DB\\STOCKDATABASE\";persi
cnDatabase = new SqlConnection(cnString);
cnDatabase.Open();
da_tblStocks = new SqlDataAdapter("SELECT * from tblStocks", cnDatabase);
da_tblStocks.Fill(virtualD
cb_tblStocks = new SqlCommandBuilder(da_tblSt
tblStocks = virtualDatabase.Tables["tb
da_tblStockPrices = new SqlDataAdapter("SELECT * from tblStockPrice", cnDatabase);
da_tblStockPrices.Fill(vir
cb_tblStockPrices = new SqlCommandBuilder(da_tblSt
tblStockPrices = virtualDatabase.Tables["tb
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.DataG
this.button1 = new System.Windows.Forms.Butto
((System.ComponentModel.IS
this.SuspendLayout();
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeC
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.b
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(720, 461);
this.Controls.Add(this.but
this.Controls.Add(this.dat
this.Name = "Form1";
this.Text = "Form1";
this.Load += new System.EventHandler(this.F
((System.ComponentModel.IS
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
DataRow[] draStockPrices;
DataRow[] draTemp;
DataRow drTemp;
foreach (DataRow drStock in draStocks)
{
draStockPrices = tblStockPrices.Select("sto
drStockPrice = (DataRow)draStockPrices.Ge
drStock["s_open"] = convert(drStockPrice["s_op
drStock["s_high"] = convert(drStockPrice["s_hi
drStock["s_low"] = convert(drStockPrice["s_lo
drStock["s_close"] = convert(drStockPrice["s_cl
}
}
private double convert(object column)
{
if (column == System.DBNull.Value)
{
return 0;
}
else
{
return System.Convert.ToDouble(co
}
}
private void button1_Click(object sender, System.EventArgs e)
{
this.virtualDatabase.Accep
this.da_tblStocks.Update(v
}
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.