checklistbox save check items in database

[xxxtian]

How to save the checked items on checkedlistbox in sql database? I searched through the web and found out that for loop or foreach is necessary. Could someone give me a sample code and an explanation. I really need to do this, but can't find a good tutorial for this.

[Momerath]

There are three ways to go about this:

1. SQL provider
2. LINQ to SQL
3. LINQ to EF

LINQ to SQL is probably the easiest to use, so let's try that one :)

First we need a table on a database. We'll pretend we have one and it's very simple, just a single column with a nvarchar as the type.

Next we need to make a class to represent this table:

[Table(Name="MyTableName"]   // the Name attribute isn't required if the table name is the same as the class name
public class MyClassName {
    [Column]
    public String Data;  // Same name as the column in the database
}

Now we create a class to interact with the database:

public class DatabaseContext : DataContext {
    public Table<MyClassName> myClassNames {
        get { return GetTable<MyClassName>(); }
    }
}

Last we need to have some code that adds the values to the database from the CheckedListBox

var context = new DatabaseContext("connection string goes here");  // connect to database
MyClassName temp;  // holds new items to be placed in database
foreach (String item in checkedListBox1.CheckedItems) {  // iterate through checked items
    temp = new MyClassName {Data = item};  // create object for inserting
    context.myClassNames.InsertOnSubmit(temp);  // add to in memory copy of table
}
context.SubmitChanges();  // write all the changes

Note: There might be some typos in there, or some casting required. I'm doing this off the top of my head :)

[xxxtian]

This is what I need

foreach (String item in checkedListBox1.CheckedItems) {  // iterate through checked items
}

I have this parameter

cmd.Parameter.AddWithValue("@checklistitem", ??)

What should be the object value for my checklistbox? It's being generate by a dataset,

private void BindCBL()
        {
            cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_displayeventtype";
            cmd.Connection = con;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds, "tblEventType");

            foreach (DataRow dr in ds.Tables["tblEventType"].Rows)
            {
                eventclb.Items.Add(dr["eventname"].ToString());
            }
        }

[Mitja Bonca]

Do you have to save only names of th checkBoxes? or What exactly? And where? Do you need a new ID (primary key) for each checklistBox? How does your storedProcedure look like (it only accepty one value at a time, or..)?

If its in any help:

private void GetCheckedItems()
        {
            List<string> list = new List<string>();
            foreach (string item in checkedListBox1.CheckedItems)
                list.Add(item);
            BindCBL(list);
        }

        private void BindCBL(List<string> list)
        {
            SqlConnection con = new SqlConnection("connectionString");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "usp_displayeventtype";
            cmd.Connection = con;
            cmd.Connection.Open();
            foreach (string item in list)
            {
                cmd.Parameters.Add("@variableNameFromStoredProcedure", SqlDbType.VarChar, 50).Value = item;
                cmd.ExecuteScalar();
            }
            cmd.Connection.Close();
        }

PS: as I told you I need more info. Its hard to tell you what exactly do you need.

[xxxtian]

I need to determine the ID of the items in checkedlistbox that is being generated by a dataset.

My stored procedure

AS

SELECT * FROM tblEventType

[Mitja Bonca]

LOL - short answer on so many questions - so dont expect us to be generous with answers!!

This is a stored procedure you have, that`s all? That will do nothing.
What you need to do:
- get the last id existed from the databse
- then you need for every new insertion add +1 (so you have a new id)
- and a stored procedure must be something like:

ALTER PROCEDURE NameOfStoredProcedure
(
@id int,
@name varchar (50)
)
AS
BEGIN
"INSERT INTO tblEventType VALUES(@id, @name)"
END

This kind of s.p. will insert only one row.

[xxxtian]

I have done this

cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "usp_addvisitor";
                cmd.Connection = con;

                cmd.Parameters.AddWithValue("@lastname", lname.Text);
                cmd.Parameters.AddWithValue("@firstname", fname.Text);
                cmd.Parameters.AddWithValue("@gender", gender.Text);
                cmd.Parameters.AddWithValue("@personalid", personalid.Text);
                cmd.Parameters.AddWithValue("@vehicleno", vehicleno.Text);
                cmd.Parameters.AddWithValue("@company", company.Text);
                cmd.Parameters.AddWithValue("@represent", representing.Text);

                //insert null in phone field
                if (phoneno.Text.Length != 0)
                {
                    cmd.Parameters.AddWithValue("@phonenumber", phoneno.Text);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@phonenumber", DBNull.Value);
                }

                //insert null in mobile field
                if (mobileno.Text.Length != 0)
                {
                    cmd.Parameters.AddWithValue("@mobilenumber", mobileno.Text);
                }
                else
                {
                    cmd.Parameters.AddWithValue("@mobilenumber", DBNull.Value);
                }

                cmd.Parameters.AddWithValue("@address", address.Text);
                cmd.Parameters.AddWithValue("@otherdetails", details.Text);

                //set parameter to receive identity output parameter
                SqlParameter visitoridparam = cmd.Parameters.AddWithValue("@visitorid", SqlDbType.BigInt);
                visitoridparam.Direction = ParameterDirection.Output;

                //use dialogresult object
                this.DialogResult = DialogResult.OK;
                this.Close();

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

                //assign output identity parameter to a variable
                int visitorid = Convert.ToInt32(visitoridparam.Value);

                MessageBox.Show("Visitor successfully registered.", "message", MessageBoxButtons.OK, MessageBoxIcon.Information);

                //determine checked items using for loop condition
                for (int i = 0; i < eventclb.Items.Count; i++)
                {
                    if (eventclb.GetItemChecked(i) == true)
                    {
                        string insert = "INSERT INTO tblVisitorEvent (visitorID, eventTypeID) select tblVisitor.visitorID, tblEventType.eventTypeID from tblVisitor CROSS JOIN tblEventType where tblEventType.eventName = @eventname AND tblVisitor.visitorID = '" + visitorid + "'";
                        cmd = new SqlCommand();
                        cmd.CommandText = insert;
                        cmd.Connection = con;
                        cmd.Parameters.AddWithValue("@eventname", eventclb.Items[i]);
                        
                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }

While my stored procedure

@visitorid bigint output,
@lastname nvarchar(50),
@firstname nvarchar(50),
@gender char(6),
@personalid nvarchar(50),
@vehicleno nvarchar(20),
@company nvarchar(50),
@represent nvarchar(100),
@phonenumber bigint,
@mobilenumber bigint,
@address nvarchar(100),
@otherdetails nvarchar(200)

AS

INSERT INTO tblVisitor
VALUES 
(@lastname, @firstname, @gender, @personalid, @vehicleno, @company, @represent, @phonenumber, @mobilenumber, @address, @otherdetails)
SET @visitorid = scope_identity()

What i'm doing here is getting the last identity of book when insert occurs, then it will be set to a variable. Checklistbox works but I think it's kind of off topic now but I really want to know if this is the proper way to get two identity columns from two tables, one from the last identity and the other is the id of the items in checkedlistbox. I used a cross join for these.

[xxxtian]

Kind of off topic but I wanna ask how can I put this inline sql statement into a stored procedure?

string insert = "INSERT INTO tblVisitorEvent (visitorID, eventTypeID) select tblVisitor.visitorID, tblEventType.eventTypeID from tblVisitor CROSS JOIN tblEventType where tblEventType.eventName = @eventname AND tblVisitor.visitorID = '" + visitorid + "'";

visitorID gets it conditional where to a variable output identity from the design's code behind.

int visitorid = Convert.ToInt32(visitoridparam.Value);