Translating Columns As Rows In a DataSet

[Jumping Matt Flash]

The code i'm writing is using VB .NET and is for a web service returning a
dataset, which is in turn to be used by an ASP .NET application displaying a
datagrid.

I'm currently populating a datagrid using a "select top 1 column 1, column2,
column3 from tblTable" statement. As there will only ever be one row
returned I want to be able to to switch the column names to be row1 and the
column values to be row 2

i.e. select house, street, state from table addresses

16, Washington Way, NYC

to be displayed as

ELEM VAL
------- -------
House 16
Street Washington Way
State NYC


To make this translation easiest i want to be able to do this using my web
service. Any ideas as to how i can manipulate my dataset in such a way?

TIA,
Matt

 

[Ken Tucker [MVP]]

Hi,

http://www.windowsformsdatagridhelp.com/default.aspx?ID=28f49f7c-ec40-4472-9b28-2b9e762316ab

Ken
------------------
The code i'm writing is using VB .NET and is for a web service returning a
dataset, which is in turn to be used by an ASP .NET application displaying a
datagrid.

I'm currently populating a datagrid using a "select top 1 column 1, column2,
column3 from tblTable" statement. As there will only ever be one row
returned I want to be able to to switch the column names to be row1 and the
column values to be row 2

i.e. select house, street, state from table addresses

16, Washington Way, NYC

to be displayed as

ELEM VAL
------- -------
House 16
Street Washington Way
State NYC


To make this translation easiest i want to be able to do this using my web
service. Any ideas as to how i can manipulate my dataset in such a way?

TIA,
Matt

 

[Jumping Matt Flash]

Many thanks Ken,

However i've continued to have issues with your code for some reason. As i'm
retrieving only the one row i've tried using the following: Draw is a web
service which returns a dataset according to inString

Dim dataTables = Accessor.Draw(inString)
Dim dt2 As New DataTable("Translated")
dataTables.Tables.Add(dt2)

'Loop table(0) translating the data
Dim dr As DataRow = dataTables.Tables(1).NewRow
For y As Integer = 0 To dataTables.Tables(0).Columns.Count - 1
dr(y) = dataTables.Tables(0).Rows(0).Item(y)
Next
dataTables.Tables(1).Rows.Add(dr)

This just returns the following error however:

Exception Details: System.IndexOutOfRangeException: Cannot find column 0.

Source Error:

Line 34: Dim dr As DataRow = dataTables.Tables(1).NewRow
Line 35: For y As Integer = 0 To
dataTables.Tables(0).Columns.Count - 1
Line 36: dr(y) = dataTables.Tables(0).Rows(0).Item(y)
Line 37: Next
Line 38: dataTables.Tables(1).Rows.Add(dr)


TIA

Matt

 

[Ken Tucker [MVP]]

Hi,

I am not sure why you are getting the error. However I would stop
using late binding it might help find your error.

Dim dataTables as dataset = Accessor.Draw(inString)


Ken
-----------------
Many thanks Ken,

However i've continued to have issues with your code for some reason. As i'm
retrieving only the one row i've tried using the following: Draw is a web
service which returns a dataset according to inString

Dim dataTables = Accessor.Draw(inString)
Dim dt2 As New DataTable("Translated")
dataTables.Tables.Add(dt2)

'Loop table(0) translating the data
Dim dr As DataRow = dataTables.Tables(1).NewRow
For y As Integer = 0 To dataTables.Tables(0).Columns.Count - 1
dr(y) = dataTables.Tables(0).Rows(0).Item(y)
Next
dataTables.Tables(1).Rows.Add(dr)

This just returns the following error however:

Exception Details: System.IndexOutOfRangeException: Cannot find column 0.

Source Error:

Line 34: Dim dr As DataRow = dataTables.Tables(1).NewRow
Line 35: For y As Integer = 0 To
dataTables.Tables(0).Columns.Count - 1
Line 36: dr(y) = dataTables.Tables(0).Rows(0).Item(y)
Line 37: Next
Line 38: dataTables.Tables(1).Rows.Add(dr)


TIA

Matt

 

[Phill. W]

The code i'm writing is using VB .NET and is for a web service
returning a dataset, which is in turn to be used by an ASP .NET
application displaying a datagrid.

From the little I know of WebServices, returning a DataSet is a
Bad Idea. Web Services are meant for cross-platform interoperability
(can hardly type it, let alone pronounce it!), and only .Net clients can
seriously make use of the amorphous DataSet.
Anyhow ...

I'm currently populating a datagrid using a "select top 1 column 1,
column2, column3 from tblTable" statement. As there will only ever
be one row returned I want to be able to to switch the column names
to be row1 and the column values to be row 2

This really shouldn't be an issue for the WebService.
Return your DataSet with one row and, containing the data, and pull
the item "names" out of the Column Names. Let the Presentation layer
(the ASP.Net thing) get all het up about how to display the thing.

If you make the WebService too complicated, you'll kick yourself
when you start trying to pass the same data back in again and have
to "unpick" all your meticulous rearranging.

Regards,
Phill W.

 

[Cor Ligthert [MVP]]

Phill,

From the little I know of WebServices, returning a DataSet is a
Bad Idea.

In my opinion only true as the next rule is true as well.

Web Services are meant for cross-platform interoperability
(can hardly type it, let alone pronounce it!), and only .Net clients can
seriously make use of the amorphous DataSet.

However when we tell that we are talking about .Net programs, than there is
again cross-platform interoperability.

A dataset is very nice to use in a webservice you know.

Just my thought.

Cor

 

[Cor Ligthert [MVP]]

Jumping Matt

Although it is possible in the an aspnet application to use a webservice,
are you in my idea overdoing it.

If you use a webservice from somebody else it has a lot of sense. However
now are you everytime visiting your webserver to get information, that you
can get direct. In my opinion a little bit inefficient operation that only
creates more clients.

However just my thought,

Cor