Transferring data between datatables and between datareader and datatable

microsoft.public.dotnet.languages.vb

4 Jul 2006 3:29 PM

neilr

Can anyone help with some problkems that have wasted 2 days of my
(inexperienced) time already?

We have a website which allows people to register for events like
conferences

We are importing a table of fees for the apporpriate event using an Sp:
FeesDataSet = New System.Data.DataSet
        Dim MySPcommand As New System.Data.SqlClient.SqlCommand
        MySPcommand.CommandText = "SP4105201getWebFeeTable"
        MySPcommand.CommandType = Data.CommandType.StoredProcedure
        MySPcommand.Connection =
GlobalLeague.Utilities.Functions.GetConnection(True)
        MySPcommand.Parameters.AddWithValue("@ProjectNo",
const_WarsawProjectNo)
        Me.FeesDataSet.Tables.Add("Fees")
        Me.FeesDataSet.Tables(0).Load(MySPcommand.ExecuteReader())
        MySPcommand.Connection.Close()
        MySPcommand = Nothing

So far, so good. We then need to add a couple of "discretionary fees"
if the customer selects the approriate boxes on the page. We get a
single row of data (same format but different SP) and ideally need to
add that row to the "Fees" table. I can get it into another table (same
method as above) or into a datareader, but cannot find exactly how to
add it as a row into the "Fees" table from either the new table or from
a datareader?

A little later, we use the controls on the web page to pick up the
customer's options and I am storing these by changing the (existing)
"Pick" field in the "Fees" table to 1 for the appropriate rows (ie
fees). I can then get use:
     FeesDataSet.Tables("Fees").Select("Pick = 1")
to get a set of rows which we need to charge to this customer. Ideally
I want to move these to another table ("Charges") in the same dataset,
so that we can perform various calculations and text changes on them
before printing them to his invoice. I have tried cloning the table to
set the schema for the "Charges" table to match the "Fees" table and
then import the rows, but I cannot seem to get the code right.

Can anyone help me with these 2 tasks - ie getting a row from a table
or datareader and copying it into the "Fees" table, asnd then taking a
set of selected rows from the "Fees" table and using them to create the
"Charges" table?

4 Jul 2006 4:03 PM

Cor Ligthert [MVP]

Neil,

That was very much reading and trying to understand.

Probably are you just looking for the command importrow

http://msdn2.microsoft.com/en-us/library/system.data.datatable.importrow.aspx

You cannot add a datarow to two tables, simple for the fact that the datarow
has a property which tells to what datatable it belongs

I hope this helps,

Cor

Show quoteHide quote

"neilr" <neilry***@yahoo.com> schreef in bericht
news:1152026998.786920.29750@m79g2000cwm.googlegroups.com...
> Can anyone help with some problkems that have wasted 2 days of my
> (inexperienced) time already?
>
> We have a website which allows people to register for events like
> conferences
>
> We are importing a table of fees for the apporpriate event using an Sp:
> FeesDataSet = New System.Data.DataSet
>        Dim MySPcommand As New System.Data.SqlClient.SqlCommand
>        MySPcommand.CommandText = "SP4105201getWebFeeTable"
>        MySPcommand.CommandType = Data.CommandType.StoredProcedure
>        MySPcommand.Connection =
> GlobalLeague.Utilities.Functions.GetConnection(True)
>        MySPcommand.Parameters.AddWithValue("@ProjectNo",
> const_WarsawProjectNo)
>        Me.FeesDataSet.Tables.Add("Fees")
>        Me.FeesDataSet.Tables(0).Load(MySPcommand.ExecuteReader())
>        MySPcommand.Connection.Close()
>        MySPcommand = Nothing
>
> So far, so good. We then need to add a couple of "discretionary fees"
> if the customer selects the approriate boxes on the page. We get a
> single row of data (same format but different SP) and ideally need to
> add that row to the "Fees" table. I can get it into another table (same
> method as above) or into a datareader, but cannot find exactly how to
> add it as a row into the "Fees" table from either the new table or from
> a datareader?
>
> A little later, we use the controls on the web page to pick up the
> customer's options and I am storing these by changing the (existing)
> "Pick" field in the "Fees" table to 1 for the appropriate rows (ie
> fees). I can then get use:
>     FeesDataSet.Tables("Fees").Select("Pick = 1")
> to get a set of rows which we need to charge to this customer. Ideally
> I want to move these to another table ("Charges") in the same dataset,
> so that we can perform various calculations and text changes on them
> before printing them to his invoice. I have tried cloning the table to
> set the schema for the "Charges" table to match the "Fees" table and
> then import the rows, but I cannot seem to get the code right.
>
> Can anyone help me with these 2 tasks - ie getting a row from a table
> or datareader and copying it into the "Fees" table, asnd then taking a
> set of selected rows from the "Fees" table and using them to create the
> "Charges" table?
>

