VBNET2008 SQLSERVER 2000 SQL string

Hullo Good Guys, :wave:
I need your help. Please help me.

I am using VBNET2008 and SQL SERVER 2000.
I tried to create this SQL string to retrieve CustomerID and a CustomerID and Name together but it's not working

Here are the SQL String that is not working.
Dim strsql As String = ""
strsql &= "Select CustomerID, "
strsql &= " ( CustomerID & " - " & CompanyName ) as [Companyname]"
strsql &= " From TestCustomers Order by CompanyName"

This is the error message
Conversion from string "Select CustomerID, CustomerID & " to type 'Double' is not valid.

Cause by this SQL String coding:
strsql &= " CustomerID & " - " & CompanyName as [Companyname]"

I am using the NORTHWIND DatacBase in SQL SERVER 2000 and the table is Customers

Look at this part:

Code:

---

" ( CustomerID & " - " & CompanyName ) as [Companyname]"

---

Does that look like a valid String to you?

that's because after your ampersand, you drop out of the string and then have a - ... which means subtract... so it tries to convert your string to a double to perform the arithmetic operation... but since the string can't be converted to a double, the error results.

Going out on a limb, I'm going to guess that what you're trying to do is concatenate the ID with the company name with a - in the middle, right?

Try this:

Code:

---

strsql &= " ( CustomerID & ' - ' & CompanyName ) as [Companyname]"

---

changed the " to ' around the -

-tg

Quote:

---

Originally Posted by techgnome

that's because after your ampersand, you drop out of the string and then have a - ... which means subtract... so it tries to convert your string to a double to perform the arithmetic operation... but since the string can't be converted to a double, the error results.

Going out on a limb, I'm going to guess that what you're trying to do is concatenate the ID with the company name with a - in the middle, right?

Try this:

Code:

---

strsql &= " ( CustomerID & ' - ' & CompanyName ) as [Companyname]"

---

changed the " to ' around the -

-tg

---

Also, SQL Server doesn't use & to concatenate strings. That's a VB thing. T-SQL uses the more usual +. & is a bitwise AND.

Jmcilhinney,
You suggestion of sample code: strsql &= " ( CustomerID & ' - ' & CompanyName ) as [Companyname]"
is not the way that I requested as per my posting earlier.

This is what I wanted as per Business Analyst specficatino and requirements:
strsql &= "CustomerID,"
strsql &= " ( CustomerID & ' - ' & CompanyName ) as [Companyname]"

Hang on... time out... you're saying the "wrong" solution that you don't want... is the same as the requirement... did I miss something?

-tg

techgnome
Your suggestion is similar to one from Jmcilhinney without complete SQL String that I posted.
Because of that I could not understand it. Please try to be complete.

That's because that's the only line that's the problem... just simply fix the line you have...

-tg

The line
strsql &= " ( CustomerID & " - " & CompanyName ) as [Companyname]"

should be this:
strsql &= " ( CustomerID + ' - ' + CompanyName ) as [Companyname]"

Is CustomerID as numeric or text field?