inserting a new record

I want to insert a new record into my db in asp.net. Im programming in vb and using an sql server database. i know this is a begginers question but that's exactly what i am. Thanks in advanceHave you had a look at the ASP.net starter kits?

Inserting a new column in a merge replication

Hi,
I am beginner in replication
I use a merge replication with a pull subscription
I Use sql2000 Server 2000 for Publisher, Distributor and all Subscriber use
MSDE
I insert a new column in the temps tables
This table contain 161 thousands rows
I use this script to insert the new column
USE JMI
GO
sp_repladdcolumn @.source_object='temps',@.column='HeuresAutres',@.typ etext='
float NOT NULL DEFAULT 0 WITH VALUES ' ,@.publication_to_add='JMI_articles'
GO
After inserting the row i received this message
Warning: only Subscribers running SQL Server 2000 can synchronize with
publication 'JMI_articles' because schema replication is performed.
Cannot add rows to sysdepends for the current stored procedure because it
depends on the missing object 'sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal'.
The stored procedure will still be created.
DO I have to worry about this message
I look in the tables and the new column was created with no errors
Also after I start a synchronisation with a subscriber to test if everything
was ok
Everything works ok but it took 45 minutes to insert the new column
"HeuresAutres" on the subcriber
Is it normal that it took so long
Is there a way I can optimise this because i find it to long
Thanks in advance
Check to see if sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal exists on the
publisher and subscriber. 45 minutes could be ok for a large table.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GC" <GC@.discussions.microsoft.com> wrote in message
news:18BC357F-18CE-4674-AE20-6B3A1CD169B0@.microsoft.com...
> Hi,
> I am beginner in replication
> I use a merge replication with a pull subscription
> I Use sql2000 Server 2000 for Publisher, Distributor and all Subscriber
> use
> MSDE
> I insert a new column in the temps tables
> This table contain 161 thousands rows
> I use this script to insert the new column
> USE JMI
> GO
> sp_repladdcolumn @.source_object='temps',@.column='HeuresAutres',@.typ etext='
> float NOT NULL DEFAULT 0 WITH VALUES '
> ,@.publication_to_add='JMI_articles'
> GO
> After inserting the row i received this message
> Warning: only Subscribers running SQL Server 2000 can synchronize with
> publication 'JMI_articles' because schema replication is performed.
> Cannot add rows to sysdepends for the current stored procedure because it
> depends on the missing object
> 'sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal'.
> The stored procedure will still be created.
>
> DO I have to worry about this message
> I look in the tables and the new column was created with no errors
> Also after I start a synchronisation with a subscriber to test if
> everything
> was ok
> Everything works ok but it took 45 minutes to insert the new column
> "HeuresAutres" on the subcriber
> Is it normal that it took so long
> Is there a way I can optimise this because i find it to long
>
> Thanks in advance
>
>
>
>
|||Yes they are on the subscriber and on the Publisher.
On the subscriber ther are a lot of store proc with a name looking like a
GUID
but all of them dont finish with _pal except this one
sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal
On the publisher they all finish by _pal
Is that normal ?
"Hilary Cotter" wrote:

> Check to see if sp_sel_65982A1ABA8F4206CD49BE8C7F40490C_pal exists on the
> publisher and subscriber. 45 minutes could be ok for a large table.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "GC" <GC@.discussions.microsoft.com> wrote in message
> news:18BC357F-18CE-4674-AE20-6B3A1CD169B0@.microsoft.com...
>
>

