Question insert data from checked list box?

[mcleane]

hi
i was wondering how to add checkedlist box data from c# windows forms into sql server 2008.
I have a form named: frmSupplier with a Tab control with 2 pages.
page 1 : name:tblSupplier
this page contains labels,textboxes,combobox and datetimepicker.
this page contains basic details of supplier such as Supplier Name,Contact Person, Date of Added,Supplier Type,Contact information of Supplier and Address detail of Supplier.
page 2: name: tblItems
this page contains a checked listbox named:LstProduct. which contains datas of items from item_master table in sql server 2008.

I populate my checked listbox in c# windows forms through following code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
    public partial class frmSupplier : Form
    {
        int patientID;
        int pUserID;
        public frmPatient()
        {
            InitializeComponent();
            SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=SEIOS;Integrated Security=true");
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            string SqlDataPull = ("Select agn,item_name from item_master where status=1");
            SqlCommand cmd = new SqlCommand(SqlDataPull);
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                SqlDataPull = dr[0].ToString() + dr[1].ToString();
                LstProduct.Items.Add(SqlDataPull);
            }
            dr.Close();
        }

I insert data from c# windows forms code into following tables in sql server 2008
a)supplier_id
b)supplier_name
c)supplier_contact
d)address
e)supplierproduct

Given below is my code in c# windows forms of how i insert data into tables:

private void btnCreate_Click(object sender, EventArgs e)
        {
            string dFieldName = "";
            Boolean vEmptyB = false;
            if (txtFName.Text.ToString().Trim() == "")
            {
                vEmptyB = true;
                dFieldName = "Please enter Supplier name";
            }
            else if (cboType.SelectedIndex == -1)
            {
                vEmptyB = true;
                dFieldName = "Please select Supplier type";
            }
            else if (txtCountry.Text.ToString().Trim() == "")
            {
                vEmptyB = true;
                dFieldName = "Please enter the country";
            }
            if (vEmptyB == true)
            {
                MessageBox.Show(dFieldName + "should not be empty");
                return;
            }
            SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=SEIOS;Integrated Security=true");
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            if (patientID == 0)
            {
                cmd = new SqlCommand("Insert into supplier_id(supplier_type,supplier_dOB,row_upd_date,user_id)" + "Values(@supplier_type,@supplier_dOB,GetDate(),@user_id)", conn);
                cmd.Parameters.AddWithValue("@supplier_type", cboType.SelectedIndex);
                cmd.Parameters.AddWithValue("@supplier_dOB", dtDOB.Value);
                cmd.Parameters.AddWithValue("@user_id", pUserID);
                cmd.ExecuteNonQuery();
            }
              int vPatientID;
              if (patientID == 0)
              {
                  vPatientID = Convert.ToInt32(cmd.Parameters.AddWithValue("@supplier_id", SqlDbType.Int).Value);
              }
              else
              {
                  cmd = new SqlCommand("Update supplier_name set status=0 where supplier_id=" + patientID, conn);
                  vPatientID = patientID;
              }
              cmd = new SqlCommand("Insert into supplier_name(supplier_id,supplier_name,supplier_contact_person,supplier_sale_name,status,row_upd_date,user_id)" + "Values(@supplier_id,@supplier_name,@supplier_contact_person,@supplier_sale_name,@status,GetDate(),@user_id)", conn);
              cmd.Parameters.AddWithValue("@supplier_id", vPatientID);
              cmd.Parameters.AddWithValue("@supplier_name", txtFName.Text.ToString());
              cmd.Parameters.AddWithValue("@supplier_contact_person", txtMName.Text.ToString());
              cmd.Parameters.AddWithValue("@supplier_sale_name", txtLName.Text.ToString());
              cmd.Parameters.AddWithValue("@status", 1);
              cmd.Parameters.AddWithValue("@user_id", pUserID);
              cmd.ExecuteNonQuery();
              if ((txtHNumber.Text != "") || (txtMNumber.Text != ""))
              {
                  if (patientID == 0)
                  {
                  cmd = new SqlCommand("Update supplier_contact set status=0 where supplier_id=" + patientID, conn);
                  }
                  cmd = new SqlCommand("Insert into supplier_contact(supplier_id,homenumber,mobilenumber,status,row_upd_date,user_id)" + "Values(@supplier_id,@homenumber,@mobilenumber,@status,GetDate(),@user_id)", conn);
                  cmd.Parameters.AddWithValue("@supplier_id", vPatientID);
                  cmd.Parameters.AddWithValue("@homenumber", txtHNumber.Text.ToString());
                  cmd.Parameters.AddWithValue("@mobilenumber", txtMNumber.Text.ToString());
                  cmd.Parameters.AddWithValue("@status", 1);
                  cmd.Parameters.AddWithValue("@user_id", pUserID);
                  cmd.ExecuteNonQuery();
              }
            if (patientID == 0)
            {
              cmd = new SqlCommand("Update address set status=0 where supplier_id=" + patientID, conn);
            }
            cmd = new SqlCommand("Insert into address(supplier_id,apartment_number,apartment_name,door_number,street_name_1,Street_name_2,Street_name_3,village,city,state,country,pincode,status,row_upd_date,user_id)" + "Values(@supplier_id,@apartment_number,@apartment_name,@door_number,@street_name_1,@Street_name_2,@Street_name_3,@village,@city,@state,@country,@pincode,@status,GetDate(),@user_id)", conn);
            cmd.Parameters.AddWithValue("@supplier_id", vPatientID);
            cmd.Parameters.AddWithValue("@apartment_number", txtApartmentNo.Text.ToString());
            cmd.Parameters.AddWithValue("@apartment_name", txtApartmentName.Text.ToString());
            cmd.Parameters.AddWithValue("@door_number", txtDoorNo.Text.ToString());
            cmd.Parameters.AddWithValue("@street_name_1", txtStreet1.Text.ToString());
            cmd.Parameters.AddWithValue("@Street_name_2", txtStreet2.Text.ToString());
            cmd.Parameters.AddWithValue("@Street_name_3", txtStreet3.Text.ToString());
            cmd.Parameters.AddWithValue("@village", txtVillageArea.Text.ToString());
            cmd.Parameters.AddWithValue("@city", txtCity.Text.ToString());
            cmd.Parameters.AddWithValue("@state", txtState.Text.ToString());
            cmd.Parameters.AddWithValue("@country", txtCountry.Text.ToString());
            cmd.Parameters.AddWithValue("@pincode", txtPCode.Text.ToString());
            cmd.Parameters.AddWithValue("@status", 1);
            cmd.Parameters.AddWithValue("@user_id", pUserID);
            cmd.ExecuteNonQuery();
            if (patientID == 0)
            {
                cmd = new SqlCommand("Update supplierproduct set status=0 where supplier_id=" + patientID, conn);
            }
            foreach (var chk in LstProduct.CheckedItems)
            {
                cmd = new SqlCommand("Insert into supplierproduct(supplier_id,product_id,status,row_upd_date,user_id)" + "Values(@supplier_id,@product_id,@status,GetDate(),@user_id)", conn);
                cmd.Parameters.AddWithValue("@supplier_id", vPatientID);
                cmd.Parameters.AddWithValue("@product_id", chk);
                cmd.Parameters.AddWithValue("@status", 1);
                cmd.Parameters.AddWithValue("@user_id", pUserID);
                cmd.ExecuteNonQuery();
            }
            ((MDIParent1)this.MdiParent).updateUserActivities(vPatientID, 1, txtFName.Text.ToString() + "supplier detail was added successfully");
            MessageBox.Show("Supplier Detail was added successfully", "SIOS", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
            this.Close();

              
                
        }
    }
}

