Re: Another try - inserting datset into sql
- From: "Ron Allen" <rallen@xxxxxxxxxxxxxxxxxx>
- Date: Tue, 10 May 2005 09:21:30 -0400
Paul,
Does this throw an exception? You are retrieving the ID field and there
is no column to put that data into so I would expect some kind of error
there. Also do your column definitions in the database match the ones for
your parameters? If you are using SQL2K I'd also think you want
SCOPE_IDENTITY() instead of @@Identity.
Ron Allen
"a" <a@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A7C30267-7B5A-47D2-B278-7DE81032A468@xxxxxxxxxxxxxxxx
> NEW Post
>
> Here's my best guess at how to insert this dataset....
> the code runs, but no new records are added to the sql table.
>
> I've read and split a delimited text file into a dataset.
> It looks fine in a datagrid (5 columns and 5,000 rows),
> but I've been trying, without success, to then insert
> the resulting dataset called "result" into a single sql
> table that has an auto-increment and PK column called ID,
> as well as the 5 columns from the dataset.
>
> Any suggestions on a way to perform the insert of the
> "result" dataset into the sql table?
>
> Thanks,
>
> Paul
>
> ==================================================================================================
>
>
> StreamReader sr = new StreamReader("C:\\test.txt"); //Read From A
> File instead of a webrequest
>
> DataSet result = new DataSet(); //The DataSet to Return
> result.Tables.Add("MyNewTable"); //Add DataTable to hold the DataSet
>
> result.Tables["MyNewTable"].Columns.Add("CompanyName"); //Add a single
> column to the DataTable
> result.Tables["MyNewTable"].Columns.Add("FormType"); //Add a single
> column
> result.Tables["MyNewTable"].Columns.Add("CIK"); //Add a single
> column
> result.Tables["MyNewTable"].Columns.Add("DateFiled"); //Add a single
> column
> result.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
> result.Tables["MyNewTable"].Rows.Add(items); //Add the item
> }
>
> for (int i = 1; i <= 11; i++) //Remove first
> 8
> rows from the DataTable/DataSet
> {
> result.Tables["MyNewTable"].Rows.RemoveAt(0);
> }
>
> dataGrid1.SetDataBinding(result, "MyNewTable"); //Binds DataGrid to
> DataSet,displaying datatable
>
> SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(); //Configure a
> data adapter
>
> //Configure the SQL connection
> SqlConnection sqlConnection1 = new SqlConnection("workstation
> id=AMD;packet
> size=4096;" +
> "integrated security=SSPI;data source=AMD;persist security
> info=False;initial catalog=SEC_XBRL_10");
>
> //Generate the 'INSERT' command
> SqlCommand sqlInsertCommand1 = new SqlCommand("INSERT INTO
> SEC_Index_01(CompanyName, FormType, CIK, " +
> "DateFiled, SECWebAddress) VALUES (@CompanyName, @FormType, @CIK,
> @DateFiled, @FileName); SELECT ID, " +
> "CompanyName, FormType, CIK, DateFiled, SECWebAddress FROM SEC_Index_01
> WHERE (ID = @@IDENTITY)", sqlConnection1);
>
> //Add the parameters
> sqlInsertCommand1.Parameters.Add("@CompanyName",
> System.Data.SqlDbType.VarChar, 8000, "CompanyName");
> sqlInsertCommand1.Parameters.Add("@FormType",
> System.Data.SqlDbType.VarChar,
> 8000, "FormType");
> sqlInsertCommand1.Parameters.Add("@CIK", System.Data.SqlDbType.VarChar,
> 8000, "CIK");
> sqlInsertCommand1.Parameters.Add("@DateFiled",
> System.Data.SqlDbType.VarChar, 8000, "DateFiled");
> sqlInsertCommand1.Parameters.Add("@FileName",
> System.Data.SqlDbType.VarChar,
> 8000, "SECWebAddress");
>
>
> sqlDataAdapter1.InsertCommand = sqlInsertCommand1; //Set the
> insert
> command
>
> sqlDataAdapter1.Update(result,"SEC_Index_01"); //Perform the
> update
>
>
.
- References:
- Prev by Date: Moving a tree node up/down
- Next by Date: Re: Print Labels C#
- Previous by thread: Re: Another try - inserting datset into sql
- Next by thread: Re: How to replace escaped strings?
- Index(es):
Relevant Pages
|