Inserting a document (PowerPoint, Word, PDF) into a report (SSRS 2

Hi,
Which is the best way to insert a document (Word, PowerPoint, PDF) into a
report, which is also rendered with the masn report?
Writing a custom report item? Rendering as image?
But how to do a page break?
Is there a commercial solution available?
I'm using SSRS 2005.
Thanks
EricHi Eric,
Welcome to the MSDN newsgroup.
As for the displaying rich binary document in SQL server 2005reporting
service report, do you mean HTML format report? If so, I think you can
consider using the Html <iframe> element to embed the binary document
(powerpoint, word, pdf....)... Of course, you may need to create a custom
report item which can render out the html <iframe> element. For normal web
page, we can embed an <iframe> like below:
=========================<iframe id="frmPPT" runat="server" src="http://pics.10026.com/?src=small.ppt" width="100%"
height="500" >
======================
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks Steven,
But the report should also be rendered to PDF, not only HTML.
After the SQL2005 documentation as custom report item only the type 'image'
is supported, this means that i can't render html. Or do you mean a new item
based on the textbox (overloaded)?
Is there no other solution?
Is MS not working on a Word render extension?
Thanks
Eric
"Steven Cheng[MSFT]" wrote:
> Hi Eric,
> Welcome to the MSDN newsgroup.
> As for the displaying rich binary document in SQL server 2005reporting
> service report, do you mean HTML format report? If so, I think you can
> consider using the Html <iframe> element to embed the binary document
> (powerpoint, word, pdf....)... Of course, you may need to create a custom
> report item which can render out the html <iframe> element. For normal web
> page, we can embed an <iframe> like below:
> =========================> <iframe id="frmPPT" runat="server" src="http://pics.10026.com/?src=small.ppt" width="100%"
> height="500" >
> ======================> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>|||Thanks for your response.
For other format (PDf, ...) since they're binary format, I'm afraid there
is no support in the reporting service built-in components. This will
require the convertion between different format binary documents. Also, the
RS 2005 used to plan shipping a RTF/WORD render extension, however, it is
cancelled and I think one of the reason maybe there are already many 3rd
party convert tools for converting different format binary documents (like
word, pdf, chm, html...).
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Steven,
I do not fully agree with you. First there are not many RDL to Word, PDF or
HTML, ... converter available. I know only SoftArtisan, which isn't a fully
RDL to Word converter.
Second many of our customer in the financial industry want to have more
flexible reporting. This means they want to include "dynamic" informations
into the report which are not based one databases, ex. a word document with
personal informations for one client.
What's the meaning of a reporting server if you have at the end 2 documents?
For that we need a fully supported word renderer, or better a word report
item.
Are there more RDL to word converter available?
Thanks
Eric|||Thanks for your respones Eric,
Yes, so far there is no RDL to word render extension, as our dev team also
mentioned it's still a planed feature in future release. I think this is
also somewhat due to the open of new OFFICE XML format, the new word
rendering extension may mainly target the xml format which will be more
portable. BTW, the converter I mentioned earlier means some 3rd party tool
which can convert pdf, html(exiting file) to word , image or other format
though they not be directly plugged in SSRS.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

Inserting a document (PowerPoint, Word, PDF) into a report (SSRS 2

Hi,
Which is the best way to insert a document into a report, which is also
rendered with the report?
Writing a custom report item? Rendering as image?
But how to do a page break?
Is there a commercial solution available?
Thanks
EricHi,
Not sure about my earlier post. Posting it again.
I feel the best way is to provide a link on the report (using action: jump
to URL) and when clicked it should open the attachments. If you want to
render it with the report, then render it as image but store it as external.
Your last ques.. How to do a page break ? with the image or with records ?
Not very sure about the question. Need to explain.
Amarnath
"Eric" wrote:
> Hi,
> Which is the best way to insert a document into a report, which is also
> rendered with the report?
> Writing a custom report item? Rendering as image?
> But how to do a page break?
> Is there a commercial solution available?
> Thanks
> Ericsql

Inserting a default Value

I have populated an SQLdata table from an XML datasource usinmg the bulk command. In my SQL table is a new column that is not in the XML table which I would like to set to a default value.

Would anyone know the best way to do this. So far I can's see how to add this value in the Bulk command. I am happy to create a new command that updates all the null values of this field to a default value but can't seem to do this either as a SQLdatasource or a APP Code/ Dataset.

Any suggestions or examples where I can do this.

Many thanks in advance

A DataColumn has a DefaultValue property - seeMSDN for usage:

private void MakeTable()
{
// Create a DataTable.
DataTable table = new DataTable("Product");

// Create a DataColumn and set various properties.
DataColumn column = new DataColumn();
column.DataType = System.Type.GetType("System.Decimal");
column.AllowDBNull = false;
column.Caption = "Price";
column.ColumnName = "Price";
column.DefaultValue = 25;

// Add the column to the table.
table.Columns.Add(column);

// Add 10 rows and set values.
DataRow row;
for(int i = 0; i < 10; i++)
{
row = table.NewRow();
row["Price"] = i + 1;

// Be sure to add the new row to the
// DataRowCollection.
table.Rows.Add(row);
}
}

Inserting a Decimal - HELP

Im trying to insert a decimal into SQL 2000 using a stored procedure. I can successfully insert it using a direct T-SQL statement inside the code, but I cant insert it correctly when calling for a stored procedure.

For instance 12.5 will insert as 12.

Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@.Num", SqlDbType.Decimal))
MyCommand.Parameters("@.Num").Precision = 5
MyCommand.Parameters("@.Num").Scale = 2
MyCommand.Parameters("@.Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]

I also declared @.Num to be a decimal in my Stored Procedure.

As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.

Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.Try running Profiler and tracing the transaction. Then you can determine if the precision is being lost when .NET sends the command to SQL Server, or when SQL Server calls the stored procedure. Have you tried not being so specific? Can you post the source code for your stored procedure?

MyCommand.Parameters.Add("@.Num", Convert.ToDecimal(TextBox1.Text))

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:
>
>

Inserting a Count into a Table

I am inserting data from one table into another. To simplify:

Table 1 has columns:
1.A, 1.B, 1.C, 1.D

Table 2 has columns
2.A, 2.B, 2.C, 2.D, 2.Tag

where 2.Tag is a counter of all unique combinations of 1.C and 1.D.
Not a count of how many records for each combination.

Therefore, if Table 1 is

x - x - x - x
x - x - x - x
x - x - x - x
x - x - x - y
x - x - x - z

then, table 2 is

x - x - x - x - 1
x - x - x - y - 2
x - x - x - z - 3

anyone help me w/ the sql on that ?

i was thinking of putting a variable in the select statement, but was unable to increment it.

thanksWhat do 2.A and 2.B hold? Is 1.A and 1.B relevant?|||This could be done in a single select statements, but I refuse to think about it further until you explain why you would want to do such a loopy thing.|||Try this but I not sure my coding.
INSERT INTO table1 SELECT DISTINCT *FROM table2|||a counter, not a count

what sequence governs this counter, A and B?

could you perhaps explain why you want this weird data?|||Try this

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
Select Distinct *,Tag=(Select count(*) from (Select Distinct D from @.t) T1 where T1.D<=T2.D) from @.t T2

Madhivanan|||madhivanan, very nice try, but not quite right

add the following to your test data and see what happens :) :) :)

insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')|||r937

try this with different combinations

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

Select Distinct *,Tag=(Select count(*) from (Select Distinct * from @.t) T1
where T1.A+T1.B+T1.C+T1.D<=T2.A+T2.B+T2.C+T2.D) from @.t T2

