(Resolved) Return Value from Stored Proc

[FastEddie]

I am trying to get a value back from a stored procedure but I haven't been able to get the code right and the samples I have looked at have added more confusion. Here is what I have:

' TestForm
    Private Sub Button1_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim myemail As String
        myemail = CStr(GetEmail(1))
        MessageBox.Show(myemail)
 
    End Sub
    Private Function GetEmail(ByVal Store As Integer) As String
 
        Dim cn As SqlConnection = New SqlConnection
        Dim cmd As SqlCommand = New SqlCommand("sproc_GetDMEmail", cn)
        Dim par As SqlParameter = New SqlParameter
 
        cn.ConnectionString = Con
        cn.Open()
 
        par.ParameterName = "@Store"
        par.Direction = ParameterDirection.Output
        par.Value = store
 
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(par)
        cmd.ExecuteNonQuery()
 
        Return cmd.Parameters.Add(par).Value.ToString
 
    End Function

The error I get is The formal parameter "@Store" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

[GaryMazzone]

What is the SP definition?

[FastEddie]

sp definition? I am very new at this so I am not sure what you mean but I can run the stored procedure from Studio Manager with this

EXEC sproc_GetDmEmail 1

and get the correct result so I know it works. The entire SP looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sproc_GetDMEmail 
(@Store VARCHAR(255))
AS
BEGIN
	SET NOCOUNT ON;
Select EmailAddress FROM tblDmEmail WHERE StoreID = @Store
END
GO

[GaryMazzone]

Well I think that in the definition you have @Store defined as an input parameter. I also think that you want to return the result set of the query where is that set as ether a dataset or datareader in the VB code?

[FastEddie]

Well a big DUHHHHH goes to me. Your post made me realize I just worked through this problem one project ago. Here is a sample for those of you who may find yourself with the same problem.

Dim strEmail As String = Nothing
        Dim strSp As String = "sproc_GetDmEmail"
        Dim cn As SqlConnection = New SqlConnection
        Dim cmd As SqlCommand = New SqlCommand(strSp, cn)
        Dim dr As SqlDataReader
        cn.ConnectionString = Con
        cn.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@Store", SqlDbType.VarChar).Value = parStore
        Try
            dr = cmd.ExecuteReader()
            While dr.Read()
                With dr
                    strEmail = CStr(.Item("EmailAddress"))
                End With
            End While
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbNewLine & _
            ex.Source, "Execute SP Error", MessageBoxButtons.OK)
        End Try
        Return strEmail