Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ADO.NET passing SQL InputOutput parameter problem

Posted on 2004-09-09
12
Medium Priority
?
688 Views
Last Modified: 2008-02-01
Hi.

I have problem with ParameterDirection.InputOutput.

I'm still in problem when passing imput value to InputOutput parameter. When i look on SQL I see that there is alway NULL pased regardles to what I set to Value.

Code:
SqlC_GetActualVersion.Parameters("@version").Value = CDec(Version)
SqlC_GetActualVersion.Parameters("@version").Direction = ParameterDirection.InputOutput
SqlC_GetActualVersion.Connection.Open()
SqlC_GetActualVersion.ExecuteReader()
SqlC_GetActualVersion.Connection.Close()
Version = CType(SqlC_GetActualVersion.Parameters("@version").Value, String)

SQL command send in background:
declare @P1 numeric(8,4)
SET @P1=NULL
EXEC dbo.GetActualVersion @version=@P1 output


Return value is Ok.
0
Comment
Question by:patrikt
12 Comments
 
LVL 10

Expert Comment

by:EBatista
ID: 12014845
you cannot access the reader value if you close the connection, and if you actually dont need the db cursor then use ExecuteNonQuery method instead:

SqlC_GetActualVersion.Connection.Open()
SqlC_GetActualVersion.ExecuteNonQuery()
SqlC_GetActualVersion.Connection.Close()
Version = CType(SqlC_GetActualVersion.Parameters("@version").Value, String)

regards
0
 
LVL 10

Expert Comment

by:EBatista
ID: 12015156
also ExecuteReader() method is for retrieve "records" from the database, it wont perform any insert or update, so if you are trying to write back changes or inserting rows to the db you have to use ExecuteNonQuery.
0
 
LVL 12

Author Comment

by:patrikt
ID: 12015157
Yes I know that. ExecuteReader is only "fosil" after my experimenting.

I'have found "way arround". Everything is working if ALL parameters are defined as InpurOutput and output in Sp. But why to do this? It looks like bug in SqlCommand.

Patrik
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 10

Expert Comment

by:EBatista
ID: 12015255
nope, no bugs, can u paste the whole code for the stored procedure
0
 
LVL 20

Expert Comment

by:ihenry
ID: 12017414
>> you cannot access the reader value if you close the connection
In addition to EBatista comment,
you can't as well retrieve the output parameters until close the datareader

Try this,
SqlC_GetActualVersion.Connection.Open()
SqlDataReader dr = SqlC_GetActualVersion.ExecuteReader()
dr.Close()
Version = CType(SqlC_GetActualVersion.Parameters("@version").Value, String)
SqlC_GetActualVersion.Connection.Close()
0
 
LVL 12

Author Comment

by:patrikt
ID: 12031223
There is no problem with reading parameters. There is problem with passing paramaters.
I investigatet this and there is my result:
1) Problem is only with SQLCommand. OLEDBCommand works well.
2) Problem is only when stored procedure has some but not all paramaters as output.
3) If 1 and 2 is true and I assign value to output parameter it is set to null when calling stored procedure regardles I set it as InputOutput.

Workaround is to set all parametere in stored procedure as output. Then it works OK. But it is still bug.

Any comments?
0
 
LVL 5

Accepted Solution

by:
ajitanand earned 2000 total points
ID: 12109537
hello,

The correct order for setting parameters is:
Create a SQLParameter Object
Assign the parameter direction and Set the value

Then add to the parameters collection of the Command, and then execute.

rgds,
Ajit Anand
0
 
LVL 12

Author Comment

by:patrikt
ID: 12367231
This question is not answered. I went arround the problem and have no time to investigate the source of errors.

Please give some points to Experts participating, but don't PAQ this question.

Patrik
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question