The code works till inserting values into address table.
The problem where i am having trouble to code in c# is below:

 cmd = new SqlCommand("Update supplierproduct set status=0 where supplier_id=" + patientID, conn);
            foreach (var chk in LstProduct.CheckedItems)
            {
                cmd = new SqlCommand("Insert into supplierproduct(supplier_id,product_id,status,row_upd_date,user_id)" + "Values(@supplier_id,@product_id,@status,GetDate(),@user_id)", conn);
                cmd.Parameters.AddWithValue("@supplier_id", vPatientID);
                cmd.Parameters.AddWithValue("@product_id", chk);
                cmd.Parameters.AddWithValue("@status", 1);
                cmd.Parameters.AddWithValue("@user_id", pUserID);
                cmd.ExecuteNonQuery();
            }

I have trouble finding solution for this particular line

cmd.Parameters.AddWithValue("@product_id", chk);

I think table named: supplierproduct and table named: item_master have a link.

Given below is structure of my tables in sql server 2008.
table name: item_master
Column Name DataType AllowNulls
user_id Int yes
row_upd_date datetime yes
deleted_status bit yes
status bit yes
item_name nvarchar(250) yes
item_parent Int yes
item_price float yes
item_desc1 ntext yes
item_desc2 ntext yes
item_desc3 ntext yes
item_type Int yes
item_code nvarchar(10) yes
agn Int(primary key, No
auto-increment)
measure_type nvarchar(25) Yes

table name:supplierproduct
Column Name DataType AllowNulls
agn Int(primary key, No
auto-increment)
supplier_id Int Yes
product_id Int yes
row_upd_date datetime yes
status bit yes
user_id Int yes
price Int yes

Now what i want is for user is after inserting values in tab page named: tblSupplier, he should select or check an item presented in LstProduct which is another page named: tblItems.

My problem is how do i insert checked listbox value into table named:supplierproduct using c# code in c# windows forms with sql server 2008.
I have tried below code but it is not working:

 foreach (var chk in LstProduct.CheckedItems)
            {
                cmd = new SqlCommand("Insert into supplierproduct(supplier_id,product_id,status,row_upd_date,user_id)" + "Values(@supplier_id,@product_id,@status,GetDate(),@user_id)", conn);
                cmd.Parameters.AddWithValue("@supplier_id", vPatientID);
                cmd.Parameters.AddWithValue("@product_id", chk);
                cmd.Parameters.AddWithValue("@status", 1);
                cmd.Parameters.AddWithValue("@user_id", pUserID);
                cmd.ExecuteNonQuery();
            }
Can any one help me how to fix my problem. Any help or guidance in solving this problem would be greatly appreciated.