Madhivanan|||sorry, that's not right either ;)

add this to your data and see what happens --

insert into @.t values('y','y','x','x')|||r937,

Can you post the expected outcome for these data?

Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

Madhivanan|||sure

x x x x 1
x x x x 2
x x x x 3
x x x y 1
x x x z 1
x x y y 1
x x z y 1|||Purpose of craziness: i want to order the data when i select from that table.

for the below
Declare @.t table(A char(1), B char(1), C char(1), D Char(1))
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','x')
insert into @.t values('x','x','x','y')
insert into @.t values('x','x','x','z')
insert into @.t values('x','x','y','y')
insert into @.t values('x','x','z','y')

the new table should have:

x-x-x-x-1
x-x-x-y-2
x-x-z-y-3|||First, I don't understand why your output isn't:

x-x-x-x-1
x-x-x-y-2
x-x-x-z-3
x-x-y-y-4
x-x-z-y-5

Second, if you are relying on the order of the data in the table for your logic, you are letting yourself in for a heap-o-hurtin'.|||aargh, i think my understanding of this has been wrong all along

it should be like in post #13!!

okay, what about if you add these rows, then what do you get --

insert into @.t values('b','b','x','y')
insert into @.t values('c','c','x','z')|||oh ya..
1. the order should be that...(sorry, it is a friday)

2. i want it ordered as such when i do an extract and display into a report.l

thanks!
C

Inserting a Control Record into a Flat Text File through SSIS

I am working on an SSIS project where I create two flat files for submission to a data contractor. This contractor requires a control record be the first line in the file. I create the control record based on the table information being exported.

What I would like to know is, is it possible to utilize the Header Section of the Flat File Destination Editor to insert the control record? And, as it is dynamic, what kind of coding must I do in order to utlise this functionality?

Thanks.

Yes, I would guess that you can do exactly this using the header section.

You can set it dynamically by putting an expression on the [<Flat File Destination Name>].[Header] property of the parent data-flow task.

-Jamie

|||

Ok, I see that this can work, but looking at the available variables, functions for expressions, I do not see how I would get the data inserted from another text file (table) already created into this second one.

Truly not trying to be dense here, just "can't seem to see the forest for the trees."

Thanks.

|||

That's a bit of a different requirement. You may be hampered by the fact that the maximum length of the result of an expression can only be 4000 chars

The way to do it would probably be to build the text up programatically in a script task.

-Jamie

sql

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

Inserting a checkbox value into bit field sql server 2000

Edited by SomeNewKid. Please post code between<code> and</code> tags.

---

This is probaly the easiest question you've ever read but here goes.

I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.

Here is my code:

Sub AddSection_Click(Sender As Object, e As EventArgs)
 Dim myCommand As SqlCommand
 Dim insertCmd As String
 ' Build a SQL INSERT statement string for all the input-form
 ' field values.
 insertCmd = "insert into Customers values (@.SectionName, @.SectionLink, @.Title, @.NewWindow, @.LatestNews, @.Partners, @.Support);"
 ' Initialize the SqlCommand with the new SQL string.
 myCommand = New SqlCommand(insertCmd, myConnection)
 ' Create new parameters for the SqlCommand object and
 ' initialize them to the input-form field values.
 myCommand.Parameters.Add(New SqlParameter("@.SectionName", SqlDbType.nVarChar, 50))
 myCommand.Parameters("@.SectionName").Value = Section_name.Value
 myCommand.Parameters.Add(New SqlParameter("@.SectionLink", SqlDbType.nVarChar, 80))
 myCommand.Parameters("@.SectionLink").Value = Section_link.Value
 myCommand.Parameters.Add(New SqlParameter("@.Title", SqlDbType.nVarChar, 50))
 myCommand.Parameters("@.Title").Value = Section_title.Value
 If New_window.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.NewWindow", SqlDbType.bit, 1))
 myCommand.Parameters("@.NewWindow").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.NewWindow", SqlDbType.bit, 1))
 myCommand.Parameters("@.NewWindow").Value = 1
 End If
 If Latest_news.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.LatestNews", SqlDbType.bit, 1))
 myCommand.Parameters("@.LatestNews").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.LatestNews", SqlDbType.bit, 1))
 myCommand.Parameters("@.LatestNews").Value = 1
 End If
 If Partners.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
 myCommand.Parameters("@.Partners").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
 myCommand.Parameters("@.Partners").Value = 1
 End If
 If Support.Checked = false Then
 myCommand.Parameters.Add(New SqlParameter("@.Support", SqlDbType.bit, 1))
 myCommand.Parameters("@.Support").Value = 0
 else
 myCommand.Parameters.Add(New SqlParameter("@.Support", SqlDbType.bit, 1))
 myCommand.Parameters("@.Support").Value = 1
 End If
 myCommand.Connection.Open()
 ' Test whether the new row can be added and display the
 ' appropriate message box to the user.
 Try
 myCommand.ExecuteNonQuery()
 Message.InnerHtml = "Record Added<br>" & insertCmd
 Catch ex As SqlException
 If ex.Number = 2627 Then
 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"
 Message.Style("color") = "red"
 End If
 End Try
 myCommand.Connection.Close()
 BindGrid()
 End Sub

Any response would be appreciatedYou're right; it's easier than you think. Try this:

myCommand.Parameters.Add(New SqlParameter("@.Partners", SqlDbType.bit, 1))
myCommand.Parameters("@.Partners").Value = Partners.Checked

|||thanks a lot and sorry for the late reply that worked

