Invalid enumeration value and SqlDataAdapter.Update(DataTable)

Question

I'm getting the ArgumentOutOfRangeException "The StatementType enumeration value, 4, is invalid" when trying to update a data table through SqlDataAdapter.Update(DataTable). According to http://msdn.microsoft.com/en-us/library/system.data.statementtype(v=vs.100).aspx, the possible enum values are Select, Insert, Update, Delete, and Batch, and I'm guessing Batch is value 4.

I'm not really sure how this value is being set, as it belongs to the SqlRowUpdatedEventArgs object I'm getting in the SqlRowUpdatedEventHandler, or why it is invalid.

Basically I'm building a DataTable from an XmlNode being returned from a web service call, sorting the data view, and then issuing the update in batches of 200.

XmlNode result = null;
using (LeadService.ClientService service = new LeadService.ClientService())
{
    const string UserName = "SomeUserName";
    const string Password = "SomePassword";
    const int ChildReportId = 746;
    result = service.GetReportResults(UserName, Password, ChildReportId, null);
}

if (result.InnerXml != "")
{
    DataTable leads = new DataTable();
    leads.Columns.Add("ID1", typeof(int));
    leads.Columns.Add("ID2", typeof(string));
    leads.Columns.Add("CREATEDDATE", typeof(DateTime));

    foreach (XmlNode n in result.ChildNodes)
    {
        DataRow row = leads.NewRow();
        row["ID1"] = n["Id1"].InnerText;
        row["ID2"] = n["Id2"].InnerText;
        row["CREATEDDATE"] = n["DateAdded"].InnerText;
        leads.Rows.Add(row);
    }

    leads.DefaultView.Sort = "CREATEDDATE";
    DataTable newTable = leads.DefaultView.ToTable();
    newTable.AcceptChanges();
    foreach (DataRow r in newTable.Rows)
    {
        r.SetModified();
    }

    const string Update = "UPDATE SOMETHING SET ID1 = @ID1 WHERE ID2 = @ID2";

    using (SqlConnection con = new SqlConnection(Dal.GetConnectionString("CONNECTIONSTRING")))
    {
        SqlDataAdapter adap = new SqlDataAdapter();
        adap.UpdateCommand = new SqlCommand(Update);
        adap.UpdateCommand.Parameters.Add("@ID1", SqlDbType.NVarChar, 75, "ID1");
        adap.UpdateCommand.Parameters.Add("@ID2", SqlDbType.VarChar, 18, "ID2");
        adap.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
        adap.UpdateBatchSize = 200;
        adap.RowUpdated += new SqlRowUpdatedEventHandler(adap_RowUpdated);
        adap.Update(newTable);
    }
}

Here is the event handler:

private static void adap_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
    _rowsProcessed += e.RowCount;
    _recordsAffected += e.RecordsAffected;
}

score 2 · Answer 1 · answered Oct 22 '13 at 19:52

2

Doh! I hadn't set the connection in the SqlCommand object! Not sure about the reasoning behind the weird error, but changing the constructor overload cleared things up:

using (SqlConnection con = new SqlConnection(Dal.GetConnectionString("CONNECTIONSTRING")))
{
    SqlDataAdapter adap = new SqlDataAdapter();
    adap.UpdateCommand = new SqlCommand(Update, con);
    // ...
    adap.Update(newTable);
}

answered Oct 22 '13 at 19:52

Brian

432
1
5
12

Thanks u saved my day this was the issue the connection string was null what an obscure error message even in .Net 4.6.1 the error occurs beware guys – Sundara Prabu Oct 31 '17 at 07:09

score 0 · Answer 2 · answered Oct 22 '13 at 15:31

0

It may be thinking you're adding rows instead of updating. Try moving SetModified to your loop:

foreach (XmlNode n in result.ChildNodes)
{
    DataRow row = leads.NewRow();
    row["ID1"] = n["Id1"].InnerText;
    row["ID2"] = n["Id2"].InnerText;
    row["CREATEDDATE"] = n["DateAdded"].InnerText;
    leads.Rows.Add(row);
    row.SetModified();
}

answered Oct 22 '13 at 15:31

D Stanley

149,601
11
178
240

The leads table is actually being sorted and sent to a new table (aptly named `newTable`). I just inspected in debugger and the row state values are Added and calling `newTable.AcceptChanges()` sets them to Unchanged. After calling `r.SetModified()` in the loop they are set to Modified. – Brian Oct 22 '13 at 15:46

Invalid enumeration value and SqlDataAdapter.Update(DataTable)

2 Answers2