0

I am currently working on accessing a database using OleDB and I am having issue with string replacement of OleDBParameter.

using (OleDbCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = "SELECT * FROM " + tablename + " WHERE ID = @ID ;";
    cmd.Parameters.AddWithValue("@ID", testslist.number + "_" + testcase.name + "_" + testcase.config);
    OleDbDataReader reader = cmd.ExecuteReader();
    RecordExists = reader.HasRows;
    reader.Close();

    if (RecordExists)
    {
        string updatestring = null;
        foreach (KeyValuePair<string, string> kv in dictionary)
            updatestring = updatestring + kv.Key + " = '@val" + kv.Key + "',";
        cmd.CommandText = "UPDATE " + tablename + " SET " + updatestring.Remove(updatestring.Length - 1) + " WHERE ID = @ID;";

        foreach(KeyValuePair<string,string> kv in dictionary)
        {
            if (kv.Value == null)
                cmd.Parameters.AddWithValue("@val" + kv.Key, DBNull.Value);
            else
                cmd.Parameters.AddWithValue("@val" + kv.Key, kv.Value);
        }
        cmd.ExecuteNonQuery();
    }
    else
    {
        string insertstring = "ID,";
        string valuestring = "@ID,";

        foreach(KeyValuePair<string,string>kv in dictionary)
        {
            insertstring = insertstring + kv.Key + ",";
            valuestring = valuestring + "@var" + kv.Key + ",";
           // valuestring = valuestring + "'"+ kv.Value+ "',";
        }
        cmd.CommandText = "INSERT INTO " + tablename + " (" + insertstring.Remove(insertstring.Length - 1) + ") VALUES(" + valuestring.Remove(valuestring.Length - 1) + ");";
        foreach (KeyValuePair<string, string> kv in dictionary)
        {
            if (kv.Value == null)
                cmd.Parameters.AddWithValue("@val" + kv.Key, DBNull.Value);
            else
                cmd.Parameters.AddWithValue("@val" + kv.Key, kv.Value);
        }
        cmd.ExecuteNonQuery();
}

For whatever reason, only the @ID is getting replaced correctly in my database. After running the code, the ID is replaced correctly in my database, but every other field values are @var[nameofthekey] (@varTestCase for example) . I really don't know where to look anymore especially since one the parameter gets replaced properly in the database, but not the other.

Thank you for your help!

edit: I forgot to mention, I am working with an access database (accdb)

leigero
  • 3,233
  • 12
  • 42
  • 63
Jeph Gagnon
  • 157
  • 3
  • 13
  • 1
    Why are you using parameters for your actual `WHERE` clauses but not the names of your tables? You should consider refactoring that. Additionally, `OleDbCommand` objects don't support named parameters, so you could consider changing your parameters to `?` instead (you can still add the parameters with a name, just ensure your order is correct). – Rion Williams May 03 '16 at 19:49
  • what type of database are you working with..? stop doing your code by building the Query's dynamically and replace them with a stored procedure as well as using parameterized queries instead.. your code will be more easier to maintain not to mention read.. also use the `Debugger` – MethodMan May 03 '16 at 19:53
  • I will update the tablename later, for now, I am just trying to figure this issue. The code is not really clean as I am trying a lot of things. If OleDBcommand doesn't support named parameter, then why is @ID working then? and even then, if named parameter is not supported, the value @var[nameofthekey] in my database should be [valueofthekey], not @var[nameofthekey]? – Jeph Gagnon May 03 '16 at 19:56
  • @MethodMan I put an edit saying that I am working with an accdb. I was not aware of the stored procedures, that might be a lot easier for me indeed. I will look into that, thanks – Jeph Gagnon May 03 '16 at 20:01
  • Line 13, you have quotes around your parameter so it gets treated as a string, not as a parameter. – yaakov May 03 '16 at 20:02
  • @codran That was indeed the mistake... thank you. I have other problems with the update record part (it doesnt update). But at least the creation of the record so that's a good start. – Jeph Gagnon May 03 '16 at 20:15

0 Answers0