c# Windows forms database issue

Question

I am trying to get manufacturer, supplier, item group id's from their tables based on the names in the combobox. Means I am passing id to a variable based on the names in the combobox and then passing that id to database. But when I run this application I am getting same result for manufacturer, supplier and item group variable. Why is that?

It was working fine earlier, but not now! I can't figure it out!

using (SqlConnection con = new SqlConnection(CS))
{
    con.Open();

    SqlCommand cmd = new SqlCommand("SELECT Manufact_Id from Manufacturer Where Name=@Name1", con);
    cmd.Parameters.AddWithValue("@Name1", combomanufacture_Createitem.Text);

    int a = Convert.ToInt32(cmd.ExecuteScalar());

    SqlCommand cmd2 = new SqlCommand("SELECT Supplier_Id from Supplier Where Supplier_Name=@Name2", con);
    cmd.Parameters.AddWithValue("@Name2", combo_supplierCreateitem.Text);

    int b = Convert.ToInt32(cmd.ExecuteScalar());

    SqlCommand cmd3 = new SqlCommand("SELECT ItemGroup_Id from ItemGroup Where Name=@Name3", con);
    cmd.Parameters.AddWithValue("@Name3", combo_itemgroupCreateitem.Text);

    int c = Convert.ToInt32(cmd.ExecuteScalar());

    try
    {
        SqlCommand cmd1 = new SqlCommand("INSERT INTO Items (Custom_Code, Name, Manufacturer, Supplier, Item_Group, Activate, Purchase_Rate, Landing_Cost, Profit_Percentage, Price_to_Customer, MRP,Opening_Stock, Manage_Stock, Description, Discount)  VALUES (@Customcode, @Name, @Manufacturer, @Supplier, @Itemgroup, @Activate, @Purchasedate, @Landingcost, @Profitpercentage, @PricetoCustomer, @MRP, @Openingstock, @Managestock, @Description1, @Discount)", con);

        cmd1.Parameters.AddWithValue("@Customcode", txt_customcode_Createitem.Text);
        cmd1.Parameters.AddWithValue("@Name", txt_nameCreateitem.Text);
        cmd1.Parameters.AddWithValue("@Manufacturer", a);
        cmd1.Parameters.AddWithValue("@Supplier", b);
        cmd1.Parameters.AddWithValue("@Itemgroup", c);
        cmd1.Parameters.AddWithValue("@Activate", combo_activateCreateitem.Text);
        cmd1.Parameters.AddWithValue("@Purchasedate", txt_purchasedateCreateitem.Text);
        cmd1.Parameters.AddWithValue("@Landingcost", txt_landingcosCreateitem.Text);
        cmd1.Parameters.AddWithValue("@Profitpercentage", txt_activateCreateitem.Text);
        cmd1.Parameters.AddWithValue("@PricetoCustomer", txt_PricetocustCreateitem.Text);
        cmd1.Parameters.AddWithValue("@MRP", txt_mrpCreateitem.Text);
        cmd1.Parameters.AddWithValue("@Openingstock", txt_openingstockCreateitem.Text);
        cmd1.Parameters.AddWithValue("@Managestock", combomanagestock_Createitem.Text);
        cmd1.Parameters.AddWithValue("@Description1", txt_descriptionCreateitem.Text);
        cmd1.Parameters.AddWithValue("@Discount", txt_DiscountCreateitem.Text);

        cmd1.ExecuteReader();

        MessageBox.Show("Items added Successfully");

        con.Close();
    }
    catch (Exception e1)
    {
        MessageBox.Show(e1 + "Please enter valid data");
    }
}

Side note: since you're not expecting any data to be returned from your INSERT statement, you should use cmd1.ExecuteNonQuery(); to execute it (not the .ExecuteReader() call which will return an IDataReader to handle a result set being returned) — marc_s
– marc_s, Commented Dec 25, 2016 at 10:24

Jeroen van Langen · Accepted Answer · 2016-12-25 08:10:51Z

1

You're creating new commands (cmd2/cmd3) but you set the parameters and execute the old instance.. (cmd)

