想找一个例子看看vb里怎样调用sql的存储过程，各位老大帮帮忙^-^

你的意思是SQL SERVER时时刻刻都要有返回值直到复制完毕吗?

这些是定义
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其实，存储过程的使用和一般的sql语句差不多，但有是需要查看一下，存储过程名是否存在。存储过程名一般在SQL数据库中的sysobjects自带的系统表中。查看有无存在就行了。

http://www.csdn.net/develop/author/pam/book8_6.shtm

存促过程：SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO/****** Object:  Stored Procedure dbo.ZL_Day_Curve_proc    Script Date: 2003-8-14 17:04:20 ******/
--exec ZL_Day_Curve_proc '2003-07-03 11:36:29','基站',1,1,1,1ALTER   PROCEDURE  ZL_Day_Curve_proc(
@date datetime, --报表日期
@SUName varchar(50), --基站名称
@FirstAddr smallint, --SU地址第1字节
@SecondAddr smallint,--SU地址第2字节
@ThirdAddr smallint,--SU地址第3字节
  @result smallint out  --返回结果 0表示成功  1表示异常其他表示异常类型（保留）
)
AS
/*
变量说明：
数值的时间（时刻）
数值量
*/
--时间的计算，查询不同的表
declare @chrDate varchar(50)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/*Create Table #DCDeviceDayTmpTable
(DataTime datetime,
  DataVal float
  )
declare @charTmp varchar(30)
declare @intTotal int
declare @intInterval int
declare @intCircle intdeclare @DateBegin datetime
declare @DateEnd datetime
declare @DateTmp datetime
declare @DataVal floatset @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/@intCirclewhile @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

调用存储过程，
第一段：
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

第二段：
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

    '²Ù×÷Á¬½Ó
    strCn = "driver=" & g_strDataBaseDriver & ";server=" & g_strDataBaseServerAddress & _
            ";uid=" & g_strDataBaseUserID & ";pwd=" & g_strDataBasePassWord & _
            ";database=" & g_strDatabaseName
    cn.ConnectionString = strCn
    cn.CommandTimeout = 120
    cn.CursorLocation = adUseClient
    cn.Open '´ò¿ª¼ÇÂ¼¼¯

    'Ñ»·´¦Àí

    Set cmd = New ADODB.Command
    '²Ù×÷ÃüÁî
    cmd.CommandText = strStorePro
    cmd.CommandType = adCmdStoredProc

    '²ÎÊý¶ÔÏó
    Set prm = cmd.CreateParameter("myDate", adVarChar, adParamInput, 30) 'Ê±¼ä
    cmd.Parameters.Append prm
    prm.value = strDateValue
    Set prm = cmd.CreateParameter("SUName", adVarChar, adParamInput, 50) 'SUÃû³Æ
    cmd.Parameters.Append prm
    prm.value = Trim(txtSelectSuCurve.text)
    Set prm = cmd.CreateParameter("FirstAddress", adSmallInt, adParamInput) 'µÚÒ»µØÖ·
    cmd.Parameters.Append prm
    prm.value = CByte(strAddressArray(0))
    Set prm = cmd.CreateParameter("SecondAddress", adSmallInt, adParamInput) 'µÚ¶þµØÖ·
    cmd.Parameters.Append prm
    prm.value = CByte(strAddressArray(1))
    Set prm = cmd.CreateParameter("ThirdAddress", adSmallInt, adParamInput) 'µÚÈýµØÖ·
    cmd.Parameters.Append prm
    prm.value = CByte(strAddressArray(2))

    Set prm = cmd.CreateParameter("myResult", adSmallInt, adParamOutput) 'Ê±¼ä
    cmd.Parameters.Append prm
    prm.value = intResult

    'Ö¸¶¨Á¬½Ó
    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

    '±¨±íÏÔÊ¾×Ö·û
    frmPrintPreview.m_strReportTitle = strReportTitle
    frmPrintPreview.m_strScName = strScName
    frmPrintPreview.m_strDateNotice = strDateNotice
    frmPrintPreview.m_strDateValue = txtDateCurve.text
    frmPrintPreview.m_strSuName = txtSelectSuCurve.text

    Me.MousePointer = vbDefault
    frmPrintPreview.Show , MeERR:
    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

http://www.ourfly.com/forum/View.aspx?fbId=9&Id=123

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"

        .Parameters.Append cmd.CreateParameter("astname", adVarChar, adParamInput, 40)
        .Parameters.Append cmd.CreateParameter("irstname", adVarChar, adParamInput, 20)
        .Parameters("astname").Value = lastName
        .Parameters("irstname").Value = firstName


    End With
    Set GetRoyalties = cmd.Execute()

调试易

想找一个例子看看vb里怎样调用sql的存储过程，各位老大帮帮忙^-^

解决方案 »