patrikt
asked on
ADO.NET passing SQL InputOutput parameter problem
Hi.
I have problem with ParameterDirection.InputOu tput.
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.Para meters("@v ersion").V alue = CDec(Version)
SqlC_GetActualVersion.Para meters("@v ersion").D irection = ParameterDirection.InputOu tput
SqlC_GetActualVersion.Conn ection.Ope n()
SqlC_GetActualVersion.Exec uteReader( )
SqlC_GetActualVersion.Conn ection.Clo se()
Version = CType(SqlC_GetActualVersio n.Paramete rs("@versi on").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.
I have problem with ParameterDirection.InputOu
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.Para
SqlC_GetActualVersion.Para
SqlC_GetActualVersion.Conn
SqlC_GetActualVersion.Exec
SqlC_GetActualVersion.Conn
Version = CType(SqlC_GetActualVersio
SQL command send in background:
declare @P1 numeric(8,4)
SET @P1=NULL
EXEC dbo.GetActualVersion @version=@P1 output
Return value is Ok.
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.
ASKER
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
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
nope, no bugs, can u paste the whole code for the stored procedure
>> 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.Conn ection.Ope n()
SqlDataReader dr = SqlC_GetActualVersion.Exec uteReader( )
dr.Close()
Version = CType(SqlC_GetActualVersio n.Paramete rs("@versi on").Value , String)
SqlC_GetActualVersion.Conn ection.Clo se()
In addition to EBatista comment,
you can't as well retrieve the output parameters until close the datareader
Try this,
SqlC_GetActualVersion.Conn
SqlDataReader dr = SqlC_GetActualVersion.Exec
dr.Close()
Version = CType(SqlC_GetActualVersio
SqlC_GetActualVersion.Conn
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Please give some points to Experts participating, but don't PAQ this question.
Patrik
SqlC_GetActualVersion.Conn
SqlC_GetActualVersion.Exec
SqlC_GetActualVersion.Conn
Version = CType(SqlC_GetActualVersio
regards