Q: send dataset as a parameter

[JIM.H.]

Hello,
Can I send a dataset as a parameter into stored procedure and import data to
a table in the stored procedure?
Thanks,
Jim.

[Scott M.]

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." <JI**@discussions.microsoft.com> wrote in message
news:00**********************************@microsof t.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.

[Wilco Bauwer]

If you insist, you could have a text parameter in your stored
procedure. You could pass the xml as text, and in the SP use OPENXML to
intepret the text as xml. At that point, you can do xpath queries, etc.

[Scott M.]

You can absolutely pass text (in whatever form you like: plain text, html,
xml, etc.). But if you pass XML as a parameter to a SP, then you find
yourself in the same boat as if you passed a DataSet in terms of dealing
with the XML. Will the DB know what XPath is?

Can you do XPath queries on a DB?
"Wilco Bauwer" <wi****@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

If you insist, you could have a text parameter in your stored
procedure. You could pass the xml as text, and in the SP use OPENXML to
intepret the text as xml. At that point, you can do xpath queries, etc.

[D.W. Warlock]

Scott M. wrote:

You can absolutely pass text (in whatever form you like: plain text, html,
xml, etc.). But if you pass XML as a parameter to a SP, then you find
yourself in the same boat as if you passed a DataSet in terms of dealing
with the XML. Will the DB know what XPath is?
Yes, SQL Server 2000/2005 (also MSDE) can utilize both XPath and XML. I
believe that Oracle 9i and up can also handle XPath/XML. Other RDBMSs
that can: FireBird (1.3x and above), MySql(limited support in the 4.x.x
versions).

Can you do XPath queries on a DB?
"Wilco Bauwer" <wi****@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

If you insist, you could have a text parameter in your stored
procedure. You could pass the xml as text, and in the SP use OPENXML to
intepret the text as xml. At that point, you can do xpath queries, etc.

[Wilco Bauwer]

Lookup OPENXML in the documentation of SQL server.

[JIM.H.]

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." <JI**@discussions.microsoft.com> wrote in message
news:00**********************************@microsof t.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.

[Scott M.]

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." <JI**@discussions.microsoft.com> wrote in message
news:6A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
news:00**********************************@microsof t.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.
>

[JIM.H.]

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." <JI**@discussions.microsoft.com> wrote in message
news:6A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
news:00**********************************@microsof t.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.
>

[Scott M.]

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." <JI**@discussions.microsoft.com> wrote in message
news:7A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
news:6A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
>> news:00**********************************@microsof t.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.
>> >
>>
>>
>>

[Sudhanshu Mishra]

Can I send a dataset as a parameter in an stored procedure.
Regards,
Sudhanshu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[JIM.H.]

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." <JI**@discussions.microsoft.com> wrote in message
news:7A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
news:6A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
>> news:00**********************************@microsof t.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.
>> >
>>
>>
>>

[Scott M.]

Actually, you'd need to write the CommandText for the DataAdapter's
InsertCommand, not the UpdateCommand (sorry about that). Yes, your Insert
statement would look something like what you've written (INSERT INTO
TableDest () VALUES ()).

You could just use a CommandBuilder object to generate this for you:

Dim cb as New SqlClientCommandBuilder(sqlDA)

This line will cause the sqlDA's InsertCommand to be automatically generated
for you based on the SELECT command used in the instantiation of the sqlDA
in the first place.
"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:9A**********************************@microsof t.com...

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." <JI**@discussions.microsoft.com> wrote in message
news:7A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
>> news:6A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
>> >> news:00**********************************@microsof t.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.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[Scott M.]

This is the same as the original question in the thread. Please go back and
read this thread for your answer.
"Sudhanshu Mishra" <su***********@sify.com> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl...

Can I send a dataset as a parameter in an stored procedure.
Regards,
Sudhanshu
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[JIM.H.]

Scott,
You helped me on this and in the past a lot. I really appreciate it. I am
completely lost from now on. The following code does not fail but does not
update the table either. SqlClientCommandBuilder is not recognized in my
environment.

I copy-paste the code I have up to now. What it does for the moment, once
user clicked Button1, it goes to this propriatery database and fills dataset
Ds and displays it on the screen and then calls ELiveConnSQL to import Ds
into TableDest which has the same structure as sqlStr select string. Can you
complete ELiveConnSQL function?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim sqlStr As String
sqlStr = "SELECT pate_id as PtID,date as ADate, time as ATime, name
as PtName FROM TableSrc WHERE date ='20050222' AND Loc='0A1'"

ELiveConn(sqlStr)
End Sub

Private Sub ELiveConn(ByVal sqlStr As String)

Dim rowNum As Integer
Dim colNum As Integer
Dim retValue As String

Dim strConn As String = "Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=\\TServer\Test;Exc lusive=No" ' working,

Dim Conn As OdbcConnection = New OdbcConnection(strConn)
Try
Conn.Open()
Catch e As System.Exception
Response.Write("Connection has failed: " & strConn)
Exit Sub
End Try

Dim Da As New OdbcDataAdapter
Dim Ds As New DataSet
Dim cmdSelect As New OdbcCommand
Dim i As Integer
Dim Dr As DataRow
Dim Dt As DataTable

' populate dataAdapter and dataset
cmdSelect = Conn.CreateCommand
cmdSelect.CommandText = sqlStr
Da.SelectCommand = cmdSelect
Da.Fill(Ds, "ReturnedRows")
rowNum = Ds.Tables(0).Rows.Count()
colNum = Ds.Tables(0).Columns.Count()
Response.Write("Row Count: " & rowNum & " Column Count: " & colNum)
' populate datagrid
DataGrid1.DataSource = Ds
DataGrid1.DataMember = "ReturnedRows"
DataGrid1.DataBind()

