none
Input string was not in a correct format. RRS feed

  • Question

  • 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 MySql.Data.MySqlClient;

    namespace proiect
    {
        public partial class StergeCarti : Form
        {
            MySqlConnection con = new MySqlConnection("DataSource=localhost;UserID=root;database=biblio1");
            MySqlCommand cmdcarti;
            //interogarea pe baza careia umplem checkListBox
            MySqlDataAdapter adaptc;
            DataTable cartiT = new DataTable();

            void Completez_CheckList_Carti()
            {

                try
                {
                    MySqlCommand cmdcarti = new MySqlCommand("SELECT Title FROM books", con);
                    adaptc = new MySqlDataAdapter(cmdcarti);
                    adaptc.Fill(cartiT);
                    cBoxC.Items.Clear();
                    //carti.DataSource=null;
                    cBoxC.DataSource = cartiT;
                    //DataTable din care sunt preluate datele pentru CheckedListBox carte
                    cBoxC.ValueMember = "Title";
                    //Valoarea din coloana BookID nu se afiseaza in CheckedListBox
                    cBoxC.DisplayMember = "books";
                    //Eelementele afisate in CheckedListBox, preluate din concatenarea mai multor coloane

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            public StergeCarti()
            {
                InitializeComponent();
                try
                {
                    con.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                Completez_CheckList_Carti();
            }

            private void button2_Click(object sender, EventArgs e)
            {
                con.Close();
                this.Close();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                MySqlCommand cmd = con.CreateCommand();
                MySqlCommand sterg = new MySqlCommand("DELETE FROM books", con);

                foreach (int i in cBoxC.CheckedIndices)
                {
                    cBoxC.SelectedIndex = i;
                    int books = Convert.ToInt32(cBoxC.SelectedValue.ToString());
                    sterg.Parameters.AddWithValue("@books", books);
                    sterg.ExecuteNonQuery();
                    sterg.Parameters.Clear();
                }

                //cBoxC.Items.Remove(cBoxC.SelectedItem);
                cartiT.Clear();
                adaptc.Fill(cartiT);

                //while(cBoxC.CheckedItems.Count > 0)
                //{
                //        cBoxC.Items.Remove(cBoxC.CheckedItems[0]);
                //}
            }
           }
        }
    Sunday, February 22, 2015 11:31 PM

Answers

  • Care to tell us which statement gets the error?

    If it's this one

    int books = Convert.ToInt32(cBoxC.SelectedValue.ToString());

    Then you may need to check that cBoxC only contains strings that can be converted to integers.

    Sunday, February 22, 2015 11:38 PM
  • It is quite possible that the line

        int books = Convert.ToInt32(cBoxC.SelectedValue.ToString());

    should be replaced with

        string books = cBoxC.SelectedValue.ToString();

    since you declared before that ValueMember is “Title”, i.e. a string.

    Also note that the DELETE statement should include the parameter, otherwise it will not remove the required book. The command probably should be changed to: "DELETE FROM books WHERE Title=@books".

    Monday, February 23, 2015 6:37 AM

All replies

  • Care to tell us which statement gets the error?

    If it's this one

    int books = Convert.ToInt32(cBoxC.SelectedValue.ToString());

    Then you may need to check that cBoxC only contains strings that can be converted to integers.

    Sunday, February 22, 2015 11:38 PM
  • It is quite possible that the line

        int books = Convert.ToInt32(cBoxC.SelectedValue.ToString());

    should be replaced with

        string books = cBoxC.SelectedValue.ToString();

    since you declared before that ValueMember is “Title”, i.e. a string.

    Also note that the DELETE statement should include the parameter, otherwise it will not remove the required book. The command probably should be changed to: "DELETE FROM books WHERE Title=@books".

    Monday, February 23, 2015 6:37 AM
  • yes there was the problem. Thank you
    Monday, February 23, 2015 3:07 PM
  • Exactly! must be string not int and the DELETE statement should include parameter

    Thank you very much!

    Monday, February 23, 2015 3:09 PM
  • Is there possible to help me on skype with another problem? I can't post here because theres to many pages to show you.

    Regards

    Monday, February 23, 2015 8:08 PM
  • Is there possible to help me on skype with another problem? I can't post here because theres to many pages to show you.

    Regards

    If you have a lot of code, I suggest you create a simple test case that shows the same problem and post that in a new thread.
    Monday, February 23, 2015 8:38 PM
  • Imprumut = loan

    I have 6 tables: 

    I have one combobox(from where I select utilizatori(users)) and 2 CheckedListBox(Between first box and second box I have 2 buttons:imprumuta(loan) and restituie(return))

    This c# code works just for first user: Utilizator, but something's not good. When I add new utilizator(user) and select it,  the loan will be add in my SQL DataBase, but... the book who I selected doesn't move to the right in CheckedListBox and the reason is that I can't make an return

    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 MySql.Data.MySqlClient;

    namespace proiect
    {
        public partial class Imprumut : Form
        {
            MySqlConnection con = new MySqlConnection("DataSource=localhost;UserID=root;database=biblio1");
            //stabilim conexiunea
            MySqlCommand comUser;//interogarea pe baza careia umplem comboBox
            MySqlDataAdapter adaptu;
            DataTable userT = new DataTable();

            MySqlCommand cmdCarti;//interogarea pe baza careia umplem checkListBox
            MySqlDataAdapter adaptCarti;
            DataTable CartiTabel = new DataTable();

            MySqlCommand cmdCartiImprumutate;//interogarea pe baza careia umplem checkListBox
            MySqlDataAdapter adaptCartiImprumutate;
            DataTable CartiImprumutateTabel = new DataTable();

            public int UserId
            {
                get
                {
                    return Convert.ToInt32(user.SelectedValue.ToString());
                }
            }

            void Completez_Combo_User()
            {

                try
                {
                    comUser = new MySqlCommand("SELECT n.userid, CONCAT(n.UserName) as UserN FROM users n left join userroles us on n.userid=us.userid left join roles r on r.roleid=us.roleid WHERE r.roleid='3'", con);
                    adaptu = new MySqlDataAdapter(comUser);
                    adaptu.Fill(userT);
                    user.Items.Clear();
                    user.DataSource = userT;
                    //DataTable din care sunt preluate datele pentru ComboBox user
                    user.ValueMember = "UserID";
                    //Valoarea din coloana UserID nu se afiseaza in combobox
                    user.DisplayMember = "UserN";
                    //Eelementele afisate in combobox, preluate din concatenarea mai multor coloane
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

            void Completez_CheckList_Carti()
            {

                try
                {
                    cmdCarti = new MySqlCommand("SELECT BookID, CONCAT(title, ' ', ISBN,' ',author)as date_carte FROM books WHERE NumberLeft > 0 ORDER BY BookID", con);
                    adaptCarti = new MySqlDataAdapter(cmdCarti);
                    adaptCarti.Fill(CartiTabel);
                    imp.Items.Clear();
                    //carti.DataSource=null;
                    imp.DataSource = CartiTabel;
                    //DataTable din care sunt preluate datele pentru ComboBox carte
                    imp.ValueMember = "BookID";
                    //Valoarea din coloana BookID nu se afiseaza in combobox
                    imp.DisplayMember = "date_carte";
                    //Eelementele afisate in combobox, preluate din concatenarea mai multor coloane


                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }

                void Completez_CheckList_Cartires()
                {

                    try
                    {
                        cmdCartiImprumutate = new MySqlCommand(string.Format("SELECT b.BookID, CONCAT(title, ' ', ISBN,' ',author) as date_carte FROM books b inner join userbooks ub on ub.bookid = b.bookid WHERE ub.userid = {0} ORDER BY BookID", UserId), con);
                        adaptCartiImprumutate = new MySqlDataAdapter(cmdCartiImprumutate);
                        adaptCartiImprumutate.Fill(CartiImprumutateTabel);
                        res.Items.Clear();
                        //carti.DataSource=null;
                        res.DataSource = CartiImprumutateTabel;
                        //DataTable din care sunt preluate datele pentru ComboBox carte
                        res.ValueMember = "BookID";
                        //Valoarea din coloana BookID nu se afiseaza in combobox
                        res.DisplayMember = "date_carte";
                        //Eelementele afisate in combobox, preluate din concatenarea mai multor coloane


                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
            }

            void Inregistrez_imprumut_in_BD()

            {
                int useridu = Convert.ToInt32(user.SelectedValue.ToString()); //useridu = id book
                int bookidi;
                try
                {
                    DateTime azi = System.DateTime.Now; //  Data imprumutului
                    DateTime atunci = termenul.Value;   //  Data restituirii
                    MySqlTransaction tranzactie = con.BeginTransaction();
                    MySqlCommand adaugImpr = new MySqlCommand("INSERT INTO bookshistory(UserID, BookID,BorrowDate) VALUES(@UserID, @BookID, CAST(@BorrowDate as datetime))", con);
                    MySqlCommand scadCarti = new MySqlCommand("UPDATE books SET numberleft=numberleft-1 WHERE bookid=@bookid", con);
                    MySqlCommand adauga_userbooks = new MySqlCommand("INSERT INTO userbooks(userId,bookID)VALUES(@userID,@bookID)", con);
                    adauga_userbooks.Transaction = tranzactie;
                    adaugImpr.Transaction = tranzactie;
                    scadCarti.Transaction = tranzactie;

                    try
                    {
                        foreach (int i in imp.CheckedIndices)
                        {
                            imp.SelectedIndex = i;
                            bookidi = Convert.ToInt32(imp.SelectedValue.ToString());

                            MessageBox.Show(bookidi.ToString());
                                     //bookidi va fi id-ul cartea bifata, pe rand din checklistBox
                                     //Inregistrez in tabela imprumut
                            adaugImpr.Parameters.AddWithValue("@UserID", useridu);
                            adaugImpr.Parameters.AddWithValue("@BookID", bookidi);
                            adaugImpr.Parameters.AddWithValue("@BorrowDate", azi);
                            adaugImpr.ExecuteNonQuery();
                            adaugImpr.Parameters.Clear();

                            adauga_userbooks.Parameters.AddWithValue("@userID", useridu);
                            adauga_userbooks.Parameters.AddWithValue("@bookID", bookidi);
                            adauga_userbooks.ExecuteNonQuery();
                            adauga_userbooks.Parameters.Clear();

                                    //Scad numarl de carti disponibile pentru cartea imprumutat
                            scadCarti.Parameters.AddWithValue("@bookid", bookidi);
                            scadCarti.ExecuteNonQuery();
                            scadCarti.Parameters.Clear();
                        }
                        tranzactie.Commit();
                    }
                    catch (Exception ex)
                    {
                        tranzactie.Rollback();
                        string message = ex.Message;
                        if (ex.Message.ToLower().Contains("duplicate entry"))
                            message = "Una dintre carti mai exista deja";
                        MessageBox.Show(message);
                    }

                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
            }

            void Inregistrez_restituire_in_BD()
            {
                int useridu = Convert.ToInt32(user.SelectedValue.ToString()); //useridu = id book
                int bookidi;
                try
                {
                    DateTime azi = System.DateTime.Now; //  Data imprumutului
                    DateTime atunci = termenul.Value;   //  Data restituirii
                    MySqlTransaction tranzactie = con.BeginTransaction();
                    MySqlCommand modificIstoric = new MySqlCommand("UPDATE bookshistory SET returndate = @returnDate WHERE userID = @userID AND bookID = @bookID", con);
                    MySqlCommand adaugCarti = new MySqlCommand("UPDATE books SET numberleft = numberleft + 1 WHERE bookID = @bookID", con);
                    MySqlCommand sterge_userbooks = new MySqlCommand("DELETE  FROM userbooks WHERE userID = @userID AND bookID = @bookID", con);
                    sterge_userbooks.Transaction = tranzactie;
                    modificIstoric.Transaction = tranzactie;
                    adaugCarti.Transaction = tranzactie;

                    try
                    {
                        foreach (int i in res.CheckedIndices)
                        {
                            res.SelectedIndex = i;
                            bookidi = Convert.ToInt32(res.SelectedValue.ToString());

                            MessageBox.Show(bookidi.ToString());
                            //bookidi va fi id-ul cartea bifata, pe rand din checklistBox
                            //Inregistrez in tabela imprumut
                            modificIstoric.Parameters.AddWithValue("@UserID", useridu);
                            modificIstoric.Parameters.AddWithValue("@BookID", bookidi);
                            modificIstoric.Parameters.AddWithValue("@returnDate", termenul.Value);
                            modificIstoric.ExecuteNonQuery();
                            modificIstoric.Parameters.Clear();

                            sterge_userbooks.Parameters.AddWithValue("@UserID", useridu);
                            sterge_userbooks.Parameters.AddWithValue("@BookID", bookidi);
                            sterge_userbooks.ExecuteNonQuery();
                            sterge_userbooks.Parameters.Clear();

                            //Scad numarl de carti disponibile pentru cartea imprumutat
                            //adaugCarti.Parameters.AddWithValue("@bookid", bookidi);
                            adaugCarti.Parameters.AddWithValue("@bookid", bookidi);
                            adaugCarti.ExecuteNonQuery();
                            adaugCarti.Parameters.Clear();
                        }
                        tranzactie.Commit();
                    }
                    catch (Exception ex)
                    {
                        tranzactie.Rollback();
                        MessageBox.Show(ex.Message);
                    }

                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
            }

            public Imprumut()
            {
                InitializeComponent();
                try
                {
                    con.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                Completez_Combo_User();
                Completez_CheckList_Carti();
                Completez_CheckList_Cartires();
                //selecteaza_carti_utilizator();
                //  Initializez termenul din dateTimePicker la data de peste 15 zile fata de data sistemului
                termenul.Value = System.DateTime.Now.AddDays(15);
            }

            private void imprumuta_Click(object sender, EventArgs e)
            {

                Confirmare c = new Confirmare("Confirmati imprumutul?");
                DialogResult dr = c.ShowDialog();

                if (dr == DialogResult.Yes)

                    try
                    {
                        Inregistrez_imprumut_in_BD();
                        MessageBox.Show("Imprumutul a fost inregistrat");
                        //Dupa inregistrarea imprumutului o parte din carti nu mai sunt disponibile pentru imprumut
                        //Reincarc in CheckList cu Carti noua lista cu carti ramase dupa imprumut
                        //Pentru asta "resetez" datele din dataTable cartiT (sursa pentru carti.DataSource)
                        CartiTabel.Clear();
                        adaptCarti.Fill(CartiTabel);
                        CartiImprumutateTabel.Clear();
                        adaptCartiImprumutate.Fill(CartiImprumutateTabel);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                if (dr == DialogResult.No)
                {
                    MessageBox.Show("Imprumutul NU a fost inregistrat");
                    imp.ClearSelected();
                    //deselecteaza cartea selectat
                    foreach (int i in imp.CheckedIndices)
                        imp.SetItemChecked(i, false);
                    //debifeaza cartile bifate
                }

                //if (imp.CheckedItems.Count > 0)
                //{
                //    //res.Items.Clear();
                //    foreach (string str in imp.CheckedItems)
                //        res.Items.Add(str);//adauga in partea cealalta, imprumuta
                //    while (imp.CheckedItems.Count > 0)
                //        imp.Items.Remove(imp.CheckedItems[0]);
                //}
            }

            private void restituie_Click(object sender, EventArgs e)
            {
                Confirmare r = new Confirmare("Confirmati restituirea?");
                DialogResult dr = r.ShowDialog();

                if (dr == DialogResult.Yes)

                    try
                    {
                        Inregistrez_restituire_in_BD();
                        MessageBox.Show("Restituirea a fost inregistrata");
                        //Dupa inregistrarea imprumutului o parte din carti nu mai sunt disponibile pentru imprumut
                        //Reincarc in CheckList cu Carti noua lista cu carti ramase dupa imprumut
                        //Pentru asta "resetez" datele din dataTable cartiT (sursa pentru carti.DataSource)
                        CartiTabel.Clear();
                        adaptCarti.Fill(CartiTabel);
                        CartiImprumutateTabel.Clear();
                        adaptCartiImprumutate.Fill(CartiImprumutateTabel);

                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                if (dr == DialogResult.No)
                {
                    MessageBox.Show("Restituirea NU a fost inregistrata");
                    res.ClearSelected();
                    //deselecteaza cartea selectat
                    foreach (int i in imp.CheckedIndices)
                        res.SetItemChecked(i, false);
                    //debifeaza cartile bifate
                }

                if (res.CheckedItems.Count > 0)
                {
                    foreach (string str in res.CheckedItems)
                        imp.Items.Add(str);
                    while (res.CheckedItems.Count > 0)
                        res.Items.Remove(res.CheckedItems[0]);
                }
            }

            private void button2_Click(object sender, EventArgs e)
            {
                con.Close();
                this.Close();
            }
            //private void selecteaza_carti_utilizator()
            //{
            //    res.Items.Clear();
            //    MySqlCommand selectcart = new MySqlCommand("select title from books,userbooks where userbooks.userid='" + user.SelectedValue.ToString() + "' and userbooks.bookid=books.bookid", con);
            //    MySqlDataReader reader = selectcart.ExecuteReader();
            //    try
            //    {
            //        while(reader.Read())
            //        {
            //            res.Items.Add(reader["title"]);
            //        }
            //    }
            //    catch(Exception ex)
            //    {
            //        MessageBox.Show(ex.Message);
            //    }
            //    finally
            //    {
            //        reader.Close();
            //    }
            //}
        }
    }

                                            

    • Edited by Vincenzzo Monday, February 23, 2015 10:25 PM the book who I selected doesn't move to the right in CheckedListBox and the reason is that I can't make an return
    Monday, February 23, 2015 10:19 PM
  • This is a new question. Please post it in a new thread. It would also be best try isolate the code that causes the problem and post only that code.
    Monday, February 23, 2015 10:29 PM