Showing posts with label below. Show all posts
Showing posts with label below. Show all posts

Friday, March 30, 2012

Inserting a dataset into an sql table

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:
>
>
Posted by predatorhjkn at 2:19 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Inserting a column in an existing table

I have an existing table (see below).

----
[FormCode] [varchar] (4) NULL ,
[FiscalYear] [char] (4) NULL
----

I want to add the column below after the [FormCode] when my SPROC runs.
----
[FiscalMonth] [char] (2) NULL
----

Any ideas would be a big help?
TIF--use this to add the column

alter table MyTable
add FiscalMonth char (2)
go

--and this to drop the column

alter table MyTable
drop column FiscalMonth
go

Cheers|||Thanks for your response, however, I'm actually after adding a column in between existing columns.

So in this case, my new column FISCALMONTH will be added between FORMCODE and FISCALYEAR.

Tnx|||Why is important to have the ordinal position of your column correct?|||The quickest and easiest (at least in most cases) way to "insert" columns into a table is to put the columns wherever they fall and construct a view to order them the way you want them.

In relational algebra, columns have no order. In relational databases, the order of columns should be considered an anomoly, not an attribute.

A view on the other hand is a template for a result set, and columns do have an order in a result set.

-PatP|||I don't understand eather... Why do you need them in a specific order?|||Did you ever get an answer to this? I know that you can create a view to order your columns but it would be nice to do this in the table. No it doesn't matter from a DB perspective but it is cleaner if you are dealing with many columns.|||Why don't you go into design view of a table in Enterprise Manager, make your changes, and save the script.

I would also summarize that ALTER TABLE anything in SQL server produces ineffeciencies at the page level...

Read Nigel's great article on the subject

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html
Posted by predatorhjkn at 2:17 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Wednesday, March 28, 2012

INSERT...SELECT and OUTPUT question