retValue = ELiveConnSQL(Me.SqlConnection1.ConnectionString, sqlStr,
Ds)

' close connection
Da.Dispose()
Conn.Close()
End Sub

Private Function ELiveConnSQL(ByVal myConnStr As String, ByVal mySQLStr
As String, ByVal myDs As DataSet) As String
Dim myConn As SqlConnection
Dim myDA As SqlDataAdapter
Dim retValue As String

myConn = New SqlConnection(myConnStr)
myDA = New SqlDataAdapter(mySQLStr, myConn)
'myDA.InsertCommand.CommandText = "insert ??"

'Dim cb As New SqlClientCommandBuilder(myDA)

myDA.Update(myDs) 'Fill the DataSet with the rows returned.
retValue = "SUCCESS"

myDA.Dispose() 'Dispose of the DataAdapter.
myConn.Close() 'Close the connection.
Return retValue
End Function

Thanks you very much.
Jim.

"Scott M." wrote:

Actually, you'd need to write the CommandText for the DataAdapter's
InsertCommand, not the UpdateCommand (sorry about that). Yes, your Insert
statement would look something like what you've written (INSERT INTO
TableDest () VALUES ()).

You could just use a CommandBuilder object to generate this for you:

Dim cb as New SqlClientCommandBuilder(sqlDA)

This line will cause the sqlDA's InsertCommand to be automatically generated
for you based on the SELECT command used in the instantiation of the sqlDA
in the first place.
"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:9A**********************************@microsof t.com...

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." <JI**@discussions.microsoft.com> wrote in message
news:7A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
>> news:6A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
>> >> news:00**********************************@microsof t.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.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[Scott M.]

Hi Jim. Make that a SQLCommandBuilder, rather than a SQLClientCommandBuilder
and see if that helps.

"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:AA**********************************@microsof t.com...

Scott,
You helped me on this and in the past a lot. I really appreciate it. I am
completely lost from now on. The following code does not fail but does not
update the table either. SqlClientCommandBuilder is not recognized in my
environment.

I copy-paste the code I have up to now. What it does for the moment, once
user clicked Button1, it goes to this propriatery database and fills
dataset
Ds and displays it on the screen and then calls ELiveConnSQL to import Ds
into TableDest which has the same structure as sqlStr select string. Can
you
complete ELiveConnSQL function?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim sqlStr As String
sqlStr = "SELECT pate_id as PtID,date as ADate, time as ATime, name
as PtName FROM TableSrc WHERE date ='20050222' AND Loc='0A1'"

ELiveConn(sqlStr)
End Sub

Private Sub ELiveConn(ByVal sqlStr As String)

Dim rowNum As Integer
Dim colNum As Integer
Dim retValue As String

Dim strConn As String = "Driver={Microsoft Visual FoxPro
Driver};SourceType=DBF;SourceDB=\\TServer\Test;Exc lusive=No" ' working,

Dim Conn As OdbcConnection = New OdbcConnection(strConn)
Try
Conn.Open()
Catch e As System.Exception
Response.Write("Connection has failed: " & strConn)
Exit Sub
End Try

Dim Da As New OdbcDataAdapter
Dim Ds As New DataSet
Dim cmdSelect As New OdbcCommand
Dim i As Integer
Dim Dr As DataRow
Dim Dt As DataTable

' populate dataAdapter and dataset
cmdSelect = Conn.CreateCommand
cmdSelect.CommandText = sqlStr
Da.SelectCommand = cmdSelect
Da.Fill(Ds, "ReturnedRows")
rowNum = Ds.Tables(0).Rows.Count()
colNum = Ds.Tables(0).Columns.Count()
Response.Write("Row Count: " & rowNum & " Column Count: " & colNum)
' populate datagrid
DataGrid1.DataSource = Ds
DataGrid1.DataMember = "ReturnedRows"
DataGrid1.DataBind()

retValue = ELiveConnSQL(Me.SqlConnection1.ConnectionString, sqlStr,
Ds)

' close connection
Da.Dispose()
Conn.Close()
End Sub

Private Function ELiveConnSQL(ByVal myConnStr As String, ByVal mySQLStr
As String, ByVal myDs As DataSet) As String
Dim myConn As SqlConnection
Dim myDA As SqlDataAdapter
Dim retValue As String

myConn = New SqlConnection(myConnStr)
myDA = New SqlDataAdapter(mySQLStr, myConn)
'myDA.InsertCommand.CommandText = "insert ??"

'Dim cb As New SqlClientCommandBuilder(myDA)

myDA.Update(myDs) 'Fill the DataSet with the rows returned.
retValue = "SUCCESS"

myDA.Dispose() 'Dispose of the DataAdapter.
myConn.Close() 'Close the connection.
Return retValue
End Function

Thanks you very much.
Jim.

"Scott M." wrote:

Actually, you'd need to write the CommandText for the DataAdapter's
InsertCommand, not the UpdateCommand (sorry about that). Yes, your Insert
statement would look something like what you've written (INSERT INTO
TableDest () VALUES ()).

You could just use a CommandBuilder object to generate this for you:

Dim cb as New SqlClientCommandBuilder(sqlDA)

This line will cause the sqlDA's InsertCommand to be automatically
generated
for you based on the SELECT command used in the instantiation of the
sqlDA
in the first place.
"JIM.H." <JI**@discussions.microsoft.com> wrote in message
news:9A**********************************@microsof t.com...

> 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." <JI**@discussions.microsoft.com> wrote in message
>> news:7A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
>> >> news:6A**********************************@microsof t.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." <JI**@discussions.microsoft.com> wrote in message
>> >> >> news:00**********************************@microsof t.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.
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>