Sql Parameters already been declared

Question

So i'm trying to use parameters in SQL as in my database someone has a surname of "O'Brien" as you can imagine without using parameters this caused me a lot of hell, before you start screaming at me telling me i should have already been using paramaterised queries, i'm very new to using SQL properly and haven't had the time to learn how to do it properly yet so I've just been using string queries for the time being so, my code is as follows:

//string constr = ConfigurationManager.ConnectionStrings["EBSLIVE"].ConnectionString;
string constr = ConfigurationManager.ConnectionStrings["EBSTEST"].ConnectionString;
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection(constr))
{
    using(SqlCommand cmd = new SqlCommand())
    {
        foreach (string name in StringNames)
        {
            string[] StuName = name.Split('|');
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Select MOBILE_PHONE_NUMBER as Number From people Where FORENAME = @FORENAME and SURNAME = @SURNAME and PERSON_CODE = @PERSONCODE";

            cmd.Parameters.AddWithValue("@PERSONCODE", StuName[0]);
            cmd.Parameters.AddWithValue("@FORENAME", StuName[1]);
            cmd.Parameters.AddWithValue("@SURNAME", StuName[2]);
            cmd.CommandType = CommandType.Text;
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.Connection = con;
                sda.SelectCommand = cmd;
                using (DataSet ds = new DataSet())
                {
                    DataTable ndt = new DataTable();
                    sda.Fill(ndt);
                    dt.Merge(ndt);
                }
            }
        }
    }              
}
Numbers.AddRange(dt.Rows.OfType<DataRow>().Select(dr => dr.Field<string>("Number")).ToList());

But for some reason it tells me that @PERSONCODE has already been declared. I have no clue of whats going on. Any help would be appreciated.

Answer 1

You are using the same SqlCommand for different DataAdapters, but you are creating and adding the parameters in the loop, so the SqlCommand gets multiple parameters with identical names. That's not allowed of course.

You don't need the SqlCommand but you can reuse DataAdapter.SelectCommand every time. I wouldn't use AddWithValue:

using (SqlConnection con = new SqlConnection(constr))
using (SqlDataAdapter da = new SqlDataAdapter("Select MOBILE_PHONE_NUMBER as Number From people Where FORENAME = @FORENAME and SURNAME = @SURNAME and PERSON_CODE = @PERSONCODE", con))
{
    var cmd = da.SelectCommand;
    cmd.Parameters.Add("@PERSONCODE", SqlDbType.NVarChar);
    cmd.Parameters.Add("@FORENAME", SqlDbType.NVarChar);
    cmd.Parameters.Add("@SURNAME", SqlDbType.NVarChar);

    foreach (string name in StringNames)
    {
        string[] StuName = name.Split('|');
        cmd.Parameters["@PERSONCODE"].Value = StuName[0];
        cmd.Parameters["@FORENAME"].Value = StuName[1];
        cmd.Parameters["@SURNAME"].Value = StuName[2];

         DataTable ndt = new DataTable();
         da.Fill(ndt);
         dt.Merge(ndt);
    }
}