CODEWITHSUNDEEP
c#sql
lahso
lahso

Reputation: 11

ExecuteCommand returns -1 yet does update record

This is my update statement built like so:

StringBuilder queryUpdate = new StringBuilder();
queryUpdate.AppendFormat("UPDATE TPMdailydata SET FISBTranIntvairmet={0},FISBTranIntvmetr={1},FISBTranIntvnexradnational = {2} ", svd.Data[1].SecPerUpdate.ToString("0.0"), svd.Data[2].SecPerUpdate.ToString("0.0"), svd.Data[3].SecPerUpdate.ToString("0.0"));
queryUpdate.AppendFormat(",FISBTranIntvnexradregional = {0},FISBTranIntvnotam = {1},FISBTranIntvoutage = {2} ", svd.Data[4].SecPerUpdate.ToString("0.0"), svd.Data[5].SecPerUpdate.ToString("0.0"), svd.Data[7].SecPerUpdate.ToString("0.0"));
queryUpdate.AppendFormat(",FISBTranIntvpirep = {0},FISBTranIntvsigmet = {1},FISBTranIntvSUA = {2} ", svd.Data[8].SecPerUpdate.ToString("0.0"), svd.Data[9].SecPerUpdate.ToString("0.0"), svd.Data[10].SecPerUpdate.ToString("0.0"));
queryUpdate.AppendFormat(",FISBTranIntvTAF = {0},FISBTranIntvTISB = {1},FISBTranIntvwind = {2}, AirportID = '{3}' ", svd.Data[11].SecPerUpdate.ToString("0.0"), svd.Data[12].SecPerUpdate.ToString("0.0"), svd.Data[13].SecPerUpdate.ToString("0.0"), des);
queryUpdate.AppendFormat("where SVID = {0} and reportdate = '{Z}' ", (int)svd.ID, rptdate.ToShortDateString());
queryUpdate.AppendFormat("and siteID = '{0}'", siteID);
string cmd = queryUpdate.ToString();

Executed by: 

rowsreturned = this.dba.ExecuteCommand(cmd);

Wrote out to Console like so:

Console.WriteLine(string.Format("cmd-{0}",cmd)); //+ "and siteID = '{0}'",siteID
Console.WriteLine(String.Format("Number of rows updated: {0} UPDATE FISB Data: report date {1} svid {2} ", rowsreturned, rptdate, svd.ID));

results as follows:

cmd-UPDATE TPMdailydata SET FISBTranIntvairmet=124.8,FISBTranIntvmetr=252.0,FISBTranIntvnexradnational = 905.4 ,FISBTranIntvnexradregional = 150.6,FISBTranIntvnotam = 248.5,FISBTranIntvoutage = 0.0 ,FISBTranIntvpirep = 494.8,FISBTranIntvsigmet = 123.8,FISBTranIntvSUA = 551.8 ,FISBTranIntvTAF = 553.7,FISBTranIntvTISB = 13.3,FISBTranIntvwind = 554.1, AirportID = 'ZDC' where SVID = 158 and reportdate = '6/20/2011' and siteID = 'DEV' Number of rows updated: -1 UPDATE FISB Data: report date 6/20/2011 12:00:00 AM svid 158

The update definitely works but I was expecting a return value for the number of rows updated. I get -1. I thinking this is bad. I can't seem to find any info on what the -1 means. Help anyone?

Upvotes: 0

Views: 840

Answers (2)

Gregory A Beamer
Gregory A Beamer

Reputation: 17010

You need to parameterize you code and add the parameters to the string rather than have people try to navigate the spaghetti mess above. Even if you don't want to do this for maintainability, you will remove the chance of SQL injection.

As for your -1, I am not sure why. There are plenty of things you could have set that accomplish this. What I would try is get away from LINQ, as there is no reason to run this query through the LINQ objects. Here is a pattern that invokes the SQL command directly rather than through the layers of implicit FUD:

        string connString = "{Connection string here}";
        string sql = "{SQL Query Here}";

        using(SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();

            using(SqlCommand cmd = new SqlCommand(sql, conn))
            {
                int numRows = cmd.ExecuteNonQuery();
            }
        }

ADDED: To try the above methodology, without creating the SQL Connection, you change this:

rowsreturned = this.dba.ExecuteCommand(cmd);

to this

        SqlConnection conn = (SqlConnection) dba.Connection;
        using(SqlCommand cmd = new SqlCommand(queryUpdate, conn))
        {
            rowsreturned = cmd.ExecuteNonQuery();
        }

This pulls off the context you already have and you can see if rowsReturned still presents an issue. If so, check the command string and consider running it directly in SQL Studio.

Upvotes: 1

Jon Raynor
Jon Raynor

Reputation: 3892

Sometimes databases have options which do not send the amount of records updated.

For example, with SQL Server if this is done in the Stored Procedure:

SET NOCOUNT ON

Then the records effected will always return -1.

There is probably some option like this either with the database or the provider that is preventing the amount of records returned.

Upvotes: 0

Related Questions