Why would this syntax be valid and the second one is not (below)?
begin tran
USE AdventureWorks
GO
DECLARE @.MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);
DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @.MyTableVar
--OUTPUT DELETED.ProductID, DELETED.ProductPhotoID, GETDATE() AS DeletedDate
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductID BETWEEN 800 and 810;
--Display the results of the table variable.
SELECT ProductID, ProductName, PhotoID, ProductModelID
FROM @.MyTableVar;
GO
rollback
This is not valid. Why not? Am I missing something?
Is it that only with UPDATE/DELETE other fields from JOIN can be in output ?
USE AdventureWorks ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL,
FirstName nvarchar(20) NOT NULL,
CurrentSales money NOT NULL,
ProjectedSales money NOT NULL
);
GO
INSERT INTO dbo.EmployeeSales
OUTPUT INSERTED.EmployeeID,
INSERTED.LastName,
INSERTED.FirstName,
INSERTED.CurrentSales,
e.EmployeeID
SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD, sp.SalesYTD * 1.1
0
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
GO
SELECT EmployeeID, LastName, FirstName, CurrentSales, ProjectedSales
FROM dbo.EmployeeSales;
GOFarmer (someone@.somewhere.com) writes:
> Why would this syntax be valid and the second one is not (below)?
> DELETE Production.ProductProductPhoto
> OUTPUT DELETED.ProductID,
> p.Name,
> p.ProductModelID,
> DELETED.ProductPhotoID
> INTO @.MyTableVar
> FROM Production.ProductProductPhoto AS ph
> JOIN Production.Product as p ON ph.ProductID = p.ProductID
> WHERE p.ProductID BETWEEN 800 and 810;
>...
> INSERT INTO dbo.EmployeeSales
> OUTPUT INSERTED.EmployeeID,
> INSERTED.LastName,
> INSERTED.FirstName,
> INSERTED.CurrentSales,
> e.EmployeeID
> SELECT e.EmployeeID, c.LastName, c.FirstName, sp.SalesYTD,
> sp.SalesYTD * 1.10
> FROM HumanResources.Employee AS e
>...
The syntax diagram in Books Online gives us:
<column_name> ::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
In the comments section we find:
from_table_name
Is a column prefix that specifies a table included in the FROM clause
of a DELETE or UPDATE statement that is used to specify the rows to
update or delete.
Thus, Books Online clearly says that you cannot use e.EmployeeID in the
OUTPUT clause of an INSERT statement.
Then remains the question why it is so. We look at the syntax diagram
for INSERT:
[ WITH <common_table_expression> [ ,...n ] ]
INSERT
[ TOP ( expression ) [ PERCENT ] ]
[ INTO]
{ <object> | rowset_function_limited
[ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
}
{
[ ( column_list ) ]
[ <OUTPUT Clause> ]
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
| derived_table
| execute_statement
}
}
| DEFAULT VALUES
[; ]
Note here that the SELECT statement appears in this grammar as a
derived table. A derived table has the property, that it does not
see things outside of if, and the outside cannot look in.
In looser terms, we can simply say that the OUTPUT clause is part of
the INSERT clause in a way that the SELECT statement is not, and thus
does not have visibility of what is in the SELECT statement.
For DELETE or UPDATE it's a different matter as the FROM clause are
part of the DELETE and UPDATE statments themselves.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you,
very good explanation on your part. I see it now. I should have read more
carefully.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns976FD66F91C3BYazorman@.127.0.0.1...
> Farmer (someone@.somewhere.com) writes:
> The syntax diagram in Books Online gives us:
> <column_name> ::=
> { DELETED | INSERTED | from_table_name } . { * | column_name }
> In the comments section we find:
> from_table_name
> Is a column prefix that specifies a table included in the FROM clause
> of a DELETE or UPDATE statement that is used to specify the rows to
> update or delete.
> Thus, Books Online clearly says that you cannot use e.EmployeeID in the
> OUTPUT clause of an INSERT statement.
> Then remains the question why it is so. We look at the syntax diagram
> for INSERT:
> [ WITH <common_table_expression> [ ,...n ] ]
> INSERT
> [ TOP ( expression ) [ PERCENT ] ]
> [ INTO]
> { <object> | rowset_function_limited
> [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
> }
> {
> [ ( column_list ) ]
> [ <OUTPUT Clause> ]
> { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
> | derived_table
> | execute_statement
> }
> }
> | DEFAULT VALUES
> [; ]
> Note here that the SELECT statement appears in this grammar as a
> derived table. A derived table has the property, that it does not
> see things outside of if, and the outside cannot look in.
> In looser terms, we can simply say that the OUTPUT clause is part of
> the INSERT clause in a way that the SELECT statement is not, and thus
> does not have visibility of what is in the SELECT statement.
> For DELETE or UPDATE it's a different matter as the FROM clause are
> part of the DELETE and UPDATE statments themselves.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Farmer, try this one
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID nvarchar(11) NOT NULL,
LastName nvarchar(20) NOT NULL
);
GO
CREATE TABLE #Temp ( EmployeeID int not null,
LastName nvarchar(20) NOT NULL)-
INSERT INTO dbo.EmployeeSales(EmployeeID,LastName)
OUTPUT INSERTED.EmployeeID, INSERTED.LastName INTO #Temp
SELECT e.EmployeeID, c.LastName
FROM HumanResources.Employee AS e
INNER JOIN Sales.SalesPerson AS sp
ON e.EmployeeID = sp.SalesPersonID
INNER JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID LIKE '2%'
ORDER BY c.LastName, c.FirstName;
select * from #Temp
go
"Farmer" <someone@.somewhere.com> wrote in message
news:%23AWUfJbNGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Thank you,
> very good explanation on your part. I see it now. I should have read more
> carefully.
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns976FD66F91C3BYazorman@.127.0.0.1...
>|||Thanks
You have missed my point though. This does not work and this can be a field
from a JOIN table from FROM statement.
OUTPUT e.EmployeeID, INSERTED.LastName INTO #Temp
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:O$1jqNeNGHA.3936@.TK2MSFTNGP12.phx.gbl
..
> Farmer, try this one
>
> IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
>
> DROP TABLE dbo.EmployeeSales;
>
> GO
>
> CREATE TABLE dbo.EmployeeSales
>
> ( EmployeeID nvarchar(11) NOT NULL,
>
> LastName nvarchar(20) NOT NULL
>
> );
>
> GO
>
> CREATE TABLE #Temp ( EmployeeID int not null,
>
> LastName nvarchar(20) NOT NULL)-
>
> INSERT INTO dbo.EmployeeSales(EmployeeID,LastName)
>
> OUTPUT INSERTED.EmployeeID, INSERTED.LastName INTO #Temp
>
> SELECT e.EmployeeID, c.LastName
>
> FROM HumanResources.Employee AS e
>
> INNER JOIN Sales.SalesPerson AS sp
>
> ON e.EmployeeID = sp.SalesPersonID
>
> INNER JOIN Person.Contact AS c
>
> ON e.ContactID = c.ContactID
>
> WHERE e.EmployeeID LIKE '2%'
>
> ORDER BY c.LastName, c.FirstName;
>
>
>
> select * from #Temp
>
> go
>
> "Farmer" <someone@.somewhere.com> wrote in message
> news:%23AWUfJbNGHA.3164@.TK2MSFTNGP11.phx.gbl...
>
>
Posted by predatorhjkn at 2:27 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

