ADO.NET passing SQL InputOutput parameter problem

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.
LVL 12
patriktAsked:
Who is Participating?
 
ajitanandCommented:
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
 
EBatistaCommented:
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
 
EBatistaCommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
patriktAuthor Commented:
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
 
EBatistaCommented:
nope, no bugs, can u paste the whole code for the stored procedure
0
 
ihenryCommented:
>> 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
 
patriktAuthor Commented:
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
 
patriktAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.