OUTPUT parameters issue. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

OUTPUT parameters issue.

Author

Topic

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-18 : 07:00:51

Salute..
PLEASE..I NEED YOUR HELP!..I AM REALLY STUCK!!
I have this stored procedure 'spAccounts'
(@Action char(10)='' Output, @AccountId int=0 Output, @CompanyId int=0 Output, @AccountName char(50)="" Output) AS
IF @Action = 'INQID'
BEGIN
SELECT @CompanyId = CompanyId, @AccountName = AcountName FROM GLR_Accounts WHERE AcountId = @AccountId
END

In VB.NET, I drag and drop an SQL command..I fill the paramters as follows then execute the command:
cmd.parameters("@Action").Value = "INQID"
cmd.parameters("@AccountId").Value = 15788
cmd.ExecuteNonQuery

MsgBox cmd.parameters("@CompanyId").Value
MsgBox cmd.parameters("@AccountName").Value

THE @CompanyId parameter or ANY INTEGER paramter is not returning the selected values but it is returning the default value..WHILE the @AccountName paramter or ANY CHAR parameter is RETURING THE CORRECT SELECTED VALUE.
I know that the stored procedure is fetching the correct values..i traced it and it is also setting the output paramters in the select statement with correct values..but when it returns to my front end the char parameters are filled correctly while the int params are filled with thier default values..
what could be the problem???..
p.s.when i remove the default values from the sp and fill the paramters manually everything works FINE!!
Thanks In Advance

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-09-18 : 07:18:51

ADO (and I presume ADO.NET) doesn't actually understand the order of the parameters, even though you specify the parameter names.
You must include all the paramaters in the same order as the stored proc is expecting them. Take a look at your parameter calls in profiler to see what ADO is actually passing to the database.

Damian

BigRetina
Posting Yak Master

144 Posts

Posted - 2002-09-18 : 08:38:35

well i dont think that this is correct because I read an article in MSDN saying that ADO.NET does not "care" about paramters ordering. It also said that u dont even have to create all the paramters in the program if the sp supplies default values!.
I am calling and using the paramters by names..and it is working!.
the problem is that If i supply all the paramters values from VB.NET everything works fine.
If I dont supply all of them it does not return correct values..but the char paramters are exception..I DONT KNOW WHY!!..

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-09-18 : 08:46:14

OK, I haven't delved into the inner workings of ADO.NET yet, so I'll have to take your word of taking MSDN's word.

But, as I said before, run profiler on your database and you will see exactly what ADO.NET is doing to your database.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources