[RESOLVED] Combine columns in SQL Query result tried CAST

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:

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

Code:

---


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.

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

Quote:

---

Originally Posted by 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).

---

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

I got it worked by use

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

Thank you