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..
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 bedbCommand.Parameters.Add("@firstname"...etc.personobject is not NULL, and its first and last name are not longer than 30 characters?