How do I use the CAST in ADO to convert Float values to String?
如何在ADO中使用CAST将Float值转换为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).
并始终获得OLE异常（未知错误）。
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.
表列包含混合数字（右对齐）和字母数字（左对齐）值。如果只有字母数字值，则ADO查询字段类型为String。
I am using Delphi 2009 ADO and Excel 2010.
我正在使用Delphi 2009 ADO和Excel 2010。
3 个解决方案
#1
4
CAST is SQL-Server expression. use SELECT Field FROM...
CAST是SQL-Server表达式。使用SELECT Field FROM ...
in delphi: ADOQuery.FieldByName('Field').AsString
在delphi中：ADOQuery.FieldByName（'Field'）。AsString
you cannot cast it via SQL statement.
你不能通过SQL语句强制转换它。
when using mixed data types:
使用混合数据类型时：
Read this from MSDN (A Caution about Mixed Data Types):
从MSDN中读取此内容（关于混合数据类型的注意事项）：
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.
ADO必须猜测Excel工作表或范围中每列的数据类型。 （这不受Excel单元格格式设置的影响。）如果在同一列中将数值与文本值混合，则可能会出现严重问题。 Jet和ODBC提供程序都返回多数类型的数据，但返回少数数据类型的NULL（空）值。如果列中的两种类型同样混合，则提供者选择数字而不是文本。
you will need to add IMEX=1 in the Extended Properties section of the connection string. the persistent field will be of TWideStringField.
您需要在连接字符串的“扩展属性”部分中添加IMEX = 1。持久字段将是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";...
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”; .. 。
#3
1
Try
尝试
SELECT IIF([Field] IS NULL, NULL, CSTR([Field])) AS [Field]
FROM ...
#1
4
CAST is SQL-Server expression. use SELECT Field FROM...
CAST是SQL-Server表达式。使用SELECT Field FROM ...
in delphi: ADOQuery.FieldByName('Field').AsString
在delphi中：ADOQuery.FieldByName（'Field'）。AsString
you cannot cast it via SQL statement.
你不能通过SQL语句强制转换它。
when using mixed data types:
使用混合数据类型时：
Read this from MSDN (A Caution about Mixed Data Types):
从MSDN中读取此内容（关于混合数据类型的注意事项）：
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.
ADO必须猜测Excel工作表或范围中每列的数据类型。 （这不受Excel单元格格式设置的影响。）如果在同一列中将数值与文本值混合，则可能会出现严重问题。 Jet和ODBC提供程序都返回多数类型的数据，但返回少数数据类型的NULL（空）值。如果列中的两种类型同样混合，则提供者选择数字而不是文本。
you will need to add IMEX=1 in the Extended Properties section of the connection string. the persistent field will be of TWideStringField.
您需要在连接字符串的“扩展属性”部分中添加IMEX = 1。持久字段将是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";...
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”; .. 。
#2
#3
1
Try
尝试
SELECT IIF([Field] IS NULL, NULL, CSTR([Field])) AS [Field]
FROM ...