How to add value from checkedlistbox into database using stored procedure c# windows forms?

Question

I'm trying to add checked values from checkedlistbox using stored procedure and C#

 public static void CreateDrugsListBox(CheckedListBox DrugsList) 
   {
       try
       {
           SqlConnection con = new SqlConnection(@"connection");
           SqlCommand cmd = new SqlCommand("InsertData_DrugsListBox", con);
           cmd.CommandType = CommandType.StoredProcedure;

           con.Open();
           foreach (string  itemchecked in DrugsList.CheckedItems) 
           {
               cmd.Parameters.Clear();           
               cmd.Parameters.AddWithValue("@DrugsValue", itemchecked); 
               cmd.ExecuteNonQuery();

           }
           con.Close();
      }

       catch (Exception ex)
       {

           MessageBox.Show("Error"+ex, "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);

       }

   }

it displays an error like:

errorSystem.invalidcastException: imposible to make a cast of an object type System.Data.DataRowView in type System.String

What's the datatype of your field in database ? "DrugsValue" — gkrishy, May 06 '15 at 10:10
see this http://stackoverflow.com/questions/4875540/how-to-get-value-of-checked-item-from-checkedlistbox — apomene, May 06 '15 at 10:19
thanks @apomene for your reply. I follow this link but i get the same problem :/ — leila.net, May 06 '15 at 10:29

Sachu · Accepted Answer · 2015-05-07T03:48:43.120

1

private void insert_Click(object sender, EventArgs e)
        {
           insert(list1);

        }
    private void insert(CheckedListBox list1)
     {
    using (SqlConnection conn = new SqlConnection())
      {
      try
      {
     conn.ConnectionString = "data source=servername;User ID=user_name;Password=password;initial catalog=database_name;integrated security=False;MultipleActiveResultSets=True;App=EntityFramework";
    conn.Open();

    SqlCommand cmd = new SqlCommand("InsertData_CityListBox", conn);
     cmd.CommandType = CommandType.StoredProcedure;
      for (int i = 0; i < list1.Items.Count; i++)
      {
       if (list1.GetItemChecked(i))
       {
   string str = (string)list1.Items[i]; //Or try  list1.Items[i].ToString();
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@city_code", str);
        cmd.ExecuteNonQuery();
      }
    }
            conn.Close();
         }
      catch(Exception e1)
        {

           }

      }
    }

Stored procedure

create PROCEDURE [dbo].[InsertData_CityListBox] 
(@city_code varchar(50) ) 
AS BEGIN 

insert into city(city_code) values (@city_code)
 END;

Form Screen enter image description here

Table after data insert

enter image description here

edited May 07 '15 at 03:48

answered May 06 '15 at 11:17

Sachu

7,555
7
55
94

same problem " errorSystem.invalidcastException: imposible to make a cast of an object type System.Data.DataRowView in type System.String" – leila.net May 06 '15 at 11:24
Can u post the code where u specifying the datatype of parameter "@Drugsvalue" also the procedure using to insert the data.. – Sachu May 06 '15 at 11:38
my procedure is : `create PROCEDURE [dbo].[InsertData_DrugsListBox] (@DrugsValue varchar(50) ) AS BEGIN Declare @PatientId int = (select IDENT_CURRENT('Patient') ); insert into Drugs (DrugsValue, FK_Patient ) values (@DrugsValue, @PatientId ) END` @Sachu – leila.net May 06 '15 at 12:58
Dear leila.net. find my edited answer..an example done in my environment and its working fine. – Sachu May 07 '15 at 03:49
Happy to hear it helped...what you changed? – Sachu May 07 '15 at 09:32

How to add value from checkedlistbox into database using stored procedure c# windows forms?

1 Answers1