CODEWITHSUNDEEP
c#sqlparameters
Brian Diez
Brian Diez

Reputation: 1

Parameterized query not working in C#

I am currently in a corner and have no idea why the following code will not execute properly and update the database (Access).

  1. newUser = All of the new user's data including their ID
  2. list = Contains a list of GermanResources (class) entries that correspond to the pages checkboxes. Class includes .Name (text value of checkbox) and .Value (checked? 1 or 0)

I want to update the database with the checkbox value of each GermanResource.

IF i replace @acc_Value with the value 1 this code works. It seems to not work with the first parameter in place. Debugging this showed me that everything had the proper values at the proper times and since "1" worked I know the data types are not mismatched.

Note: There were no errors with or without the parameter in place. I would appreciate any input about this.

This is one of the CommandTexts that are generated: UPDATE VMS_GRM_GermanResource_Access SET VTOFZN = @acc_Value WHERE UserId = @userId

private bool NewUser_Insert_GermanResourceAccess(OleDbConnection connection, User newUser, List<GermanResource> list)
    {
        bool result = false;

        try
        {
            foreach (var item in list)
            {
                string column = item.Name.Replace(" ", "");

                string query = @"UPDATE VMS_GRM_GermanResource_Access SET " + column + " = @acc_Value WHERE UserId = @userId";

                OleDbCommand command = new OleDbCommand(query, connection);

                command.Parameters.AddWithValue("@userId", newUser.Id);
                command.Parameters.Add(new OleDbParameter("@acc_Value", OleDbType.Integer, 1));
                command.Parameters["@acc_Value"].Value = item.Access;

                command.ExecuteNonQuery();                  
            }

            result = true;
        }
        catch (OleDbException ex)
        {
            UADConnection.Close();
            MessageBox.Show(ex.ErrorCode.ToString() + ": " + ex.Message);
            return result;
        }

        return result;
    }

Upvotes: 0

Views: 465

Answers (2)

Engineer
Engineer

Reputation: 29

Use this to prepare sql statement :-

string query = @"UPDATE VMS_GRM_GermanResource_Access SET column_name=" + 
@acc_Value + " WHERE UserId = "  +@userId+";

Upvotes: 1

Brian Diez
Brian Diez

Reputation: 1

@Tetsuya Yamamoto:

OLEDB parameters were not in order according to the query. Swapping them around to match the order in the query set things straight. All good again and thanks for everyone's inputs.

Upvotes: 0

Related Questions