[RESOLVED] Combine columns in SQL Query result tried CAST

[elmnas]

Hello People,

I am trying to combine different database columns in my query result.
my issue is the result contains both digits and also numerics

I know I there are few methods you use:

CAST
CONVERT.

Here is my code:

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

Here is an example of the query

SELECT tt.ordernr 'Ordernr' FROM [myDB].dbo.OpusOrder as tt INNER JOIN[Teknotrans_dev].dbo.CompanyMain as c On tt.bolagsnr = c.id INNER JOIN[Teknotrans_dev].dbo.OpusOrderrow as ord On ord.ordernr = tt.ordernr INNER JOIN[Teknotrans_dev].dbo.PostIt as PostIt On PostIt.ordernr = tt.ordernr INNER JOIN[Teknotrans_dev].dbo.OrderVolvoLanguageName as snSrc ON ord.kallspraknr = snSrc.spraknr INNER JOIN[Teknotrans_dev].dbo.OrderVolvoLanguageName as snTrg ON ord.malspraknr = snTrg.spraknr
union
SELECT CAST(PostIt.Text as int) FROM [myDB].dbo.OpusOrder as tt INNER JOIN[Teknotrans_dev].dbo.CompanyMain as c On tt.bolagsnr = c.id INNER JOIN[Teknotrans_dev].dbo.OpusOrderrow as ord On ord.ordernr = tt.ordernr INNER JOIN[Teknotrans_dev].dbo.PostIt as PostIt On PostIt.ordernr = tt.ordernr INNER JOIN[Teknotrans_dev].dbo.OrderVolvoLanguageName as snSrc ON ord.kallspraknr = snSrc.spraknr INNER JOIN[Teknotrans_dev].dbo.OrderVolvoLanguageName as snTrg ON ord.malspraknr = snTrg.spraknr

Error 245: Conversion failed when converting the nvarchar value 'Contains the new texts which have been published since 1900.' to data type int.

Could someone help me?

Thank you in advance.

[si_the_geek]

If something contains text (such as "Contains the new...") then it cannot be converted to a non-text format (such as Int, which only accepts a whole number).

On the other hand, all data types (including Int and Date etc) can be converted to char/text based.

As such, what you should do is cast/convert the numeric value to varchar (or another text based data type).

[elmnas]

If something contains text (such as "Contains the new...") then it cannot be converted to a non-text format (such as Int, which only accepts a whole number).

On the other hand, all data types (including Int and Date etc) can be converted to char/text based.

As such, what you should do is cast/convert the numeric value to varchar (or another text based data type).

the problem is the numerics can be anywhere in the result on each row, its not a pattern... does it still work?

I tried to use CAST could you provide me with an working example?

here is one I tried with


Thank you in advance

[elmnas]

I got it worked by use

"CAST (tt.OrderNr AS varchar(10)) + '----' + PostIt.Text AS 'New Column' " & _

Thank you