c# run query to update sql server database not all records being updated

Question

I'm having issue with running a query in c# that updates a table in sql server.

In total I'm updating about 2500 records in my table. However there are about 350 rows where the acq_rank field is not being updated. So I have checked the query by looking at the cmdText variable and the parameter values. If I copy these values and run the query in sql server manually (bottom of this post) the query updates the table. I'm not sure what is happening.

UPDATE

Comparing the records that did update and those that didn't it appears any companies with a comma in their name are the ones not being updated. How do I get round this?

When debugging the code I can see the company names are normally "some name" however when they contain a comma however it looks like "\"some name with comma\""

C#

 for (int i = 0; i < rankList.Count; i++)
            {
                string cmdText;

                if(rankList[i].company_id == "NULL")                    
                    cmdText = "update MA_DEALS_COUNTRY set acq_rank = @rank where deal_id = @deal and company_id is null and co_name = @coname";                    
                else                    
                    cmdText = "update MA_DEALS_COUNTRY set acq_rank = @rank where deal_id = @deal and company_id = @co_id and co_name = @coname";                    

                _command = new SqlCommand(cmdText, _connection);

                if (rankList[i].company_id == "NULL")
                {
                    _command.Parameters.Add("@deal", System.Data.SqlDbType.VarChar).Value = rankList[i].DealId;
                    _command.Parameters.Add("@coname", System.Data.SqlDbType.VarChar).Value = rankList[i].CoName;
                    _command.Parameters.Add("@rank", System.Data.SqlDbType.Int).Value = rankList[i].Rank;
                }
                else
                {
                    _command.Parameters.Add("@deal", System.Data.SqlDbType.VarChar).Value = rankList[i].DealId;
                    _command.Parameters.Add("@co_id", System.Data.SqlDbType.VarChar).Value = rankList[i].company_id;
                    _command.Parameters.Add("@coname", System.Data.SqlDbType.VarChar).Value = rankList[i].CoName;
                    _command.Parameters.Add("@rank", System.Data.SqlDbType.Int).Value = rankList[i].Rank;
                }

                _command.ExecuteNonQuery();

                Console.WriteLine("row " + i.ToString());
            }

SQL Server Manual Update

I have included both update statement - one for when the company_id is null and one when it is not

declare @rank int
declare @deal varchar(20)
declare @co_id varchar(5)
declare @coname varchar(max)

set @rank = 0
set @deal = '136344MM'
set @co_id = 'AB240'
set @coname = 'Company Capital Partners, Inc.'

update MA_DEALS_COUNTRY set acq_rank = @rank where deal_id = @deal and company_id is null and acquirer_name = @coname

update MA_DEALS_COUNTRY set acq_rank = @rank where deal_id = @deal and company_id = @co_id and acquirer_name = @coname

I think you want to escape special characters in your values from C#, when you have "Morgan's PC" in C#, sql will interprete this differently. To correct it you'll have to pass it as "Morgan''s PC". — Bayeni
– Bayeni, Commented Aug 13, 2014 at 9:48
I don't commas are your problem, maybe something else in your update statement is not working correctly. Also, it also a good practice to use stored procedures to avoid things like sql Injection and also provide an easy maintainance when you want to make changes in future. — Bayeni
– Bayeni, Commented Aug 13, 2014 at 12:52

Vectoria · Accepted Answer · 2014-08-14 09:14:32Z

1

the problem is likely when the update statement is comparing co_name = @coname. You have to follow the code where this is being compared for a company with a comma.

You also mentioned that instead of "some name," it looks like "\"some name,\""

Try:

A.

_command.Parameters.Add("@coname", System.Data.SqlDbType.VarChar).Value = Convert.ToString(rankList[i].CoName);

B.

_command.Parameters.Add("@coname", System.Data.SqlDbType.VarChar).Value = rankList[i].CoName.ToString();

C.

Search for the '\' and '"' characters in the string and remove them. This is not the best of ways to go about it as the '\' or '"' should not be there in the first place when the assignment is made.

string valueString = rankList[i].CoName.ToString();
            while (valueString.IndexOf('"') > -1)
        {
            if (valueString.Length - 1 == valueString.IndexOf('"'))
                valueString = valueString.Remove(valueString.IndexOf('"'));
            else
            valueString = valueString.Remove(valueString.IndexOf('"'), valueString.IndexOf('"')+1);
        }
        while (valueString.IndexOf('\\') > -1)
        {
            if (valueString.Length - 1 == valueString.IndexOf('\\'))
                valueString = valueString.Remove(valueString.IndexOf('\\'));
            else
            valueString = valueString.Remove(valueString.IndexOf('\\'), valueString.IndexOf('\\')+1);
        }
_command.Parameters.Add("@coname", System.Data.SqlDbType.VarChar).Value = valueString;

edited Aug 14, 2014 at 9:14

answered Aug 13, 2014 at 10:42

Vectoria

1,69714 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mHelpMe Over a year ago

none of the above methods make any difference sadly

Vectoria Over a year ago

I have edited the code to remove unwanted characters in the string, it should work now...

Roger Wolf · Accepted Answer · 2014-08-13 12:39:41Z

1

Well, for starters, the following version of the query will work regardless of the nullability of the @co_id parameter:

update MA_DEALS_COUNTRY set acq_rank = @rank
where deal_id = @deal
and nullif(@co_id, company_id) is null
and co_name = @coname

Considering how you test the rankList[i].company_id for nullability, this should help.

answered Aug 13, 2014 at 12:39

Roger Wolf

7,7872 gold badges29 silver badges37 bronze badges

Collectives™ on Stack Overflow

c# run query to update sql server database not all records being updated

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related