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. [email protected], [email protected]

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

Im not sure, if you could give me an examble i would love it. — Carsten Løvbo Andersen
– Carsten Løvbo Andersen, Commented Jan 20, 2014 at 14:47
What is not working? Do you get an exception or is the SP result always null? — Dannydust
– Dannydust, Commented Jan 20, 2014 at 14:51
Sorry forgot to say thats the problem is, my bad. The problem is that nothing get displayed in the Textbox when i click the button. — Carsten Løvbo Andersen
– Carsten Løvbo Andersen, Commented Jan 20, 2014 at 14:52

Pawan · Accepted Answer · 2014-01-20 15:01:17Z

1

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

answered Jan 20, 2014 at 15:01

Pawan

1,0855 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Carsten Løvbo Andersen Over a year ago

Thanks now it displays something, but it ONLY display the first 2 emails. not the 3'rd 4th and so on.

Pawan Over a year ago

Try to increase the size of @Email variable

Iztoksson · Accepted Answer · 2014-01-20 15:02:07Z

0

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

answered Jan 20, 2014 at 15:02

Iztoksson

9911 gold badge17 silver badges33 bronze badges

Collectives™ on Stack Overflow

Getting Data from database with Stored Procedure

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related