1、数据访问方法
Public Function GetID(ByVal NodeSetID As Integer, ByVal CUI As Integer) As Integer Dim rowsAffected As Integer '创建参数集
Dim parameters As SqlParameter() = {New SqlParameter("@tableName", SqlDbType.VarChar, 15), New SqlParameter("@CUI", SqlDbType.Int, 4), _
New SqlParameter("@ID", SqlDbType.Int, 4)} ' 给参数赋值
parameters(0).Value = GetTableName(NodeSetID).Trim
parameters(1).Value = CUI
parameters(2).Direction = ParameterDirection.Output
' 调用存储过程
ExecuteMyCommand("ebsp_GetID", parameters, rowsAffected)'
Return CInt(parameters(2).Value) End Function
其中的ExecuteMyCommand()方法
Protected Overloads Function ExecuteMyCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter(), _
ByRef rowsAffected As Integer) _
As Integer Dim result As Integer myConnection.Open()
Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters) '这个方法没问题
rowsAffected = command.ExecuteNonQuery()
result = CInt(command.Parameters("ReturnValue").Value)
myConnection.Close() Return result End Function
2、存储过程
ALTER PROCEDURE dbo.ebsp_GetID
@tableName varchar(15),
@CUI int,
@ID int=Null output
AS
declare @str as nvarchar(4000)
set @str=N'select @ID=ID from '+ @tableName + ' where CUI=@CUI '
exec sp_executesql @str,N'@ID int output,@CUI int,@tableName varchar(15)',@ID output,@CUI,@tableName
select @ID
RETURN
*******测试是对的
运行dbo."ebsp_GetID" ( @tableName = eb_c_28, @CUI = 10201, @ID = <DEFAULT> ).
-----------
7
没有更多的结果。
(返回 1 行)
@ID = 7
@RETURN_VALUE = 0
完成 dbo."ebsp_GetID" 运行。3、程序运行返回值不对
parameter(0)和parameter(1) 都是对的;但是parameter(2)却是DBNULL,但是单独运行存储过程却是对的(=7),问题出在哪里????
Public Function GetID(ByVal NodeSetID As Integer, ByVal CUI As Integer) As Integer Dim rowsAffected As Integer '创建参数集
Dim parameters As SqlParameter() = {New SqlParameter("@tableName", SqlDbType.VarChar, 15), New SqlParameter("@CUI", SqlDbType.Int, 4), _
New SqlParameter("@ID", SqlDbType.Int, 4)} ' 给参数赋值
parameters(0).Value = GetTableName(NodeSetID).Trim
parameters(1).Value = CUI
parameters(2).Direction = ParameterDirection.Output
' 调用存储过程
ExecuteMyCommand("ebsp_GetID", parameters, rowsAffected)'
Return CInt(parameters(2).Value) End Function
其中的ExecuteMyCommand()方法
Protected Overloads Function ExecuteMyCommand( _
ByVal storedProcName As String, _
ByVal parameters As IDataParameter(), _
ByRef rowsAffected As Integer) _
As Integer Dim result As Integer myConnection.Open()
Dim command As SqlCommand = BuildIntCommand(storedProcName, parameters) '这个方法没问题
rowsAffected = command.ExecuteNonQuery()
result = CInt(command.Parameters("ReturnValue").Value)
myConnection.Close() Return result End Function
2、存储过程
ALTER PROCEDURE dbo.ebsp_GetID
@tableName varchar(15),
@CUI int,
@ID int=Null output
AS
declare @str as nvarchar(4000)
set @str=N'select @ID=ID from '+ @tableName + ' where CUI=@CUI '
exec sp_executesql @str,N'@ID int output,@CUI int,@tableName varchar(15)',@ID output,@CUI,@tableName
select @ID
RETURN
*******测试是对的
运行dbo."ebsp_GetID" ( @tableName = eb_c_28, @CUI = 10201, @ID = <DEFAULT> ).
-----------
7
没有更多的结果。
(返回 1 行)
@ID = 7
@RETURN_VALUE = 0
完成 dbo."ebsp_GetID" 运行。3、程序运行返回值不对
parameter(0)和parameter(1) 都是对的;但是parameter(2)却是DBNULL,但是单独运行存储过程却是对的(=7),问题出在哪里????
很方便的