Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
'Dim par As Parameter
'set par=cmd.CreateParameter (
With cmd
.ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "au_info"
这些是定义
Dim conn As ADODB.connection
Dim rs As ADODB.Recordset
这些是使用。
Set conn = New ADODB.connection
Set rs = New ADODB.Recordset
Dim connstring As String
connstring = "Provider=SQLOLEDB.1;Password=lirh;Persist Security Info=True;User ID=sa;Initial Catalog=tkmanage;Data Source= 192.168.0.32"
Dim sql As String
sql = "create procedure 存储过程名 as select * from 表名"
conn.Execute sql
第二段:
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim prm As ADODB.Parameter
Dim strCn As String
Dim intResult As Integer
'Ö¸¶¨Á¬½Ó
Set cmd.ActiveConnection = cn
'·µ»Ø¼Ç¼¼¯
Set rs = cmd.Execute
'Èç¹û´æ´¢¹ý³ÌÖ´ÐÐʧ°ÜÔò·µ»Ø
If cmd.Parameters("myResult") <> 0 Then
MsgBox "Ö´Ðд洢¹ý³Ìʧ°Ü£¡", vbExclamation, g_cnststrMsgTitle
GoTo ERR
End If
'Èç¹û¼Ç¼ÊýΪ0Ôò·µ»Ø
If rs.RecordCount <= 0 Then 'ûÓмǼ
MsgBox "ÎÞÏÔʾÊý¾Ý£¡", vbExclamation, g_cnststrMsgTitle
GoTo ERR
End If
'µ÷Ô¤ÀÀ´°¿Ú
Load frmPrintPreview
If frmPrintPreview.DrawPicture(rs) = False Then
MsgBox "¸ºÔØÇúÏß»æÖÆʧ°Ü£¡", vbExclamation, g_cnststrMsgTitle
Unload frmPrintPreview
GoTo ERR
End If
Me.MousePointer = vbDefault
frmPrintPreview.Show , Me
ERR:
If ERR.Number <> 0 Then MsgBox ERR.Description, vbExclamation, g_cnststrMsgTitle
On Error Resume Next
Me.MousePointer = vbDefault 'ÉèÖÃÊó±ê,µÈ´ýÐÎ̬
If rs.state > 0 Then rs.Close
If cn.state > 0 Then cn.Close
Set rs = Nothing
Set cn = Nothing
Set prm = Nothing
Set cmd = Nothing
Erase strAddressArray
End Sub
调用存储过程,
第一段:
Private Sub DealWithAcDeviceCurve()
On Error GoTo ERR
Dim i As Integer
Dim intNumber As Integer
Dim strEFTrueDes As String '¾ù¸¡³äΪtrueʱµÄÎÄ×ÖÃèËÙ
Dim strEFFalseDes As String '¾ù¸¡³äΪFalseʱµÄÎÄ×ÖÃèËÙ
Dim strReportTitle As String '±¨±í±êÌâ
Dim strScName As String '¼à¿ØÖÐÐÄÃû³Æ
Dim strDateNotice As String '±¨±íʱ¼äÃèËÙ
Dim strDateValue As String '±¨±íʱ¼äÖµ
Dim strStorePro As String '´æ´¢¹ý³ÌÃû³Æ
Dim strSelectSuName As String 'Ñ¡ÔñµÄ»ùÕ¾Ãû
Dim strAddressArray() As String '´æ·ÅÑ¡ÖеĵØÖ·Êý×é
'¼ì²éʱ¼ä
If txtDateCurve.text = "" Then
MsgBox "δѡÔñ±¨±íʱ¼ä£¡", vbExclamation, g_cnststrMsgTitle
GoTo ERR
End If
strDateValue = Format(Trim(DTPDateCurve.value), "yyyy-mm-dd") & " " & Format(Trim(DTPTimeCurve.value), "hh:nn:ss")
'ͳ¼Æ»ùÕ¾ÊýÁ¿
If txtSelectSuCurve.text = "" Or txtSelectSuCurve.Tag = "" Then
MsgBox "δѡÔñ»ùÕ¾£¡", vbExclamation, g_cnststrMsgTitle
GoTo ERR
End If
If CheckIpAddress(Trim(txtSelectSuCurve.Tag)) = False Then
MsgBox "»ùÕ¾Âß¼µØÖ·´íÎó£¡", vbExclamation, g_cnststrMsgTitle
GoTo ERR
End If
AnalyzeString Trim(txtSelectSuCurve.Tag), ".", strAddressArray
If UBound(strAddressArray) <> 3 Then
MsgBox "»ùÕ¾Âß¼µØÖ·´íÎó£¡", vbExclamation, g_cnststrMsgTitle
GoTo ERR
Else
For i = 0 To 3
If IsNumeric(Trim(strAddressArray(i))) = False Then
MsgBox "»ùÕ¾Âß¼µØÖ·´íÎó£¡", vbExclamation, g_cnststrMsgTitle
GoTo ERR
End If
Next
End If
Me.MousePointer = vbHourglass
'±¨±íÍ·
If frmMain.TreeViewLog.Nodes.Count <= 0 Then
strScName = "" '¼à¿ØÖÐÐÄÃû³Æ
Else
strScName = frmMain.TreeViewLog.Nodes(1).text '¼à¿ØÖÐÐÄÃû³Æ
End If
If optHourCurve.value = True Then 'ÈÕ¸ºÔØÇúÏß
strReportTitle = "½»Á÷É豸ʱ¸ºÔØÇúÏß" '±¨±í±êÌâ
strDateNotice = "ͳ¼ÆСʱ" '±¨±íʱ¼äÃèËÙ
strStorePro = "JL_Hour_Curve_proc"
ElseIf optDay.value = True Then 'ÈÕ±¨±í
strReportTitle = "½»Á÷É豸ÈÕ¸ºÔØÇúÏß" '±¨±í±êÌâ
strDateNotice = "ͳ¼ÆÈÕÆÚ" '±¨±íʱ¼äÃèËÙ
strStorePro = "JL_Day_Curve_proc"
ElseIf optMonth.value = True Then 'Ô±¨±í
strReportTitle = "½»Á÷É豸Ô¸ºÔØÇúÏß" '±¨±í±êÌâ
strDateNotice = "ͳ¼ÆÔ·Ý" '±¨±íʱ¼äÃèËÙ
strStorePro = "JL_Month_Curve_proc"
ElseIf optYear.value = True Then
strReportTitle = "½»Á÷É豸Ä긺ÔØÇúÏß" '±¨±í±êÌâ
strDateNotice = "ͳ¼ÆÄê·Ý" '±¨±íʱ¼äÃèËÙ
strStorePro = "JL_Year_Curve_proc"
End If
set @chrDate=cast(year(@date) as varchar) +'-'+cast(month(@date) as varchar)+'-'+cast(day(@date) as varchar)+' 0:0:0'
set @date=cast(@chrDate as datetime)
if year(@date)=year(getdate()) and month(@date)=month(getdate()) and day(@date)=day(getdate())
BEGIN
select cast(DataTime as datetime) as 'DataTime',cast(VariableData as float) as 'DataVal'
from CurrentAnalog where FirstAddress=@FirstAddr and SecondAddress=@SecondAddr and
ThirdAddress=@ThirdAddr and FourthAddress=237 and DataTime>@date and DataTime<dateadd(dd,1,@date)
order by DataTime
END
else
BEGIN
select cast(DataTime as datetime) as 'DataTime',cast(VariableData as float)as 'DataVal'
from HistoryAnalog where FirstAddress=@FirstAddr and SecondAddress=@SecondAddr and
ThirdAddress=@ThirdAddr and FourthAddress=237 and DataTime>@date and DataTime<dateadd(dd,1,@date)
order by DataTime
END
set @result=0
return 0
set @charTmp=cast(year(@date) as varchar) +'-'+cast(month(@date) as varchar)+'-'+cast(day(@date) as varchar)
set @DateBegin=cast(@charTmp as datetime)
set @DateEnd=dateadd(dd,1,@DateBegin)
set @intTotal=datediff(ss,@DateBegin,@DateEnd)
set @intCircle=200
set @intInterval=@intTotal/@intCircle
while @intCircle>=0
begin
set @DateTmp=dateadd(ss,@intInterval*(200-@intCircle),@DateBegin)
set @DataVal=RAND(@intCircle*@intInterval)*200
set @intTotal=cast(@DataVal as int)
set @DataVal=(@DataVal-@intTotal)*1000
set @intCircle=@intCircle-1
--插入基站名称
insert #DCDeviceDayTmpTable(DataTime,DataVal) values(@DateTmp,@DataVal)
end
--计算结束
select * from #DCDeviceDayTmpTable
*/
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO