Re: send dataset as a parameter

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: JIM.H. (JIMH_at_discussions.microsoft.com)
Date: 02/28/05 

Date: Mon, 28 Feb 2005 11:15:02 -0800

Scoot thanks again for the reply. You are at the heart of my problem, how
should I write my update command, I could not just say "insert into TableDest
(...) Values(...)", you know the values are in dataset, not in a table.

"Scott M." wrote:

> Hi Jim,
>
> Since you populated the dataset using a dataadapter that is linked to the
> proprietary db and you want to take that data and append it to a different
> db, you can't (as you probably know) use the same dataadapter that got you
> the data in the first place.
>
> So yes, you must create a different dataadapter that is linked to the SQL
> Server table and pass the dataset data to this new dataadapter. It would go
> something like this (VB.NET pseudo-code):
>
> 'Create the connection and dataadapter objects needed for SQL Server
> Dim connectionString As String = your connection string here or get from
> Web.Config
> Dim strSQL As String = "SELECT * FROM TableDest"
> Dim sqlCon as New SqlClientConnection(connectionString)
> Dim sqlDA as New SqlClientDataAdapter(strSQL, sqlCon)
>
> 'Configure the dataadapter's UPDATE command to suit your needs
> sqlDA.UpdateCommand.CommandText = your update statement here
>
> sqlDA.Update(DataSet1)
>
> Remember this is pseudo-code off the top of my head, so I might have missed
> something, but this is the process you'll need to go through.
>
> Good luck!
>
>
> "JIM.H." <JIMH@discussions.microsoft.com> wrote in message
> news:7A7F09CA-AD46-4E1D-9431-405094E52D7A@microsoft.com...
> >
> > Hello Scott,
> > First, thank you very mush for your great help. I had the following
> > question
> > in the past, nobady replied. I have a proprietary database keeping table:
> > TableSrc: fname,lname,age In my asp.net application I am retrieving data
> > from TableSrc into a dataset: DataSet1 though an adapter. Now I created a
> > SQL
> > Server table TableDest: Fname,Lname,Age and I want to append DataSet1 into
> > TableDest. How should I do this? Remember, DataSet1 is linked to TableSrc,
> > not TableDest. Are you suggesting me to create another adapter for
> > TableDest
> > and use adapter.update(DataSet1), if yes, do you have any example for
> > this, I
> > am quite new in asp.net.
> > Thanks,
> > Jim.
> >
> >
> > "Scott M." wrote:
> >
> >> Well, first understand that a DataSet is much more than just a DataTable.
> >> Technically, a DataSet does not hold any data at all. It is just a
> >> container to hold DataTables.
> >>
> >> But the point of your question is, can you programmatically move the data
> >> from a DataTable into a SQL table. Absolutely! There are several way
> >> to
> >> accomplish this:
> >>
> >> 1. Use a .NET DataAdapter to populated the DataTable in the first place
> >> (or
> >> to map the original table's structure to a .NET DataTable) and just call
> >> the
> >> DataAdapter's Update method, which will move the DataTable data to the
> >> source table.
> >>
> >> 2. Write a custom .NET Command (either SQL or SP) and call the
> >> ExecuteNonQuery method of the Command to update the original table.
> >>
> >> 3. Serialize the data from the DataSet (via the GetXML method of the
> >> DataSet) and either store the XML or parse the XML on the DB.
> >>
> >>
> >> "JIM.H." <JIMH@discussions.microsoft.com> wrote in message
> >> news:6A015938-0870-46CC-9ECE-B517F5D226F1@microsoft.com...
> >> > Thanks guys. I will try XML as last resort. I am wondering if I have a
> >> > dataset, which is basically a DataTable, couldn't I create a SQL table
> >> > and
> >> > dump all the data from DataTable to this SQL table programmatically in
> >> > asp.net. I have some data in my dataset, I am trying to dump it to a
> >> > table
> >> > in
> >> > SQL Server.
> >> >
> >> > "Scott M." wrote:
> >> >
> >> >> I don't think a stored proc. would accept a DataSet because the SP is
> >> >> running on your DB and the DB doesn't know what a DataSet is.
> >> >>
> >> >>
> >> >> "JIM.H." <JIMH@discussions.microsoft.com> wrote in message
> >> >> news:0081FB45-3583-44F1-9160-A8F19B408BEE@microsoft.com...
> >> >> > Hello,
> >> >> > Can I send a dataset as a parameter into stored procedure and import
> >> >> > data
> >> >> > to
> >> >> > a table in the stored procedure?
> >> >> > Thanks,
> >> >> > Jim.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Relevant Pages

  • Re: Loading a DataSet - Newby
    ... Than you can make a connection ... dim conn as new sqlclient.connection ... Dim cmd As New SqlCommand ... Than the dataadapter ...
    (microsoft.public.dotnet.languages.vb)
  • Re: DataAdapter vs Coding
    ... With a DataAdapter, you still set up your connection as normal (connection ... Dim ds as New DataSet ... > connection but always fails when using the DataAdapter wizard. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Syntax error in INSERT INTO statement
    ... I have the following code to import data from an XML file but I got an error in the update command "da.Update ... Dim DS As New DataSet ... 'Create connection ... Dim newRow As DataRow ...
    (microsoft.public.dotnet.languages.vb)
  • Re: DataAdapter vs Coding
    ... He asked why the Wizard is failing ... you still set up your connection as normal (connection ... >> When I am creating a project and code my connection using Dim ... if I set up the exact same connection using the DataAdapter ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: BUG-Access 97 db insert via Jet 4.0 using stored procedure fails - any help?
    ... > 3) I attach another dataadapter connected to my MSAccess 97 database, ... > When I set the insert command of the dataadapter to oledb commandtype ... > 'Dim cmdInsert As OleDbCommand ...
    (microsoft.public.dotnet.framework.adonet)