Getting Data from database with Stored Procedure

Question

Hello and thanks for reading.

I have a database that contains a Unique ID and Email. When I click a button I want to get all the Emails from the Database and display in my Textbox with id Emailliste.

If it works it should list all emails in the textbox with a , between them. Like this. Email1@email.com, Email2@email.com

Here is my C# code:

using (SqlConnection connection = new SqlConnection("Data Source=PCM13812;Initial Catalog=Newsletter;Integrated Security=True"))
    {
        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = "GetAllEmail";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = connection;

        connection.Open();

        string email = (string)cmd.ExecuteScalar();
        EmailListe.Text = email;

        connection.Close(); 
    }

Here is my script to create the stored procedure:

CREATE PROCEDURE [dbo].[GetAllEmail]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Email nvarchar(50)
SELECT @Email = COALESCE(@Email+ ', ', '') + Email
FROM Newsletter

END

Im not sure what Im doing wrong but i hope someone can help me

Answer 1

Your stored procedure (if we're talking about MS SQL) is not actually returning a result, you are missing an output SELECT. With the current procedure you are only assigning a value to @Email variable.

CREATE PROCEDURE [dbo].[GetAllEmail]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Email nvarchar(50)
SELECT @Email = COALESCE(@Email+ ', ', '') + Email
FROM Newsletter

SELECT @Email --// this is missing, so the proc will actually return a result

END

Answer 2

You need to select @Email from your procedure

CREATE PROCEDURE [dbo].[GetAllEmail]
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Email nvarchar(50)
SELECT @Email = COALESCE(@Email+ ', ', '') + Email
FROM Newsletter

-- You need do this
SELECT @Email
END