Monday, March 26, 2012

Insert Values Error

Hey Guys:

I am trying to create a form, and then insert the values entered by a user into a sql database, I have enclosed the page code below. Everything works except the data is not being inserted into the database, and i keep getting the default message in my error message section. I took this right from the quick start tutorial and started working with it, and keep getting an error.

I believe the error is located in the INSERT statement


<%@. Page Language="vb" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<html
<script language="VB" runat="server"
 Dim MyConnection As SqlConnection
 Sub Page_Load(Sender As Object, E As EventArgs)
 MyConnection = New SqlConnection("server=localhost;database=planetauction;uid=planetauction;pwd=bean13")
 If Not (IsPostBack)
 BindGrid()
 Page.DataBind()
 End If
 End Sub
 Sub AddAuthor_Click(Sender As Object, E As EventArgs)
 Page.Validate()
 If Not Page.IsValid
 Return
 End If
 Dim DS As DataSet
 Dim MyCommand As SqlCommand
 If txtLastName.Value = ""
 Message.InnerHtml = "ERROR: Null values not allowed for Author ID, " & _
 "Name or Phone"
 Message.Style("color") = "red"
 BindGrid()
 Return
 End If
 Dim InsertCmd As String = "insert into users (txtLastName) values (@.lastname)"
 MyCommand = New SqlCommand(InsertCmd, MyConnection)
 MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
 MyCommand.Parameters("@.lastname").Value = txtLastName.Value
 MyCommand.Connection.Open()
 Try
 MyCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the " & _
 "same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
 "the fields are correctly filled out"
 End If
 Message.Style("color") = "red"
 End Try
 MyCommand.Connection.Close()
 BindGrid()
 End Sub
 Sub BindGrid()
 Dim MyCommand As SqlDataAdapter = new SqlDataAdapter( _
 "select * from users", MyConnection)
 Dim DS As DataSet = new DataSet()
 MyCommand.Fill(DS, "Users")
 MyDataGrid.DataSource=DS.Tables("Users").DefaultView
 MyDataGrid.DataBind()
 End Sub
</script
<body style="font: 10pt verdana"
 <form runat="server" ID="Form1"
 <h3><font face="Verdana">Inserting a Row of Data</font></h3
 <table width="95%">
 <tr>
 <td valign="top"
 <ASP:DataGrid id="MyDataGrid" runat="server"
 Width="700"
 BackColor="#ccccff"
 BorderColor="black"
 ShowFooter="false"
 CellPadding=3
 CellSpacing="0"
 Font-Name="Verdana"
 Font-Size="8pt"
 HeaderStyle-BackColor="#aaaadd"
 EnableViewState="false"
 /
 </td>
 <td valign="top"
 <table style="font: 8pt verdana">
 <tr>
 <td colspan="2" bgcolor="#aaaadd" style="font:10pt verdana">Add a New Author:</td>
 </tr>
 <tr>
 <td nowrap>Last Name: </td>
 <td>
 <input type="text" id="txtLastName" runat="server" NAME="txtLastName"><br
 </td>
 </tr>
 <tr>
 <td></td>
 <td style="padding-top:15">
 <input type="submit" OnServerClick="AddAuthor_Click" value="Add Author" runat="server" ID="Submit1" NAME="Submit1">
 </td>
 </tr>
 <tr>
 <td colspan="2" style="padding-top:15" align="center">
 <span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/>
 </td>
 </tr>
 </table
 </td>
 </tr>
 </table
 </form
