我对delphi不熟悉.我用adoquery来进行数据库的操作,在执行exesql(str)时出错,提示大概为 {...class EOleEception with message '字符串''之前有未闭合引号.'} 我是这样设置adoquery1.sql的, var selsql, exportsql:string;selsql:='select * from [3050701005] union all select * from [3050701060] union all select * from [3050701048] union all select * from [3050701094] union all select * from [3050625025]'
+' union all select * from [3050701099] union all select * from [3050625091] union all select * from [3050701046] union all select * from [3050701100] union all select * from [3050625090]'
+' union all select * from [3050625027] union all select * from [3050701071]'
+' union all select * from [3050701022] union all select * from [3050701002] union all select * from [3050701039]'
+' union all select * from [3050701030] union all select * from [3061118036] union all select * from [3061118173] union all select * from [3061118079] union all select * from [3061118090]';
selsql:=concat(selsql,' union all select * from [3061118155] union all select * from [3061118050]'); //如果把这个赋值去掉则程序执行成功,把
//上面任意一个union all所在的字符串去掉也会成功.exportsql:='declare @execsql nvarchar(1000) set @execsql = ''insert into openrowset(''''MICROSOFT.JET.OLEDB.4.0'''', '''''+ filename + ''''';''''admin'''';'''''''','+
'''''select * from tblmsg'''') '+selsql+' where uptime<>'''''''' and uptime is not null and uptime>='''''+fromtime+
''''' and uptime<='''''+totime+''''''+''' exec(@execsql)';
sql.Text:=exportsql;
//这里引号较多,但已经测试过不会出错.重点要说明是,selsql中的表名在这里我写死,实际程序中我是在数据库中查出来的,所以表名可多可少,而当表名不多的时候(10来个)程序执行没有问题,如上面注释所说.当表名较多时(如30个)就会出现这个错误提示,我怀疑是不是sql.Text在赋值把exportsql后面的给截掉了,但听说string类型又是几乎无限长的,请问是什么原因,多谢多谢!! (考虑过用sql.add(),但又出现另一个莫名的错误...)
+' union all select * from [3050701099] union all select * from [3050625091] union all select * from [3050701046] union all select * from [3050701100] union all select * from [3050625090]'
+' union all select * from [3050625027] union all select * from [3050701071]'
+' union all select * from [3050701022] union all select * from [3050701002] union all select * from [3050701039]'
+' union all select * from [3050701030] union all select * from [3061118036] union all select * from [3061118173] union all select * from [3061118079] union all select * from [3061118090]';
selsql:=concat(selsql,' union all select * from [3061118155] union all select * from [3061118050]'); //如果把这个赋值去掉则程序执行成功,把
//上面任意一个union all所在的字符串去掉也会成功.exportsql:='declare @execsql nvarchar(1000) set @execsql = ''insert into openrowset(''''MICROSOFT.JET.OLEDB.4.0'''', '''''+ filename + ''''';''''admin'''';'''''''','+
'''''select * from tblmsg'''') '+selsql+' where uptime<>'''''''' and uptime is not null and uptime>='''''+fromtime+
''''' and uptime<='''''+totime+''''''+''' exec(@execsql)';
sql.Text:=exportsql;
//这里引号较多,但已经测试过不会出错.重点要说明是,selsql中的表名在这里我写死,实际程序中我是在数据库中查出来的,所以表名可多可少,而当表名不多的时候(10来个)程序执行没有问题,如上面注释所说.当表名较多时(如30个)就会出现这个错误提示,我怀疑是不是sql.Text在赋值把exportsql后面的给截掉了,但听说string类型又是几乎无限长的,请问是什么原因,多谢多谢!! (考虑过用sql.add(),但又出现另一个莫名的错误...)
是很乱(还有理乱的:)).问题是selsql太长才出错.查询分析器我试一下先.
union all select...后就可以执行, 请教了!
declare @execsql nvarchar(1000)
set @execsql = 'insert into openrowset(''MICROSOFT.JET.OLEDB.4.0'',
''D:\data\20071gpsmz.mdb'';''admin'';'''',''select * from tblUpMsg'') select * from [3050701005]
union all select * from [3050701048]
union all select * from [3050625025]
union all select * from [3050701060]
union all select * from [3050701094]
union all select * from [3050701099]
union all select * from [3050625091]
union all select * from [3050701046]
union all select * from [3050701100]
union all select * from [3050625090]
union all select * from [3050625027]
union all select * from [3050701071]
union all select * from [3050701022]
union all select * from [3050701002]
union all select * from [3061118036]
union all select * from [3061118173]
union all select * from [3050701039]
union all select * from [3050701030]
union all select * from [3061118079]
union all select * from [3061118090]
union all select * from [3061118155]
union all select * from [3061118050]
where uptime<>'''' and uptime is not null and uptime>=''2007-1-1 0:00:00'' and uptime<=''2007-1-31 23:59:59'''
exec(@execsql)
exportsql:='declare @execsql nvarchar(1000) set @execsql = ''insert into openrowset(''''MICROSOFT.JET.OLEDB.4.0'''', '''''+ filename + ''''';''''admin'''';'''''''','+
'''''select * from tblmsg'''') '+selsql+' where uptime<>'''''''' and uptime is not null and uptime>='''''+fromtime+
''''' and uptime<='''''+totime+''''''+''' exec(@execsql)';
sql.Text:=exportsql;
使用adocommand
ADOCommand.CommandType:= cmdText;
ADOCommand.CommandText:=exportsql;
ADOCommand.Execute;
谢谢各位啊,原因是@execsql定小了,怪不得selsql短时就行,都怪自己一直没认真看这句,好像以前也犯过这样的错,哈哈.
我用adoquery先,adocommand以后再说.单引号是配对的.在分析器里比较容易发现错误.