none
how to create a simple data entry program in vb.net 2008 using local database

    Question

  • .how to manipulate(add,update,delete,save) data from Form1.textbox/combobox to Form2.table as my sql compact server 3.5 or local database in vb.net 2008. i attached here the printscreen of my forms.

    thanks and regards to all!

    Monday, May 07, 2012 9:32 AM

Answers

  • Create an appropriate sql queries (select, update and delete). Use Sqlconnection, sqlcommand classes to achive that.

    Examples:

    - TO INSERT: you have to specify all the columns you want to insert (in my case I will specify all the parameters):

    using(SqlConnection conn = new SqlConnection("connString")) //specify your connection string of database
    {
        string insertQuery = @"INSERT INTO Orders VALUES (@p1, @p2, @p3, @p4,  @p5, @p6";
        using(SqlCommand cmd = new SqlCommand(insertQuery, conn))
        {
            cmd.Parameters.AddWithValue("@p1", int.Parse(textBox1.Text)); //rename to your textBoxes (applied to all)
            cmd.Parameters.AddWithValue("@p2", textBox2.Text); 
            cmd.Parameters.AddWithValue("@p3", textBox3.Text);
            cmd.Parameters.AddWithValue("@p4", textBox4.Text);
            cmd.Parameters.AddWithValue("@p5", decimal.Parse(textBox5.Text));
            cmd.Parameters.AddWithValue("@p6", decimal.Parse(textBox6.Text)); 
            cmd.ExecuteNonQuery();
        }
    }


    NOTE: Check your fileds (columns) type in database. I used Integer for ID column, and decimals for money type. Change appropriatelly!!!

    TO UPDATE: you have to specify columns you want to update AND a WHERE clause - this is necessary, that the command knows which row(s) to update.

    For example, lets say you want to update Total field (for where I will take an ID column - ID is always the best to use - if there is any):

    using(SqlConnection conn = new SqlConnection("connString")) //specify your connection string of database
    {
        string updateQuery = @"UPDATE Orders SET Total = @p1 WHERE OrderID = @p2";
        using(SqlCommand cmd = new SqlCommand(updateQuery, conn))
        {
            cmd.Parameters.AddWithValue("@p1", decimal.Parse(textBox6.Text)); 
            cmd.Parameters.AddWithValue("@p2", int.Parse(textBox1.Text)); //rename to your textBoxes (applied to all)
            cmd.ExecuteNonQuery();
        }
    }

    - TO DELETE: you have to make sure what to delete. You can:

    - delete whole data from some table (like: "DELETE FROM Order"  - this will delete whole data from Orders)

    - use WHERE clause (like in update query), where you specify for which row it deletion for (which row you want to delete exactly) - "DELETE * FROM Order WHERE OrderID =@param" //@param is columnID (for examle, can even bo some other data)

    using(SqlConnection conn = new SqlConnection("connString")) //specify your connection string of database
    {
        string deleteQuery = @"DELETE * FROM Orders WHERE OrderID = @p1";
        using(SqlCommand cmd = new SqlCommand(deleteQuery, conn))
        {
            cmd.Parameters.AddWithValue("@p1", int.Parse(textBox1.Text)); //rename to your textBoxes (applied to all)
            cmd.ExecuteNonQuery();
        }
    }

    ---

    I would suggest you to use try, catch block around cmd.ExecuteNonQuery() method, so yoz can see if there any error occurs (this is possible specially on the beginning when you are creating code). I will look like:

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

    Hope it helps explaining basics of working with database - regarding your example.

    bye


    Mitja

    • Marked as answer by LendlCerdina Tuesday, May 08, 2012 1:44 AM
    Monday, May 07, 2012 2:22 PM

