Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@.id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@.parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.It's DBNull.Value|||Try:
sqlPar = new SqlParameter("@.parent_id", DBNull.Value)|||Perfect, it works, but now I have another problem.
I get this error:
This SqlTransaction has completed; it is no longer usable.
When I run Commit or Rollback code.|||Are you committing the transaction twice accidentally?|||No, I'm sure.
The only "strange" thing I do it's to call 2 different functions in my class to begin and commit/rollback transaction.|||Then you will have to show some code...|||'CConn is my class
CConn.BeginTransaction()
Dim arParameters as new ArrayList()
Dim sqlParOutput = new SqlParameter("@.id", SqlDbType.Int)
sqlParOutput.Direction = ParameterDirection.Output
arParameters.Add(sqlParOutput)
Dim sqlPar = new SqlParameter("@.parent_id", DBNull.Value)
arParameters.Add(sqlPar)
objTransaction = CConn.ExecuteNonQuery("sp", arParameters, false)
if objTransaction is nothing then objTransaction = CConn.ExecuteNonQuery("...")
if objTransaction is nothing then
CConn.CommitTransaction()
else
CConn.RollbackTransaction()
end if
The Begin, Commit and Rollback functions in CConn class simply call the same functions of SqlConnection object.
The ExecuteNonQuery function override standard function.|||This helps sort of not at all. We do not know what your class is doing. Is the return from CConn.ExecuteNonQuery a transaction object? What is the significance of objTransaction being nothing?|||Sorry, you're right.
Here the class methods:
Public function BeginTransaction() as SqlTransaction
Me.Conn = New SqlConnection(Me.sConnStr)
Me.Conn.Open()
'Start a local transaction
Me.myTransaction = Conn.BeginTransaction()
end function Public sub CommitTransaction()
Me.myTransaction.Commit()
Me.Conn.Close()
end sub
Public function RollbackTransaction() 'transaction as SqlTransaction)
Me.myTransaction.Rollback()
Me.Conn.Close()
end function
Public function ExecuteNonQuery(ByVal SQL As String, optional sqlParameters as ArrayList = nothing, optional toClose as boolean = true) as object
Dim objReturn as object
if Me.Conn is nothing then
Me.Conn = New SqlConnection(Me.sConnStr)
Me.Conn.Open()
else
if Me.Conn.State <> ConnectionState.Open then Me.Conn.Open()
end if
Dim sqlCmd As New SqlCommand(SQL, Me.Conn)
'Must assign transaction object to Command object for a pending local transaction
if not Me.myTransaction is nothing then sqlCmd.Transaction = Me.myTransaction
Try
if not sqlParameters is nothing then
sqlCmd.CommandType = CommandType.StoredProcedure
Dim sqlPar as SqlParameter
for each sqlPar in sqlParameters
sqlCmd.Parameters.Add(sqlPar)
next
else
sqlCmd.CommandType = CommandType.Text
end if
sqlCmd.ExecuteNonQuery()
Catch e As Exception
objReturn = e
End Try
'must be clean up?
if toClose then
Me.Conn.Close()
sqlCmd.Dispose()
Me.Conn.Dispose()
end if
return objReturn
End function
