RockOn RockOn - 26 days ago 7
SQL Question

Stored Procedure - Select with parameters and fill textboxes

I want my stored procedure that selects data from a table and puts this data into textboxes. Essentially this is used when a person selects an item from a dropdown menu and then wants to edit that item.

What am I doing wrong here?

C#

string eventName = TextBoxEventName.Text;
string location = TextBoxLocation.Text;
string city = TextBoxCity.Text;
string state = DropDownListState2.SelectedValue;
string description = TextBoxDescription.Text;

string eventSelected = DropDownListEvents.SelectedValue;

using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Events2"].ConnectionString))
{ using (var command = sqlConn.CreateCommand())
{

command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "spGetEvent";

command.Parameters.Add("@EventId", SqlDbType.Int).Value = eventSelected;

using (var reader = command.ExecuteReader())
{
while (reader.Read())
{

command.Parameters.Add("@EventName", SqlDbType.NVarChar, 255).Value = eventName;
command.Parameters.Add("@Location", SqlDbType.NVarChar, 255).Value = location;
command.Parameters.Add("@City", SqlDbType.NVarChar, 30).Value = city;
command.Parameters.Add("@State", SqlDbType.NVarChar, 2).Value = state;
command.Parameters.Add("@Description", SqlDbType.NVarChar, -1).Value = description;
}
}


Part of my stored procedure

PROCEDURE [dbo].[spGetEvent]
@EventId int,
@EventName nvarchar(255),
@Location nvarchar(255),
@City nvarchar(30),
@State nvarchar(2),
@Description nvarchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT *
FROM Event
WHERE EventId = @EventId AND
EventName = @EventName AND
Location = @Location AND
City = @City AND
State = @State AND
Description = @Description
END

Answer

You are executing Command before setting all the parameters.

using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Events2"].ConnectionString))
{ using (var command = sqlConn.CreateCommand())
    {

        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.CommandText = "spGetEvent";

         command.Parameters.Add("@EventId", SqlDbType.Int).Value = eventSelected;

        using (var reader = command.ExecuteReader())
         {
             if(reader.Read())
             {
                 eventName.Text = reader["EventName"].ToString();
                 location.Text = reader["Location"].ToString();
                 city.Text = reader["City"].ToString();
                 state.Text = reader["State"].ToString();
                 description.Text = reader["Description"].ToString();
             }
         }
    }
}

Change your stored proc to accept only EventId as parameter:

PROCEDURE [dbo].[spGetEvent]
        @EventId int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT *
    FROM Event
    WHERE EventId = @EventId 
END