ASP中要执行某一段SQL语句，如何实现啊

[hms0103]

ASP中要执行某一段SQL语句，如何实现啊//如下语句。。。

我要执行以下这段程序。ASP中如何实现啊？？
if exists (select * from sysobjects where id = object_id(N'[dbo].[仓库比较表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[仓库比较表]


create table 仓库比较表
(
Usercode varchar(25),
Fullname varchar(100),
Standard varchar(150),
area varchar(255)
)


insert into 仓库比较表(Usercode,Fullname,Standard,area)
SELECT dbo.ptype.usercode as 编码,dbo.ptype.Fullname as 品名,dbo.ptype.standard as 规格,dbo.ptype.area as 产地
FROM ptype INNER JOIN #TempSwitch ON ptype.typeId = #TempSwitch.PtypeId

[tantaiyizu]

Dim con

Set con = Server.CreateObject("Adodb.Connection")

con.open = "Provider=SQLOLEDB.1;Persist Security Info=false;Initial Catalog=*;Data Source=*;User ID=*;password=*;"

dim sql
sql = "你的sql"
con.execute(sql)

[littlelam]

con.Execute("Sql1;Sql2;...")

[hms0103]

执行以下这段好像也不得呢？？这是怎么回事？？高手们麻烦您们指教一下！！！！
<%
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlDatabaseName = "gxjztyy"
SqlPassword = "75102020"
SqlUsername = "sa"
SqlLocalName = "192.168.1.8"
ConnStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & ";"
Set conn = Server.CreateObject("ADODB.Connection")
conn.open ConnStr

SQL="if exists (select * from sysobjects where id = object_id(N'[dbo].[股东库存]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[股东库存]

create table 股东库存
(
供货商ID varchar(10),
商品ID varchar(10),
批号 varchar(20) NULL,
效期 [datetime] NULL,
库存数量 numeric (18, 4) NULL,
单价 numeric(18, 4)NULL,
金额 numeric(18, 4)NULL,
仓库 varchar(10) NULL,
货位 varchar(10) NULL,
)

declare @a as varchar(50),@b as varchar(25)
set @a ='2号仓'
set @b='11'

insert into 股东库存(供货商ID,商品ID,批号,效期,库存数量,单价,金额,仓库,货位)
SELECT ISNULL(dbo.clients.client_id, '') AS 供货商ID,
dbo.storehouse.p_id AS 商品ID, dbo.storehouse.batchno AS 批号,
dbo.storehouse.validdate AS 效期,
dbo.storehouse.quantity AS 库存数量, dbo.storehouse.costprice AS 单价,
dbo.storehouse.costtotal AS 金额,
dbo.storages.name AS 仓库,
ISNULL(dbo.location.loc_name, '') AS 货位
FROM dbo.storehouse LEFT OUTER JOIN
dbo.storages ON dbo.storehouse.s_id = dbo.storages.storage_id LEFT OUTER JOIN
dbo.location ON dbo.storehouse.location_id = dbo.location.loc_id LEFT OUTER JOIN
dbo.clients ON dbo.storehouse.supplier_id = dbo.clients.client_id LEFT OUTER JOIN
dbo.unit RIGHT OUTER JOIN
dbo.unit unit_1 RIGHT OUTER JOIN
dbo.products LEFT OUTER JOIN
dbo.medtype ON dbo.products.medtype = dbo.medtype.mt_id LEFT OUTER JOIN
dbo.unit unit_3 ON dbo.products.unit4_id = unit_3.unit_id ON
unit_1.unit_id = dbo.products.unit2_id LEFT OUTER JOIN
dbo.unit unit_2 ON dbo.products.unit3_id = unit_2.unit_id ON
dbo.unit.unit_id = dbo.products.unit1_id ON
dbo.storehouse.p_id = dbo.products.product_id
WHERE (dbo.products.deleted = 0) AND (dbo.storages.deleted = 0) AND
(dbo.storages.name = '2号仓') AND (ISNULL(dbo.location.loc_name, '') = '11')
ORDER BY dbo.products.product_id DESC

select * from 股东库存"
con.execute(sql)
%>

[hms0103]

这一段SQL语句放在查询分析器里是有结果的。但想通过ASP来运行不知如何生成记录集。。。

[tantaiyizu]

要生成记录集只需：

dim rs

set rs = con.execute(sql)

rs即为记录集！

[hms0103]

执行以下这段好像也不得呢？？这是怎么回事？？高手们麻烦您们指教一下！！！！
<%
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlDatabaseName = "gxjztyy "
SqlPassword = "75102020 "
SqlUsername = "sa "
SqlLocalName = "192.168.1.8 "
ConnStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & "; "
Set conn = Server.CreateObject( "ADODB.Connection ")
conn.open ConnStr

SQL= "if exists (select * from sysobjects where id = object_id(N '[dbo].[股东库存] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1) drop table [dbo].[股东库存]

create table 股东库存
(
供货商ID varchar(10),
商品ID varchar(10),
批号 varchar(20) NULL,
效期 [datetime] NULL,
库存数量 numeric (18, 4) NULL,
单价 numeric(18, 4)NULL,
金额 numeric(18, 4)NULL,
仓库 varchar(10) NULL,
货位 varchar(10) NULL,
)

declare @a as varchar(50),@b as varchar(25)
set @a = '2号仓 '
set @b= '11 '

insert into 股东库存(供货商ID,商品ID,批号,效期,库存数量,单价,金额,仓库,货位)
SELECT ISNULL(dbo.clients.client_id, ' ') AS 供货商ID,
dbo.storehouse.p_id AS 商品ID, dbo.storehouse.batchno AS 批号,
dbo.storehouse.validdate AS 效期,
dbo.storehouse.quantity AS 库存数量, dbo.storehouse.costprice AS 单价,
dbo.storehouse.costtotal AS 金额,
dbo.storages.name AS 仓库,
ISNULL(dbo.location.loc_name, ' ') AS 货位
FROM dbo.storehouse LEFT OUTER JOIN
dbo.storages ON dbo.storehouse.s_id = dbo.storages.storage_id LEFT OUTER JOIN
dbo.location ON dbo.storehouse.location_id = dbo.location.loc_id LEFT OUTER JOIN
dbo.clients ON dbo.storehouse.supplier_id = dbo.clients.client_id LEFT OUTER JOIN
dbo.unit RIGHT OUTER JOIN
dbo.unit unit_1 RIGHT OUTER JOIN
dbo.products LEFT OUTER JOIN
dbo.medtype ON dbo.products.medtype = dbo.medtype.mt_id LEFT OUTER JOIN
dbo.unit unit_3 ON dbo.products.unit4_id = unit_3.unit_id ON
unit_1.unit_id = dbo.products.unit2_id LEFT OUTER JOIN
dbo.unit unit_2 ON dbo.products.unit3_id = unit_2.unit_id ON
dbo.unit.unit_id = dbo.products.unit1_id ON
dbo.storehouse.p_id = dbo.products.product_id
WHERE (dbo.products.deleted = 0) AND (dbo.storages.deleted = 0) AND
(dbo.storages.name = '2号仓 ') AND (ISNULL(dbo.location.loc_name, ' ') = '11 ')
ORDER BY dbo.products.product_id DESC

select * from 股东库存 "
con.execute(sql)
%>

[强大的农民]

我觉得可能是思路上的问题，

一些语句用ASP代码来就可以实现的。我想应该把它们分解开，能用ASP代码来代替的就用ASP代码来代替。需要SQL语句的再用SQL（conn.execute(sql))语句来执行。

[toury]

你用OLEDB连库通常是无法返回记录集的,改成ODBC连接试试

[hms0103]

那如何用ODBC连接数据库？？

[hms0103]

出现这样的提示：

ADODB.Recordset (0x800A0E78)
对象关闭时，不允许操作。
/SZT/123.asp, 第 94 行

[toury]

<%
dim conn,connStr
set conn=server.createobject("adodb.connection")

connStr="Driver={aql server};server=机器名;database=数据库名;uid=sa;pwd=密码"
conn.open connStr
response.write conn.state '1为连通 0--没连上库
%>

[tantaiyizu]

诶 。楼主sql水平过高了

一般的sql解释器时不行的

[tantaiyizu]

赶紧结贴吧

[hms0103]

意思是说这样的一段SQL程序在ASP中不能执行的？？那如果用存储过程能完成吗？？

[tantaiyizu]

asp 调存储过程不挺好吗？

为什么要这样做呢？

这样出错 ，你认为好找吗？

代码又便于维护吗？

[ruihuahan]

不应该在程序中drop、create表的，表一旦被drop掉，与表相关的有关信息都会失效，比如表的权限的分配、表上的触发器等等。
如果需要清空表的话，truncate命令就可以了。

[fcuandy]

首先你的sql语句在asp里是个字串, 你怎么什么换行都出来了,当然报错了.要么一个字串一行串到底,要么用vb的换行连接符,要么一行一行的加字串
第二,句与句之间以分号分隔

比如

dim sql
sql= "CREATE TABLE test (id int identity(1,1),v varchar(10)" & _
     ";INSERT test SELECT 'x'" & _
     ";SELECT * FROM test"

或者

dim sql
sql= "CREATE TABLE test (id int identity(1,1),v varchar(10)"
sql=sql & ";INSERT test SELECT 'x'"
sql=sql & ";SELECT * FROM test"