Intermittent Error: Must use updateable query

[Doug_B]

I have a legacy C# program that has been working for years. It runs on Windows Server 8. Connects to an .mdb via ace.oledb.12

This is a small extract program that updates each divisions UPS shipping interface table - an .mdb



This program runs as a scheduled task - every 1/2 hr. We have 3 divisions, each a separate task. Months ago, we moved two of the divisions to this server - all is well. This week I moved the third division. Same folder structure, as the other two, same config.

Intermittently I'm getting "Operation must use an updateable query. I've Googled this and found it could be a question of permissions - but all the folders have the same permissions, additionally it works some times. The other two work fine.

[CJ_London]

have you checked whether you have nulls in the data? nulls can cause errors which misleadingly can throw out this type of message

[Doug_B]

The program produces a log - when it fails - all orders in the batch fail. It's not nulls - this is all text.

8/11/2016 10:15:02 AM -- 1 Sales Orders Appended
8/11/2016 10:45:02 AM -- 0 Sales Orders Appended
8/11/2016 11:15:03 AM -- 4 Sales Orders Appended
8/11/2016 11:45:03 AM -- 1 Sales Orders Appended
8/11/2016 12:15:09 PM -- 1 Sales Orders Appended
8/11/2016 12:45:03 PM -- Operation must use an updateable query.
Insert into shipto(so_nbr,so_cust,so_ship,ad_addr,ad_name,ad_l ine1,ad_line2,ad_city,ad_state,ad_zip,ad_type,ad_r ef,ad_sort,ad_country,ad_ext2,qry_time) Values('634984', '76392', 'HG0742', 'HG0742', 'HOMEGOODS #0742', '8700 PRESTON RD STE 111', 'PRESTON VILLIAGE SHP CTR', 'PLANO', 'TX', '75024', 'ship-to', '76392', 'HOMEGOODS #0742', 'United States', '450289', '2016/08/11 - 12:45:02')

[Bulzie]

Is shipto a table or query? If query can you provide the SQL code for it. Maybe something with the special characters in the data? So once it fails, you can rerun it immediately and it works?

[Doug_B]

It's a table. The C# calls a program that extracts the data into XML, passes it back to C#, and the C# updates the table. If I set the right flags, the extract program will pull it again, and it will update the .mdb just fine.

[CJ_London]

Operation must use an updateable query.

So what is the query? Can you provide the sql?

sorry - now see the sql, ignore my request!!

[Doug_B]

Here's the code. It creates this string and inserts each record:

if (oXmlReader.NodeType == XmlNodeType.EndElement)
{
if (cText != "")
{
if (cFieldList == "")
{
cFieldList = "Insert into shipto(" + oXmlReader.Name.ToString();
}
else
{
cFieldList = cFieldList + "," + oXmlReader.Name.ToString();


if (ltrace)
{
Console.WriteLine("#5 XML Data: " + oXmlReader.Name.ToString());
}
}
}


//Console.WriteLine(" {0}='{1}'", cField, cText);
cText = "";


if (oXmlReader.Name.ToString() == "shipto")
{
StringBuilder stb = new StringBuilder();


try
{
cFieldList = cFieldList + ") ";
cValuesList = cValuesList + ")";
stb.Append(cFieldList.ToString());
stb.Append(cValuesList.ToString());
cFieldList = "";
cValuesList = "";


//Update the Access database
iSoCount++ ;
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = stb.ToString();
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

cmd.Dispose();
}
catch (Exception ex)
{
StreamWriter logSW;
logSW = File.AppendText(sLogFile); // ConfigurationSettings.AppSettings["UPSlogFile"]);
logSW.Write(DateTime.Now.ToString());
logSW.Write(" -- ");
logSW.WriteLine(ex.Message);
logSW.Write(" ");
logSW.WriteLine(stb);
logSW.Close();
}


}
}
} //while (oXmlReader.Read())

[orange]

Can you show us the design of the ShipTo table.

All field values appear to be text in your SQL.
If your qry_time field is a Date data type, then you'll need # date time value#

[Doug_B]

All the fields are text. And as I said it has worked for years.

[orange]

Just curious --is there a Primary key defined on the ShipTo table?

Here is a link (Read Only)that may be helpful.

Potentially other users or processes using the database?

[Bulzie]

Do all 3 of your process run at the same time on the scheduler? Maybe something is locking that table?

[Doug_B]

First - I want to thank you guy's for helping!

so_nbr is unique, all the fields are text.

I changed the time in the task scheduler to run at 15 and 45 - the others run on the half hour. Did not help.

[orange]

Is so_nbr defined as Primary Key?

[Doug_B]

so-nbr is the primary key

[orange]

The link I gave before identifies various reasons why a recordset is not updateable.