0

I am doing an INSERT query to a db with the following code which works fine.

public int InsertPerson(Person person, out string errormsg)
{
    SqlConnection dbConnection = new SqlConnection();

    dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=PersonDB;Integrated Security=True";

    String sqlstring = "INSERT INTO PersonTable ( FirstName, LastName ) VALUES ( @firstname, @lastname )";
    SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);

    dbCommand.Parameters.Add("FirstName", SqlDbType.NVarChar, 30).Value = person.FirstName;
    dbCommand.Parameters.Add("LastName", SqlDbType.NVarChar, 30).Value = person.LastName;

    try
    {
        dbConnection.Open();
        
        int i = 0;
        i = dbCommand.ExecuteNonQuery();
        
        if (i == 1) 
        { 
            errormsg = ""; 
        }
        else
        {
            errormsg = "Could not add person";
        }

        return i;
    }
    catch (Exception e)
    {
        errormsg = e.Message;
        return 0;
    }
    finally
    {
        dbConnection.Close();
    }
}

However when I try to use the logic in a DELETE query it does not convert the @firstname and @lastname to the parameter values passed to the method call.

public int DeletePerson(Person person, out string errormsg)
{
    SqlConnection dbConnection = new SqlConnection();

    dbConnection.ConnectionString = @"Data Source=(localdb)\mssqllocaldb;Initial Catalog=PersonDB;Integrated Security=True";

    String sqlstring = "DELETE FROM PersonTable WHERE FirstName = @firstname AND LastName = @lastname";

    SqlCommand dbCommand = new SqlCommand(sqlstring, dbConnection);

    dbCommand.Parameters.Add("FirstName", SqlDbType.NVarChar, 30).Value = person.FirstName;
    dbCommand.Parameters.Add("LastName", SqlDbType.NVarChar, 30).Value = person.LastName;

    try
    {
        dbConnection.Open();
        int i = 0;
        i = dbCommand.ExecuteNonQuery();

        if (i == 1)
        {
            errormsg = "";
        }
        else
        {
            //errormsg = "Could not delete person";
            errormsg = sqlstring;
        }

        return i;
    }
    catch (Exception e)
    {
        errormsg = e.Message;
        return 0;
    }
    finally
    {
        dbConnection.Close();
    }
}

The query that is created from the DELETE method looks like this DELETE FROM PersonTable WHERE FirstName = @firstname AND LastName = @lastname

It works fine when I hardcode the SQL query but not when I use the attributes of the Person parameter as parts of the query..

9
  • 3
    "The query that is created from the DELETE method looks like this" Where are you seeing this? You won't see the values injected except perhaps in some SQL logs. Are you seeing the values replaced with the INSERT query? Commented Sep 29, 2020 at 22:13
  • 2
    i = dbCommand.ExecuteNonQuery(); Are you saying i = 0? Or is there an exception? If the hardcoded version works, then you have to use the debugger to see what the difference is. Although it seemingly works, I would prefer matching the parameter name: dbCommand.Parameters.Add("FirstName"... should be dbCommand.Parameters.Add("@firstname"... etc. Commented Sep 29, 2020 at 22:13
  • 2
    Can you confirm the person object is not NULL, and its first and last name are not longer than 30 characters? Commented Sep 29, 2020 at 22:19
  • 1
    Try including the @ symbol in the variable name and using the same case like dbCommand.Parameters.Add("@firstname",... Commented Sep 29, 2020 at 22:20
  • 1
    Ooooh right @LarsTech my mistake, It doesn't return 1 if successful it returns the amount of affected rows or something - when I changed the paramter name from "FirstName" to "@firstname" it works and returns the amount of posts that were deleted :D Could you possibly explain why? Commented Sep 29, 2020 at 22:31

1 Answer 1

4

You may misunderstand how parameters work. The value is not "replaced" in the SQL statement. The SQL statement is sent as-is with the parameter values provided separately. So you won't see a SQL string on the client side with the values replaced.

In other words, the value of sqlstring does not change. If that's what you're looking at then it won't tell you anything. Something else is wrong - either that name combination is not found in the DB, or there's some difference in casing, whitespace, etc.

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

1 Comment

Oh ok, my mistake :) Thanks for the clarification!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.