Update query not updating

[Guest]

I have a strange problem with a source code generator I have created,
everything works fine for all SQL Server queries, and all the Access queries
(Insert, FetchAll, Find and Delete queries) work correctly. The update query
does not work on any table, but the code works, the queries are created
correctly, and the query is exuted without any errors, but the update does
not occur.

I have an update query, create with C# and DAO, using the CreateQueryDef
method, the query has the following text:

Update [ReferencesSets] Set [ProjectID] = @ProjectID,
[ReferencesSetName] = @ReferencesSetName

Where [ReferencesSets.ReferencesSetID] = @ReferencesSetID

When I create the query and open in Access 2003, view SQL model >>

Access shows the query like the following:

UPDATE ReferencesSets SET
ReferencesSets.ProjectID = @ProjectID,
ReferencesSets.ReferencesSetName = @ReferencesSetName
WHERE ((([ReferencesSets.ReferencesSetID])=[@ReferencesSetID]));

I have tried to manually change the update query to the same as the Query
Text above, and the result is the same, no records affected.

Access wraps the where clause expression in nested parentheses "("
for some reason. But the problem is when I execute the query with the
following code:

// initial value
int rowsAffected = 0;

// Create Command Object
OleDbCommand command = new OleDbCommand();

// Set properties
command.Connection = databaseConnection;
command.CommandType = commandType;
command.CommandText = procedureName;

// Set Parameters
SetParameters(command, parameters);

// Execute Query
rowsAffected = command.ExecuteNonQuery();

// Code for Set Parameters converts the SQL Parameter collection to an
OleDbCollection, (because my StoredProcedure class has a SQL[] parameter
array, but the code below shows how the SqlParameters were created

// Create parameter for [ProjectID]
param = new SqlParameter("@ProjectID",
referencesSets.ProjectID);
parameters[0] = param;

// Create parameter for [ReferencesSetID]
param = new SqlParameter("@ReferencesSetID",
ReferencesSetID);
parameters[1] = param;

// Create parameter for [ReferencesSetName]
param = new SqlParameter("@ReferencesSetName",
referencesSets.ReferencesSetName);
parameters[2] = param;

The code for set parameters loops through each parameter and creates an
OleDbParameter with the following code:

// verify all objects exist
if ((parameters != null) && (command != null) &&
(command.Parameters != null))
{
// Clear All Parameters
command.Parameters.Clear();

// Add each parameter
foreach(SqlParameter param in parameters)
{
// if the value is null
if(param.Value == null)
{
// Set type to string
if(param.DbType == DbType.String)
{
// set value to "";
param.Value = "";
}
}

// Create OleDbParam
OleDbParameter oleDbParam = new
OleDbParameter(param.ParameterName, param.Value);

// Add each param
command.Parameters.Add(oleDbParam);
}
}

I have stepped through code and all the values are supplied correctly, the
@ReferencesSetID is set to an existing record in the database, the
command.ExecuteNonQuery() does not return any rows but an error does not
occur. Is there any way to diagnose this, the same code as a SQL stored proc
works fine. I am pulling my hair (singular) out. Is there a way to get the
execution path or find out what Access did with the query and why?

Any help or clue as to how to solve this would be greatly appreciated.

Corby Nichols

The rowsAffected comes back as 0, and the query does not get updated.

 

[Guest]

One more thing to add, if I manually run the query from within Access, the
query works.

Corby Nichols

 

[Guest]

I solved my problem so I thought I post the reason the query failed, in
Access, although I had the query parameters named correctly, the order of the
queries must be the exact same as the query is expecting, I did not encounter
this problem using SQL Server, the same parameter creator was used for SQL
Server and the update queries worked, so I believe this is an Access issue.
Any MVP's want to confirm my theory, that the queries must be in the exact
order even though the names match the query parameter namesZ? This solved my
problem any way.

Just thought I would post my own solution.

Corby Nichols