CODEWITHSUNDEEP
c#asp.netsqldatabasedataset
user1698144
user1698144

Reputation: 764

Update Database from Dataset

My goal is to update the QTY for each SKU. I am using a SqlDataAdapter to accomplish this. The program runs fine. Just that no result happens.

QUESTION: Why is no result happening? My database remains unchanged.

Code below

public static void updateInventoryfromAMZ(DataTable datatable)
{
        int index = 0;
        string connString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString();
        DataSet amzInventoryDataSet = new DataSet("AMZINVDATASET");
        amzInventoryDataSet.Tables.Add(datatable);

        // FOR EACH ROW - PERFORM AN UPDATE //
        using (SqlConnection connection = new SqlConnection(connString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter();

            foreach (DataRow row in amzInventoryDataSet.Tables[index].Rows)
            {
                string sku = datatable.Rows[index]["seller-sku"].ToString();
                string qty = datatable.Rows[index]["quantity"].ToString();
                // Create the UpdateCommand.
                SqlCommand command = new SqlCommand(
                     "UPDATE Inventory SET qty = @qty" +
                     "WHERE sku = @sku", connection);
                // Add the parameters for the UpdateCommand.
                command.Parameters.Add("@qty", SqlDbType.Int, qty.Length, qty);
                command.Parameters.Add("@sku", SqlDbType.VarChar, sku.Length, sku);
                adapter.UpdateCommand = command;
                adapter.Update(amzInventoryDataSet.Tables[index]);     
                index++;
            }
        }
    }

Upvotes: 1

Views: 12747

Answers (2)

Damith
Damith

Reputation: 63105

When you concatenating two sql strings, you better add space at the end of first string or at the beginning of second string. As Gordon Linoff pointed out your sql statement is incorrect. And also setting parameters and the values need to be change depending on the type of the parameters.

try below code, I have use SqlCommand and ExecuteNonQuery method to update each row data 

using (SqlConnection connection = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand("UPDATE Inventory SET qty = @qty WHERE sku = @sku", connection))
{
    connection.Open();
    var paramqty=  cmd.Parameters.Add("@qty", SqlDbType.Int);
    var parasku = cmd.Parameters.Add("@sku", SqlDbType.VarChar);
    foreach (DataRow row in amzInventoryDataSet.Tables[0].Rows)
    {
        parasku.Value = row["seller-sku"].ToString();
        paramqty.Value = int.Parse(row["quantity"].ToString());
        cmd.ExecuteNonQuery();
    }
}

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271141

I am thinking your problem are these lines:

 "UPDATE Inventory SET qty = @qty" +
 "WHERE sku = @sku", connection);

They are going to produce a string like:

 "UPDATE Inventory SET qty = @qtyWHERE sku = @sku", connection);

And the variable @qtyWHERE is not defined.

Try this instead:

 "UPDATE Inventory SET qty = @qty WHERE sku = @sku", connection);

Upvotes: 2

Related Questions