I've cleaned up and parsed a delimited file into a dataset.
Now I want to add that dataset to an sql table.
Simple, right?
My code is below.
It runs, but no records from the parsed file are added to the table.
Can anyone look at this code and make it work?
Thanks
Paul
PS Bulk Insert is a nice idea, and if anyone knows how to use it to insert
my dataset, I'm all ears.
========================================
=========
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
namespace Bot_01
{
/// <summary>
/// Summary description for Form1.
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button button1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
private System.Data.SqlClient.SqlConnection sqlConnection1;
private Bot_01.DataSet1 dataSet11;
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.dataSet11 = new Bot_01.DataSet1();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(0, 0);
this.button1.Name = "button1";
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// dataGrid1
//
this.dataGrid1.DataMember = "";
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(88, 8);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(200, 256);
this.dataGrid1.TabIndex = 1;
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table",
"SEC_Index_01", new System.Data.Common.DataColumnMapping[] {
new
System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
new
System.Data.Common.DataColumnMapping("FormType", "FormType"),
new
System.Data.Common.DataColumnMapping("CIK", "CIK"),
new
System.Data.Common.DataColumnMapping("DateFiled", "DateFiled"),
new
System.Data.Common.DataColumnMapping("SECWebAddress", "SECWebAddress")})});
this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT ID, CompanyName, FormType,
CIK, DateFiled, SECWebAddress FROM SEC_Index_01" +
"";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
//
// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = "INSERT INTO
SEC_Index_01(CompanyName, FormType, CIK, DateFiled, SECWebAddress) VA" +
"LUES (@.CompanyName, @.FormType, @.CIK, @.DateFiled, @.SECWebAddress);
SELECT ID, Com" +
"panyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
WHERE (ID = " +
"@.@.IDENTITY)";
this.sqlInsertCommand1.Connection = this.sqlConnection1;
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CompanyName",
System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.FormType",
System.Data.SqlDbType.VarChar, 8000, "FormType"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
8000, "CIK"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.DateFiled",
System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
this.sqlInsertCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
//
// sqlUpdateCommand1
//
this.sqlUpdateCommand1.CommandText = @."UPDATE SEC_Index_01 SET
CompanyName = @.CompanyName, FormType = @.FormType, CIK = @.CIK, DateFiled =
@.DateFiled, SECWebAddress = @.SECWebAddress WHERE (ID = @.Original_ID) AND (CI
K
= @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS NULL) AND (CompanyName =
@.Original_CompanyName OR @.Original_CompanyName IS NULL AND CompanyName IS
NULL) AND (DateFiled = @.Original_DateFiled OR @.Original_DateFiled IS NULL AN
D
DateFiled IS NULL) AND (FormType = @.Original_FormType OR @.Original_FormType
IS NULL AND FormType IS NULL) AND (SECWebAddress = @.Original_SECWebAddress O
R
@.Original_SECWebAddress IS NULL AND SECWebAddress IS NULL); SELECT ID,
CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01 WHERE
(ID = @.ID)";
this.sqlUpdateCommand1.Connection = this.sqlConnection1;
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CompanyName",
System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.FormType",
System.Data.SqlDbType.VarChar, 8000, "FormType"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
8000, "CIK"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.DateFiled",
System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_ID", System.Data.SqlDbType.Int
,
4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CIK",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_FormType",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
System.Data.DataRowVersion.Original, null));
this.sqlUpdateCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.ID", System.Data.SqlDbType.Int, 4,
"ID"));
//
// sqlDeleteCommand1
//
this.sqlDeleteCommand1.CommandText = @."DELETE FROM SEC_Index_01 WHERE (ID
= @.Original_ID) AND (CIK = @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS
NULL) AND (CompanyName = @.Original_CompanyName OR @.Original_CompanyName IS
NULL AND CompanyName IS NULL) AND (DateFiled = @.Original_DateFiled OR
@.Original_DateFiled IS NULL AND DateFiled IS NULL) AND (FormType =
@.Original_FormType OR @.Original_FormType IS NULL AND FormType IS NULL) AND
(SECWebAddress = @.Original_SECWebAddress OR @.Original_SECWebAddress IS NULL
AND SECWebAddress IS NULL)";
this.sqlDeleteCommand1.Connection = this.sqlConnection1;
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_ID", System.Data.SqlDbType.Int
,
4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CIK",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_FormType",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
System.Data.DataRowVersion.Original, null));
this.sqlDeleteCommand1.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
System.Data.DataRowVersion.Original, null));
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "workstation id=AMD;packet
size=4096;integrated security=SSPI;data source=AMD;pers" +
"ist security info=False;initial catalog=SEC_XBRL_10";
//
// dataSet11
//
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new System.Globalization.CultureInfo("en-US");
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(292, 273);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.button1);
this.Name = "Form1";
this.Text = "Form1";
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void button1_Click(object sender, System.EventArgs e)
{
dataGrid1.DataSource=null; //Clear the
datagrid
sqlDataAdapter1.Fill(dataSet11,"SEC_Index_01"); //Fill result dataset
with SEC_Index_01 table data via the sqlDataAdapter1
//WebRequest req =
WebRequest.Create("http://www.sec.gov/Archives/edgar/daily-index/" +
"company." + "20041222" + ".idx"); // Create the Request object
//WebResponse response = req.GetResponse(); //Create the
Response object
//Stream stream = response.GetResponseStream(); //Create a
Stream
//StreamReader sr = new StreamReader(stream); //Open the file
in a stream reader
StreamReader sr = new StreamReader("C:\\test.txt"); //Read From
A File instead of a webrequest
dataSet11.Tables.Add("MyNewTable"); //Add DataTable to
hold the DataSet
dataSet11.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a
single column to the DataTable
dataSet11.Tables["MyNewTable"].Columns.Add("FormType"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("CIK"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a
single column
dataSet11.Tables["MyNewTable"].Columns.Add("SECWebAddress"); //Add a
single column
string AllData1 = sr.ReadToEnd(); //Read the rest
of the data in the file.
string[] rows = AllData1.Split("\n".ToCharArray()); //Split off
each row at the Line Feed
foreach(string r in rows) //Now add each
row to the DataSet
{
string delimStr1 = "\t";
string[] items = r.Split(delimStr1.ToCharArray()); //Split the row
at the delimiter
dataSet11.Tables["MyNewTable"].Rows.Add(items); //Add the item
}
for (int i = 1; i <= 11; i++) //Remove first 11
rows from the DataTable/DataSet
{
dataSet11.Tables["MyNewTable"].Rows.RemoveAt(0);
}
sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Insert the new
rows from the "result" dataset
//sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
update
dataGrid1.SetDataBinding(dataSet11, "MyNewTable"); //Binds DataGrid to
DataSet,displaying datatable.
MessageBox.Show("Data has been added to the SQL Table SEC_XBRL_Index");
}
}
}Here's the reply to your earlier post...
A quick browse through your code, you have this line:
sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
update
which sends the updates (i.e. insert, update, delete operation) to sqlserver
for SEC_Index_01 table.
You have to addrow to dataSet11.Tables["SEC_Index_01"] if you want the
inserts!
The gist of it is you would want to add this to your code before calling
da.update.
e.g.
foreach(DataRow dr in dataSet11.Tables["MyNewTable"].Rows)
{
DataRow r = dataSet11.Tables["SEC_Index_01"].NewRow();
r["CompanyName"] = dr["CompanyName"];
// ... do the same for the rest of your columns
dataSet11.Tables["SEC_Index_01"].Rows.Add(r);
}
-oj
"a" <a@.discussions.microsoft.com> wrote in message
news:35A6293F-CA8B-415E-9DEE-36ECD43B5F3D@.microsoft.com...
> I've cleaned up and parsed a delimited file into a dataset.
> Now I want to add that dataset to an sql table.
> Simple, right?
> My code is below.
> It runs, but no records from the parsed file are added to the table.
> Can anyone look at this code and make it work?
> Thanks
> Paul
> PS Bulk Insert is a nice idea, and if anyone knows how to use it to insert
> my dataset, I'm all ears.
> ========================================
=========
> using System;
> using System.Drawing;
> using System.Collections;
> using System.ComponentModel;
> using System.Windows.Forms;
> using System.Data;
> using System.Data.SqlClient;
> using System.Net;
> using System.IO;
> using System.Text;
> using System.Text.RegularExpressions;
> using System.Collections.Specialized;
> namespace Bot_01
> {
> /// <summary>
> /// Summary description for Form1.
> /// </summary>
> public class Form1 : System.Windows.Forms.Form
> {
> private System.Windows.Forms.Button button1;
> private System.Windows.Forms.DataGrid dataGrid1;
> private System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
> private System.Data.SqlClient.SqlCommand sqlSelectCommand1;
> private System.Data.SqlClient.SqlCommand sqlInsertCommand1;
> private System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
> private System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
> private System.Data.SqlClient.SqlConnection sqlConnection1;
> private Bot_01.DataSet1 dataSet11;
> /// <summary>
> /// Required designer variable.
> /// </summary>
> private System.ComponentModel.Container components = null;
> public Form1()
> {
> //
> // Required for Windows Form Designer support
> //
> InitializeComponent();
> //
> // TODO: Add any constructor code after InitializeComponent call
> //
> }
> /// <summary>
> /// Clean up any resources being used.
> /// </summary>
> protected override void Dispose( bool disposing )
> {
> if( disposing )
> {
> if (components != null)
> {
> components.Dispose();
> }
> }
> base.Dispose( disposing );
> }
> #region Windows Form Designer generated code
> /// <summary>
> /// Required method for Designer support - do not modify
> /// the contents of this method with the code editor.
> /// </summary>
> private void InitializeComponent()
> {
> this.button1 = new System.Windows.Forms.Button();
> this.dataGrid1 = new System.Windows.Forms.DataGrid();
> this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
> this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
> this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
> this.dataSet11 = new Bot_01.DataSet1();
> ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
> ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).BeginInit();
> this.SuspendLayout();
> //
> // button1
> //
> this.button1.Location = new System.Drawing.Point(0, 0);
> this.button1.Name = "button1";
> this.button1.TabIndex = 0;
> this.button1.Text = "button1";
> this.button1.Click += new System.EventHandler(this.button1_Click);
> //
> // dataGrid1
> //
> this.dataGrid1.DataMember = "";
> this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
> this.dataGrid1.Location = new System.Drawing.Point(88, 8);
> this.dataGrid1.Name = "dataGrid1";
> this.dataGrid1.Size = new System.Drawing.Size(200, 256);
> this.dataGrid1.TabIndex = 1;
> //
> // sqlDataAdapter1
> //
> this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
> this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
> this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
> this.sqlDataAdapter1.TableMappings.AddRange(new
> System.Data.Common.DataTableMapping[] {
> new System.Data.Common.DataTableMapping("Table",
> "SEC_Index_01", new System.Data.Common.DataColumnMapping[] {
> new
> System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
> new
> System.Data.Common.DataColumnMapping("FormType", "FormType"),
> new
> System.Data.Common.DataColumnMapping("CIK", "CIK"),
> new
> System.Data.Common.DataColumnMapping("DateFiled", "DateFiled"),
> new
> System.Data.Common.DataColumnMapping("SECWebAddress",
> "SECWebAddress")})});
> this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
> //
> // sqlSelectCommand1
> //
> this.sqlSelectCommand1.CommandText = "SELECT ID, CompanyName, FormType,
> CIK, DateFiled, SECWebAddress FROM SEC_Index_01" +
> "";
> this.sqlSelectCommand1.Connection = this.sqlConnection1;
> //
> // sqlInsertCommand1
> //
> this.sqlInsertCommand1.CommandText = "INSERT INTO
> SEC_Index_01(CompanyName, FormType, CIK, DateFiled, SECWebAddress) VA" +
> "LUES (@.CompanyName, @.FormType, @.CIK, @.DateFiled, @.SECWebAddress);
> SELECT ID, Com" +
> "panyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
> WHERE (ID = " +
> "@.@.IDENTITY)";
> this.sqlInsertCommand1.Connection = this.sqlConnection1;
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CompanyName",
> System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.FormType",
> System.Data.SqlDbType.VarChar, 8000, "FormType"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
> 8000, "CIK"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.DateFiled",
> System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
> this.sqlInsertCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
> //
> // sqlUpdateCommand1
> //
> this.sqlUpdateCommand1.CommandText = @."UPDATE SEC_Index_01 SET
> CompanyName = @.CompanyName, FormType = @.FormType, CIK = @.CIK, DateFiled =
> @.DateFiled, SECWebAddress = @.SECWebAddress WHERE (ID = @.Original_ID) AND
> (CIK
> = @.Original_CIK OR @.Original_CIK IS NULL AND CIK IS NULL) AND (CompanyName
> =
> @.Original_CompanyName OR @.Original_CompanyName IS NULL AND CompanyName IS
> NULL) AND (DateFiled = @.Original_DateFiled OR @.Original_DateFiled IS NULL
> AND
> DateFiled IS NULL) AND (FormType = @.Original_FormType OR
> @.Original_FormType
> IS NULL AND FormType IS NULL) AND (SECWebAddress = @.Original_SECWebAddress
> OR
> @.Original_SECWebAddress IS NULL AND SECWebAddress IS NULL); SELECT ID,
> CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
> WHERE
> (ID = @.ID)";
> this.sqlUpdateCommand1.Connection = this.sqlConnection1;
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CompanyName",
> System.Data.SqlDbType.VarChar, 8000, "CompanyName"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.FormType",
> System.Data.SqlDbType.VarChar, 8000, "FormType"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.CIK", System.Data.SqlDbType.VarChar,
> 8000, "CIK"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.DateFiled",
> System.Data.SqlDbType.VarChar, 8000, "DateFiled"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, "SECWebAddress"));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_ID",
> System.Data.SqlDbType.Int,
> 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
> ((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CIK",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_FormType",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
> System.Data.DataRowVersion.Original, null));
> this.sqlUpdateCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.ID", System.Data.SqlDbType.Int, 4,
> "ID"));
> //
> // sqlDeleteCommand1
> //
> this.sqlDeleteCommand1.CommandText = @."DELETE FROM SEC_Index_01 WHERE (ID
> = @.Original_ID) AND (CIK = @.Original_CIK OR @.Original_CIK IS NULL AND CIK
> IS
> NULL) AND (CompanyName = @.Original_CompanyName OR @.Original_CompanyName IS
> NULL AND CompanyName IS NULL) AND (DateFiled = @.Original_DateFiled OR
> @.Original_DateFiled IS NULL AND DateFiled IS NULL) AND (FormType =
> @.Original_FormType OR @.Original_FormType IS NULL AND FormType IS NULL) AND
> (SECWebAddress = @.Original_SECWebAddress OR @.Original_SECWebAddress IS
> NULL
> AND SECWebAddress IS NULL)";
> this.sqlDeleteCommand1.Connection = this.sqlConnection1;
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_ID",
> System.Data.SqlDbType.Int,
> 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)),
> ((System.Byte)(0)), "ID", System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CIK",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CIK",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_CompanyName",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_DateFiled",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "DateFiled",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_FormType",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "FormType",
> System.Data.DataRowVersion.Original, null));
> this.sqlDeleteCommand1.Parameters.Add(new
> System.Data.SqlClient.SqlParameter("@.Original_SECWebAddress",
> System.Data.SqlDbType.VarChar, 8000, System.Data.ParameterDirection.Input,
> false, ((System.Byte)(0)), ((System.Byte)(0)), "SECWebAddress",
> System.Data.DataRowVersion.Original, null));
> //
> // sqlConnection1
> //
> this.sqlConnection1.ConnectionString = "workstation id=AMD;packet
> size=4096;integrated security=SSPI;data source=AMD;pers" +
> "ist security info=False;initial catalog=SEC_XBRL_10";
> //
> // dataSet11
> //
> this.dataSet11.DataSetName = "DataSet1";
> this.dataSet11.Locale = new System.Globalization.CultureInfo("en-US");
> //
> // Form1
> //
> this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
> this.ClientSize = new System.Drawing.Size(292, 273);
> this.Controls.Add(this.dataGrid1);
> this.Controls.Add(this.button1);
> this.Name = "Form1";
> this.Text = "Form1";
> ((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
> ((System.ComponentModel.ISupportInitialize)(this.dataSet11)).EndInit();
> this.ResumeLayout(false);
> }
> #endregion
> /// <summary>
> /// The main entry point for the application.
> /// </summary>
> [STAThread]
> static void Main()
> {
> Application.Run(new Form1());
> }
> private void button1_Click(object sender, System.EventArgs e)
> {
> dataGrid1.DataSource=null; //Clear the
> datagrid
> sqlDataAdapter1.Fill(dataSet11,"SEC_Index_01"); //Fill result dataset
> with SEC_Index_01 table data via the sqlDataAdapter1
> //WebRequest req =
> WebRequest.Create("http://www.sec.gov/Archives/edgar/daily-index/" +
> "company." + "20041222" + ".idx"); // Create the Request object
> //WebResponse response = req.GetResponse(); //Create the
> Response object
> //Stream stream = response.GetResponseStream(); //Create a
> Stream
> //StreamReader sr = new StreamReader(stream); //Open the file
> in a stream reader
> StreamReader sr = new StreamReader("C:\\test.txt"); //Read From
> A File instead of a webrequest
> dataSet11.Tables.Add("MyNewTable"); //Add DataTable to
> hold the DataSet
> dataSet11.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a
> single column to the DataTable
> dataSet11.Tables["MyNewTable"].Columns.Add("FormType"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("CIK"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a
> single column
> dataSet11.Tables["MyNewTable"].Columns.Add("SECWebAddress"); //Add a
> single column
> string AllData1 = sr.ReadToEnd(); //Read the rest
> of the data in the file.
> string[] rows = AllData1.Split("\n".ToCharArray()); //Split off
> each row at the Line Feed
> foreach(string r in rows) //Now add each
> row to the DataSet
> {
> string delimStr1 = "\t";
> string[] items = r.Split(delimStr1.ToCharArray()); //Split the row
> at the delimiter
> dataSet11.Tables["MyNewTable"].Rows.Add(items); //Add the item
> }
> for (int i = 1; i <= 11; i++) //Remove first 11
> rows from the DataTable/DataSet
> {
> dataSet11.Tables["MyNewTable"].Rows.RemoveAt(0);
> }
> sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Insert the new
> rows from the "result" dataset
> //sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
> update
> dataGrid1.SetDataBinding(dataSet11, "MyNewTable"); //Binds DataGrid to
> DataSet,displaying datatable.
> MessageBox.Show("Data has been added to the SQL Table SEC_XBRL_Index");
> }
> }
> }|||oj:
Thank You...your answer solved my problem. I'd buy you a drink if I could.
I suppose in retrospect that the answer is somewhat obvious, but not being
all that familiar with what code "I" needed to add to make the data adapters
work, it wasn't obvious to me at all.
Thanks,
Paul
========================================
=======
"oj" wrote:
> Here's the reply to your earlier post...
> A quick browse through your code, you have this line:
> sqlDataAdapter1.Update(dataSet11,"SEC_Index_01"); //Perform the
> update
> which sends the updates (i.e. insert, update, delete operation) to sqlserv
er
> for SEC_Index_01 table.
> You have to addrow to dataSet11.Tables["SEC_Index_01"] if you want the
> inserts!
> The gist of it is you would want to add this to your code before calling
> da.update.
> e.g.
> foreach(DataRow dr in dataSet11.Tables["MyNewTable"].Rows)
> {
> DataRow r = dataSet11.Tables["SEC_Index_01"].NewRow();
> r["CompanyName"] = dr["CompanyName"];
> // ... do the same for the rest of your columns
> dataSet11.Tables["SEC_Index_01"].Rows.Add(r);
> }
> --
> -oj|||You're welcome.
A virtual drink is welcome also. ;-)
-oj
"a" <a@.discussions.microsoft.com> wrote in message
news:82ACC91A-6558-4FBC-A06A-486477197C42@.microsoft.com...
> oj:
> Thank You...your answer solved my problem. I'd buy you a drink if I could.
> I suppose in retrospect that the answer is somewhat obvious, but not being
> all that familiar with what code "I" needed to add to make the data
> adapters
> work, it wasn't obvious to me at all.
> Thanks,
> Paul
> ========================================
=======
> "oj" wrote:
>
>
Mugambo wrote: