none
I have two functions, one using parameters and the other creates an update sql, what wrong with parameters? RRS feed

  • Question

  •    Hi, I have two functions that updates an ACESS table. One uses parameters, and the other creates an UDATE sql. Why doesn't the one that uses parameters work? No error messages are thrown, but the ACCESS table isn't updated when I try to use the function with parameters.

     

     

    Code Snippet

    private void UpdateAlternateData(DataSet dset, Int64 akey)

    {

      String sql = "";

      string[] ya ={ "y1", "y2", "y3", "y4", "y5", "y6", "y7", "y8", "y9", "y10" };

      using (OleDbCommand cmd = new OleDbCommand(sql, pdata.Connection))

      {

        if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open();

        cmd.Parameters.Add("@key", OleDbType.BigInt);

        cmd.Parameters.Add("@akey", OleDbType.BigInt);

        cmd.Parameters.Add("@category", OleDbType.SmallInt);

        cmd.Parameters.Add("@y1", OleDbType.Single);

        cmd.Parameters.Add("@y2", OleDbType.Single);

        cmd.Parameters.Add("@y3", OleDbType.Single);

        cmd.Parameters.Add("@y4", OleDbType.Single);

        cmd.Parameters.Add("@y5", OleDbType.Single);

        cmd.Parameters.Add("@y6", OleDbType.Single);

        cmd.Parameters.Add("@y7", OleDbType.Single);

        cmd.Parameters.Add("@y8", OleDbType.Single);

        cmd.Parameters.Add("@y9", OleDbType.Single);

        cmd.Parameters.Add("@y10", OleDbType.Single);

        cmd.CommandText = String.Format("UPDATE [AlternateData] SET " +

          "[{0}] = @{0}, [{1}] = @{1}, [{2}] = @{2}, [{3}] = @{3}, [{4}] = @{4}, " +

          "[{5}] = @{5}, [{6}] = @{6}, [{7}] = @{7}, [{8}] = @{8}, [{9}] = @{9} " +

          "WHERE [key] = @key",ya);

        String fl = String.Format("akey = {0}", akey);

        String srt = "category ASC";

        foreach (DataRow dr in dset.Tables["AlternateData"].Select(fl, srt))

        {

          if (dr.RowState == DataRowState.Modified)

          {

            cmd.Parameters["@key"].Value = Convert.ToInt64(dr["key"]);

            cmd.Parameters["@akey"].Value = Convert.ToInt64(dr["akey"]);

            cmd.Parameters["@category"].Value = Convert.ToInt16(dr["category"]);

            cmd.Parameters["@y1"].Value = Convert.ToSingle(dr["y1"]);

            cmd.Parameters["@y2"].Value = Convert.ToSingle(dr["y2"]);

            cmd.Parameters["@y3"].Value = Convert.ToSingle(dr["y3"]);

            cmd.Parameters["@y4"].Value = Convert.ToSingle(dr["y4"]);

            cmd.Parameters["@y5"].Value = Convert.ToSingle(dr["y5"]);

            cmd.Parameters["@y6"].Value = Convert.ToSingle(dr["y6"]);

            cmd.Parameters["@y7"].Value = Convert.ToSingle(dr["y7"]);

            cmd.Parameters["@y8"].Value = Convert.ToSingle(dr["y8"]);

            cmd.Parameters["@y9"].Value = Convert.ToSingle(dr["y9"]);

            cmd.Parameters["@y10"].Value = Convert.ToSingle(dr["y10"]);

            cmd.ExecuteNonQuery();

          }

        }

      }

    }

    private void UpdateAlternateData2(DataSet dset, Int64 akey)

    {

      String sql = "";

      string[] ya ={ "y1", "y2", "y3", "y4", "y5", "y6", "y7", "y8", "y9", "y10" };

      using (OleDbCommand cmd = new OleDbCommand(sql, pdata.Connection))

      {

        if (cmd.Connection.State == ConnectionState.Closed) cmd.Connection.Open();

        String fl = String.Format("akey = {0}", akey);

        String srt = "category ASC";

        String[] sa = new String[11];

        foreach (DataRow dr in dset.Tables["AlternateData"].Select(fl, srt))

        {

          sql = "";

          if (dr.RowState == DataRowState.Modified)

          {

            sql = "UPDATE [AlternateData] SET " +

              "[y1] = {0}, [y2] = {1}, [y3] = {2}, [y4] = {3}, [y5] = {4}, " +

              "[y6] = {5}, [y7] = {6}, [y8] = {7}, [y9] = {8}, [y10] = {9} " +

              "WHERE [key] = {10}";

          }

          if (sql.Length > 0)

          {

            sa[10] = dr["key"].ToString();

            sa[0] = dr["y1"].ToString();

            sa[1] = dr["y2"].ToString();

            sa[2] = dr["y3"].ToString();

            sa[3] = dr["y4"].ToString();

            sa[4] = dr["y5"].ToString();

            sa[5] = dr["y6"].ToString();

            sa[6] = dr["y7"].ToString();

            sa[7] = dr["y8"].ToString();

            sa[8] = dr["y9"].ToString();

            sa[9] = dr["y10"].ToString();

            cmd.CommandText = String.Format(sql,sa);

            cmd.ExecuteNonQuery();

          }

        }

      }

    }

     

    Thanks for your help

     

    Sunday, July 15, 2007 2:08 PM

