Output parameters return empty using VB.Net

By richard | 24th April 2009

[et_pb_section bb_built=”1″][et_pb_row][et_pb_column type=”4_4″][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

Output parameters from a SQL Server stored procedure are not returned when you run an ADO.NET command in Visual Basic

Problem

A stored procedure needs to return 2 parameters, which are set as output parameters in the stored procedure and in the VB.Net code that calls the SP. However, the return values are always returned blank.

The stored procedure is known to be creating the output values correctly.

Solution

The answer lies in the way the direction of the parameter is set in the VB.Net code.

[/et_pb_text][/et_pb_column][/et_pb_row][et_pb_row][et_pb_column type=”1_2″][et_pb_dmb_code_snippet _builder_version=”3.0.106″ title=”Original code:” style=”default” linenums=”off” usetabwidth=”off”]RGltIHBhcmFtZXRlcnMgQXMgU3FsUGFyYW1ldGVyKCkgPSB7TmV3IFNxbFBhcmFtZXRlcigiQHNOYW1lIiwgU3FsRGJUeXBlLk5WYXJDaGFyLCA1MCksIF8KTmV3IFNxbFBhcmFtZXRlcigiQHNFbWFpbCIsIFNxbERiVHlwZS5OVmFyQ2hhciwgMTAwKSwgXwpOZXcgU3FsUGFyYW1ldGVyKCJAc0lEIiwgU3FsRGJUeXBlLk5WYXJDaGFyLCAyMCwgUGFyYW1ldGVyRGlyZWN0aW9uLk91dHB1dCksIF8KTmV3IFNxbFBhcmFtZXRlcigiQHNQYXNzd29yZCIsIFNxbERiVHlwZS5OVmFyQ2hhciwgMjAsIFBhcmFtZXRlckRpcmVjdGlvbi5PdXRwdXQpfQoKcGFyYW1ldGVycygwKS5WYWx1ZSA9IG15Q29tbW9uLnNDaGVja1N0cmluZyhSZXF1ZXN0LkZvcm0oIk5BTUUiKSkKcGFyYW1ldGVycygxKS5WYWx1ZSA9IFJlcXVlc3QuRm9ybSgiRU1BSUwiKQpwYXJhbWV0ZXJzKDIpLlZhbHVlID0gIiIKcGFyYW1ldGVycygzKS5WYWx1ZSA9ICIi[/et_pb_dmb_code_snippet][/et_pb_column][et_pb_column type=”1_2″][et_pb_dmb_code_snippet _builder_version=”3.0.106″ title=”Modified code, working:” style=”default” linenums=”off” usetabwidth=”off”]RGltIHBhcmFtZXRlcnMgQXMgU3FsUGFyYW1ldGVyKCkgPSB7TmV3IFNxbFBhcmFtZXRlcigiQHNOYW1lIiwgU3FsRGJUeXBlLk5WYXJDaGFyLCA1MCksIF8KTmV3IFNxbFBhcmFtZXRlcigiQHNFbWFpbCIsIFNxbERiVHlwZS5OVmFyQ2hhciwgMTAwKSwgXwpOZXcgU3FsUGFyYW1ldGVyKCJAc0lEIiwgU3FsRGJUeXBlLk5WYXJDaGFyLCAyMCksIF8KTmV3IFNxbFBhcmFtZXRlcigiQHNQYXNzd29yZCIsIFNxbERiVHlwZS5OVmFyQ2hhciwgMjApfQoKcGFyYW1ldGVycygwKS5WYWx1ZSA9IG15Q29tbW9uLnNDaGVja1N0cmluZyhSZXF1ZXN0LkZvcm0oIk5BTUUiKSkKcGFyYW1ldGVycygxKS5WYWx1ZSA9IFJlcXVlc3QuRm9ybSgiRU1BSUwiKQpwYXJhbWV0ZXJzKDIpLlZhbHVlID0gIiIKcGFyYW1ldGVycygzKS5EaXJlY3Rpb24gPSBQYXJhbWV0ZXJEaXJlY3Rpb24uT3V0cHV0CnBhcmFtZXRlcnMoMikuVmFsdWUgPSAiIgpwYXJhbWV0ZXJzKDMpLkRpcmVjdGlvbiA9IFBhcmFtZXRlckRpcmVjdGlvbi5PdXRwdXQ=[/et_pb_dmb_code_snippet][/et_pb_column][/et_pb_row][et_pb_row][et_pb_column type=”4_4″][et_pb_text _builder_version=”3.0.106″ background_layout=”light”]

More information here: http://support.microsoft.com/kb/308051

[/et_pb_text][/et_pb_column][/et_pb_row][/et_pb_section]

Posted in Tips