CODEWITHSUNDEEP
c#asp.net
gamered123
gamered123

Reputation: 57

Adding from one table and Update the other table

It is adding to to the inventory table, but as I click it it does not update a field in the another table. Is it possible to add and update 2 tables.

This is the add button click outside the listview button

protected void btnAdd_Click(object sender, EventArgs e) {
    bool existingSupply = IsExisting();
    foreach (ListViewItem item in lvPODetails.Items) {
        TextBox quantity = (TextBox)item.FindControl("txtQuantity");
        Label ltr = (Label)item.FindControl("lblProduct");
        TextBox odr = (TextBox)item.FindControl("txtOrdered");

        string name = ltr.Text;
        int delivered = Convert.ToInt32(quantity.Text);
        int ordered = Convert.ToInt32(odr.Text);
        string status = String.Empty;

        if (delivered >= ordered) {
            status = "Complete";
        }

        if (delivered < ordered) {
            status = "Partially Completed";
        }

        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        if (existingSupply) {
            cmd.CommandText = "UPDATE Inventory SET Quantity=quantity + @Quantity WHERE ProductID=@ProductID";

        } else {
            cmd.CommandText = "INSERT INTO Inventory VALUES (@ProductID, @ProdCatID, @SupplierName, " +
                "@Quantity, @CriticalLevel, @Price, @Status, @DateAdded, @DateModified)";

        }

        cmd.Parameters.AddWithValue("@ProductID", name);
        cmd.Parameters.AddWithValue("@ProdCatID", DBNull.Value);
        cmd.Parameters.AddWithValue("@SupplierName", txtSupplier.Text);
        cmd.Parameters.AddWithValue("@Quantity", delivered);
        cmd.Parameters.AddWithValue("@CriticalLevel", DBNull.Value);
        cmd.Parameters.AddWithValue("@Price", DBNull.Value);
        cmd.Parameters.AddWithValue("@Status", DBNull.Value);
        cmd.Parameters.AddWithValue("@DateAdded", DateTime.Now);
        cmd.Parameters.AddWithValue("@DateModified", DateTime.Now);
        cmd.CommandText = "UPDATE PurchaseOrder SET POStatus=@POStatus WHERE PONo=@PONo";
        cmd.Parameters.AddWithValue("@POStatus", status);
        cmd.Parameters.AddWithValue("@PONo", txtPONo.Text);
        cmd.ExecuteNonQuery();
        con.Close();

    }
    Response.Redirect("Default.aspx");         
}

What could be my mistake in this code?

Upvotes: 0

Views: 48

Answers (2)

Imad
Imad

Reputation: 7490

I would suggest you to use stored procedure but if you want ti stick with inline queries then replace your code by this.

protected void btnAdd_Click(object sender, EventArgs e)
{
    bool existingSupply = IsExisting();
    foreach (ListViewItem item in lvPODetails.Items)
    {
        TextBox quantity = (TextBox)item.FindControl("txtQuantity");
        Label ltr = (Label)item.FindControl("lblProduct");
        TextBox odr = (TextBox)item.FindControl("txtOrdered");

        string name = ltr.Text;
        int delivered = Convert.ToInt32(quantity.Text);
        int ordered = Convert.ToInt32(odr.Text);
        string status = String.Empty;


        if (delivered >= ordered)
        {
            status = "Complete";

        }




        if (delivered < ordered)
        {
            status = "Partially Completed";

        }

        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        if (existingSupply)
        {
            cmd.CommandText = "UPDATE Inventory SET Quantity=quantity + @Quantity WHERE ProductID=@ProductID";

        }

        else
        {
            cmd.CommandText = "INSERT INTO Inventory VALUES (@ProductID, @ProdCatID, @SupplierName, " +
                "@Quantity, @CriticalLevel, @Price, @Status, @DateAdded, @DateModified)";

        }

        cmd.Parameters.AddWithValue("@ProductID", name);
        cmd.Parameters.AddWithValue("@ProdCatID", DBNull.Value);
        cmd.Parameters.AddWithValue("@SupplierName", txtSupplier.Text);
        cmd.Parameters.AddWithValue("@Quantity", delivered);
        cmd.Parameters.AddWithValue("@CriticalLevel", DBNull.Value);
        cmd.Parameters.AddWithValue("@Price", DBNull.Value);
        cmd.Parameters.AddWithValue("@Status", DBNull.Value);
        cmd.Parameters.AddWithValue("@DateAdded", DateTime.Now);
        cmd.Parameters.AddWithValue("@DateModified", DateTime.Now);
        cmd.ExecuteNonQuery();
        SqlCommand cmd1 = new SqlCommand("UPDATE PurchaseOrder SET POStatus=@POStatus WHERE PONo=@PONo", con);

        cmd1.Parameters.AddWithValue("@POStatus", status);
        cmd1.Parameters.AddWithValue("@PONo", txtPONo.Text);

        cmd1.ExecuteNonQuery();
        con.Close();

    }
    Response.Redirect("Default.aspx");         
}

Upvotes: 1

Dgan
Dgan

Reputation: 10285

try like this

        cmd.Parameters.AddWithValue("@ProductID", name);
        cmd.Parameters.AddWithValue("@ProdCatID", DBNull.Value);
        cmd.Parameters.AddWithValue("@SupplierName", txtSupplier.Text);
        cmd.Parameters.AddWithValue("@Quantity", delivered);
        cmd.Parameters.AddWithValue("@CriticalLevel", DBNull.Value);
        cmd.Parameters.AddWithValue("@Price", DBNull.Value);
        cmd.Parameters.AddWithValue("@Status", DBNull.Value);
        cmd.Parameters.AddWithValue("@DateAdded", DateTime.Now);
        cmd.Parameters.AddWithValue("@DateModified", DateTime.Now);
cmd.ExecuteNonQuery();

cmd = new SqlCommand();
cmd.Connection = con;



cmd.CommandText = "UPDATE PurchaseOrder SET POStatus=@POStatus WHERE PONo=@PONo";
cmd.Parameters.AddWithValue("@POStatus", status);
cmd.Parameters.AddWithValue("@PONo", txtPONo.Text);
cmd.ExecuteNonQuery();

Upvotes: 1

Related Questions