C# Database Insert not working from textbox to listbox

Question

I've made a library management system. Put 4 boxes containing the title, author, no of pages and publisher. And then add a button to add it on the database, and I also have 3 listboxes containing the title, author and publisher. It seems that when I accomplished filling up the 4 textboxes and then clicking the button for insert, it's not updating the listboxes and even the database. Nothing is happening. How do you do that?

     OleDbCommand cmd = new OleDbCommand();
        OleDbConnection cn = new OleDbConnection();
        OleDbDataReader dr;

        public frm1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            cn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database2.accdb;Persist Security Info=True";
            cmd.Connection = cn;
            loaddata();
        }

        private void loaddata()
        {
            lstbxTitle.Items.Clear();
            lstbxAuthor.Items.Clear();
            lstbxPub.Items.Clear();

            try
            {
                string q = "SELECT * FROM Table2";
                cmd.CommandText = q;
                cn.Open();
                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        lstbxTitle.Items.Add(dr[1].ToString());
                        lstbxAuthor.Items.Add(dr[2].ToString());
                        lstbxPub.Items.Add(dr[6].ToString());

                    }
                }
                dr.Close();
                cn.Close();
            }

            catch (Exception e)
            {
                cn.Close();
                MessageBox.Show(e.Message.ToString());
            }

        }


private void lstbxTitle_Click(object sender, EventArgs e)
        {
            ListBox l = sender as ListBox;
            try
            {
                if (l.SelectedIndex != 1)
                {
                    lstbxTitle.SelectedIndex = l.SelectedIndex;
                    lstbxAuthor.SelectedIndex = l.SelectedIndex;
                    lstbxAuthor.Text = lstbxAuthor.SelectedItem.ToString();
                    lstbxPub.SelectedIndex = l.SelectedIndex;
                    lstbxPub.Text = lstbxPub.SelectedItem.ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }


        private void btnAdd_Click(object sender, EventArgs e)
        {
            if (txtbxAddT.Text != "")
            {
                string q = "insert into Table2 (Title) values ('" + txtbxAddT.Text.ToString() + "')";
                txtbxAddT.Text = null;
            }

            if (txtbxAddA.Text != "")
            {
                string q = "insert into Table2 (Author) values ('" + txtbxAddA.Text.ToString() + "')";
                txtbxAddA.Text = null;
            }

            if (txtbxAddNP.Text != "")
            {
                string q = "insert into Table2 (Page Number) values ('" + txtbxAddNP.Text.ToString() + "')";
                txtbxAddNP.Text = null;
            }

            if (txtbxAddP.Text != "")
            {
                string q = "insert into Table2 (Publisher) values ('" + txtbxAddP.Text.ToString() + "')";
                txtbxAddP.Text = null;
            }

            loaddata();
        }

Shaharyar · Accepted Answer · 2013-12-17 15:06:57Z

1

The problem is you are not executing your query. It is in the string q, but you are not executing it anywhere.

Create a method:

private void UpdateData(query)
{
    try 
    { 
        cn.Open(); 
        cmd.CommandText = query; //set query to execute 
        cmd.ExecuteNonQuery();  //executing the query
        cn.Close(); 
    } 
    catch (Exception ex)
    {
    }
}

Call this method in every if-statement of method btnAdd_Click:

if (txtbxAddT.Text != "")
{
    string q = "insert into Table2 (Title) values ('" + txtbxAddT.Text.ToString() + "')";
    UpdateData(q);
    txtbxAddT.Text = String.Empty; //Set it to "Empty", Null is not Empty
}
//... and so on for every if check

Best practice advice:

Assigning textbox.Text to "" or null is not a good practice.

Use String.Empty instead.

While checking for textbox.Text for being Empty, never use textbox.Text == null or textbox.Text == "", these are also not the best practices and some times create problems.

Use String.IsNullOrEmpty(textbox.Text) instead.

edited Dec 17, 2013 at 15:06

answered Dec 17, 2013 at 14:37

Shaharyar

12.5k4 gold badges50 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2774820 Over a year ago

`try { cn.Open(); cmd.CommandText = "update Table1 set [name]=@name where id=@id"; cmd.Parameters.AddWithValue("@name", textBox2.Text.ToString()); cmd.Parameters.AddWithValue("@id", listBox1.SelectedItem.ToString()); cmd.ExecuteNonQuery(); cn.Close(); loaddata(); } catch (Exception e) I have this. But it says "Object reference not set to instance" Please help. I don't know the codes.

user2774820 Over a year ago

do i have to add "cmd.Parameters.AddWithValue(#iDon'tKnowWhatToPut);" ?

Shaharyar Over a year ago

@user2774820 you can use it as you need

user2774820 Over a year ago

There's still a problem, after the Title and Author being stored, there's a messagebox saying "Syntax Error in INSERT INTO statement." And after clicking ok, another message box saying "The connection was not closed. Current connection's state is open."

Collectives™ on Stack Overflow

C# Database Insert not working from textbox to listbox

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related