CODEWITHSUNDEEP
c#sql
Kohnarik
Kohnarik

Reputation: 377

C# SQLCommand does not write data into db

I have the following SQL statement (which works if I execute it like this inside my DB):

IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = '445')
   UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + 1
ELSE
   INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked)VALUES('445', 1);

I now need to do that from code-behind.

I have the following two parameters:

private const int _pickCount = 1;
string dexID

The dexID is a parameter given to the function I call.

Doing the standard procedure with making a new Connection and opening it works fine, but I possibly have a syntax error when using SQLCommand:

try
{
    using (myConnection)
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = myConnection;
            command.CommandType = CommandType.Text;
            command.CommandText = "IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = @Dex_ID) UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + @PickCount"
                + "ELSE" 
                + "INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked)"
                + "VALUES(@Dex_ID, @PickCount)";
            command.Parameters.AddWithValue("@Dex_ID", dexID);
            command.Parameters.AddWithValue("@PickCount", _pickCount);
            command.ExecuteNonQuery();
        }
    }
}

Am I missing something?

Upvotes: 2

Views: 88

Answers (2)

Vahid Heydarinezhad
Vahid Heydarinezhad

Reputation: 126

Modify

try
{
    using (myConnection)
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = myConnection;
            command.CommandType = CommandType.Text;
            command.CommandText = "IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = @Dex_ID) UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + @PickCount"
                + "ELSE" 
                + "INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked)"
                + "VALUES(@Dex_ID, @PickCount)";
            command.Parameters.AddWithValue("@Dex_ID", dexID);
            command.Parameters.AddWithValue("@PickCount", _pickCount);
            command.ExecuteNonQuery();
        }
    }
}

TO:

try
{
    using (myConnection)
    {
        using (SqlCommand command = new SqlCommand())
        {
            command.Connection = myConnection;
            command.CommandType = CommandType.Text;
            command.CommandText = "IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = @Dex_ID) UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + @PickCount"
                + "  ELSE" 
                + "  INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked)"
                + "  VALUES(@Dex_ID, @PickCount)";
            command.Parameters.AddWithValue("@Dex_ID", dexID);
            command.Parameters.AddWithValue("@PickCount", _pickCount);
            command.ExecuteNonQuery();
        }
    }
}

Upvotes: 1

Soner Gönül
Soner Gönül

Reputation: 98840

I think you need to put a white space at the end of your every string that creates your command.

command.CommandText = "IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = @Dex_ID) UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + @PickCount "
            + "ELSE " 
            + "INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked) "
            + "VALUES(@Dex_ID, @PickCount)";

Or use verbatim string literal as;

command.CommandText = @"IF EXISTS (SELECT * FROM dbo.PopularityPokemon WHERE Dex_ID = @Dex_ID) UPDATE dbo.PopularityPokemon SET TimesPicked = TimesPicked + @PickCount 
                        ELSE 
                        INSERT into dbo.PopularityPokemon (Dex_ID, TimesPicked) 
                        VALUES(@Dex_ID, @PickCount)";

Also don't use AddWithValue as much as you can. It may generate unexpected and surprising results sometimes. Use Add method overload to specify your parameter type (SqlDbType) and it's size.

Upvotes: 5

Related Questions