7

How do I use the CAST in ADO to convert Float values to String?

I tried

SELECT CAST([Field] AS VARCHAR(20)) FROM ...

and

SELECT CAST([Field] AS STRING) FROM ...

and always get an OLE Exception (Unknown error).

The table column contains mixed numeric (right justified) and alphanumeric (left justified) values. If there are only alphanumeric values, the ADO query field type is String.

I am using Delphi 2009 ADO and Excel 2010.

mjn
  • 36,362
  • 28
  • 176
  • 378

3 Answers3

4

CAST is SQL-Server expression. use SELECT Field FROM...

in delphi: ADOQuery.FieldByName('Field').AsString

you cannot cast it via SQL statement.

when using mixed data types:

Read this from MSDN (A Caution about Mixed Data Types):

ADO must guess at the data type for each column in your Excel worksheet or range. (This is not affected by Excel cell formatting settings.) A serious problem can arise if you have numeric values mixed with text values in the same column. Both the Jet and the ODBC Provider return the data of the majority type, but return NULL (empty) values for the minority data type. If the two types are equally mixed in the column, the provider chooses numeric over text.

you will need to add IMEX=1 in the Extended Properties section of the connection string. the persistent field will be of TWideStringField.

The connection string should look something like this:

Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=C:\MyFile.xls;Mode=Share Deny None;Extended Properties="Excel 8.0;IMEX=1";...

kobik
  • 21,001
  • 4
  • 61
  • 121
  • The Delphi application uses persistent Fields, so the error happens when opening the result set - this means I can not use .AsString because the Open already fails – mjn Dec 16 '11 at 11:34
  • it fails even if you use a simple select? ie: "select * from $sheet1" (without casting)? and what do you mean by "persistent Fields"? – kobik Dec 16 '11 at 11:38
  • "Persistent fields" are like components, storing properties like type, size, precision, caption, etc for each table column. A simple select * will work. But if there are special requirements, these field components are there and if they have a type which does not match the actual data type, the Delphi ADO component complains and raises an exception. – mjn Dec 16 '11 at 11:45
  • It is a TStringField, so I need to cast Float to String in the SQL statement. – mjn Dec 16 '11 at 12:59
  • 1
    simply add IMEX=1 in the Extended Properties section of the connection string and it will work. the persistent fidle itself should be TWideStringField – kobik Dec 16 '11 at 13:21
  • iirc IMEX=1 is already in the connection string - I will check on Monday – mjn Dec 17 '11 at 07:56
  • sorry, no - IMEX is set to 0, with 1 other error messages appeared regarding field types for other columns. I will check if these can be solved (next week) – mjn Jan 04 '12 at 17:29
3

Perhaps using CStr would work, ie

SELECT CStr([Field]) FROM...
ain
  • 22,394
  • 3
  • 54
  • 74
  • The CSTR seems to be a valid conversion function. Now it gives another error: Field "Field" not found. I guess the CSTR function changes the column name and. So an alias field name is needed: SELECT CStr([Field]) as [Field]. Will Google for help :) – mjn Dec 16 '11 at 11:50
  • Even after casting there is still an error while opening the result set - "E_FAIL". No other information is given. I so love Excel as a data exchange format :P – mjn Dec 16 '11 at 11:56
  • Perhaps the CSTR fails if the field is already a string? Try to cast only those rows which don't have value of string type in the field, something like `SELECT iif(fieldType([field]) <> string, CStr([field]), [field]) FROM`. I don't know the right syntax off the top of my head but you get the idea... – ain Dec 16 '11 at 12:44
  • @mjn Field "Field" is not found because when you call CStr the column loses it's identifier, you need to call something like 'SELECT CStr([Field]) AS [FIELDNAMEHERE] FROM' –  Dec 16 '11 at 13:54
  • @ain: rather than `fieldType()`, I think you meant `TYPENAME()`. – onedaywhen Dec 16 '11 at 14:32
2

Try 

SELECT IIF([Field] IS NULL, NULL, CSTR([Field])) AS [Field]
  FROM ...
onedaywhen
  • 55,269
  • 12
  • 100
  • 138