存储过程——选择参数并填充文本框

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?

我在这里做错了什么?

    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

1 个解决方案

#1

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:

将存储的proc更改为仅接受EventId作为参数:

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

#1