4 Jul 2006 5:25 PM

neilr

Cor,
That got me started, thank you - I had tried that before but must have
got the code wrong. It now works, as follows if anyone else needs help:
        Dim ChargesTable As Data.DataTable
        ChargesTable = Me.FeesDataSet.Tables("Fees").Clone
        ChargesTable.TableName = "Charges"
        For Each ChargesRow In FeesDataSet.Tables("Fees").Select("Pick
= 1")
            ChargesTable.ImportRow(ChargesRow)
        Next
        If Not Me.FeesDataSet.Tables("Charges") Is Nothing Then
            Me.FeesDataSet.Tables.Remove("Charges")
        End If
        Me.FeesDataSet.Tables.Add(ChargesTable)

By the way, my note was long because I had read so many where novices
like me were criticised for not including their code and explaining the
problem!

Thanks again,   Neil

Cor Ligthert [MVP] wrote:
Show quoteHide quote

> Neil,
>
> That was very much reading and trying to understand.
>
> Probably are you just looking for the command importrow
>
> http://msdn2.microsoft.com/en-us/library/system.data.datatable.importrow.aspx
>
> You cannot add a datarow to two tables, simple for the fact that the datarow
> has a property which tells to what datatable it belongs
>
> I hope this helps,
>
> Cor
>
>
>
> "neilr" <neilry***@yahoo.com> schreef in bericht
> news:1152026998.786920.29750@m79g2000cwm.googlegroups.com...
> > Can anyone help with some problkems that have wasted 2 days of my
> > (inexperienced) time already?
> >
> > We have a website which allows people to register for events like
> > conferences
> >
> > We are importing a table of fees for the apporpriate event using an Sp:
> > FeesDataSet = New System.Data.DataSet
> >        Dim MySPcommand As New System.Data.SqlClient.SqlCommand
> >        MySPcommand.CommandText = "SP4105201getWebFeeTable"
> >        MySPcommand.CommandType = Data.CommandType.StoredProcedure
> >        MySPcommand.Connection =
> > GlobalLeague.Utilities.Functions.GetConnection(True)
> >        MySPcommand.Parameters.AddWithValue("@ProjectNo",
> > const_WarsawProjectNo)
> >        Me.FeesDataSet.Tables.Add("Fees")
> >        Me.FeesDataSet.Tables(0).Load(MySPcommand.ExecuteReader())
> >        MySPcommand.Connection.Close()
> >        MySPcommand = Nothing
> >
> > So far, so good. We then need to add a couple of "discretionary fees"
> > if the customer selects the approriate boxes on the page. We get a
> > single row of data (same format but different SP) and ideally need to
> > add that row to the "Fees" table. I can get it into another table (same
> > method as above) or into a datareader, but cannot find exactly how to
> > add it as a row into the "Fees" table from either the new table or from
> > a datareader?
> >
> > A little later, we use the controls on the web page to pick up the
> > customer's options and I am storing these by changing the (existing)
> > "Pick" field in the "Fees" table to 1 for the appropriate rows (ie
> > fees). I can then get use:
> >     FeesDataSet.Tables("Fees").Select("Pick = 1")
> > to get a set of rows which we need to charge to this customer. Ideally
> > I want to move these to another table ("Charges") in the same dataset,
> > so that we can perform various calculations and text changes on them
> > before printing them to his invoice. I have tried cloning the table to
> > set the schema for the "Charges" table to match the "Fees" table and
> > then import the rows, but I cannot seem to get the code right.
> >
> > Can anyone help me with these 2 tasks - ie getting a row from a table
> > or datareader and copying it into the "Fees" table, asnd then taking a
> > set of selected rows from the "Fees" table and using them to create the
> > "Charges" table?
> >