如何使用多重选择列表框进行更新 [英] how to update using multiselect listbox

问题描述

专家您好，

我正在使用multiselect列表框更新Evnts.我正在使用mysql作为后端.

表 crm_user_evnt_assigned 包含3个字段，id(autoinc)，user_id和event_id.

我正在为选定的事件使用foreach循环

就像

Hi expert,

I am using multiselect listbox for updating Evnts.I am using mysql as backend.

table crm_user_evnt_assigned contains 3 field ,id(autoinc) ,user_id and event_id.

I am using foreach loop for selected events

like

foreach (ListItem lstAssign in lstEvent.Items)
                {
                    if (lstAssign.Selected == true)
                    {
                        command1 = new MySqlCommand(QueryUserEventAssign, connect);
                        command1.Parameters.AddWithValue("?user_id", CurrentUserId);

                        int eventAssignID = Convert.ToInt32(lstAssign.Value);
                        command1.Parameters.AddWithValue("?event_id", eventAssignID);
                        command1.ExecuteNonQuery();
                    }
                }

1.仅当未为特定的user_id分配event_id时，我才能编写插入查询.

2.如果特定的user_id的旧event_id不匹配，如何删除现有行.

3.如果当前的user_id和event_id与旧的相同，则不执行任何操作.

1. How i can write query for insert only if event_id not assigned for perticular user_id.

2. How to delete existing row if old event_id for perticular user_id not match.

3. do nothing if current user_id and event_id are sames as old one.

推荐答案

查询将采用这种方式.

query will be somehow like this.

Declare @myEvtID int 
Declare @myUserID int
Declare @savedEvtID int

IF (Select Count(*) from crm_user_evnt_assigned cuea WHERE cuea.user_id = @myUserID) < 1
THEN
   /*Insert Query write here */
Else
   Select @savedEvtID=cuea.event_id from crm_user_evnt_assigned cuea where cuea.user_id = @myUserID
   IF(@myEvtID <> @savedEvtID)
   THEN
       /*write update query here*/
   Else
       /*No Need for Else part. this part will be accesses when current event id is equal to previously saved event_id.*/

End IF

希望它会有用.如果它工作正常甚至在使用过程中您发现问题，都应该做出响应.

Hope it will be useful. should response if it work fine or even it you find issues while using it.

hmm ..然后您需要在首次加载时将数据表存储在会话中，并将其用于查询中的决策.
即

hmm..then you need to store datatable in session while first load and use it for decision making in you query.
i.e.

DataTable dt = ((DataTable) Session["crm_user_evnt_assigned"]);
foreach (ListItem lstAssign in lstEvent.Items)
                {
                    if (lstAssign.Selected == true)
                    {
                      if(((DataRow())dt.Select("user_id=" & CurrentUserId)).Count < 1)
                       {
                          //Insert Query place here
                       }
                       else
                       {
                         int dbEventID = dt.Select("user_id=" & CurrentUserId)[0]["event_id"];
                         if(dbEventID != eventAssignID)
                         {
                            //Update Query to be placed here.
                         }  
                       }
                      
                  }

这篇关于如何使用多重选择列表框进行更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！