Delphi 中的 SQL 语句 如何使用 引号？ ( 积分: 50 )

//ADOConnection1连接一个文本文件
ADOConnection1.Connected := False;
ADOConnection1.ConnectionString := ' Provider=Microsoft.Jet.OLEDB.4.0;'
+ ' Data Source=D:/Projects/Tmp;'
+ ' Extended Properties=Text;'
+ ' Persist Security Info=False';
ADOConnection1.LoginPrompt := False;
ADOConnection1.Connected := True;
ShowMessage('连接成功！');

//下面的语句将文本文件导入 SQL Server，可通过；
ADOConnection1.Execute(' SELECT Company, '
+' SUM(Amount) AS EAmount,'
+' SUM(Amount) AS IAmount'
+' INTO tmpTable1 IN [ODBC]'
+' [ODBC;Driver=SQL Server;UID=sa;PWD=;'
+' Server=192.168.0.2;DataBase=vv;]'
+' FROM SD0509.txt'
+' GROUP BY Company');
ShowMessage('导入成功！');

//下面的语句将文本文件导入 SQL Server，不能通过；
ADOConnection1.Execute(' SELECT Company, '
+' SUM(case IE when ''0'' then Amount else 0 end) AS EAmount,'
+' SUM(case IE when ''1'' then Amount else 0 end) AS IAmount'
+' INTO tmpTable2 IN [ODBC]'
+' [ODBC;Driver=SQL Server;UID=sa;PWD=;'
+' Server=192.168.0.2;DataBase=vv;]'
+' FROM SD0509.txt'
+' GROUP BY Company');
ShowMessage('导入成功！');

//ADOConnection1连接一个 SQL Server 数据库;
ADOConnection2.Connected := False;
ADOConnection2.ConnectionString := 'Provider=SQLOLEDB.1;'
+ 'Persist Security Info=False;'
+ 'User ID=sa;'
+ 'Initial Catalog=vv;'
+ 'Data Source=192.168.0.2';
ADOConnection2.LoginPrompt := False;
ADOConnection2.Connected := True;
ShowMessage('连接成功！');

//下面的查询可通过；
ADOConnection2.Execute(' SELECT Company, '
+' SUM(case IE when ''0'' then Amount else 0 end) AS EAmount,'
+' SUM(case IE when ''1'' then Amount else 0 end) AS IAmount'
+' FROM Business'
+' GROUP BY Company');
ShowMessage('导入成功！');

//下面的查询不能通过；
ADOConnection2.Execute(' SELECT Company, '
+' SUM(case IE when ''0'' then Amount else 0 end) AS EAmount,'
+' SUM(case IE when ''1'' then Amount else 0 end) AS IAmount'
+' INTO tmpTable3'
+' FROM Business'
+' GROUP BY Company');
ShowMessage('导入成功！');

请各位富翁指点：
1、为什么第二条加入SUM(case...when...then..else..end)，语句就不能通过呢？
2、但是直接查询 SQL Server 则可以使用SUM(case...when...then..else..end)？
3、直接查询 SQL Server 为什么不能插入临时表？

//ADOConnection1连接一个文本文件
ADOConnection1.Connected := False;
ADOConnection1.ConnectionString := ' Provider=Microsoft.Jet.OLEDB.4.0;'
+ ' Data Source=D:/Projects/Tmp;'
+ ' Extended Properties=Text;'
+ ' Persist Security Info=False';
ADOConnection1.LoginPrompt := False;
ADOConnection1.Connected := True;
ShowMessage('连接成功！');

//下面的语句将文本文件导入 SQL Server，可通过；
ADOConnection1.Execute(' SELECT Company, '
+' SUM(Amount) AS EAmount,'
+' SUM(Amount) AS IAmount'
+' INTO tmpTable1 IN [ODBC]'
+' [ODBC;Driver=SQL Server;UID=sa;PWD=;'
+' Server=192.168.0.2;DataBase=vv;]'
+' FROM SD0509.txt'
+' GROUP BY Company');
ShowMessage('导入成功！');

//下面的语句将文本文件导入 SQL Server，不能通过；
ADOConnection1.Execute(' SELECT Company, '
+' SUM(case IE when ''0'' then Amount else 0 end) AS EAmount,'
+' SUM(case IE when ''1'' then Amount else 0 end) AS IAmount'
+' INTO tmpTable2 IN [ODBC]'
+' [ODBC;Driver=SQL Server;UID=sa;PWD=;'
+' Server=192.168.0.2;DataBase=vv;]'
+' FROM SD0509.txt'
+' GROUP BY Company');
ShowMessage('导入成功！');

