convert any data to string?

[dejswa]

Yes, BTW, streaming the .csv data works as expected.

But I have another question while we are on the subject. Perhaps there is a simple answer.

I am creating a Sub that takes a table or view name and then sends out the .csv data. CSV doesn't know or care about the data type and all the data is, of course, in text format. Is there a function that reliably will convert any returned data types to string and perhaps return an empty string when it encounters a NULL. At first I tried the .string method but found that it doesn't function properly with integer data.

Here is a excerpt from my subroutine:

' fname is name to send to the client; ncol is the number of columns, and the view name is also sent but you don't see it here

Dim rstr as String = ""
Response.Clear()
Response.ContentType = "application/csv"Response.AddHeader("content-disposition", "attachement; filename=" & fname)
Do While (myReader.Read())
For I = 0 To ncol-1
If I = 0 Then
rstr = "'" & myReader.Item(0).ToString
Else
rstr += "','" & myReader.Item(I).ToString
End If
Next
rstr += "'" + Chr(13)
Response.Write(rstr)
Loop
Response.End()

This is the design of the view that is queried:

select 'id' AS `id`,'name' AS `name`,'num' AS `num`
union
select `junk2`.`id` AS `id`,
`junk2`.`fruit` AS `fruit`,
`junk2`.`num` AS `num`
from `junk2`

This is what the view looks like:

id name num
1 apple 123
2 pear 456
3 orange 789

FYI, the data types for the 3 columns in table 'junk2' are
integer, varchar(16), integer
I know I could retrieve column names from the info schema but it is easier and practical for me to just design views and UNION the column names.

Perhaps I should cast the data types to character within the view??
Anyway, here is the problem . . .

This is what the subroutine writes:

'System.Byte[]','name','System.Byte[]'
'System.Byte[]','apple','System.Byte[]'
'System.Byte[]','pear','System.Byte[]'
'System.Byte[]','orange','System.Byte[]'

So, the .string method doesn't convert the integer data. In addition, tried just using the '+' or '&' operator hoping it was overloaded to concatenate numeric data, but both give errors. Also tried concatenation, which did not work.

 

[dejswa]

FYI, I changed by view design to implement casting the integers to char(32) and it works

select cast(`junk2`.`id` as char(32)) AS `id`, etc

However, I would still like to know the answer to the above if you can make a suggestion to convert to string in my routine regardless of data type (integer, date, boolean, . . .)

 

[JohnH]

I split the thread as this is a different topic. Generally all values of primitive types can be converted to String using the ToString method. A byte array is not a primitive type, and it seems weird a integer field would return this kind of data, perhaps another forum user knows more about that. You're referring to a 'myReader' and I'm guessing it is some kind of IDataReader, this has a IsDBNull method that can be used to check if a field is DBNull.