This one's really got me. I have a VB.NET (version 1.1.4322) project
that provides an easy way to execute stored procedures on a generic
level. When I run the code on computer A (running SQL Server 2000
version 08.00.0194) the code works great. However, computer B
(running SQL Server 2000 version 08.00.0534) bombs when I try to
execute the sproc saying 'Could not find stored procedure
'spmw_ReadByPag e'. My thought process went as follows...
1. Does the procedure really exist. Yes it did. (I tried fully
qualifying it too...'dbo.spmw _ReadByPage')
2. Do I have permission to execute the procedure with the way I'm
logging into the database. Yes I did.
3. Can I execute a different stored procedure in that db with the
exact same code. Yes I could.
4. Can I run the same procedure simpliefied to just return a value and
no parameters. YES I COULD!!
5. So it must be an error in the stored procedure. NO, it executes
with the same parameters in Query Analyzer just fine.
6. At this point I guess that what I've come to is....that in version
08.00.0534 of SQL SERVER 2000, I could NOT execute any stored
procedure in VB.NET if it accepted parameters (Of course, I mean by
using the OleDBCommand object)
Is this true? Is it just me? Any help would be greatly appreciated.
Here's what my code looks like. (By the way, the Parameters collection
just has some home-made objects that have the same properties as a
OleDBParameter object, so you don't need it to try the example. Any
sproc that takes parameters should reproduce the error.)
Public Function ExecuteProc(ByV al ExecutionStyle As ExecutionStyle,
Optional ByVal sSQL As String = "") As Boolean
'Create a command object
Dim oCommand As New OleDbCommand
'Create a connection to our default database and open it
Dim oConn As New OleDbConnection (DBConn.Default ConnectString)
oConn.Open()
Try
'Go ahead and assing our connection to our Command object
oCommand.Connec tion = oConn
'OK. Did they pass us an SQL statement?
If sSQL.Trim <> "" And IsNothing(Param eters) Then
Try to use the sql statement
oCommand.Comman dType = CommandType.Tex t
oCommand.Comman dText = sSQL
Else
'Don't sweat it, we'll do it for ya
oCommand.Comman dType = CommandType.Sto redProcedure
'What's the name of the procedure?
oCommand.Comman dText = ProcedureName
'Use the Parameters the user has specified to create
the
'command object parameters
For l = 1 To Parameters.Coun t
With Parameters(l)
Dim oParm As New OleDb.OleDbPara meter
'Create a new parameter
oParm = oCommand.Create Parameter()
'Set our parm properties
oParm.Parameter Name = .Name
oParm.Direction = .Direction
oParm.OleDbType = .Type
oParm.Value = .Value
'Add parameter to our command
oCommand.Parame ters.Add(oParm)
End With
Next
End If
'Execute our command the way we specified
Select Case ExecutionStyle
Case ExecutionStyle. ExecuteNonQuery
mRowsAffected = oCommand.Execut eNonQuery
Case ExecutionStyle. ExecuteResultSe t
'Throw that guy in a table so that we can
Dim oAdapter As New OleDbDataAdapte r(oCommand)
Dim oSet As New DataSet
'Use our data adapter to fill our data set
oAdapter.Fill(o Set, "ResultSet" )
'User our new data table to set our properties
mResultSet = oSet.Tables("Re sultSet")
mRowsAffected = 0
mResultCount = oSet.Tables("Re sultSet").Rows. Count
Case ExecutionStyle. ExecuteScalar
'Execute this guy returning a single value as an
object
mScalarValue = oCommand.Execut eScalar()
If Not IsNothing(mScal arValue) Then
mResultCount = 1
End If
End Select
'Now that we have executed our commands, we need to
'populate the value property for our Output and Return
values
For l = 0 To oCommand.Parame ters.Count - 1
With oCommand.Parame ters(l)
If .Direction = ParameterDirect ion.InputOutput _
Or .Direction = ParameterDirect ion.Output Then
Parameters(l).V alue = .Value
End If
End With
Next
'Cleanup
oConn.Close()
ExecuteProc = True
Catch ex As Exception
mResultDesc = ex.Message
mResultCode = Err.Number
End Try
End Function
that provides an easy way to execute stored procedures on a generic
level. When I run the code on computer A (running SQL Server 2000
version 08.00.0194) the code works great. However, computer B
(running SQL Server 2000 version 08.00.0534) bombs when I try to
execute the sproc saying 'Could not find stored procedure
'spmw_ReadByPag e'. My thought process went as follows...
1. Does the procedure really exist. Yes it did. (I tried fully
qualifying it too...'dbo.spmw _ReadByPage')
2. Do I have permission to execute the procedure with the way I'm
logging into the database. Yes I did.
3. Can I execute a different stored procedure in that db with the
exact same code. Yes I could.
4. Can I run the same procedure simpliefied to just return a value and
no parameters. YES I COULD!!
5. So it must be an error in the stored procedure. NO, it executes
with the same parameters in Query Analyzer just fine.
6. At this point I guess that what I've come to is....that in version
08.00.0534 of SQL SERVER 2000, I could NOT execute any stored
procedure in VB.NET if it accepted parameters (Of course, I mean by
using the OleDBCommand object)
Is this true? Is it just me? Any help would be greatly appreciated.
Here's what my code looks like. (By the way, the Parameters collection
just has some home-made objects that have the same properties as a
OleDBParameter object, so you don't need it to try the example. Any
sproc that takes parameters should reproduce the error.)
Public Function ExecuteProc(ByV al ExecutionStyle As ExecutionStyle,
Optional ByVal sSQL As String = "") As Boolean
'Create a command object
Dim oCommand As New OleDbCommand
'Create a connection to our default database and open it
Dim oConn As New OleDbConnection (DBConn.Default ConnectString)
oConn.Open()
Try
'Go ahead and assing our connection to our Command object
oCommand.Connec tion = oConn
'OK. Did they pass us an SQL statement?
If sSQL.Trim <> "" And IsNothing(Param eters) Then
Try to use the sql statement
oCommand.Comman dType = CommandType.Tex t
oCommand.Comman dText = sSQL
Else
'Don't sweat it, we'll do it for ya
oCommand.Comman dType = CommandType.Sto redProcedure
'What's the name of the procedure?
oCommand.Comman dText = ProcedureName
'Use the Parameters the user has specified to create
the
'command object parameters
For l = 1 To Parameters.Coun t
With Parameters(l)
Dim oParm As New OleDb.OleDbPara meter
'Create a new parameter
oParm = oCommand.Create Parameter()
'Set our parm properties
oParm.Parameter Name = .Name
oParm.Direction = .Direction
oParm.OleDbType = .Type
oParm.Value = .Value
'Add parameter to our command
oCommand.Parame ters.Add(oParm)
End With
Next
End If
'Execute our command the way we specified
Select Case ExecutionStyle
Case ExecutionStyle. ExecuteNonQuery
mRowsAffected = oCommand.Execut eNonQuery
Case ExecutionStyle. ExecuteResultSe t
'Throw that guy in a table so that we can
Dim oAdapter As New OleDbDataAdapte r(oCommand)
Dim oSet As New DataSet
'Use our data adapter to fill our data set
oAdapter.Fill(o Set, "ResultSet" )
'User our new data table to set our properties
mResultSet = oSet.Tables("Re sultSet")
mRowsAffected = 0
mResultCount = oSet.Tables("Re sultSet").Rows. Count
Case ExecutionStyle. ExecuteScalar
'Execute this guy returning a single value as an
object
mScalarValue = oCommand.Execut eScalar()
If Not IsNothing(mScal arValue) Then
mResultCount = 1
End If
End Select
'Now that we have executed our commands, we need to
'populate the value property for our Output and Return
values
For l = 0 To oCommand.Parame ters.Count - 1
With oCommand.Parame ters(l)
If .Direction = ParameterDirect ion.InputOutput _
Or .Direction = ParameterDirect ion.Output Then
Parameters(l).V alue = .Value
End If
End With
Next
'Cleanup
oConn.Close()
ExecuteProc = True
Catch ex As Exception
mResultDesc = ex.Message
mResultCode = Err.Number
End Try
End Function
Comment