473,903 Members | 4,060 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VB.NET SQL Server Stored Procedure

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
Jul 20 '05 #1
3 18612
Michael (mh*******@core-techs.net) writes:
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...
8.00.194 is the RTM release, and 534 is SP2. The most current service
pack is SP3, which is build 760. Many run with a security fix which
has build number 818.
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)


I am right to assume that if you bave no parameters you will actually
use CommandType.Tex t? I cannot really be sure if I can conclude this
from your VB code.

Anyway, maybe you should add a parameter for the return value. Name
RETURN_VALUE, type OleDbTypes.Inte ger and Direction.Retur nValue. This
should be first parameter.

You could also use the Profiler to see what the program submits to
SQL Server.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
DFS
"Erland Sommarskog" <so****@algonet .se> wrote in message
8.00.194 is the RTM release, and 534 is SP2. The most current service
pack is SP3, which is build 760. Many run with a security fix which
has build number 818.

And it looks like you can determine this value by executing

SELECT SERVERPROPERTY( 'ProductVersion ')

or by looking at the About screen in Query Analyzer.

I have SP3a installed; it shows 8.00.760.

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)


I am right to assume that if you bave no parameters you will actually
use CommandType.Tex t? I cannot really be sure if I can conclude this
from your VB code.

Anyway, maybe you should add a parameter for the return value. Name
RETURN_VALUE, type OleDbTypes.Inte ger and Direction.Retur nValue. This
should be first parameter.

You could also use the Profiler to see what the program submits to
SQL Server.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3
or SELECT @@VERSION

On Wed, 28 Jan 2004 19:06:19 -0500, "DFS" <no****@nospam. com> wrote:
And it looks like you can determine this value by executing

SELECT SERVERPROPERTY( 'ProductVersion ')

or by looking at the About screen in Query Analyzer.


Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
7326
by: Yves Touze | last post by:
Hi All, I'm trying to migrate from SQL Server 7.0 to SQL Server 2000. I've got some ASP page which call VB components that retrieve shaped recordsets from SQL Server using the MSDATASHAPE provider. Precisely, here is the code i have Dim Cmdobj As New ADODB.Command Cmdobj.ActiveConnection = oconn Cmdobj.CommandType = adCmdStoredProc
18
10334
by: Robin Lawrie | last post by:
Hi again, another problem! I've moved from an Access database to SQL server and am now having trouble inserting dates and times into seperate fields. I'm using ASP and the code below to get the date and time, but my script is erroring. '-- Get login date and time cmdLoginDate = Date() cmdLoginTime = Time()
5
3503
by: gilles27 | last post by:
I've ready many of the posts on this and other newsgroups in which people describe working practices for source control of database scripts. We are looking to implement something similar in my current workplace. We have agreed that developers should not modify objects such as views or stored procedures directly, they should check the script out of VSS first, modify it, run it, and then check it back in. The problem we are having is...
26
3849
by: David W. Fenton | last post by:
A client is panicking about their large Access application, which has been running smoothly with 100s of thousands of records for quite some time. They have a big project in the next year that will lead to a lot of use of the database and the adding of quite a lot of new data (though I can't conceive of them adding more than than 10s of thousands of records, which won't change the current performance profile at all). If there is a SQL...
4
2947
by: Bill Sonia | last post by:
Hello, I have a Widnows Service that creates a system event that I would like to have a Sql Server stored procedure fire when the stored procedure is called. I have a Windows Service that runs on my database server that will wait for this event before it will execute (via the Win32.CreateEvent and Win32.WaitForSingleObject). The trouble I'm having is actually throwing the event from inside a Sql Server Stored Procedure.
0
2661
by: Amber | last post by:
Stored procedures are faster and more efficient than in-line SQL statements. In this article we will look at two SQL Server stored procedures; one using an input parameter and one not, and see how to call them from an ASP.Net page Every modern database system has a stored procedure language. SQL Server is no different and has a relatively sophisticated and easy to use system. This article will not attempt to go into depth in explaining...
2
6993
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of attending interviews. If you own a company best way to judge if the candidate is worth of it. http://www.questpond.com/InterviewRatingSheet.zip
12
2271
by: Light | last post by:
Hi all, I posted this question in the sqlserver.newusers group but I am not getting any response there so I am going to try it on the fine folks here:). I inherited some legacy ASP codes in my office. The original code's backend is using the SQL Server 2000 and I am testing to use it on the Express edition. And I run into the following problem.
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11279
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10872
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10981
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9675
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8047
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3323
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.