By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,579 Members | 1,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,579 IT Pros & Developers. It's quick & easy.

How to transpose data for databse?

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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" <le******@poczta.onet.pl> wrote in message
news:ck**********@81.210.18.210...
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

Nov 21 '05 #2

P: n/a
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("A1:D3").Select
Range("D3").Activate
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
End Sub
Użytkownik "One Handed Man ( OHM - Terry Burns )" <news.microsoft.com>
napisał w wiadomości news:uJ*************@tk2msftngp13.phx.gbl...
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" <le******@poczta.onet.pl> wrote in message
news:ck**********@81.210.18.210...
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


Nov 21 '05 #3

P: n/a
Leszek,

See this one I once made for Chris,

http://groups.google.com/groups?selm...TNGP11.phx.gbl

I hope this helps?

Cor
Nov 21 '05 #4

P: n/a
How paste this code?
I think that make me a lot of problems... :(
Could you help me?

Użytkownik "Cor Ligthert" <no************@planet.nl> napisał w wiadomości
news:ed**************@TK2MSFTNGP15.phx.gbl...
Leszek,

See this one I once made for Chris,

http://groups.google.com/groups?selm...TNGP11.phx.gbl
I hope this helps?

Cor

Nov 21 '05 #5

P: n/a
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" <le******@poczta.onet.pl> schreef in bericht
news:ck**********@81.210.18.210...
How paste this code?
I think that make me a lot of problems... :(
Could you help me?

Użytkownik "Cor Ligthert" <no************@planet.nl> napisał w wiadomości
news:ed**************@TK2MSFTNGP15.phx.gbl...
Leszek,

See this one I once made for Chris,

http://groups.google.com/groups?selm...TNGP11.phx.gbl

I hope this helps?

Cor


Nov 21 '05 #6

P: n/a
What must be as dt parameter?
(private function tranposedatatable(byval dt as datatable) as datatable)?

Użytkownik "Cor Ligthert" <no************@planet.nl> napisał w wiadomości
news:u8*************@TK2MSFTNGP14.phx.gbl...
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" <le******@poczta.onet.pl> schreef in bericht
news:ck**********@81.210.18.210...
How paste this code?
I think that make me a lot of problems... :(
Could you help me?

Użytkownik "Cor Ligthert" <no************@planet.nl> napisał w wiadomości
news:ed**************@TK2MSFTNGP15.phx.gbl...
Leszek,

See this one I once made for Chris,

http://groups.google.com/groups?selm...TNGP11.phx.gbl

I hope this helps?

Cor



Nov 21 '05 #7

P: n/a
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
Nov 21 '05 #8

P: n/a
Thanks! :)
I think i will have a chance to finish it :)
Tomorrow i must finish it :)
Użytkownik "Cor Ligthert" <no************@planet.nl> napisał w wiadomości
news:u5***************@tk2msftngp13.phx.gbl...
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

Nov 21 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.