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

jcdwin5 2003-10-16 08:09:34
我准备写一段程序,程序调用sqlserver里查看sqlserver复制状态的存储过程,达到在程序里查看sqlserver复制状态的效果,谢谢帮助,我写好后送大家300分
...全文
84 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
j4sxw 2003-10-17
  • 打赏
  • 举报
回复
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()
jcdwin5 2003-10-16
  • 打赏
  • 举报
回复
up
suntt 2003-10-16
  • 打赏
  • 举报
回复
http://www.csdn.net/develop/author/pam/book8_6.shtm
阿建像熊猫 2003-10-16
  • 打赏
  • 举报
回复
这些是定义
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自带的系统表中。查看有无存在就行了。
golden24kcn 2003-10-16
  • 打赏
  • 举报
回复
你的意思是SQL SERVER时时刻刻都要有返回值直到复制完毕吗?
online 2003-10-16
  • 打赏
  • 举报
回复
http://www.ourfly.com/forum/View.aspx?fbId=9&Id=123
hc_z 2003-10-16
  • 打赏
  • 举报
回复
第二段:
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 , 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
hc_z 2003-10-16
  • 打赏
  • 举报
回复
调用存储过程,
第一段:
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

hc_z 2003-10-16
  • 打赏
  • 举报
回复
存促过程: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,1

ALTER 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 int

declare @DateBegin datetime
declare @DateEnd datetime
declare @DateTmp datetime
declare @DataVal float

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

yoki 2003-10-16
  • 打赏
  • 举报
回复
msdn上就有呀

1,216

社区成员

发帖
与我相关
我的任务
社区描述
VB 数据库(包含打印,安装,报表)
社区管理员
  • 数据库(包含打印,安装,报表)社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