how to save listitems using loop in sql table?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

C#

ASP.NET

, +

hi how to save selected listitems via loop in sqltable?

Posted 8-Feb-12 22:34pm

ythisbug

Add a Solution

Comments

Herman<T>.Instance 9-Feb-12 5:05am

delete current set in table and insert the new set in the table

ythisbug 9-Feb-12 5:07am

i dint get u?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jai000 · Answer 1 · 2012-02-08T23:31:00

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

SQL

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...

jai000 · Answer 2 · 2012-02-09T20:25:00

Solution 2

Write your code in following way

C#

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

Posted 9-Feb-12 20:25pm

jai000

Comments

ythisbug 10-Feb-12 4:08am

its comin error like unassigned value for Lid_value,Bid.....Tid...

jai000 10-Feb-12 4:21am

what did you do? Can you paste your new code? It seems that you have not assigned values...

ythisbug 10-Feb-12 4:38am

for (int i = 0; i < lbAddedItems.Items.Count; i++)
{
int Lid_value;
int Bid_value;
int Tid_value;
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();
}

jai000 · Answer 3 · 2012-02-09T22:46:00

Solution 3

Try below code and read comment carefully

C#

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();
        }

Posted 9-Feb-12 22:46pm

jai000

Updated 9-Feb-12 22:47pm

v2

Comments

ythisbug 10-Feb-12 4:53am

hey thanks.but here adding 1,2,3 to tat columns..its not adding selected values from list

jai000 10-Feb-12 5:05am

Is it working?
You are right it will save 1,2,3 every time in your table, just think how can you assign your values to these variables

If it is working fine then click on "Accept Solution" button and accept the answer

ythisbug 10-Feb-12 5:15am

ya working.bt saving 1,2,3...
for two dropdown list m getting values from othr tables how to get it by using session??

jai000 10-Feb-12 5:20am

Whatever you asked that has been answered so please end this discussion here..

For session related thing you can post new question... I am not able to help you in same matter.

Thnaks

ythisbug 10-Feb-12 5:43am

i tried other logic its working for me..thanks for ur help

jai000 10-Feb-12 5:51am

You welcome, kindly accept solution

ythisbug 10-Feb-12 6:38am

but its adding one item trice if i select 3 items from itemlist

how to save listitems using loop in sql table?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us