</body>
</html>
Put a more specific error message in while debugging:

Try
 MyCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & InsertCmd.ToString()
 Catch Exp As SQLException
 If Exp.Number = 2627
 Message.InnerHtml = "ERROR: A record already exists with the " & _
 "same primary key"
 Else
 Message.InnerHtml = "ERROR: Could not add record, please ensure " & _
 "the fields are correctly filled out. Exception: " & Exp.ToString()
 End If
 Message.Style("color") = "red"
 End Try
|||Mr. Reily:

Thanks for your advice. I added the detailed error message and here is what I got:

ERROR: Could not add record, please ensure the fields are correctly filled out. Exception: System.Data.SqlClient.SqlException: Invalid column name 'txtLastName'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.reference3_aspx.AddAuthor_Click(Object Sender, EventArgs E) in http://localhost/planetauction/reference3.aspx:line 47

I really think the error is in the insert statement. I need to put either double or single quotes around something in the insert statement, right now the database believes the value is referring to a column name, rather than inserting that data into the column specified.

Let me know what u think.

Thanks|||Thanks guys, got it all figured out.

It was the insert statment that was screwed up. For anyone else with the same problem, I simply mixed up the values, and the column names in the sql insert statements.

For Reference:

 Dim InsertCmd As String = "insert into users (COLUMNNAME1, COLUMNNAME2) values (@.FIRST SQL PARAMETER LISTED BELOW, SECOND SQL PARAMETER LISTED BELOW, etc)"
 MyCommand = New SqlCommand(InsertCmd, MyConnection)
 MyCommand.Parameters.Add(New SqlParameter("@.lastname", SqlDbType.NVarChar, 50))
 MyCommand.Parameters("@.lastname").Value = txtLastName.Value
 MyCommand.Connection.Open()
Posted by predatorhjkn at 5:08 AM 0 comments
Labels: , , , , , , , , , , , , , , , , ,

Friday, March 23, 2012

Insert using multiple field terminators

Hi all,
In Oracle, SQL Loader allows you to use the statement below:
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
Is there a way to do this with BULK INSERT or some other import function?
The data files that I am attempting to import has fields terminated by commas
but when some of those text fields may contain a comma, they are also
enclosed by pipes. The above statement in Oracle deals with this issue
effectively... hoping I can find something for MS SQL Server. Thanks!
PatrickPatrick
CREATE TABLE TmpStList
(
stFName varchar (10) NOT NULL,
stLName varchar (10) NOT NULL,
stEmail varchar (30) NOT NULL
)
go
The data file (hawk.dat):
"Kelly","Reynold","kelly@.reynold.com"
"John","Smith","bill@.smith.com"
"Sara","Parker","sara@.parker.com"
The format file (hawk.bcp):
8.0
4
1 SQLCHAR 0 1 "\"" 0 first_quote ""
2 SQLCHAR 0 10 "\",\"" 1 stFName ""
3 SQLCHAR 0 10 "\",\"" 2 stLName ""
4 SQLCHAR 0 30 "\"\r\n" 3 stEmail ""
bulk insert TmpStList from 'C:\Staging\hawk.dat'
with (formatfile = 'C:\Staging\hawk.bcp')
select * from TmpStList
stFName stLName stEmail
-- -- --
Kelly Reynold kelly@.reynold.com
John Smith bill@.smith.com
Sara Parker sara@.parker.com
drop table TmpStList
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:CA167741-DE9E-4752-AEE1-97D3A67C3837@.microsoft.com...
> Hi all,
> In Oracle, SQL Loader allows you to use the statement below:
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '|'
> Is there a way to do this with BULK INSERT or some other import function?
> The data files that I am attempting to import has fields terminated by
> commas
> but when some of those text fields may contain a comma, they are also
> enclosed by pipes. The above statement in Oracle deals with this issue
> effectively... hoping I can find something for MS SQL Server. Thanks!
> Patrick
>
Posted by predatorhjkn at 4:42 AM 0 comments
Labels: , , , , , , , , , , , , , , , , ,