Answers

  • VMazur is right on the money here... I just wanted to emphasize something he says as it can be a little confusing.

     

    Access parameters are referenced by POSITION in the SQL statement and in your parameter collection NOT by name.

     

    As an example:

     

    Your SQL statement ends with

     

    WHERE [key] = ?"

     

    But, your parameter collection starts with

     

    cmd.Parameters["@key"].Value = Convert.ToInt64(dr["key"]);

     

    In order for these two to get wired up correctly, the first reference of a parameter in your sql must match positionally with the occurance in your collection.  In this case, your parameter collection must END with this parameter not begin with it.  I am suer there are other order issues you need to take care of...

     

    What your code is curretly doing is basically this:

     

    Update .... Where [Key] = Convert.ToSingle(dr["y10"]);

     

    Since this is lexically correct (both are numbers) you get no error message, but nothing is updated as there is no "key value" equal to Convert.ToSingle(dr["y10"])

     

     

    Monday, July 16, 2007 5:07 PM

All replies

  • I believe you cannot use named parameters with Access. You need to use question marks (?) instead, like

     

    cmd.CommandText ="UPDATE [AlternateData] SET [{0}] = ?, [{1}] = ?, [{2}] = ?, [{3}] = ?, [{4}] = ?, [{5}] = ?, [{6}] = ?, [{7}] = ?, [{8}] = ?, [{9}] = ? WHERE [key] = ?";

     

    Then you need to pass parameters as you do right now, but keep in mind that since there are no named parameters anymore, sequence you create them in your code has to be exact the same as they should be in your UPDATE SQL statement. Strange that you do not get any error using named parameters. Is it possible that your code catches exception and just does nothing?

    Monday, July 16, 2007 10:57 AM
    Moderator
  • VMazur is right on the money here... I just wanted to emphasize something he says as it can be a little confusing.

     

    Access parameters are referenced by POSITION in the SQL statement and in your parameter collection NOT by name.

     

    As an example:

     

    Your SQL statement ends with

     

    WHERE [key] = ?"

     

    But, your parameter collection starts with

     

    cmd.Parameters["@key"].Value = Convert.ToInt64(dr["key"]);

     

    In order for these two to get wired up correctly, the first reference of a parameter in your sql must match positionally with the occurance in your collection.  In this case, your parameter collection must END with this parameter not begin with it.  I am suer there are other order issues you need to take care of...

     

    What your code is curretly doing is basically this:

     

    Update .... Where [Key] = Convert.ToSingle(dr["y10"]);

     

    Since this is lexically correct (both are numbers) you get no error message, but nothing is updated as there is no "key value" equal to Convert.ToSingle(dr["y10"])

     

     

    Monday, July 16, 2007 5:07 PM