Output parameters return empty using VB.Net

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. This was the original code: -

Dim parameters As SqlParameter() = {New SqlParameter("@sName", SqlDbType.NVarChar, 50), _
New SqlParameter("@sEmail", SqlDbType.NVarChar, 100), _
New SqlParameter("@sID", SqlDbType.NVarChar, 20, ParameterDirection.Output), _
New SqlParameter("@sPassword", SqlDbType.NVarChar, 20, ParameterDirection.Output)}

parameters(0).Value = myCommon.sCheckString(Request.Form("NAME"))
parameters(1).Value = Request.Form("EMAIL")
parameters(2).Value = ""
parameters(3).Value = ""

However, this worked:-

Dim parameters As SqlParameter() = {New SqlParameter("@sName", SqlDbType.NVarChar, 50), _
New SqlParameter("@sEmail", SqlDbType.NVarChar, 100), _
New SqlParameter("@sID", SqlDbType.NVarChar, 20), _
New SqlParameter("@sPassword", SqlDbType.NVarChar, 20)}

parameters(0).Value = myCommon.sCheckString(Request.Form("NAME"))
parameters(1).Value = Request.Form("EMAIL")
parameters(2).Value = ""
parameters(3).Direction = ParameterDirection.Output
parameters(2).Value = ""
parameters(3).Direction = ParameterDirection.Output

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

Kayako Helpdesk Software

Professional hosting services for Kayako's range of helpdesk ticket systems and on-line sales and support chat, including installation, customisation and support.

Output parameters return empty using VB.Net

Problem

Solution

Kayako Helpdesk Software

Site Map

Useful Links