//ADOConnection1连接一个 SQL Server 数据库;
ADOConnection2.Connected := False;
ADOConnection2.ConnectionString := 'Provider=SQLOLEDB.1;'
+ 'Persist Security Info=False;'
+ 'User ID=sa;'
+ 'Initial Catalog=vv;'
+ 'Data Source=192.168.0.2';
ADOConnection2.LoginPrompt := False;
ADOConnection2.Connected := True;
ShowMessage('连接成功！');

//下面的查询可通过；
ADOConnection2.Execute(' SELECT Company, '
+' SUM(case IE when ''0'' then Amount else 0 end) AS EAmount,'
+' SUM(case IE when ''1'' then Amount else 0 end) AS IAmount'
+' FROM Business'
+' GROUP BY Company');
ShowMessage('导入成功！');

//下面的查询不能通过；
ADOConnection2.Execute(' SELECT Company, '
+' SUM(case IE when ''0'' then Amount else 0 end) AS EAmount,'
+' SUM(case IE when ''1'' then Amount else 0 end) AS IAmount'
+' INTO tmpTable3'
+' FROM Business'
+' GROUP BY Company');
ShowMessage('导入成功！');

请各位富翁指点：
1、为什么第二条加入SUM(case...when...then..else..end)，语句就不能通过呢？
2、但是直接查询 SQL Server 则可以使用SUM(case...when...then..else..end)？
3、直接查询 SQL Server 为什么不能插入临时表？

Execute改成Open

execute是拥在delete,insert,update这几个语句中的.select要用open方法.

但是，下面的语句可以执行。
ADOConnection1.Execute(' SELECT Company, Trade,'
+' SUM(Amount) AS eeee,'
+' FROM Business'
+' GROUP BY Company, Trade');

改成
ADOConnection1.Execute(' SELECT Company, Trade,'
+' SUM(case IE when '''+'0'+''' then Amount else 0 end) AS eeee,'
+' SUM(case IE when '''+'0'+''' then Amount else 0 end) AS iiii'
+' FROM Business'
+' GROUP BY Company, Trade');

引号可以用#39表示
把sql中的引号换成#39试一下，另外不要了忘了空格

ADOConnection1.Open(' SELECT Company, Trade,'
+' SUM(case IE when '''+'0'+''' then Amount else 0 end) AS eeee,'
+' SUM(case IE when '''+'0'+''' then Amount else 0 end) AS iiii'
+' FROM Business'
+' GROUP BY Company, Trade');

ADOConnection1.Execute(' SELECT Company, Trade,'
+' SUM(case IE when ''0'' then Amount else 0 end) AS eeee,'
+' SUM(case IE when ''0'' then Amount else 0 end) AS iiii'
+' FROM Business'
+' GROUP BY Company, Trade');

楼主按照jeansonliu或dean_2008的写法都可以,
你要知道引号里面的引号要两个引号来表示的哦!

数值类型干嘛要去引号引起

楼上各位的方法都试过了，仍然返回“未指定的错误”。

你的IE是啥类型的？

上面的好像是不行啊。。。。。。
我沒有用過用ADOconnection來執行SQL語句。
試試用ADOQuery語用哦

IE 字符型。

ADOConnection1.Execute(' SELECT Company, Trade,'
+' SUM(case IE when '+QuotedStr('0')+' then Amount else 0 end) AS eeee,'
+' SUM(case IE when '+QuotedStr('0')+' then Amount else 0 end) AS iiii'
+' FROM Business'
+' GROUP BY Company, Trade');

我一直都是用QuotedStr这东西，试一下看
'SELECT * FROM tb_ename WHERE ( clo= '+QuotedStr(Text) + ') '

ADOConnection1.Execute(' SELECT Company, Trade,'
+' SUM(case IE when ''0'' then Amount else 0 end) AS eeee,'
+' SUM(case IE when ''0'' then Amount else 0 end) AS iiii'
+' FROM Business'
+' GROUP BY Company, Trade');

看看DB设置

我估计是SQL SERVER临时表的创建问题。
因为引号和查询都没有错，但从tmpTable2开始你就无法创建临时文件了。