Stored Procedure and ADO.NET
-
I have a Stored Procedure with one input parameter and one output parameter. I create a SQLCommand for a Stored Procedure I add parameter1 with direction input I add parameter2 with direction output I add parameter 1 and paramter2 to the Cmd's parameter Collection I assign an input value to parameter1 I invoke the command's ExecuteReader() and try to output the result(Parameter2) via response.write I get a blank? ps: 1. Procedure works on Query Analyzer 2. At the very end of code I verify two parameter are in the Paramters collection.
-
I have a Stored Procedure with one input parameter and one output parameter. I create a SQLCommand for a Stored Procedure I add parameter1 with direction input I add parameter2 with direction output I add parameter 1 and paramter2 to the Cmd's parameter Collection I assign an input value to parameter1 I invoke the command's ExecuteReader() and try to output the result(Parameter2) via response.write I get a blank? ps: 1. Procedure works on Query Analyzer 2. At the very end of code I verify two parameter are in the Paramters collection.
Try assigning a value to the output parameter and see if it changes. Make it in/out and change the SP to store the incoming value somewhere so you can see if it's getting there OK. Check your spelling. :-) Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
-
Try assigning a value to the output parameter and see if it changes. Make it in/out and change the SP to store the incoming value somewhere so you can see if it's getting there OK. Check your spelling. :-) Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
Christian: Thanks for the quick reply. Although I coded as an output while in Query Analyzer, it actually stores it as input/ouput, since while retrieving the output you have to read it into a variable.My spelling must be OK, since no exceptions are raised. I will diagnose some more, but I will get there. I just do not want to reinvent the wheel. Is 'Command.ExecuteReader' the correct way? The above is invokation is OK for a stored procedure with one input returning a number of rows of result. mysorian
-
Christian: Thanks for the quick reply. Although I coded as an output while in Query Analyzer, it actually stores it as input/ouput, since while retrieving the output you have to read it into a variable.My spelling must be OK, since no exceptions are raised. I will diagnose some more, but I will get there. I just do not want to reinvent the wheel. Is 'Command.ExecuteReader' the correct way? The above is invokation is OK for a stored procedure with one input returning a number of rows of result. mysorian
Here's a snippet of my code that does what you're trying to do:
SqlCommand cmd = new SqlCommand("SetTrackThumbNail", Connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@thumbNailPath", thumbNailPath); cmd.Parameters.Add("@artistName", artistName); cmd.Parameters.Add("@trackID", trackID); cmd.Parameters.Add("@thumbID", -1); cmd.Parameters["@thumbID"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); return (int) cmd.Parameters["@thumbID"].Value;Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer -
Here's a snippet of my code that does what you're trying to do:
SqlCommand cmd = new SqlCommand("SetTrackThumbNail", Connection); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@thumbNailPath", thumbNailPath); cmd.Parameters.Add("@artistName", artistName); cmd.Parameters.Add("@trackID", trackID); cmd.Parameters.Add("@thumbID", -1); cmd.Parameters["@thumbID"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); return (int) cmd.Parameters["@thumbID"].Value;Christian I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard StringerChristian: Thanks a lot. Actually, I finally made it work with ExecuteReader()method of the command. What happens actually, is that there is no streaming data and you cannot use the DataReader's Read(). There is nothing to read. However, you may just read out the Output parameter's value. I will try if the ExecuteNonQuery() works as well, as nothing is returned, but everything is contained. Thanks jay