我用vb作了一个程序,用来连接sql server2000中的一个表。用一个数组来记录此表中的一个字段,然后把此数组作为where语句中的查询条件,但是t-sql语句处报错。具体代码如下
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim i As Integer
Dim str1(1000) As StringSet cn = New ADODB.Connection
cn.Open "Provider=sqloledb;Data Source=SERVER ;Initial Catalog=OFFICE;User Id=sa;Password="
sql = "select 名称 from 表"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
i = 1
While (rs.EOF = False)
str1(i) = rs.Fields(0) '名称
i = i + 1
rs.MoveNext
WendFor i = 1 To 1000
Set cn = New ADODB.Connection
cn.Open "Provider=sqloledb;Data Source=SERVER ;Initial Catalog=OFFICE;User Id=sa;Password="
sql = "select 数量 from 表 where 名称=str1(i)"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
j = 0
While (rs.EOF = False)
j = j + rs.Fields(0) '数量
rs.MoveNext
Wend
str3(i) = j
Next isql = "select 数量 from 表 where 名称=str1(i)"报错为:“str1不是可识别的函数名”请问大家该怎样解决
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim i As Integer
Dim str1(1000) As StringSet cn = New ADODB.Connection
cn.Open "Provider=sqloledb;Data Source=SERVER ;Initial Catalog=OFFICE;User Id=sa;Password="
sql = "select 名称 from 表"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
i = 1
While (rs.EOF = False)
str1(i) = rs.Fields(0) '名称
i = i + 1
rs.MoveNext
WendFor i = 1 To 1000
Set cn = New ADODB.Connection
cn.Open "Provider=sqloledb;Data Source=SERVER ;Initial Catalog=OFFICE;User Id=sa;Password="
sql = "select 数量 from 表 where 名称=str1(i)"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sql, cn, adOpenDynamic, adLockOptimistic
j = 0
While (rs.EOF = False)
j = j + rs.Fields(0) '数量
rs.MoveNext
Wend
str3(i) = j
Next isql = "select 数量 from 表 where 名称=str1(i)"报错为:“str1不是可识别的函数名”请问大家该怎样解决
改成:名称='"&strl(i)&"' 看行不行了
text1.text="2004-09"sql = "select 数量 from 入库表 where 名称='" & str1(i) & "' and [规格/型号]='" & str2(i) & "' and (year(入库时间)<year( '" & Format(Text1.Text, "yyyy-mm") & " ')) or (year(入库时间)=year('" & Format(Text1.Text, "yyyy-mm") & " ') and month(入库时间)< month('" & Format(Text1.Text, "yyyy-mm") & " ') ) "在执行sql语句时提示错误为:
从字符串转换为datetime是发生语法错误
请问大家,这情况将如何处理?
(year(入库时间)=year(" & Format(Text1.Text, "yyyy-mm") & ")
上面已经指出了yyyy-mm 无需在判断year和month了
还有year('" & Format(Text1.Text, "yyyy-mm") & " ') 这个本来是datatime ,而 year()转换字符串,当然去提示出错了!
就用 入库时间<format(text1.text,yyyy-mm) 就可以了!
sql = "select 数量 from 表 where 名称='" &str1(i) &"'"