SQL Server 2005 - fieldname alias issue (datatypes)

[rw409168]

Greetings,

I have the following vb.net code

Using populateRoomsAdaptor = New SqlDataAdapter("Select r.RoomID, r.RoomID & ' - ' & r.Location & ' - ' & r.Facilities as roomLookup from Rooms r", conn)

This errors with

SQL Exception - Conversion failed converting the nvarchar value 'Meeting Room1' to data type int.

This I believe is because roomID is an int whereas the other fields are nvarchar.

If I remove roomID for the alias field the it runs without error.

Can I create an alias field containing differing datatypes, if so how?

Thanks
Rob

 

[gmmastros]

You can convert integers to varchars. Also, you should use the + operator instead of &.

Using populateRoomsAdaptor = New SqlDataAdapter("Select r.RoomID, Convert(nVarChar(20), r.RoomID) + ' - ' + r.Location + ' - ' + r.Facilities as roomLookup from Rooms r", conn)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rw409168]

Just found this out but used Cast instead.

Using populateRoomsAdaptor = New SqlDataAdapter("Select r.RoomID, Cast(r.RoomID AS varchar) + ' - ' + r.Location + ' - ' + r.Facilities roomLookup from Rooms r", conn)

Thanks anyway, just had to step away from the problem and approach it from a different angle.

Cheers.

 

[gmmastros]

Cast is not significantly different from convert, but there is a significant difference in the way you implemented it.

Cast(r.RoomID AS [!]varchar[/!])

When you convert or cast to varchar, you should ALWAYS specify a size. In fact, whenever you use varchar, you should specify a size. Make it a habit and then never deviate from that habit. If you are using a varchar (without a size specified) as a parameter, the default size is 1 character. During cast/convert, the size is 32 characters. Clearly 32 characters is enough for an integer, but.... this can trip you up in other "hard to find" places.

I encourage you to read this:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-stored-procedure-with-nvarcha

Nevermind that it says nvarchar. nvarchar and varchar behave exactly the same for the purposes of this discussion.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rw409168]

Thanks I will give it a read, cheers again for all the other help

Will specify a size from now on, best I learn these things early as still a newbie to VB.net/Ado.net.

 

[gmmastros]

It's easier to form good habits than it is to break a bad one.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Bad habits die hard.