Question

asp中SQL语句的写法

oabusyname=request.cookies("oabusyname")

if myyear<>"" then
mydate=myyear & "-" & mymonth & "-" & 1
mydate1=dateadd("m",1,mydate)
else
mydate=""
end if
set conn=opendb("oabusy","conn","accessdsn")
Set rs=Server.CreateObject("ADODB.recordset")
sql="select * from diaocha where b75 =" & sqlstr(oabusyname) & " order by id desc"
if request("lookfor")="yes" then sql="select * from diaocha where b75 = " & sqlstr(oabusyname) & " b69 between " & mydate & " and " & mydate1 & " order by id desc"

上面的代码是不完全的，出错地方是
sql="select * from diaocha where b75 = " & sqlstr(oabusyname) & " b69 between " & mydate & " and " & mydate1 & " order by id desc"
不知道那里出错了，麻烦大家帮忙解决一下
JovenShao - 江湖新秀 五级 回答的，不会报错，但是我查询不出信息来

Answer 1

1. mydate=myyear & "-" & mymonth & "-" & 1
改正：最后的1是字符串，和前面的年月相连，比如2008-12-1，1要使用双引号""括起来。

2. lookfor是文本型吗？如果是文本型，写成request("lookfor")="yes"是正确的，如果是布尔型，那么要写成request("lookfor")=true

3. ：你的SqlStr函数是这样写的吗？
Function SqlStr( data )
SqlStr = "" & Replace( data ", "" ) & ""
End Function

4. sql内错误，b69前缺少一个and；
改正：
if request("lookfor")="yes" then
sql="select * from diaocha where b75 = " & sqlstr(oabusyname) & " and b69 between " & mydate & " and " & mydate1 & " order by id desc"

5.sql重复：已经有了sql，为什么还继续写if ... the sql=....
如果要判断，就这样写
if request("lookfor")="yes" then
sql="select * from diaocha where b75 = " & sqlstr(oabusyname) & " b69 between " & mydate & " and " & mydate1 & " order by id desc"
else
sql="select * from diaocha where b75 =" & sqlstr(oabusyname) & " order by id desc"
end if
6.如果是access数据库，日期mydate最好要使用#括起来，asp可以更好的读取。
mydate = "#" & myyear & "-" & mymonth & "-" & "1#"

Answer 2

这样就行了：
sql="select * from diaocha where b75 = '"&sqlstr(oabusyname)&"' and b69 between '"&mydate&"' and '"&mydate1&"' order by id desc"
是有两点要注意：1．字符型变量值形成的SQL语句中要有单引号(B75)。甚至日期形变量值一般也要加单引号(B69).数值形就不用加了。
2．日期形字段写值时要保证格式确。最好是YY-MM-DD格式

Answer 3

sql="select * from diaocha where b75 = " & sqlstr(oabusyname) & " and b69 < " & mydate & " and b69 > " & mydate1 & " order by id desc"