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

Transpose data

P: n/a
I wrote an aplication that write something into tableA in sql2000.
I want to write the same, but transposed into tableB.
Someone can help me?
Any example?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Well just change your INSERT statement to reflect the new table name. Is
that what your looking for ?

--
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************@kana.com.pl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
I wrote an aplication that write something into tableA in sql2000.
I want to write the same, but transposed into tableB.
Someone can help me?
Any example?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 21 '05 #2

P: n/a
Yes, but i want to make it like datafill.transpose (i know, that it
don't exists) or something like that.
But in my full aplication there will be about 50-100 columns.
I thought, that there is a way to transpose data in dataset and fill it
into other table (TableB). I created TableB with correct columns and i'm
trying to fill it. It's not qa problem, that Table or dataset will have
more columns then other one. How only fill rows?

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")

Dim sNowywpisSQLpiv = "INSERT INTO KanaSecRep" & "(Nazwastacji, DC, MS,
Data, AuditBaseObjects, ShutdownWithoutLogon" & " VALUES ('" &
strKomputer & "','" & IsDC(strKomputer) & "','" & IsMS(strKomputer) &
"','" & DateTime.Now & "','" & AuditBaseObjects(strKomputer) & "','" &
ShutdownWithoutLogon(strKomputer) & "'
Dim objNewCmd As New SqlCommand(sNowywpisSQLpiv, objConn)
objNewCmd.ExecuteNonQuery()

Catch myException As System.Exception
Console.WriteLine(myException.Message)
End Try
Console.Write("Koniec")
End If
End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #3

P: n/a
Cor's previous post on this subject ( Untested by me ), does this help

-----------------------------------------------------------------

Cor Ligthert
Hi Chris,

It is very simple call the rows col and the col rows

Something like this, (goes for every table)

I hope this helps?

Cor
\\\
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
///
--
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************@kana.com.pl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Yes, but i want to make it like datafill.transpose (i know, that it
don't exists) or something like that.
But in my full aplication there will be about 50-100 columns.
I thought, that there is a way to transpose data in dataset and fill it
into other table (TableB). I created TableB with correct columns and i'm
trying to fill it. It's not qa problem, that Table or dataset will have
more columns then other one. How only fill rows?

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")

Dim sNowywpisSQLpiv = "INSERT INTO KanaSecRep" & "(Nazwastacji, DC, MS,
Data, AuditBaseObjects, ShutdownWithoutLogon" & " VALUES ('" &
strKomputer & "','" & IsDC(strKomputer) & "','" & IsMS(strKomputer) &
"','" & DateTime.Now & "','" & AuditBaseObjects(strKomputer) & "','" &
ShutdownWithoutLogon(strKomputer) & "'
Dim objNewCmd As New SqlCommand(sNowywpisSQLpiv, objConn)
objNewCmd.ExecuteNonQuery()

Catch myException As System.Exception
Console.WriteLine(myException.Message)
End Try
Console.Write("Koniec")
End If
End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 21 '05 #4

P: n/a
Why not just use two data adapters... updating from the same dataset, just
don't accept changes after update on the first one.
"Leszek Gruszka" <le************@kana.com.pl> wrote in message
news:%2****************@TK2MSFTNGP10.phx.gbl...
Yes, but i want to make it like datafill.transpose (i know, that it
don't exists) or something like that.
But in my full aplication there will be about 50-100 columns.
I thought, that there is a way to transpose data in dataset and fill it
into other table (TableB). I created TableB with correct columns and i'm
trying to fill it. It's not qa problem, that Table or dataset will have
more columns then other one. How only fill rows?

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")

Dim sNowywpisSQLpiv = "INSERT INTO KanaSecRep" & "(Nazwastacji, DC, MS,
Data, AuditBaseObjects, ShutdownWithoutLogon" & " VALUES ('" &
strKomputer & "','" & IsDC(strKomputer) & "','" & IsMS(strKomputer) &
"','" & DateTime.Now & "','" & AuditBaseObjects(strKomputer) & "','" &
ShutdownWithoutLogon(strKomputer) & "'
Dim objNewCmd As New SqlCommand(sNowywpisSQLpiv, objConn)
objNewCmd.ExecuteNonQuery()

Catch myException As System.Exception
Console.WriteLine(myException.Message)
End Try
Console.Write("Koniec")
End If
End Sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 21 '05 #5

P: n/a

Could you help me how put it into my code?
I used samples and wrote my code, but i don't know where put your
example.. :(

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #6

P: n/a
How do this?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #7

P: n/a
How are you updating your data now?

or doing your inserts that is... using a self written command? or something
like a SQL data adapter to do the work for you?

"Leszek Gruszka" <le************@kana.com.pl> wrote in message
news:uU****************@TK2MSFTNGP10.phx.gbl...
How do this?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 21 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.