Question

VB.NET 将参数传入 存储过程 的 问题?

用的是System.Data.OleDb

想将两个变量 传入 存储过程(SQLSERVER的)
(存储过程里执行的是INSERT操作,即将这两个变量INSERT进某表)

将两个变量 传入 存储过程这代码在VB.NET里怎么写?

还有 如果是 SELECT , DELETE,UPDATE 又该如何写呢? 谢谢~!

Answer 1

//存储过程代码
CREATE PROCEDURE [AddNewInvoiceInfo] (@sUserID int,@sClaimMethod varchar(50),@sInvoiceNum varchar(50),@sInvoiceDate datetime,@sBuyCity varchar(50))AS

Insert Into SMB_InvoiceInfo (MemberID,ClaimMethod,InvoiceNum,InvoiceDate,InvoiceCity) Values (@sUserID,@sClaimMethod,@sInvoiceNum,@sInvoiceDate,@sBuyCity)
return @@IDENTITY
GO

//调用存储过程代码
Dim CN As New SqlConnection()
Dim InvoiceID As String
Try
CN.ConnectionString = DBConStr
CN.Open()
Dim CMD As SqlCommand
CMD = New SqlCommand(AddNewInvoiceInfo, CN)
CMD.CommandType = CommandType.StoredProcedure

CMD.Parameters.Add(New SqlParameter(@sUserID, SqlDbType.Int))
CMD.Parameters(@sUserID).Value = CInt(UserID.Text)

CMD.Parameters.Add(New SqlParameter(@sClaimMethod, SqlDbType.VarChar, 50))
CMD.Parameters(@sClaimMethod).Value = TransName.Text

CMD.Parameters.Add(New SqlParameter(@sInvoiceNum, SqlDbType.VarChar, 50))
CMD.Parameters(@sInvoiceNum).Value = InvoiceNum.Text

CMD.Parameters.Add(New SqlParameter(@sInvoiceDate, SqlDbType.DateTime))
CMD.Parameters(@sInvoiceDate).Value = InvoiceDate.SelectedDate.Date

CMD.Parameters.Add(New SqlParameter(@sBuyCity, SqlDbType.VarChar, 50))
CMD.Parameters(@sBuyCity).Value = BuyCity.Text

CMD.Parameters.Add(New SqlParameter(@sInvoiceID, SqlDbType.Int))
CMD.Parameters(@sInvoiceID).Direction = ParameterDirection.ReturnValue

CMD.ExecuteNonQuery()
InvoiceID = CMD.Parameters(@sInvoiceID).Value.ToString()
CMD.Dispose()
Catch ex As Exception
Response.Write(ex.ToString())
Finally
CN.Close()
End Try

Answer 2

哦,存储过程名:liu,两个变更:aa,bb
Dim str as string="exec liu 'dd','ss'"
执行就是了啊,呵呵