Insert Trigger Using Variables Help

Can somebody please help me with compiling my insert trigger below. I am fairly new to SQL server 2000 and I am having troubles with using variables in insert triggers. The trigger that I am creating will basically update another table based on a certain criteria that is not specified below. I am hoping to first get my trigger to work then apply the criteria on when to fire afterwards. I just need help with being able to store values in my declared variables for insert into another table. Thanks in advance for everyones help.

Use database_testing

IF EXISTS (SELECT name FROM sysobjects
WHERE type = 'TR' AND name = 'Trigger_Name')
DROP TRIGGER Trigger_Name
GO

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS
Declare @.resource_id int = inserted.resource
@.type = varchar(100) = inserted.type
@.date_logged (datetime) = inserted.creation_date
@.created varchar(100) = inserted.username

Insert into table_A (resource_id, resource_type, date_created, created_by)
values (resource_id,type, date_logged, created)You didn't specified that exactly what you want to fulfill. I think you want to track users for insert row or update row.

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS

SET NOCOUNT ON

Begin
Insert into table_A (resource_id, resource_type, date_created, created_by)
SELECT inserted.resource
inserted.type,
inserted.creation_date,
inserted.username
FROM inserted
End

SET NOCOUNT OFF|||rajeshpatel gave you probably the nicest sollution. If you want to hold on to your own script for some reason, I filtered some errors out of it. This is what it should look like:

CREATE TRIGGER Trigger_Name
ON [trigger_table] FOR INSERT
AS
BEGIN
Declare @.resource_id int
, @.type varchar(100)
, @.date_logged datetime
, @.created varchar(100)

select @.resource_id = inserted.resource
, @.type = inserted.type
, @.date_logged = inserted.creation_date
, @.created = inserted.username

Insert into table_A
(resource_id, resource_type, date_created, created_by)
values
(@.resource_id,@.type, @.date_logged, @.created)
...
END

Gr,
Yveau
Posted by predatorhjkn at 4:32 AM 0 comments
Labels: , , , , , , , , , , , , ,

Monday, March 12, 2012

insert statement

