CODEWITHSUNDEEP
c#asp.netsql-server-2008
NoBullMan
NoBullMan

Reputation: 2184

MS SQL Can't update table

I have a one column table to keep track of number of visits. In Global.asax.cs I attempt to increment this value by 1 inside application_start but the field does't get updated. I get no exceptions but number of rows affected is always zero.

I tried the same simple query in SSMS and I get the same thing: 0 rows affected. There is one int column in that table called NumVisits. This is part of Application_Start in Global.asax.cs:

Application.Lock();
int iNumVisits = SomeClass.GetNumVisits();
SomeClass.UpdateNumVists(iNumVisits + 1);
Application.UnLock();

This is in SomeClass (BLL):

public static void UpdateNumVists(int iNumVisists)
{
    LocaleRepository oLocaleRepository = new LocaleRepository(new SqlDbContext());
    oLocaleRepository.UpdateNumVists(iNumVisists);
}

and this is in DAL:

public void UpdateNumVists(int iNumVisits)
{
    int iRet = 0;
    try
    {
        dbContext.Open();
        List<SqlParameter> spParams = new List<SqlParameter>();
        string sQuery = "update Visits set NumVisits = @NumVisits";
        spParams.Add(dbContext.CreateSqlParam("@NumVisits", SqlDbType.Int, 0, iNumVisits));
        dbContext.ExecuteSqlNonQuery(sQuery, spParams, ref iRet);
    }
    catch (Exception e)
    {
        throw e;
    }
    finally
    {
        dbContext.Close();
    }
    return;
}

I use the following for all commands using executeNonQuery:

public void ExecuteSqlNonQuery(string sQuery, List<SqlParameter> spParams, ref int iRet)
{
    using (SqlCommand command = new SqlCommand())
    {
        command.CommandType = CommandType.Text;
        command.Parameters.AddRange(spParams.ToArray<SqlParameter>());
        command.Connection = DbConnection;
        command.CommandText = sQuery;
        try
        {
            iRet = command.ExecuteNonQuery();
        }
        catch(Exception e)
        { }
    }
}

when the update command is executed, iRet is zero. I can't see why a simple update query would not work.

This is the create script I got from SSMS:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Visits](
    [NumVisits] [int] NULL
) ON [PRIMARY]

GO

Upvotes: 1

Views: 2454

Answers (2)

HLGEM
HLGEM

Reputation: 96542

In general there are a few possible reasons why an update would not happen.

First, if the field is also an identity or calculated field, an ordinary update is not going to work. This doesn't look to be your case, but it is good to know for the future.

Next if there is a trigger on the table, it may be preventing the update. SSMS doesn't necessarily script triggers out when you script a table, so I can't tell if you have a trigger.

Third And most common, your application may not be sending what you expect as the update statement or even communicating with the database at all when you expect it to. This is often true when there is a problem of nulls. If your variable is not properly populating, then you may indeed be updating a null value to a null value. Run Profiler to capture exactly what is being sent when you try to do the update. Often when you see the statement that is actually being run, you will see the problem. Sometimes it is a matter of a missing space, sometimes a variable that was not populated that you thought was populated, etc.

Another possibility is that the user running the code has no update rights to the table. You should have gotten a message if this were the case.

If you have run Profiler, try running that exact code in SSMS and see if it updates. Sometimes you get a better error bubble up when you do that. Especially if your error handling in the application code is not well designed.

Of course if the table has no data, you need to do an insert not an update. Or the update might not be finding any records to update, try doing a select using the same conditions and it may turn out there is not record to update.

Upvotes: 2

Andrew Diamond
Andrew Diamond

Reputation: 6335

It seems like there is no data in the table, are there any records in Visits?

Upvotes: 1

Related Questions