How to transpose data for databse?

[Leszek Gruszka]

I wrote code, that fill TableA with records by executenonquery.
But i want to have second TableB, that will be transposed TableA.
My code:

Public Sub SQL_Wpis()
Dim sSQL As String
sSQL = "SELECT * FROM KanaSecRep"
Dim objConn As New SqlConnection(sConnection)
Dim objDataAdapter As New SqlDataAdapter(sSQL, objConn)
Dim objDS As New DataSet("KA_STAN")
Dim objInsertCommand As New SqlCommand
Dim sInsertSQL As String
Dim objParam As SqlParameter
If objConn.State = ConnectionState.Open Then
Try
objDataAdapter.MissingSchemaAction() = MissingSchemaAction.AddWithKey
objDataAdapter.Fill(objDS, "KanaSecRep")
objConn.Close()
Dim objTable As DataTable
objTable = objDS.Tables("KanaSecRep")
Dim drRows As DataRowCollection
Dim objCurrentRow As DataRow
drRows = objTable.Rows
objConn.Open()
objDataAdapter.Update(objDS, "KanaSecRep")
Catch myException As System.Exception
Console.WriteLine(myException.Message)
End Try
Console.Write("Koniec")
End If
End Sub

 

[One Handed Man \( OHM - Terry Burns \)]

It is still not clear what you mean by 'Transposed', do you mean you want
'Exactly' the same data into a new 'TableB' at the same time as you fill
'TableA'. Or do you mean something else, please explain more clearly.

--
OHM ( Terry Burns ) * Use the following to email me *

Dim ch() As Char = "ufssz/cvsotAhsfbuTpmvujpotXjui/OFU".ToCharArray()
For i As Int32 = 0 To ch.Length - 1
ch(i) = Convert.ToChar(Convert.ToInt16(ch(i)) - 1)
Next
Process.Start("mailto:" & New String(ch))

 

[Leszek Gruszka]

I'm trying to fill TableA with transposed records that are write to TableA.
It's the same data, but transposed. (rows/columns)

My code write into TableA:
Station | Data | Parameter1 | Parameter2 | etc
-------------------------------------------------------
station1 | 22/01/2004 | 1 | 2 | etc
station2 | 23/01/2003 | 0 | 1 | etc

I want in TableB:
Station1 | Station 2 |
-----------------------------------------------------------
(row for Data) 22/01/2004 | 23/01/2003 |
(row for parameter1) 1 | 0 |
(row for parameter2) 2 | 1 |

I have ready SQL SELECT for getting only newest data for every station, so
data isn't problem.
Only transposing....


Maybe is a chance to use Excel function from VB 6.0? (example)
Sub Makro1()
Makro1 Makro
Range("A13").Select
Range("D3").Activate
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub

 

[Cor Ligthert]

Leszek,

See this one I once made for Chris,

http://groups.google.com/[email protected]

I hope this helps?

Cor

 

[Leszek Gruszka]

How paste this code?
I think that make me a lot of problems...
Could you help me?

 

[Cor Ligthert]

Leszek,

What you mean, I copied it from the webpage and than made this from it.
Have you problems with that?

\\\
private function tranposedatatable(byval dt as datatable) as datatable
Dim dtnew As New DataTable
For i As Integer = 0 To dt.Rows.Count - 1
dtnew.Columns.Add(i.ToString)
Next
For i As Integer = 0 To dt.Columns.Count - 1
Dim dr As DataRow = dtnew.NewRow
dtnew.Rows.Add(dr)
Next
For i As Integer = 0 To dt.Rows.Count - 1
For j As Integer = 0 To dt.Columns.Count - 1
dtnew.Rows(i).item(j) = _
dt.Rows(j).Item(i).tostring
Next
Next
Return dtnew
end function
///

I hope this helps?

Cor

 

[Leszek Gruszka]

What must be as dt parameter?
(private function tranposedatatable(byval dt as datatable) as datatable)?

 

[Cor Ligthert]

Leszek,

objDS.tables(0) what is the same in your program as objTable what is the
same in your program as objDS.tables("KanaSecRep") so take the one you want.

What must be as dt parameter?
(private function tranposedatatable(byval dt as datatable) as datatable)?

I hope this helps?

Cor

 

[Leszek Gruszka]

Thanks!
I think i will have a chance to finish it
Tomorrow i must finish it