declare @str_sql varchar(8000) declare @col_name varchar(100) set @col_name='Ashare_code' set @str_sql= 'select '+@col_name+' from Basic.dbo.C1T1' exec(@str_sql)
1。 楼主,你这样写rs(name)取不到值??用的是什么语言开发前台,如果是asp,那么是可以的啊,我即兴test了一下,可以~ <%@LANGUAGE="VBSCRIPT" CODEPAGE="950"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=big5"> <title>無標題文件</title> </head><body> <% dim gScriptSQL_CnStr,cn,supplier,strSQL dim strVar,strVar1 gScriptSQL_CnStr="***** " '我的连接字串 set cn = Server.CreateObject("ADODB.Connection") cn.Open gScriptSQL_CnStr Set supplier = Server.CreateObject("ADODB.Recordset") supplier.CursorLocation = 1 'strSQL = "select top 1 AUTO_ID as xx from cs where cs_ID like '0507%' order by auto_id desc" strSQL = "select * from cs where cs_ID like '0507%' order by auto_id desc" strVar = "cs_vend" strVar1 = "cs_id" supplier.Open strSQL,cn,1,3 if not supplier.eof then response.Write supplier(strVar) & "<br>" & supplier(strVar1) end if %> </body> </html>---在荧幕上面就显示结果 B503 0507200021 我不会这样用,但是我测试是可以,如果是vb也应该可以,反正asp和vb很像~2。楼上的人说的动态sql,是可以的,但是楼主你是要前台用是吧 或许你要动态去抓取不同的,是不是这样,就在你的前台用动态sql语句 <%@LANGUAGE="VBSCRIPT" CODEPAGE="950"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=big5"> <title>無標題文件</title> </head><body> <% dim gScriptSQL_CnStr,cn,supplier,strSQL dim strVar,strVar1 gScriptSQL_CnStr="Provider=SQLOLEDB.1;Data Source=tgmis005;User ID=sa;Password=00000;Initial Catalog=wlsql" set cn = Server.CreateObject("ADODB.Connection") cn.Open gScriptSQL_CnStr Set supplier = Server.CreateObject("ADODB.Recordset") supplier.CursorLocation = 1 strVar = "cs_vend" strVar1 = "cs_id" 'strSQL = "select top 1 AUTO_ID as xx from cs where cs_ID like '0507%' order by auto_id desc" strSQL = "select " & strVar & "," & strVar1 & " from cs where cs_ID like '0507%' order by auto_id desc" 'strSQL = "select * from cs where cs_ID like '0507%' order by auto_id desc" ' response.Write strsql 'response.end
supplier.Open strSQL,cn,1,3 if not supplier.eof then response.Write supplier(strVar) & "<br>" & supplier(strVar1) end if %> </body> </html> ---在荧幕上面就显示结果 B503 0507200021 3。如果是要在sql server语句里面使用动态sql,那么就是传变量进取执行,写成sp,这样程式可读性会好很多
动态的sql语句是不是必须在存储过程里面实现?还是可以放到asp页面实现?
to MorningTea(早茶) 谢谢你写了一大堆。是用asp,前台用。我按你的再去试试。
可以,下面是asp.net的例子: System .Data .SqlClient .SqlParameter[] parm=new System .Data .SqlClient .SqlParameter [] { new SqlParameter("@tbName",SqlDbType.VarChar) }; parm[0].Value="d_order";this.DataGrid1 .DataSource =DBHelper.ExecuteDataTable("exec('select * from '+@tbName)",parm,CommandType.Text); this.DataGrid1 .DataBind();
declare @col_name varchar(100)
set @col_name='Ashare_code'
set @str_sql=
'select '+@col_name+' from Basic.dbo.C1T1'
exec(@str_sql)
<%@LANGUAGE="VBSCRIPT" CODEPAGE="950"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5">
<title>無標題文件</title>
</head><body>
<%
dim gScriptSQL_CnStr,cn,supplier,strSQL
dim strVar,strVar1
gScriptSQL_CnStr="***** " '我的连接字串
set cn = Server.CreateObject("ADODB.Connection")
cn.Open gScriptSQL_CnStr
Set supplier = Server.CreateObject("ADODB.Recordset")
supplier.CursorLocation = 1
'strSQL = "select top 1 AUTO_ID as xx from cs where cs_ID like '0507%' order by auto_id desc"
strSQL = "select * from cs where cs_ID like '0507%' order by auto_id desc"
strVar = "cs_vend"
strVar1 = "cs_id"
supplier.Open strSQL,cn,1,3
if not supplier.eof then
response.Write supplier(strVar) & "<br>" & supplier(strVar1)
end if
%>
</body>
</html>---在荧幕上面就显示结果
B503
0507200021 我不会这样用,但是我测试是可以,如果是vb也应该可以,反正asp和vb很像~2。楼上的人说的动态sql,是可以的,但是楼主你是要前台用是吧
或许你要动态去抓取不同的,是不是这样,就在你的前台用动态sql语句
<%@LANGUAGE="VBSCRIPT" CODEPAGE="950"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=big5">
<title>無標題文件</title>
</head><body>
<%
dim gScriptSQL_CnStr,cn,supplier,strSQL
dim strVar,strVar1
gScriptSQL_CnStr="Provider=SQLOLEDB.1;Data Source=tgmis005;User ID=sa;Password=00000;Initial Catalog=wlsql"
set cn = Server.CreateObject("ADODB.Connection")
cn.Open gScriptSQL_CnStr
Set supplier = Server.CreateObject("ADODB.Recordset")
supplier.CursorLocation = 1
strVar = "cs_vend"
strVar1 = "cs_id"
'strSQL = "select top 1 AUTO_ID as xx from cs where cs_ID like '0507%' order by auto_id desc"
strSQL = "select " & strVar & "," & strVar1 & " from cs where cs_ID like '0507%' order by auto_id desc"
'strSQL = "select * from cs where cs_ID like '0507%' order by auto_id desc"
' response.Write strsql
'response.end
supplier.Open strSQL,cn,1,3
if not supplier.eof then
response.Write supplier(strVar) & "<br>" & supplier(strVar1)
end if
%>
</body>
</html>
---在荧幕上面就显示结果
B503
0507200021 3。如果是要在sql server语句里面使用动态sql,那么就是传变量进取执行,写成sp,这样程式可读性会好很多
谢谢你写了一大堆。是用asp,前台用。我按你的再去试试。
System .Data .SqlClient .SqlParameter[] parm=new System .Data .SqlClient .SqlParameter []
{
new SqlParameter("@tbName",SqlDbType.VarChar)
};
parm[0].Value="d_order";this.DataGrid1 .DataSource =DBHelper.ExecuteDataTable("exec('select * from '+@tbName)",parm,CommandType.Text);
this.DataGrid1 .DataBind();