473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Transpose data

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
7 3576
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
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
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
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

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
How do this?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #7
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: jenny.rhodes | last post by:
Hello, Please can anyone guide me on how to transpose an access table where I have many records per id eg UserID Question Answer 1 1 a 1 2 d 1 3 ...
1
by: Jenny | last post by:
Hello, Please can anyone guide me on how to transpose an access table where I have many records per id eg UserID Question Answer 1 1 a 1 2 d
7
by: sangeetha | last post by:
Hi, i need to transpose a nx1 matrix to 1xn matrix inorder to multiply with nxn matrix in c language ... can anyone help in this coding ..the nx1 matrix is pi the data type is double *pi..this is...
8
by: Leszek Gruszka | last post by:
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 =...
3
by: Gerard Brunick | last post by:
My way is ugly. These has to be a better way. Thanks, Gerard
6
by: JayDawg | last post by:
Excel has this cool little function where you can copy data, and then paste it transposed so that which runs across the rows now runs down a colum and visa versa. Is there a way in access to...
0
by: shantanu | last post by:
I am trying to convert a macro code to c# that will copy the values of a column and paste to anather through paste special. Everything is working fine but the transpose meathod to paste the column...
4
by: pc_whocares | last post by:
My forehead is flat from pounding. I am building a DLL in VS2005 C++ for use in another software development platform. I am required to pass my array data in/out of the function via a...
5
by: jenniferhelen | last post by:
I have been searching threads for a while and found the instructions listed below many times, however when I get to step 6 and select to save, I always receive the following error, "The information...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.