Click here to Skip to main content
15,936,903 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello everyone,

I have made a SQL Query string that gather data from two different columns in a database (column "ordernr" and column "CM") actually I would like to combine the columns to one from the query but I didn't succeed I tried a couple of different ways.

that is why I have created a data set where I put the query result, my issue I have still got 2 different columns.

I have written an example loop that loops through first row and first object then next object next column same row and then so on..
(see in code below)

now I need a function that combine the two objects same row into one string. and for all the rows put them in a table.

example:

this is what I get:
Column "ordernr" column "CM"
1000 this is an example test

this is what I want:

(new table called "test")

1000 ---- this is an example test


Could someone help me?


here is my code so far:

VB
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

       Dim da As SqlDataAdapter
       Dim SQLStr As String
       Dim cnn As SqlConnection
       DataGridView1.DataSource = Nothing
       cnn = New SqlConnection(connectionString)
       cnn.Open()

       'Query för alla kolumner
       SQLStr = "SELECT " & _
       "tt.ordernr 'Ordernr'," & _
       "PostIt.Text as 'CM'" & _
       "FROM [Teknotrans_dev].dbo.OpusOrder as tt INNER JOIN" & _
       "[MyDB].dbo.CompanyMain as c On tt.bolagsnr = c.id INNER JOIN" & _
       "[MyDB].dbo.OpusOrderrow as ord On ord.ordernr = tt.ordernr INNER JOIN" & _
       "[MyDB].dbo.PostIt as PostIt On PostIt.ordernr = tt.ordernr INNER JOIN" & _
       "[MyDB].dbo.OrderVolvoLanguageName as snSrc ON ord.kallspraknr = snSrc.spraknr INNER JOIN" & _
       "[MyDB].dbo.OrderVolvoLanguageName as snTrg ON ord.malspraknr = snTrg.spraknr"
       da = New SqlDataAdapter(SQLStr, TTCon)
       ds2 = New DataSet
       da.Fill(ds2)

       DataGridView2.DataSource = ds2.Tables(0)
       Console.WriteLine(SQLStr)

       For Each Row As DataRow In ds2.Tables(0).Rows
           For Each Coll As DataColumn In ds2.Tables(0).Columns
               Dim s As String = Row(Coll.ColumnName).ToString()

               MsgBox(s)

           Next
       Next

   End Sub



Could someone help me?


Thank you in advance
Posted
Updated 25-Feb-16 23:14pm
Comments
Basmeh Awad 13-Jan-16 9:07am    
did you try to concatenate them? "SELECT TT.ORDERNR + ' ' + PostIt.Text FROM...."
this will combine them
Daniel Elmnas 13-Jan-16 9:38am    
I have already tried that.

Error 245: Conversion failed when converting the nvarchar value 'my example text 12338' to data type int.
Nigam,Ashish 26-Feb-16 5:11am    
you Need to Convert ordernr field in varchar/char type. You can use like

"SELECT convert(varchar(100),TT.ORDERNR) + ' ' + PostIt.Text FROM"

Ashish Nigam
Daniel Elmnas 13-Jan-16 9:39am    
The problem its both digits and also numerics

1 solution

You Have to Convert ordernr field in varchar/char type. You can use like:

SELECT convert(varchar(100),TT.ORDERNR) +''+ PostIt.Text FROM ;

Ashish Nigam
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900