So you're executing the same cmd, thats why you're getting the same result:

SqlCommand cmd = new SqlCommand("SELECT Manufact_Id from Manufacturer Where Name=@Name1", con);
cmd.Parameters.AddWithValue("@Name1", combomanufacture_Createitem.Text);
int a = Convert.ToInt32(cmd.ExecuteScalar());

SqlCommand cmd2 = new SqlCommand("SELECT Supplier_Id from Supplier Where Supplier_Name=@Name2", con);
// HERE!!  cmd.Parameters... should be cmd2.Parameters
cmd.Parameters.AddWithValue("@Name2", combo_supplierCreateitem.Text);
int b = Convert.ToInt32(cmd.ExecuteScalar());

SqlCommand cmd3 = new SqlCommand("SELECT ItemGroup_Id from ItemGroup Where Name=@Name3", con);
// AND HERE  cmd.Parameters... should be cmd3
cmd.Parameters.AddWithValue("@Name3", combo_itemgroupCreateitem.Text);
// cmd.execute?  should be cmd3....
int c = Convert.ToInt32(cmd.ExecuteScalar());

Copy/paste bugs..

answered Dec 25, 2016 at 8:10

Jeroen van Langen

22.1k3 gold badges46 silver badges63 bronze badges

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

2 Comments

Jeroen van Langen Over a year ago

It happens all the time. The problem is that you can't see it anymore.. That makes this site incredible, because of a fresh view of others will help!

Abhijith Over a year ago

To be honest its not copy paste! its written by me. And i dont like copy pasting which will make u a lazy dumb programmer. So you can guess that when u r beginner and making ur own codes.. simple errors goes unnoticed and later make u crazy! thanks for help!

the_endian · Accepted Answer · 2016-12-25 08:14:37Z

you are executing on the first "cmd" for both the second and third commands.

SqlCommand cmd = new SqlCommand("SELECT Manufact_Id from Manufacturer Where Name=@Name1", con);
            cmd.Parameters.AddWithValue("@Name1", combomanufacture_Createitem.Text);
            int a = Convert.ToInt32(cmd.ExecuteScalar());

            SqlCommand cmd2 = new SqlCommand("SELECT Supplier_Id from Supplier Where Supplier_Name=@Name2", con);
            cmd.Parameters.AddWithValue("@Name2", combo_supplierCreateitem.Text);
            int b = Convert.ToInt32(cmd.ExecuteScalar());

            SqlCommand cmd3 = new SqlCommand("SELECT ItemGroup_Id from ItemGroup Where Name=@Name3", con);
            cmd.Parameters.AddWithValue("@Name3", combo_itemgroupCreateitem.Text);
            int c = Convert.ToInt32(cmd.ExecuteScalar());

Should be:

SqlCommand cmd = new SqlCommand("SELECT Manufact_Id from Manufacturer Where Name=@Name1", con);
            cmd.Parameters.AddWithValue("@Name1", combomanufacture_Createitem.Text);
            int a = Convert.ToInt32(cmd.ExecuteScalar());

            SqlCommand cmd2 = new SqlCommand("SELECT Supplier_Id from Supplier Where Supplier_Name=@Name2", con);
            cmd2.Parameters.AddWithValue("@Name2", combo_supplierCreateitem.Text); //UPDATED!
            int b = Convert.ToInt32(cmd2.ExecuteScalar()); //UPDATED!

            SqlCommand cmd3 = new SqlCommand("SELECT ItemGroup_Id from ItemGroup Where Name=@Name3", con);
            cmd3.Parameters.AddWithValue("@Name3", combo_itemgroupCreateitem.Text); //UPDATED!
            int c = Convert.ToInt32(cmd3.ExecuteScalar()); //UPDATED!

You likely copy/pasted and forgot to add the 2 and 3 to the end of cmd!

Actually its not copy paste from other sites. I created this code.. although its not a big logical code. bt im using this logic in other forms also. As a beginner i gues this silly mistakes happen and also that will go u crazy!!!! Thanks for the help! :)

Collectives™ on Stack Overflow

c# Windows forms database issue

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related