CODEWITHSUNDEEP
c#sql-serverazuresql-insert
Dave Gordon
Dave Gordon

Reputation: 1835

Why is this SQL INSERT failing without raising and exception?

I have a problem with an INSERT into a table on Azure SQL which is failing - returning a -1 on rows affected using the ExecuteNonQuery command.

The Table looks like this:

URLS Table

and the code is like this:

private void SaveUrls(ArrayList alUrls)
{
    int rows = 0;
    this.Cursor = Cursors.WaitCursor;

    foreach (string url in alUrls)
    {
        try
        {
            // INSERT INTO URLS (UrlHash,URLPath,DiscoveryDate,IsInProcessingQueue) VALUES('83B2DE6A8247D2F0C1F674DA0DC4E173230C541542D0A4852A8F83AC8A874D43E2A9335B3A505A50DBC735592D792E9A2564FF556EB3286C7F974A0EDE995F46','http://google.com/blog-entry-4835.html', getutcdate(), False)
            // INSERT INTO URLS (UrlHash,URLPath,DiscoveryDate,DiscoveredById,IsInProcessingQueue) VALUES('83B2DE6A8247D2F0C1F674DA0DC4E173230C541542D0A4852A8F83AC8A874D43E2A9335B3A505A50DBC735592D792E9A2564FF556EB3286C7F974A0EDE995F46','http://google.com/blog-entry-4835.html', getutcdate(), 1006, False)
            // INSERT INTO [dbo].[URLS] ([UrlId], [UrlHash], [URLPath], [DiscoveryDate], [DiscoveredById], [ProcessedDate], [ProcessedById], [IsInProcessingQueue], [JoinedProcessingQueueDate]) VALUES (1, N'83B2DE6A8247D2F0C1F674DA0DC4E173230C541542D0A4852A8F83AC8A874D43E2A9335B3A505A50DBC735592D792E9A2564FF556EB3286C7F974A0EDE995F46', N'http://google.com/blog-entry-4835.html', N'2015-03-05 22:44:18', 1006, NULL, NULL, 0, NULL)
            // INSERT INTO URLS ([UrlId], [UrlHash], [URLPath], [DiscoveryDate], [DiscoveredById], [ProcessedDate], [ProcessedById], [IsInProcessingQueue], [JoinedProcessingQueueDate]) VALUES(N'83B2DE6A8247D2F0C1F674DA0DC4E173230C541542D0A4852A8F83AC8A874D43E2A9335B3A505A50DBC735592D792E9A2564FF556EB3286C7F974A0EDE995F46',N'http://google.com/blog-entry-4835.html',N'getutcdate()', 1006, ,,False,)
            // INSERT INTO URLS ([UrlId], [UrlHash], [URLPath], [DiscoveryDate], [DiscoveredById], [ProcessedDate], [ProcessedById], [IsInProcessingQueue], [JoinedProcessingQueueDate]) VALUES(N'83B2DE6A8247D2F0C1F674DA0DC4E173230C541542D0A4852A8F83AC8A874D43E2A9335B3A505A50DBC735592D792E9A2564FF556EB3286C7F974A0EDE995F46',N'http://google.com/blog-entry-4835.html',N'getutcdate()', 1006, '','',False,'')

            SqlCommand command = new SqlCommand("INSERT INTO URLS ([UrlId], [UrlHash], [URLPath], [DiscoveryDate], [DiscoveredById], [ProcessedDate], [ProcessedById], [IsInProcessingQueue], [JoinedProcessingQueueDate]) VALUES(N'" + HashPassword(url) + "',N'" + url + "',N'getutcdate()', 1006, '" + null+ "','" + null + "'," + false + ",'" + null + "')");
            command.CommandType = CommandType.Text;
            command.Connection = con;

            rows += command.ExecuteNonQuery();
        }
        catch (SqlException sqle)
        {
            Log("SQL ERROR: SaveUrls " + sqle.Message);
        }

        Application.DoEvents();
    }

    this.Cursor = Cursors.Default;

    Log("Seeded URLS Table with " + rows.ToString("N0") + " rows");

    alUrls.Clear();           
}

UPDATE:

Here is how the tables are related:

Diagram

UPDATE 2:

  1. There is no exception generated
  2. Even with the UrlId removed the insert fails.

Upvotes: 1

Views: 139

Answers (2)

Dave Gordon
Dave Gordon

Reputation: 1835

Well the answer turns out that the URLHash column of varchar(128) was too small to hold a string of 128 characters!

I have no idea how that works but after removing the constraints and changing the varchar(128) to nvarchar(256) (not sure why it was varchar anyway) I reinstated the constraints and hey presto it works.

Upvotes: 0

Richard
Richard

Reputation: 30628

Your INSERT statement specifies the UrlId field, but the first value you supply is the hash. 

INSERT INTO URLS ([UrlId], [UrlHash], [URLPath]...

should be

INSERT INTO URLS ([UrlHash], [URLPath]...

Also, as others have said in the comments, use parameters instead of building your SQL string up!

Upvotes: 3

Related Questions