All replies

  • Create an appropriate sql queries (select, update and delete). Use Sqlconnection, sqlcommand classes to achive that.

    Examples:

    - TO INSERT: you have to specify all the columns you want to insert (in my case I will specify all the parameters):

    using(SqlConnection conn = new SqlConnection("connString")) //specify your connection string of database
    {
        string insertQuery = @"INSERT INTO Orders VALUES (@p1, @p2, @p3, @p4,  @p5, @p6";
        using(SqlCommand cmd = new SqlCommand(insertQuery, conn))
        {
            cmd.Parameters.AddWithValue("@p1", int.Parse(textBox1.Text)); //rename to your textBoxes (applied to all)
            cmd.Parameters.AddWithValue("@p2", textBox2.Text); 
            cmd.Parameters.AddWithValue("@p3", textBox3.Text);
            cmd.Parameters.AddWithValue("@p4", textBox4.Text);
            cmd.Parameters.AddWithValue("@p5", decimal.Parse(textBox5.Text));
            cmd.Parameters.AddWithValue("@p6", decimal.Parse(textBox6.Text)); 
            cmd.ExecuteNonQuery();
        }
    }


    NOTE: Check your fileds (columns) type in database. I used Integer for ID column, and decimals for money type. Change appropriatelly!!!

    TO UPDATE: you have to specify columns you want to update AND a WHERE clause - this is necessary, that the command knows which row(s) to update.

    For example, lets say you want to update Total field (for where I will take an ID column - ID is always the best to use - if there is any):

    using(SqlConnection conn = new SqlConnection("connString")) //specify your connection string of database
    {
        string updateQuery = @"UPDATE Orders SET Total = @p1 WHERE OrderID = @p2";
        using(SqlCommand cmd = new SqlCommand(updateQuery, conn))
        {
            cmd.Parameters.AddWithValue("@p1", decimal.Parse(textBox6.Text)); 
            cmd.Parameters.AddWithValue("@p2", int.Parse(textBox1.Text)); //rename to your textBoxes (applied to all)
            cmd.ExecuteNonQuery();
        }
    }

    - TO DELETE: you have to make sure what to delete. You can:

    - delete whole data from some table (like: "DELETE FROM Order"  - this will delete whole data from Orders)

    - use WHERE clause (like in update query), where you specify for which row it deletion for (which row you want to delete exactly) - "DELETE * FROM Order WHERE OrderID =@param" //@param is columnID (for examle, can even bo some other data)

    using(SqlConnection conn = new SqlConnection("connString")) //specify your connection string of database
    {
        string deleteQuery = @"DELETE * FROM Orders WHERE OrderID = @p1";
        using(SqlCommand cmd = new SqlCommand(deleteQuery, conn))
        {
            cmd.Parameters.AddWithValue("@p1", int.Parse(textBox1.Text)); //rename to your textBoxes (applied to all)
            cmd.ExecuteNonQuery();
        }
    }

    ---

    I would suggest you to use try, catch block around cmd.ExecuteNonQuery() method, so yoz can see if there any error occurs (this is possible specially on the beginning when you are creating code). I will look like:

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

    Hope it helps explaining basics of working with database - regarding your example.

    bye


    Mitja

    • Marked as answer by LendlCerdina Tuesday, May 08, 2012 1:44 AM
    Monday, May 07, 2012 2:22 PM
  • hmmm...wait..is that in JAVA? anyway thanks a lot Mitja! you're a great help! cheers!!!

    atleast i got some ideas.:D


    Tuesday, May 08, 2012 3:15 AM
  • another question with regards to connection..i used add data source on my form,does it connect me to the database or i should use the connection string? 

    thanks

    Tuesday, May 08, 2012 3:56 AM
  • Add datasouce will have its own connection string, which is a part of its own. To use connection string in your code (like in SqlConnection class), you have to specify it by your self (btw, its the same connection string as in your data source, but you cannot link them together).

    What you can do, is to specify your connection string in app.Config file (this can be done automatically when trying to add new database), or you can simple write it into some string variable, like:

    string connString = @""Data Source=.\SQLEXPRESS;AttachDbFilename=Path_to_your_databasefile;Integrated Security=True;Connect Timeout=30;User Instance=True";

    (or some similar connection string - depends on database), and use this variable like I showed you in example code up there.


    Mitja

    Tuesday, May 08, 2012 1:08 PM