When I run below statement, I got 3 records insertion.
I only want 1 record added when there was any update on any column on the
source data. I don't want update statement because, I would like to see all
the change from time to time.
Please help,
Culam.
INSERT INTO CUSTOMER_PROFILE_HIST
([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
FROM
CUSTOMER_PROFILE src
LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
WHERE
ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)try using inner join.. Just a guess
--
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||You will have problems after there are 2 records for the customer in
the history file because one will always be different than the current.
You need to only compare to the latest historical record.|||Thanks Jeff.
Do you know the way to insert 1 record when multiple fields are changed?
My method will insert new records for each changed field.
Lam
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||try this.
INSERT INTO CUSTOMER_PROFILE_HIST
([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
FROM
CUSTOMER_PROFILE src
WHERE
not exists( select 1 from CUSTOMER_PROFILE_HIST dst where
src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
ISNULL(src.[RATE], 0) = ISNULL(dst.[RATE],0)
AND ISNULL(src.[AGE1], 0) = ISNULL(dst.[AGE1], 0)
AND ISNULL(src.[AGE2], 0) = ISNULL(dst.[AGE2], 0)
)|||Thanks, it works.
"culam" wrote:

> When I run below statement, I got 3 records insertion.
> I only want 1 record added when there was any update on any column on the
> source data. I don't want update statement because, I would like to see a
ll
> the change from time to time.
> Please help,
> Culam.
> INSERT INTO CUSTOMER_PROFILE_HIST
> ([CUSTOMER_ID, [RATE], [AGE1], [AGE2])
> SELECT src.[CUSTOMER_ID, src.[RATE], src.[AGE1], src.[AGE2]
> FROM
> CUSTOMER_PROFILE src
> LEFT OUTER JOIN CUSTOMER_PROFILE_HIST dst
> ON src.[CUSTOMER_ID] = dst.[CUSTOMER_ID]
> WHERE
> ISNULL(src.[RATE], 0) <> ISNULL(dst.[RATE],0)
> OR ISNULL(src.[AGE1], 0) <> ISNULL(dst.[AGE1], 0)
> OR ISNULL(src.[AGE2], 0) <> ISNULL(dst.[AGE2], 0)|||On Tue, 9 May 2006 13:32:03 -0700, culam wrote:

>Thanks Jeff.
>Do you know the way to insert 1 record when multiple fields are changed?
>My method will insert new records for each changed field.
Hi Lam,
No, it won't.
It will insert new rows for each existing row in the history table. If
you have three rows in CUSTOMER_PROFILE_HIST, you'll get three
additional rows (or rather: maximum three rows - if any of the existing
history rows happens to match the current rw on all columns, you'll only
get two new rows).
Check JeffB's reply - he hit the nail right on the head.
If yoou need more assitance, then please check out www.aspfaq.com/5006
to find out what additional information yoou need to give to make it
possible for us to help you.
Hugo Kornelis, SQL Server MVP|||All Credit goes to Jeff. I just ex[anded his point of view.
--
"culam" wrote:
> Thanks, it works.
> "culam" wrote:
>|||Unless 3 changed fields actually causes 3 separate inserts. I have seen
this happen (on the application side) where a change to department, salary,
and jobcode actually triggers 3 separate transactions. This is not to
suggest that your post is inaccurate, only that the OP could possibly be
referring to something else here...
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:e23262pmojsjfnt5aph2jdausbrmmv9lcc@.
4ax.com...
> On Tue, 9 May 2006 13:32:03 -0700, culam wrote:
>
> Hi Lam,
> No, it won't.
> It will insert new rows for each existing row in the history table. If
> you have three rows in CUSTOMER_PROFILE_HIST, you'll get three
> additional rows (or rather: maximum three rows - if any of the existing
> history rows happens to match the current rw on all columns, you'll only
> get two new rows).
> Check JeffB's reply - he hit the nail right on the head.
> If yoou need more assitance, then please check out www.aspfaq.com/5006
> to find out what additional information yoou need to give to make it
> possible for us to help you.
> --
> Hugo Kornelis, SQL Server MVP
Posted by predatorhjkn at 4:12 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

Wednesday, March 7, 2012

insert query question

Hi I have two temporary tables in a query and need to combine them as
described below. Thanks.
Table 1-results from query 1 based on a start and end date
*********************************************
*day * location * type * cost * Name* color*weight*
*********************************************
*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
***********************************************
* 2/3/07* calif * food * .50 * candy * blue *.1lb *
***********************************************
Table 2 list of all names
***************************
* name * location * Cost * Type *
***************************
*cat * AZ * $2.00 * animal *
***************************
*hamer *Texas *$1.0 *tool *
***************************
*candy *calif *.50 * food *
****************************
table 2 lists all of the named items. I would like to insert records from
table2 into table 1 in a fashion that will leave table 2 with all of the
named items for each date, as shown below. It does not write over what is in
table one but inserts records so all named items show up for every day.
Table1 after updated
*********************************************
*day * location * type * cost * Name* color*weight*
*********************************************
*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
***********************************************
*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
table2
**********************************************
*2/1/07* calif * food * .50 * candy *NULL *NULL *from
table2
**********************************************
* 2/3/07* calif * food * .50 * candy * blue *.1lb *
***********************************************
* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
***********************************************
*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
***********************************************
--
Paul G
Software engineer.Without knowing really basic stuff like keys it is pure guesswork
trying to write a query.
Something like this might get you started. Or not. The general idea
is to use a CROSS JOIN of the dates against the names to get the set
of rows you want in the results, then join that result to the detail
to fill in the rest.
SELECT A.day, B.location, B.type, B.cost, B.Name,
C.color, C.weight
FROM (SELECT DISTINCT day FROM Tbl1) as A
CROSS JOIN
Tbl2 as B
LEFT OUTER
JOIN Tbl1 as C
ON A.day = C.day
AND A.name = C.name
Roy Harvey
Beacon Falls, CT
On Mon, 20 Aug 2007 13:36:00 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I have two temporary tables in a query and need to combine them as
>described below. Thanks.
>Table 1-results from query 1 based on a start and end date
>*********************************************
>*day * location * type * cost * Name* color*weight*
>*********************************************
>*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
>***********************************************
>* 2/3/07* calif * food * .50 * candy * blue *.1lb *
>***********************************************
>Table 2 list of all names
>***************************
>* name * location * Cost * Type *
>***************************
>*cat * AZ * $2.00 * animal *
>***************************
>*hamer *Texas *$1.0 *tool *
>***************************
>*candy *calif *.50 * food *
>****************************
>table 2 lists all of the named items. I would like to insert records from
>table2 into table 1 in a fashion that will leave table 2 with all of the
>named items for each date, as shown below. It does not write over what is in
>table one but inserts records so all named items show up for every day.
>Table1 after updated
>*********************************************
>*day * location * type * cost * Name* color*weight*
>*********************************************
>*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
>***********************************************
>*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
>table2
>**********************************************
>*2/1/07* calif * food * .50 * candy *NULL *NULL *from
>table2
>**********************************************
>* 2/3/07* calif * food * .50 * candy * blue *.1lb *
>***********************************************
>* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
>***********************************************
>*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
>***********************************************|||thanks for the response. I left off the key column. Table 1 it is
**************************************************
day_id prim key * day (datetime)* type,name cost are all (varchar(20))
and table2 is
***************************************************
name_id prim key * location cost type are all (varchar(20)).
--
I will try what you have provided.
Paul G
Software engineer.
"Roy Harvey" wrote:
> Without knowing really basic stuff like keys it is pure guesswork
> trying to write a query.
> Something like this might get you started. Or not. The general idea
> is to use a CROSS JOIN of the dates against the names to get the set
> of rows you want in the results, then join that result to the detail
> to fill in the rest.
> SELECT A.day, B.location, B.type, B.cost, B.Name,
> C.color, C.weight
> FROM (SELECT DISTINCT day FROM Tbl1) as A
> CROSS JOIN
> Tbl2 as B
> LEFT OUTER
> JOIN Tbl1 as C
> ON A.day = C.day
> AND A.name = C.name
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 20 Aug 2007 13:36:00 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I have two temporary tables in a query and need to combine them as
> >described below. Thanks.
> >
> >Table 1-results from query 1 based on a start and end date
> >
> >*********************************************
> >*day * location * type * cost * Name* color*weight*
> >*********************************************
> >*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
> >***********************************************
> >* 2/3/07* calif * food * .50 * candy * blue *.1lb *
> >***********************************************
> >Table 2 list of all names
> >***************************
> >* name * location * Cost * Type *
> >***************************
> >*cat * AZ * $2.00 * animal *
> >***************************
> >*hamer *Texas *$1.0 *tool *
> >***************************
> >*candy *calif *.50 * food *
> >****************************
> >table 2 lists all of the named items. I would like to insert records from
> >table2 into table 1 in a fashion that will leave table 2 with all of the
> >named items for each date, as shown below. It does not write over what is in
> >table one but inserts records so all named items show up for every day.
> >Table1 after updated
> >*********************************************
> >*day * location * type * cost * Name* color*weight*
> >*********************************************
> >*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
> >***********************************************
> >*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
> >table2
> >**********************************************
> >*2/1/07* calif * food * .50 * candy *NULL *NULL *from
> >table2
> >**********************************************
> >* 2/3/07* calif * food * .50 * candy * blue *.1lb *
> >***********************************************
> >* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
> >***********************************************
> >*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
> >
> >***********************************************
>
Posted by predatorhjkn at 8:55 AM 0 comments
Labels: , , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)