一种理解:dim cmd as new adodb.commandcmd.activeconnection=cnn
cmd.commandtype=adcmdtext
cmd.commandtext="select * from table1 where time1 between #" & time11 & "# and #" & time22 & "#"
cmd.execute
cmd.commandtype=adcmdtext
cmd.commandtext="select * from table1 where time1 between #" & time11 & "# and #" & time22 & "#"
cmd.execute
解决方案 »
- 工业上用的探伤或检测等等设备是用什么编程的,初学者应该怎样入门
- vb连接access数据库问题
- vb+水晶报表9在win98中《文本》(text object)的第一二个字挤在一起有重叠,其它则没事。
- 请问各位大虾,FtpPutFile这个API能不能在ocx里面用呢?
- 存储过程的小问题,我不会,请高手相助!在线回分!
- vb6.0 form窗体上面的控件名点不出来
- 大家能否告诉我
- 请教网络编程高手!!!!!!!!!!!!!!!!!!!(本人是新手!!!!)
- 判断子节数
- html与VB的集成问题。
- 谁要能帮我解决这个问题,或提供思路被采纳者,我另开帖子给他加分,决不失言!!!
- 高分求救,关于显示所有文件夹以及文件的问题!!!
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String ' 定义存储过程的命令对象。
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = cnn1
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.CommandTimeout = 15
' 定义存储过程的输入参数。
intRoyalty = Trim(InputBox( _
"Enter royalty:"))
Set prmByRoyalty = New ADODB.Parameter
prmByRoyalty.Type = adInteger
prmByRoyalty.Size = 3
prmByRoyalty.Direction = adParamInput
prmByRoyalty.Value = intRoyalty
cmdByRoyalty.Parameters.Append prmByRoyalty
' 通过执行该命令创建记录集。
Set rstByRoyalty = cmdByRoyalty.Execute()
' 打开作者表以便显示作者姓名。
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", strCnn, , , adCmdTable
' 打印记录集中的当前数据,从作者表中添加作者姓名。
Debug.Print "Authors with " & intRoyalty & _
" percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print , rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & _
rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop rstByRoyalty.Close
rstAuthors.Close
cnn1.Close
End Sub
@time1 datetime,
@time2 datetime
AS
select * from table1 where time between @time1 and @time2
go在SQL 的查询分析器里执行一下 Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
cmd.ActiveConnection = ""
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "p1"
cmd.Parameters.Append cmd.CreateParameter("@time1", adDBTime, adParamInput, , "")
cmd.Parameters.Append cmd.CreateParameter("@time2", adDBTime, adParamInput, , "")
Set rs = cmd.Execute Do While Not rs.EOF
..... rs.MoveNext
LoopSet cmd = Nothing
rs.Close
Set rs = Nothing
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
cmd.ActiveConnection = ""
cmd.CommandType = adCmdText
cmd.CommandText = select * from table1 where time between ? and ? cmd.Parameters.Append cmd.CreateParameter("time1", adDBTime, adParamInput, , "")
cmd.Parameters.Append cmd.CreateParameter("time2", adDBTime, adParamInput, , "")
cmd.value=time1' 变量
cmd.value=time2'
Set rs = cmd.Execute
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
cmd.ActiveConnection = ""
cmd.CommandType = adCmdText
cmd.CommandText = select * from table1 where time between ? and ? cmd.Parameters.Append cmd.CreateParameter("time1", adDBTime, adParamInput, , "")
cmd.Parameters.Append cmd.CreateParameter("time2", adDBTime, adParamInput, , "")
cmd.value=time1' 变量
cmd.value=time2'
Set rs = cmd.Execute
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
cmd.ActiveConnection = ""
cmd.CommandType = adCmdText
cmd.CommandText = select * from table1 where time between ? and ? cmd.Parameters.Append cmd.CreateParameter("time1", adDBTime, adParamInput, , "")
cmd.Parameters.Append cmd.CreateParameter("time2", adDBTime, adParamInput, , "")
cmd.value=time1' 变量
cmd.value=time2'
Set rs = cmd.Execute
所以我用了DataEnvirement和DataReport,DataEnvirement中有一个
connection 和几个command,因为要生成报表,我只能传参数给DataEnvirement的command,所以 阿甘 的第一种方法不行(我的command带有一些父子关系等,我还很难直接控制用代码写command的comandtext,可能也不是很难吧)
阿甘的第二种方法我也见过,但因为我还不会用存储过程,所以我不理解byroyalty到底怎么来得,这是一个存储过程名?好像是突然冒出来得,不懂啊
后面两种方法可以一试,但是这样做我的两个参数怎么传过去啊?
antshome的
cmd.Parameters.Append cmd.CreateParameter("@time1", adDBTime, adParamInput, , "") “”中间就是我的time1的值?
water_j的cmd.value=time1' 变量 time1就是我要传的值?
可能要等明天试了我才清楚了.多谢三位的帮助!
通过 ADO Command 对象,可以象用 Connection 对象和 Recordset 对象那样执行查询,唯一的不同在于用 Command 对象您可以在数据库源上准备、编译您的查询并且反复使用一组不同的值来发出查询。这种方式的编译查询的优点是您可以最大程度地减少向现有查询重复发出修改的请求所需的时间。另外,您还可以在执行之前通过您的查询的可变部分的选项使 SQL 查询保持局部未定义。Command 对象的 parameter 集合减少了您的麻烦,使您不必在每次重新发出查询时重新建立查询。例如,如果需要有规律地更新基于库存清单的 Web 系统中的供应和价格信息,可以用下面的方法预先定义查询:<%
'Open a connection using Connection object Command object
'does not have an Open method for establishing a connection
strDSN = "FILEDSN=MyDatabase.dsn"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open strDSN 'Instantiate Command object; use ActiveConnection property to attach
'connection to Command object
Set cm= Server.CreateObject("ADODB.Command")
Set cm.ActiveConnection = cn
'Define SQL query
cm.CommandText = "INSERT INTO Inventory (Material, Quantity) VALUES (?, ?)" 'Save a prepared (or pre-compiled) version of the query specified in CommandText
'property before a Command object's first execution.
cm.Prepared = True 'Define query parameter configuration information
cm.Parameters.Append cm.CreateParameter("material_type",200, ,255 )
cm.Parameters.Append cm.CreateParameter("quantity",200, ,255 ) 'Define and execute first insert
cm("material_type") = "light bulbs"
cm("quantity") = "40"
cm.Execute
'Define and execute second insert
cm("material_type") = "fuses"
cm("quantity") = "600"
cm.Execute
%>
当我要传的一个参数在查询语句总要用到两次甚至多次时,
用?好像就不大好了。比如select * from myTable where Firsttime between
@time1 and @time2 And LastTime between @time1 and @time2
这时用”select * from myTable where Firsttime between
? and ? And LastTime between ? and ? “ 岂不是要四个参数?
而且我用这种方法写在DataEnvirement中的command的SQL statement 中时
总是使得vb崩溃(如全部用代码写则不会,可能是DataEnvirement中的command的SQL statement 不支持这种写法把)