Inserting a block of numbers sequentially into a table

I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7, bu
t
don't have the experience to understand this enough. I also looked in
previous questions on this group, but find I still need help. I need to
insert a series of MSR (Medical Service Record) numbers into a table of
appointments over a selected date range. The last used MSR is recorded in
this table in column LastMSR:
CREATE TABLE [UserVars] (
[LastMSR] [int] NOT NULL ,
[RequireMSR] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
Beginning with LastMSR+1, I want to insert numbers sequentially into the
column MSR of the Appointments table (see below), selecting rows where MSR =
0, and APPT_DATE is BETWEEN '<lowDate>' AND '<HighDate>'.
CREATE TABLE [Appointment] (
[APPT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[APPT_DATE] [datetime] NULL ,
[RESOURCE_ID] [int] NOT NULL ,
[DEPARTMENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[START_TIME] [datetime] NULL ,
[DURATION] [datetime] NULL ,
[STATUS] [smallint] NOT NULL ,
[CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MSR] [int] NOT NULL ,
[ChgTcktPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
DEFAULT (getdate()),
[ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Once I'm done, I want to write my highest assigned MSR back to table
UserVars; however, I don't want any other user to allocate a block of MSRs
until I'm done.
Thank you...My first question is: Do you want to single thread access to the table? In
2000, do something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
In 2005, look at rownumber()
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:FD1DF8A7-5646-4C4B-8755-E74767116D41@.microsoft.com...
>I looked for a technique in Joe Celko's SQL book and found Chapter 1.2.7,
>but
> don't have the experience to understand this enough. I also looked in
> previous questions on this group, but find I still need help. I need to
> insert a series of MSR (Medical Service Record) numbers into a table of
> appointments over a selected date range. The last used MSR is recorded in
> this table in column LastMSR:
> CREATE TABLE [UserVars] (
> [LastMSR] [int] NOT NULL ,
> [RequireMSR] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> ) ON [PRIMARY]
> GO
> Beginning with LastMSR+1, I want to insert numbers sequentially into the
> column MSR of the Appointments table (see below), selecting rows where MSR
> =
> 0, and APPT_DATE is BETWEEN '<lowDate>' AND '<HighDate>'.
> CREATE TABLE [Appointment] (
> [APPT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [APPT_DATE] [datetime] NULL ,
> [RESOURCE_ID] [int] NOT NULL ,
> [DEPARTMENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [START_TIME] [datetime] NULL ,
> [DURATION] [datetime] NULL ,
> [STATUS] [smallint] NOT NULL ,
> [CLIENT_ID] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [MSR] [int] NOT NULL ,
> [ChgTcktPrinted] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [ts_timestamp] [datetime] NULL CONSTRAINT [DF__Appointme__ts_ti__023D5A04]
> DEFAULT (getdate()),
> [ts_user] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> Once I'm done, I want to write my highest assigned MSR back to table
> UserVars; however, I don't want any other user to allocate a block of MSRs
> until I'm done.
> Thank you...|||Sorry, I don't understand. When I start, the appointments table might be
Appt_ID Appt_Date MSR
-- -- --
1 11/07/2005 0
2 11/07/2005 0
etc.
If the last used MSR number (stored in UserVar table) is 20, then when I'm
done I want the appointments table to look like this:
Appt_ID Appt_Date MSR
-- -- --
1 11/07/2005 21
2 11/07/2005 22
etc.
Starting with the stored LastMSR+1, each row increases MSR by 1.
"Louis Davidson" wrote:

> My first question is: Do you want to single thread access to the table?
In
> 2000, do something like:
> select 'Blue' as color
> into #testtable
> union all
> select 'Red'
> union all
> select 'Green'
> select color, (select count(*) from #testTable as t2 where t2.color <=
> #testTable.color) as rowNumber
> from #testTable
> order by 2
> In 2005, look at rownumber()
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:FD1DF8A7-5646-4C4B-8755-E74767116D41@.microsoft.com...
>
>

Inserting a blank line betwen groupings in my matrix report

I have a matrix report with 3 column groups. My main group is called
"Location" and then I dump out a bunch of data related to that location.
I want to insert a blank line before each new "Location" in my report but I
can't figure out how to do this with my matrix report.
Any ideas?Try putting this into expression for the location:
=(Fields!Location.Value+Environment.newline())
Good luck!
Peace,
Dan
"AdamB" <AdamB@.discussions.microsoft.com> wrote in message
news:6B279444-7502-43B3-B238-9C282A3C8858@.microsoft.com...
>I have a matrix report with 3 column groups. My main group is called
> "Location" and then I dump out a bunch of data related to that location.
> I want to insert a blank line before each new "Location" in my report but
> I
> can't figure out how to do this with my matrix report.
> Any ideas?

Inserting a 0

Hi,
Im having trouble with the money data type, for instance I have a column that calculates a price but it will output the price as 470.2 instead of 470.20 which is how I want it displayed on a web page.

Anyone know how to automatically insert a zero on the end of the price?

THanksNoone knows how to insert zeros on the end of numbers??|||I'm gettin '470.2000'
from this simple query I ran from query analyser

declare @.dollar as money
set @.dollar=470.2
select @.dollar

I can't understand why your only getting 470.2. Maybe you can write your calculation query for us to figure?|||I figured it out but thanks anyhow : )sql

INSERTING 20Mill Records into a Table with 100Mill Records..

Hi, Iam new to SQL Srvr 2005 with a Oracle Background..

I have three tables
Table 1 (100 Mill Rows)
Table 2 (20 Mill Rows)
Table 3 (10 Mill Rows)

INSERT INTO Table1
select Table2.* from Table 2
where exists (select 1 from Table3
where Table2.xyz = Table3.xyz
and Table2.abc = Table3.abc);

Whats the most efficient way to do this..
Iam already DISABL'ng the Indexes before the Insert on Table 1
Also -- Whats the SQLSRVR's equivalent to Rollback Segment?

I would suggest to use a join and be sure to have indexes in table2 and table3 by the columns used in the join.

- index on table2 by (xyz, abc)

- index on table3 by (xyz, abc)

The index could be also by (abc, xyz), but it will depend on the order of an existing constraint like primary key or foreign key, or in case there not a constraint, then the selectivity of those columns..

INSERT INTO Table1

select

Table2.*

from

Table 2 inner join Table3

on Table2.xyz = Table3.xyz and Table2.abc = Table3.abc

AMB

|||

Inserting 20 mil rows at one time will create a log of Transaction Log activity.

IF, and that is a big IF, this is a singular operation, and if there is no other activity in the database, you may wish to change the recovery model to 'SIMPLE' (after first making a backup.)

Then do the import in batches of 100k rows - this will greatly reduce the logging pressure and could make a radical difference in speed.

When finished, return the recovery model to the previous setting. AND then make a full backup.

|||

If you go with changing the Recovery Model to simple and back, you'll want to be sure to run a full back-up immediately after reverting back.

Switching to the Simple model breaks the log chain and a full back-up is required to establish a new chain.

|||

Thanks, Dale,

I should have explicitly mentioned that (assumptions, assumptions, etc.)

Inserting 2 tables with Pk/Fk

Hello all... I'm working on a C++ Windows service that writes to a SQL Server database. I consider myself quite a novice at SQL Server, but I have played around with it over the years... Performance is going to be a concern with this project.

Let's say...
Table A has columns PkA(identity), Stuff(text), FkB (Table B's Pk)
Table B has columns PkB(identity), MoreStuff(text)

I'll be executing SQL statements from my service - INSERTs, etc...

What's the most efficient way to write to these two tables? The immediate challenge I have is getting that PkB value after inserting Table B and using it for Table A's FkB.

Is there a way I can insert into both tables with one SQL statement?

Thanks!! Curt.First, I recommend that your service call a stored procedure to make this happen, and not issue an ad-hoc query. the sproc would do both inserts, you'd just call it with the values you need to put in Stuff and MoreStuff. So as far as your service is concerned, both inserts happen in "one statement". Within the sproc it's still two inserts though.

Second, in your sproc after your insert into tableB, you can call SCOPE_IDENTITY() to get the identity value that was just inserted. use this value as the fk in tableA when you do the insert there.

take a look at SCOPE_IDENTITY() in BOL. @.@.IDENTITY is a related beast, but SCOPE_IDENTITY() is preferred since it's scoped, as the name implies.

Edit: since I have my roots in C++ as well, thought I would add this: leaving your tables open to ad-hoc queries from client apps is like designing a class in C++ where all the fields are public. If your table structure changes, you have to recompile and redeploy your service. You wouldn't want to do that would you? :)

I think of sprocs as analogous to the public member functions on a class. use them to control how clients are allowed to manipulate the private fields (your tables), and make all fields (tables) private.|||jezemine, yeah I guess I should have qualfied that a bit more... We are in fact planning to put that into a sproc a little later on. As I mentioned, I'm not really a sql server pro and sprocs are on my list of items to conquer... Right now we just need to get something up and running to help prove concept. Thanks for the tips, though! Perhaps I'll conquer that beast sooner than I thought! :)|||ok, but remember that prototype code sometimes has a way of "sticking" :)

inserting 100 records

How to insert 100 record at a time by explicit inserting of identity column i.e.., by setting identity column to false

You mean like:

INSERT INTO t1(c1,c2)

SELECT '1','2'

UNION

SELECT '3','4'

UNION

...

?

|||

This will turn off the identity column for a table,

set identity_insert <tablename> on

[insert 100 records .. ]

set identity_insert <tablename> off

|||

No i mean if identity column is off i.e.., the we should explicitly insert ID column by fetching an XML having 100 records for example

Table1

ID StudRollNo StudName

Inserting into table1(Identity column for column ID is OFF) where i will get the XML of table having 100 records like

ID StudRollNo Studname

|||If you mean to read data from XML into datbase table,?I?suggest?you?learn?XQuery?in?SQL2005

Inserting 1:M relationship data via One Stored Procedure

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html

But if you have a max of 3, then just write the proc with 3 parameters (something like):

create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error

begin tran

insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)

declare @.newContactId int
set @.newContactId = scope_identity()

insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null

commit tran

|||

Hi Louis,

Thanks for the Response, this cleared my mind and the problem. Thank you again!

sql

Inserting 1 row and getting a message that two rows were inserted.

Why does this code tell me that I inserted 2 rows when I really only inserted one? I am using SQL server 2005 Express. I can open up the table and there is only one record in it.

Dim InsertSQL As String = "INSERT INTO dbCG_Disposition ( BouleID, UserName, CG_PFLocation ) VALUES ( @.BouleID, @.UserName, @.CG_PFLocation )"

Dim StatusAs Label = lblStatusDim ConnectionStringAs String = WebConfigurationManager.ConnectionStrings("HTALNBulk").ConnectionStringDim conAs New SqlConnection(ConnectionString)Dim cmdAs New SqlCommand(InsertSQL, con) cmd.Parameters.AddWithValue("BouleID", BouleID) cmd.Parameters.AddWithValue("UserName", UserID) cmd.Parameters.AddWithValue("CG_PFLocation", CG_PFLocation)Dim addedAs Integer = 0Try con.Open() added = cmd.ExecuteNonQuery() Status.Text &= added.ToString() &" records inserted into CG Process Flow Inventory, Located in Boule_Storage."Catch exAs Exception Status.Text &="Error adding to inventory. " Status.Text &= ex.Message.ToString()Finally con.Close()End Try

Anyone have any ideas? Thanks

Change

Status.Text &=

To

Status.Text =

When you put &=, every time when you fire event, the text will be appended

(After I posted it, I realized probably it was able to solve your issue. Sorry for that).

|||

Yeah, somehow, added is getting set to 2 at the

added = cmd.ExecuteNonQuery()

step. I have a trigger set on this table to increase other rows with the same BouleID by one but that shouldn't

affect the ADO object should it?

There is this in the MSDN:

Although theExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

ForUPDATE, INSERT, and DELETE statements, the return value is the numberof rows affected by the command. When a trigger exists on a table beinginserted or updated, the return value includes the number of rowsaffected by both the insert or update operation and the number of rowsaffected by the trigger or triggers. For all other types of statements,the return value is -1. If a rollback occurs, the return value is also-1.

The problem is that these are new inserts into the table so the trigger is not affecting any other rows because they don't exist yet.

|||

Quite possibly. Do you have a SET NOCOUNT ON in your triggers?

|||

No, I dont'. I only have

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

set.

|||

In your trigger, right after the "AS", add "SET NOCOUNT ON". Then any queries the trigger does won't be seen. For example:

CREATE TRIGGER ...

AS

SET NOCOUNT ON

...

Inserting / Updating LongText Field using WriteText?

Hi, I'm trying to store large strings to a database, so am using the
text field type (LongText). I have used this before when storing the
html of a webpage, and was able to store more than 255 characters by
using just a normal update sql statement. Now I'm trying to store the
body of research papers, and must be doing something different, as I
can only store 255 characters.

Can someone explain why SQL Server doesn't like what I am doing -
should I be using the WriteText / UpdateText function? If so, please
explain by example how I would do that, and why doing that works.

Thanks so much,
IainIain Porter (stuff@.intraspin.com) writes:
> Hi, I'm trying to store large strings to a database, so am using the
> text field type (LongText). I have used this before when storing the
> html of a webpage, and was able to store more than 255 characters by
> using just a normal update sql statement. Now I'm trying to store the
> body of research papers, and must be doing something different, as I
> can only store 255 characters.
> Can someone explain why SQL Server doesn't like what I am doing -
> should I be using the WriteText / UpdateText function? If so, please
> explain by example how I would do that, and why doing that works.

You need to tell us of what you are doing and how you are diagnosing
that you only store 255 characters. Did you check datalength() for
the column?

A common trap is that the default output in Query Analyzer is 255 chars;
this is an option in QA that you can change under Tools.

If this does not help, you need to supply more information. Code snippets,
and table defintions are welcome.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Apologies for the lack of detail. The problem is apparent because
when selecting the longtext feild, using READTEXT as below, the text
data cuts off after 255. The problem could be here, getting data out -
I'm not convinced I have the code right, I just think it's in the
getting data in.

Thanks very much for your help, let me know if you need different
info,
Iain

Code samples:

To get data out from the longtext column(content) by id:
javascript:
try {
conn.Open(strConn);
cmd.ActiveConnection = conn;
cmd.CommandText = "SelectContent";
cmd.CommandType = adCmdStoredProc;
cmd.Parameters.Append(cmd.CreateParameter("@.id", adInteger,
adParamInput, 4, id));
contentRS = cmd.Execute();

var content = "";
while (!contentRS.EOF) {
content += contentRS("content");
contentRS.MoveNext;
}
}
...
return content;

SQL (SelectContent):
SET NOCOUNT ON

DECLARE
@.txtptrval VARBINARY(16),
@.startOffset INT,
@.readLength INT,
@.datalength INT

SELECT @.txtptrval = TEXTPTR(content) FROM papers WHERE id=@.id
SELECT @.startOffset = 0
SELECT @.readLength = 255
SELECT @.datalength = DATALENGTH(content) FROM papers WHERE id=@.id

-- If last chunk, reduce buffer size to the nChars remaining
IF ((@.startOffset + @.readLength) > @.datalength) (
SELECT @.readLength = @.datalength - @.startOffset
)

WHILE (@.startOffset < @.datalength)
BEGIN
READTEXT papers.content @.txtptrval @.startOffset @.readLength
SELECT @.startOffset = @.startOffset + @.readLength

-- Last chunk, reduce buffer size to the get the last nChars
remaining
IF (@.startOffset + @.readLength) > @.datalength
SELECT @.readLength = @.datalength - @.startOffset
END
SET NOCOUNT OFF
END
---------------------

To get data in:

CREATE proc InsertPaper
@.authors nvarchar(20),
@.title nvarchar(255),
@.pubyear int,
@.journal nvarchar(255),
@.issue int,
@.pages nvarchar (13),
@.paperabstract text,
@.content text,
@.id int OUTPUT

as

insert into Papers(authors, title, pubyear, journal, issue, pages,
abstract, content)
values (@.authors, @.title, @.pubyear, @.journal, @.issue, @.pages,
@.paperabstract, @.content)

select @.id = @.@.identity
GO
------------------|||Iain Porter (stuff@.intraspin.com) writes:
> Apologies for the lack of detail. The problem is apparent because
> when selecting the longtext feild, using READTEXT as below, the text
> data cuts off after 255. The problem could be here, getting data out -
> I'm not convinced I have the code right, I just think it's in the
> getting data in.
> Thanks very much for your help, let me know if you need different
> info,

When I run a modified version of you script, I seem to get all the data in
the text column, sliced in pieces of 255 chars at a time. So the SQL seems
to be OK.

The problem is like to be in the Javascript code:

> while (!contentRS.EOF) {
> content += contentRS("content");
> contentRS.MoveNext;
> }

You are assuming that you have one result set with all the slices of
the text column. But you have one result set for each slice, instead
of MoveNext, you should have:

contentsRS = contentsRS.NextRecordset

and the stop condition should be on whether contentsRS is a valid
object or not.

Disclaimer: I have no experience or knowledge of Javascript programming.
I am assuming that you are using ADO, because it looks like ADO.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, I'm sorry I've taken so long to come back to you - I didn't
get an email through alerting me to your post, not sure why - thank
you so much for responding again.

I've updated my code as you suggested, but unfortunately get the same
result. My change is listed below:

var content = "";
// while (!contentRS.EOF) {
// content += contentRS("content");
// contentRS.MoveNext;
// }

while (contentRS.State != 0) { // 0 means adStateClosed
content += contentRS("content");
contentRS.NextRecordset;
}

I'm at a loss as to why it's not working - any further thoughts? Is
there a way to count the number of RecordSets returned and reference
each by number or something? I can't see anything like that in a ADO
RecordSet Object Reference.

Thanks very much, I'll check back sooner this time.
Iain|||Hi!! I figured it out - thanks so much for your help. The solution,
for anyone searching, is that you have to set the recordset to the
recordset's NextRecordSet:

contentRS = contentRS.NextRecordSet

Thanks again Erland,
Iain

Inserting .doc data into varbinary column

I need to put .doc data into a varbinary column for full text searching. I have created the db and columns but am unsure as to how to insert the varbinary data. I have found some discussions about inserting images but nothing explicitly on .doc files. Can anyone suggest resources or sample code?

The varbinary datatype does not differentiate the contents of the field, it's all just binary data as far as SQL is concerned. The samples you've found for images should apply equally to any type of binary object, it just seems that most examples are focues on image since most people want to store image data.

Mike

|||Thanks Mike. I will try those examples.

inserting <NULL>

how do you write an insert into statement and keep <NULL> in the null cells?
i was trying something like this BUT when you try to write an IS NULL statement it doesnt work.
ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO D1, NULL) AS Mod1,ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO , NULL) AS Mod1,|||how is this any different?

ISNULL(dbo.qry_TRUNK02_LastVersion_PayableClaims01 .MOD1, NULL) AS Mod1

after i truncate and insert into my table i need to be able to query with an IS NULL statement.

select mod1
from table
where mod1 is null|||Books online: ISNULL Replaces NULL with the specified replacement value.
So what is the point of ISNULL([A], Null)?

Please explain more clearly what you are trying to do, and give some sample data.|||Yeah, what The Blind One said...

are you just meaning to insert a NULL into a column of a table?

as in:
INSERT INTO dbo.TRUNK02_LastVersion_PayableClaims_01 (Mod1, ...)
VALUES (NULL, ...)???

Inserting " ' "

Hello,
I have a text field which consists of strings. And I want to insert
whatever the user types into the database. But if the user types some
punctuation marks, such as " ' ", it generates this error:
Unclosed quotation mark after the character string ')'.
the solutions should be so easy, but I couldn't find out where the
close it.
my update command is as follows:
sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
& "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
"','" & Today.Now & "','Y')"Hi
Add one more "'"
CREATE TABLE #Test (c VARCHAR(10))
INSERT INTO #Test VALUES ('O'' Connor')
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1155534287.413841.202530@.m79g2000cwm.googlegroups.com...
> Hello,
> I have a text field which consists of strings. And I want to insert
> whatever the user types into the database. But if the user types some
> punctuation marks, such as " ' ", it generates this error:
>
> Unclosed quotation mark after the character string ')'.
> the solutions should be so easy, but I couldn't find out where the
> close it.
> my update command is as follows:
> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
> "','" & Today.Now & "','Y')"
>|||This really isn't a SQL question because you need to know how to do
this in VB.NET before the query is passed to the database.
You would do this:
> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text.Replace("'","''") & "','" & TextBox2.Text.Replace("'","''") &
> "','" & Today.Now & "','Y')"
Izzy
Dot Net Daddy wrote:
> Hello,
> I have a text field which consists of strings. And I want to insert
> whatever the user types into the database. But if the user types some
> punctuation marks, such as " ' ", it generates this error:
>
> Unclosed quotation mark after the character string ')'.
> the solutions should be so easy, but I couldn't find out where the
> close it.
> my update command is as follows:
> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
> "','" & Today.Now & "','Y')"|||Thank you for your helps.
Izzy wrote:
> This really isn't a SQL question because you need to know how to do
> this in VB.NET before the query is passed to the database.
> You would do this:
> > sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> > & "','" & Today.Date & "','" & TextBox1.Text.Replace("'","''") & "','" & TextBox2.Text.Replace("'","''") &
> > "','" & Today.Now & "','Y')"
> Izzy
>
> Dot Net Daddy wrote:
> > Hello,
> >
> > I have a text field which consists of strings. And I want to insert
> > whatever the user types into the database. But if the user types some
> > punctuation marks, such as " ' ", it generates this error:
> >
> >
> > Unclosed quotation mark after the character string ')'.
> >
> > the solutions should be so easy, but I couldn't find out where the
> > close it.
> >
> > my update command is as follows:
> >
> > sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> > & "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
> > "','" & Today.Now & "','Y')"sql

Inserting " ' "

Hello,
I have a text field which consists of strings. And I want to insert
whatever the user types into the database. But if the user types some
punctuation marks, such as " ' ", it generates this error:
Unclosed quotation mark after the character string ')'.
the solutions should be so easy, but I couldn't find out where the
close it.
my update command is as follows:
sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
& "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
"','" & Today.Now & "','Y')"Hi
Add one more "'"
CREATE TABLE #Test (c VARCHAR(10))
INSERT INTO #Test VALUES ('O'' Connor')
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1155534287.413841.202530@.m79g2000cwm.googlegroups.com...
> Hello,
> I have a text field which consists of strings. And I want to insert
> whatever the user types into the database. But if the user types some
> punctuation marks, such as " ' ", it generates this error:
>
> Unclosed quotation mark after the character string ')'.
> the solutions should be so easy, but I couldn't find out where the
> close it.
> my update command is as follows:
> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
> "','" & Today.Now & "','Y')"
>|||This really isn't a SQL question because you need to know how to do
this in VB.NET before the query is passed to the database.
You would do this:

> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text.Replace("'","''") & "','" & T
extBox2.Text.Replace("'","''") &
> "','" & Today.Now & "','Y')"
Izzy
Dot Net Daddy wrote:
> Hello,
> I have a text field which consists of strings. And I want to insert
> whatever the user types into the database. But if the user types some
> punctuation marks, such as " ' ", it generates this error:
>
> Unclosed quotation mark after the character string ')'.
> the solutions should be so easy, but I couldn't find out where the
> close it.
> my update command is as follows:
> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
> "','" & Today.Now & "','Y')"|||Thank you for your helps.
Izzy wrote:[vbcol=seagreen]
> This really isn't a SQL question because you need to know how to do
> this in VB.NET before the query is passed to the database.
> You would do this:
>
> Izzy
>
> Dot Net Daddy wrote:

Insertin fonts

Is it possible to store my specific font insql 2k and if yes then how?

In what way do you want to use a font stored. You could store the font file in a BLOB field but that wouldn't be of much use. If you want to distribute it with your app then you sholud put it in a setup package.

Not quite sure what you want to achieve.

inserted/deleted tables

Does the data in the rows in the inserted and deleted tables always correspond? For instance, row 1 in inserted corresponds with row 1 in deleted.
Thanks,

OK, if you

-Insert n rows you have n rows in the inserted.
-Delete n rows you have n rows in the deleted table.
-Update n rows you have n rows in the inserted and n rows in the deleted table.

So for an update the rowcount is always corresponding.

HTH, Jens Suessmeyer

|||

Well, what I was really wanting to know is if I could assume that the data in row [1] (the new data to be inserted) of the inserted table corresponds with the data in row [1] (the data that was deleted) in the deleted table.

Example:

Update people

Set person_id = (select person_id from inserted)

Where people.person_id = (select person_id from deleted)

This type of update statement will only work if there is a single row being updated. I wanted to step through the inserted and deleted tables one row at a time for multiple row updates, but I did not know if it was safe to say that the data in inserted row # corresponded with the data in deleted row #.

|||

> Update people

>

> Set person_id = (select person_id from inserted)

>

> Where people.person_id = (select person_id from deleted)

What table is this trigger attached to? People, or another table? Are you

just trying to undo the update to people, or replicate the update to another

table? In what scenario?

> This type of update statement will only work if there is a single row

> being updated.

Absolutely correct, and a very common tripping point for hundreds of people

before you.

> I wanted to step through the inserted and deleted tables

> one row at a time for multiple row updates

No, no, no. You are going about this all wrong. Think about it in SETS.

If you give some proper DDL and specs (see http://www.aspfaq.com/5006) we

can help you do this in one statement and abandon this idea of iterating

through every row and trying to match some hypothetical "row number"...

|||

> Does the data in the rows in the inserted and deleted tables always

> correspond? For instance, row 1 in inserted corresponds with row 1 in

> deleted.

There is no "row 1"... a table, by definition, is an unordered set of rows.

Typically you identify a row by some unique value, like a primary key, not

whether it came first or last or somewhere in between.

|||

Hello to everyone.

here i want to know some more details regarding inserted/deleted tables.

consider the scenario that more than 100 users are inserting/updating rows of same or othere tables of a database and tiggers of after update upon each insert and/or update is been fired.

what will be the response of the SQL 2005 server to these operations as i am moving the updated data to the audit tables from the delted table. by using the following trigger.

CREATE TRIGGER [TrigAUTblA]
ON [TblA]
AFTER UPDATE AS
BEGIN
INSERT INTO [TblAHistory]
(
[guidA],
[Description]
)
SELECT deleted.guidA,
deleted.Description
FROM deleted

Also what issues can emerge using this scenario

|||

It is possible to update the unique key for multiple rows in a table. In that case, there is nothing to correlate the rows in "inserted" to the rows in "deleted" other than the order in which they are returned by a select statement.

So the question is a valid one, I think: If a table contains one unique key, and multiple rows in that table are updated such that the value of that key changes, can we count on the rows in the "inserted" and "deleted" tables being returned in the same order so that they can be matched up one to one?

Thanks,

Ron