CODEWITHSUNDEEP
azureazure-storageazure-blob-storage
Kirsten
Kirsten

Reputation: 18076

Azure Table Storage Bad Request - Error in query syntax

The following used to work.

      public void CreateTableIfMissing()
    {
        var info = new StorageInfo(); // initialized with tablename and connectionstring
        var storageAccount = CloudStorageAccount.Parse(info.ConnectionString);
        var tableClient = storageAccount.CreateCloudTableClient();
        var table = tableClient.GetTableReference(info.TableName);
        try
        {
            table.CreateIfNotExists();
            var batchOperation = new TableBatchOperation();
            var s = DateTime.Now.ToString();
            var entry = new TableEntity("partkey"+s,"rowkey"+s);
            batchOperation.Insert(entry);
            table.ExecuteBatch(batchOperation);

        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            throw;
        }
    }

Error information is

{Microsoft.WindowsAzure.Storage.StorageException: 
ErrorCode "InvalidInput"
Element 0 in the batch returned an unexpected response code.
StatusMessage:0:Bad Request - Error in query syntax

The table is in use for error logging via Serilog with an Azure sync. I can see that it is still getting log records if I connect with Azure Storage Explorer.

I have not changed connection strings

[Update]

I am trying a single operation but having trouble

'TableOperation' does not contain a constructor that takes 2 arguments
Cannot access internal constructor 'TableOperation' here

single op

more info

[Update]

If I follow Ivan's advice but omit the ToString("o") parameter the error is

ErrorMessage:The 'PartitionKey' parameter of value 'partkey3/7/2019 8:33:25 PM' is out of range.

This makes sense.

I wonder why it ever worked!

Upvotes: 4

Views: 3106

Answers (1)

Ivan Glasenberg
Ivan Glasenberg

Reputation: 29940

Update:

For the error message in your previous code(not the update one):

{Microsoft.WindowsAzure.Storage.StorageException: 
ErrorCode "InvalidInput"
Element 0 in the batch returned an unexpected response code.
StatusMessage:0:Bad Request - Error in query syntax

The reason is that the partkey and rowkey in table storage does not accept characters like "/". And when you use DateTime.Now.ToString(),which contains characters "/", as the suffix of partkey and rowkey, then will cause the error. Please format the datetime and remove the "/", you can use DateTime.Now.ToString("o") in your code(or other correct format).

For the updated code:

The error is because TableOperation class does not has constructor(parameter or parameterless). You can nav to TableOperation class and take a look at its usage.

enter image description here

In you case, you should use it's static Insert method like var op = TableOperation.Insert(entry) instead of var op = new TableOperation(entry,TableOperationType.Insert).

And also one thing you need to know, the partkey and rowkey in table storage does not accept characters like "/", so when you use datetime.now for a suffix of partkey and rowkey, you should use var s = DateTime.Now.ToString("o"). Or it will cause error.

The sample code works fine for me:

        public void CreateTableIfMissing()
        {
           var info = new StorageInfo(); // initialized with tablename and connectionstring
           var storageAccount = CloudStorageAccount.Parse(info.ConnectionString);
           var tableClient = storageAccount.CreateCloudTableClient();
           var table = tableClient.GetTableReference(info.TableName);

            try
            {
                table.CreateIfNotExists();

                var s = DateTime.Now.ToString("o");
                var entry = new TableEntity("partkey" + s, "rowkey" + s);
                var op = TableOperation.Insert(entry);
                table.Execute(op);               

            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                throw;
            }
        }

For more code samples about table storage, you can refer to this article.

Upvotes: 18

Related Questions