如何在SQL表中使用循环保存列表项? [英] how to save listitems using loop in sql table?

问题描述

您好如何通过sqltable中的循环保存选定的listitem?

hi how to save selected listitems via loop in sqltable?

推荐答案

我假设您有一个ListBox
您可以在ListBox中创建所选项目的xml字符串，然后将该xml字符串作为参数发送到存储过程，然后可以将数据插入表中.

遵循T-SQL代码段可以为您提供实现相同目标的简要提示

I assume you have a ListBox
You can create an xml string of selected item in ListBox and that xml string you can send as parameter to your stored procedure and then you can insert your data to your table

Following T-SQL code snippet can give you brief idea for achieving the same

DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc ='
<root>
<customer customerid="VINET" contactname="Paul Henriot">
   <order customerid="VINET" employeeid="5" orderdate="1996-07-04T00:00:00">
      <orderdetail orderid="10248" productid="11" quantity="12" />
      <orderdetail orderid="10248" productid="42" quantity="10" />
   </order>
</customer>
<customer customerid="LILAS" contactname="Carlos Gonzlez">
   <order customerid="LILAS" employeeid="3" orderdate="1996-08-16T00:00:00">
      <orderdetail orderid="10283" productid="72" quantity="3" />
   </order>
</customer>
</root>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- INSERT data to table variable using SELECT statement that uses the OPENXML rowset provider.
--Note: You can use your own table here instead of table variable for inserting data from xml
DECLARE @table TABLE
(
CustomerID varchar(10)
,CustomerName varchar(20)
)
INSERT INTO @table
SELECT    *
FROM       OPENXML (@idoc, '/ROOT/Customer',1)
            WITH (CustomerID  varchar(10),
                  ContactName varchar(20))

SELECT * FROM @table

出于您的理解目的，如果您需要任何帮助，请告诉我...

It is for your understanding purpose, If you need any help let me know...

按以下方式编写代码

Write your code in following way

for (int i = 0; i < lbAddedItems.Items.Count; i++)
        {
         //Note: values of Lid_value, Bid_value and Tid_value will get changed based on your list item
         cmd.Parameters.AddWithValue("@LID", Lid_value);
         cmd.Parameters.AddWithValue("@BID", Bid_value);
         cmd.Parameters.AddWithValue("@TID", Tid_value);
         cmd.ExecuteNonQuery();
        //Clear parameters
         cmd.Parameters.Clear();
        }

希望对您有帮助

I hope this will help you

尝试以下代码并仔细阅读评论

Try below code and read comment carefully

for (int i = 0; i < lbAddedItems.Items.Count; i++)
        {
            //Try this code and check it's working or not
            //This is not a correct way, you should assign these 
            //values based on your list item
            int Lid_value = 1;
            int Bid_value = 2;
            int Tid_value = 3;

            string selectedItem = lbAddedItems.Items[i].Text;
            //cmd.Parameters.Add(pLID);
            //cmd.Parameters.Add(pTID);
            //cmd.Parameters.Add(pBID);
            cmd.Parameters.AddWithValue("@LID", Lid_value);
            cmd.Parameters.AddWithValue("@BID", Bid_value);
            cmd.Parameters.AddWithValue("@TID", Tid_value);
            cmd.ExecuteNonQuery();
            //Clear parameters
            cmd.Parameters.Clear();
            //cmd.ExecuteNonQuery();
        }

这篇关于如何在SQL表中使用循环保存列表项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！