473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

VB.Net & SQL2005 Stored Procedures

i have a simple application that is calling a storedprocedure from a SQL 2005
server. It executes the stored procdure fine, however when i added a @RTN
INT OUTPUT to my stored procedure and tried to get the result , i fell into
all sorts of trouble

Dim objSQLConn As New SqlConnection("SERVER=.; UID=BossSystem;
PWD=AE93lo175; DATABASE=BossData;")
Dim SQLCMD As SqlClient.SqlCommand
SQLCMD = New SqlClient.SqlCommand
SQLCMD.CommandType = CommandType.StoredProcedure
SQLCMD.CommandText = "Bossdata.dbo.VBNET_XML_ARUCSAS"
SQLCMD.Connection = objSQLConn
'---------------------------------------
' New code Added to get return value
Dim myParm As SqlParameter = SQLCMD.Parameters.Add("@RTN",
SqlDbType.Int)
myParm.Direction = ParameterDirection.ReturnValue
'---------------------------------------
Dim Reader As SqlClient.SqlDataReader
objSQLConn.Open()
Reader = SQLCMD.ExecuteReader
' Insert code to read through the datareader.
Reader.Close()
objSQLConn.Close()
when exacuting this the application fails with

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.

yet if i remove the output parameter from the stored proc and remove the two
lines of code from my vb application it works fine... I need to get a return
code from the stored procedure ..

Thank you in advance for any help
Mar 10 '06 #1
1 1199
EC
I think you want ParameterDirection.Output instead of
ParameterDirection.ReturnValue

ReturnValue is usually used for Rows Affected, you're trying to capture
an output parameter.

http://msdn.microsoft.com/library/de...turnvalues.asp

Peter Newman wrote:
i have a simple application that is calling a storedprocedure from a SQL 2005
server. It executes the stored procdure fine, however when i added a @RTN
INT OUTPUT to my stored procedure and tried to get the result , i fell into
all sorts of trouble

Dim objSQLConn As New SqlConnection("SERVER=.; UID=BossSystem;
PWD=AE93lo175; DATABASE=BossData;")
Dim SQLCMD As SqlClient.SqlCommand
SQLCMD = New SqlClient.SqlCommand
SQLCMD.CommandType = CommandType.StoredProcedure
SQLCMD.CommandText = "Bossdata.dbo.VBNET_XML_ARUCSAS"
SQLCMD.Connection = objSQLConn
'---------------------------------------
' New code Added to get return value
Dim myParm As SqlParameter = SQLCMD.Parameters.Add("@RTN",
SqlDbType.Int)
myParm.Direction = ParameterDirection.ReturnValue
'---------------------------------------
Dim Reader As SqlClient.SqlDataReader
objSQLConn.Open()
Reader = SQLCMD.ExecuteReader
' Insert code to read through the datareader.
Reader.Close()
objSQLConn.Close()
when exacuting this the application fails with

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.

yet if i remove the output parameter from the stored proc and remove the two
lines of code from my vb application it works fine... I need to get a return
code from the stored procedure ..

Thank you in advance for any help


Mar 10 '06 #2

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

Similar topics

3
by: Dan Sikorsky | last post by:
How can I get the recordset attributes for a table field in SQL Server 2000 to report the field updatable attribute correctly ... mine keeps saying the fields are not updatable? That is, (...
7
by: helmut woess | last post by:
Hi, has anybody knowledge about the safetyness of encrypting stored procs in SQL-Server 2005 using WITH ENCRYPTION? Or can they be hacked with the same old tools which exists for SQL 2000? ...
0
by: Jim Sneeringer | last post by:
I have a GridView that loads correctly from a SQL table using a TableAdapter and stored procedures. However, when I try to delete from the GridView, I get "Procedure or Function 'EventDelete'...
1
by: Demetri | last post by:
I have a question / concern regarding the new suggested way of creating a data access layer in an n-tier application. Typically, a web application specifically, using the SOA (Service Oriented...
4
by: scparker | last post by:
Hello, We have a stored procedure that does a basic insert of values. I am then able to retrieve the ID number created for this new record. We are currently using ASP.NET 2.0 and use N-Tier...
9
by: Nemisis | last post by:
Hi everyone, hope your all looking forward to xmas. I am setting up a Sql2005 database on a Windows Server, running Windows Server 2003. The database is going to be accessed via users using an...
3
by: Blasting Cap | last post by:
I'm using VS 2005, SQL 2005 reporting services. SQL reporting services is working, and I have it both on my local computer, as well as on a server. I've created a report in the SQL Business...
3
mafaisal
by: mafaisal | last post by:
Hello Experts how to retrieve images From sql2005 to vb.net2005 Also i insert image into database using stored procedures, Is any other way to insert in vb.net2005 Faisal
1
by: mansi sharma | last post by:
Functions ia a block of code that performs some task & returns value. Can somebody tell me What are Stored Procedures? I found abt Stored procedues from the Net-->Stored Procedures is a group of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.