用vb调用存储过程方法

2023年6月21日发(作者：)

中如何调用存储过程

2011-03-28 14:08:45| 分类： .net |举报|字号 订阅

下载LOFTER客户端

此页涉及到的存储过程的创建，在存储过程的创建中有源码

1、调用无参数存储过程

dim conn As New SqlConnection

dim FBasicDataRecord as new BasicDataRecord()

tionString = "Data Source=(local);Initial Catalog=LineManager;User

ID=sa;Password=1"

()

Dim cmd As SqlCommand

cmd = New SqlCommand("GetNewestData", conn) -------要调用存储过程名称

dType = Procedure

-----------关键，要想调用存储过程，需将

commandtype类型设置为storedprocedure

Dim myReader As SqlDataReader

Try

myReader = eReader() -------------执行存储过程

If () Then

ateTime = eTime(0)

HourCost = imal(1)

Time = 32(2)

areTime = 32(3)

tCurrency = imal(4)

Name = ing(5)

Return True

Else

Return False

End If

Catch ex As Exception

Return e

Finally

()

End Try

2、调用有参数存储过程

Dim cmd As SqlCommand

dim conn As New SqlConnection

tionString = "Data Source=(local);Initial Catalog=LineManager;User

ID=sa;Password=1"

()

cmd = New SqlCommand("ProcCharge", conn) --------存储过程名称

dType = Procedure ----------改变类型

Dim a As New SqlParameter("@CardNo", r) ------------将存储过程中涉及的参数加入进来，并附

值。就像给函数的各个参数赋值一样

= No

(a)

Dim b As New SqlParameter("@ChargeDateTime", me)

= geDateTime

(b)

Dim c As New SqlParameter("@Charge", )

= ge

(c)

Dim d As New SqlParameter("@UserName", r)

= Name

(d)

Dim e As New SqlParameter("@LastBalance", )

= tBalance

(e)

Dim f As New SqlParameter("@CurrentBalance", l)

= entBalance

(f) ----------------参数块

Try

Return eNonQuery() > 0 ------------------执行存储过程。当存储过程涉及到的是插入删除或 更新操作时，用executenonquery（），而如果是查询，则用executereader（）

Catch ex As Exception

MsgBox(e)

Return False

Finally

()

End Try

3、调用有返回值的存储过程

dim conn As New SqlConnection

tionString = "Data Source=(local);Initial Catalog=LineManager;User

ID=sa;Password=1"

()

Dim cmd As SqlCommand

cmd = New SqlCommand("procBalanceUserName", ) ---------存储过程名称

dType = Procedure

----------改变类型

(New SqlParameter("@CardNo", r)) -------添加参数，此为输入参数

ters("@CardNo").Value = CardNo

(New SqlParameter("@MachineNo", r))

ters("@MachineNo").Value = MachineNo

(New SqlParameter("@Balance", ,

---------定义输出参数

ters("@Balance").Direction =

20))

-------输出参数用此标识

'输出参数必须有指定长度，否则不会成功。

(New SqlParameter("@UserName", r, 10))

-------输出参数必须是名字，类

型，长度。不可省略。否则，会出错误。

ters("@UserName").Direction =

Try

eReader()

---------------执行存储过程

myBalance = ters("@Balance").ng() ----------将存储过程的返回值赋给定义的变量

myUserName = ters("@UserName").ng()

Catch ex As Exception

MsgBox(e)

Finally

()

End Try

总结一下，调用存储过程，首先，实例化sqlcommand，然后修改commandtype类型，如果参数，则给参数赋值或定义输出参数。然后执行。

2023年6月21日发(作者：)

中如何调用存储过程

2011-03-28 14:08:45| 分类： .net |举报|字号 订阅

下载LOFTER客户端

此页涉及到的存储过程的创建，在存储过程的创建中有源码

1、调用无参数存储过程

dim conn As New SqlConnection

dim FBasicDataRecord as new BasicDataRecord()

tionString = "Data Source=(local);Initial Catalog=LineManager;User

ID=sa;Password=1"

()

Dim cmd As SqlCommand

cmd = New SqlCommand("GetNewestData", conn) -------要调用存储过程名称

dType = Procedure

-----------关键，要想调用存储过程，需将

commandtype类型设置为storedprocedure

Dim myReader As SqlDataReader

Try

myReader = eReader() -------------执行存储过程

If () Then

ateTime = eTime(0)

HourCost = imal(1)

Time = 32(2)

areTime = 32(3)

tCurrency = imal(4)

Name = ing(5)

Return True

Else

Return False

End If

Catch ex As Exception

Return e

Finally

()

End Try

2、调用有参数存储过程

Dim cmd As SqlCommand

dim conn As New SqlConnection

tionString = "Data Source=(local);Initial Catalog=LineManager;User

ID=sa;Password=1"

()

cmd = New SqlCommand("ProcCharge", conn) --------存储过程名称

dType = Procedure ----------改变类型

Dim a As New SqlParameter("@CardNo", r) ------------将存储过程中涉及的参数加入进来，并附

值。就像给函数的各个参数赋值一样

= No

(a)

Dim b As New SqlParameter("@ChargeDateTime", me)

= geDateTime

(b)

Dim c As New SqlParameter("@Charge", )

= ge

(c)

Dim d As New SqlParameter("@UserName", r)

= Name

(d)

Dim e As New SqlParameter("@LastBalance", )

= tBalance

(e)

Dim f As New SqlParameter("@CurrentBalance", l)

= entBalance

(f) ----------------参数块

Try

Return eNonQuery() > 0 ------------------执行存储过程。当存储过程涉及到的是插入删除或 更新操作时，用executenonquery（），而如果是查询，则用executereader（）

Catch ex As Exception

MsgBox(e)

Return False

Finally

()

End Try

3、调用有返回值的存储过程

dim conn As New SqlConnection

tionString = "Data Source=(local);Initial Catalog=LineManager;User

ID=sa;Password=1"

()

Dim cmd As SqlCommand

cmd = New SqlCommand("procBalanceUserName", ) ---------存储过程名称

dType = Procedure

----------改变类型

(New SqlParameter("@CardNo", r)) -------添加参数，此为输入参数

ters("@CardNo").Value = CardNo

(New SqlParameter("@MachineNo", r))

ters("@MachineNo").Value = MachineNo

(New SqlParameter("@Balance", ,

---------定义输出参数

ters("@Balance").Direction =

20))

-------输出参数用此标识

'输出参数必须有指定长度，否则不会成功。

(New SqlParameter("@UserName", r, 10))

-------输出参数必须是名字，类

型，长度。不可省略。否则，会出错误。

ters("@UserName").Direction =

Try

eReader()

---------------执行存储过程

myBalance = ters("@Balance").ng() ----------将存储过程的返回值赋给定义的变量

myUserName = ters("@UserName").ng()

Catch ex As Exception

MsgBox(e)

Finally

()

End Try

总结一下，调用存储过程，首先，实例化sqlcommand，然后修改commandtype类型，如果参数，则给参数赋值或定义输出参数。然后执行。