Cmd.ExecuteNonQuery Question..help Please

I have a DataAccess that has "cmd.ExecuteNonQuery" and another file SqlTableProfileProvider for a SandBox project(from ASP.NET) for a Custom TableProfileProvider. That also has a cmd.ExecuteNonQuery even thou these are 2 different files i get an error after debug.



Question is are they conflicting ? if so how can i fix this.



Code Snippets for both provided below the 2nd cmd.ExecuteNonQuery that is producing the Error is Commented in Orange...Thxs for the help Rattlerr


Incorrect syntax near ','.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','.

Source Error:





Line 454: cmd.CommandType = CommandType.Text;
Line 455:
Line 456: cmd.ExecuteNonQuery();
Line 457:
Line 458: // Need to close reader before we try to update



[SqlException (0x80131904): Incorrect syntax near ','.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +903
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
Microsoft.Samples.SqlTableProfileProvider.SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) in d:Programming ProgramsXtremesystemsXtremesystemsxsApp_CodeSqlTableProfileProvider.cs:456
System.Configuration.SettingsBase.SaveCore() +379
System.Configuration.SettingsBase.Save() +77
System.Web.Profile.ProfileBase.SaveWithAssert() +31
System.Web.Profile.ProfileBase.Save() +63
System.Web.Profile.ProfileModule.OnLeave(Object source, EventArgs eventArgs) +2374047
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64


DataAccess.cs::






Code Snippet

public abstract class DataAccess

{

private string _connectionString = "";

protected string ConnectionString

{

get { return _connectionString; }

set { _connectionString = value; }

}

private bool _enableCaching = true;

protected bool EnableCaching

{

get { return _enableCaching; }

set { _enableCaching = value; }

}

private int _cacheDuration = 0;

protected int CacheDuration

{

get { return _cacheDuration; }

set { _cacheDuration = value; }

}

protected Cache Cache

{

get { return HttpContext.Current.Cache; }

}

protected int ExecuteNonQuery(DbCommand cmd)

{

if (HttpContext.Current.User.Identity.Name.ToLower() == "sampleeditor")

{

foreach (DbParameter param in cmd.Parameters)

{

if (param.Direction == ParameterDirection.Output ||

param.Direction == ParameterDirection.ReturnValue)

{

switch (param.DbType)

{

case DbType.AnsiString:

case DbType.AnsiStringFixedLength:

case DbType.String:

case DbType.StringFixedLength:

case DbType.Xml:

param.Value = "";

break;

case DbType.Boolean:

param.Value = false;

break;

case DbType.Byte:

param.Value = byte.MinValue;

break;

case DbType.Date:

case DbType.DateTime:

param.Value = DateTime.MinValue;

break;

case DbType.Currency:

case DbType.Decimal:

param.Value = decimal.MinValue;

break;

case DbType.Guid:

param.Value = Guid.Empty;

break;

case DbType.Double:

case DbType.Int16:

case DbType.Int32:

case DbType.Int64:

param.Value = 0;

break;

default:

param.Value = null;

break;

}

}

}

return 1;

}

else

return cmd.ExecuteNonQuery();

}

protected IDataReader ExecuteReader(DbCommand cmd)

{

return ExecuteReader(cmd, CommandBehavior.Default);

}

protected IDataReader ExecuteReader(DbCommand cmd, CommandBehavior behavior)

{

return cmd.ExecuteReader(behavior);

}

protected object ExecuteScalar(DbCommand cmd)

{

return cmd.ExecuteScalar();

}



SecondFile::








Code Snippet

public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection) {

string username = (string)context["UserName"];

bool userIsAuthenticated = (bool)context["IsAuthenticated"];

if (username == null || username.Length < 1 || collection.Count < 1)

return;

SqlConnection conn = null;

SqlDataReader reader = null;

SqlCommand cmd = null;

try {

bool anyItemsToSave = false;

// First make sure we have at least one item to save

foreach (SettingsPropertyValue pp in collection) {

if (pp.IsDirty) {

if (!userIsAuthenticated) {

bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];

if (!allowAnonymous)

continue;

}

anyItemsToSave = true;

break;

}

}

if (!anyItemsToSave)

return;

conn = new SqlConnection(_sqlConnectionString);

conn.Open();

List<ProfileColumnData> columnData = new List<ProfileColumnData>(collection.Count);

foreach (SettingsPropertyValue pp in collection) {

if (!userIsAuthenticated) {

bool allowAnonymous = (bool)pp.Property.Attributes["AllowAnonymous"];

if (!allowAnonymous)

continue;

}

//Normal logic for original SQL provider

//if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to



//Can eliminate unnecessary updates since we are using a table though

if (!pp.IsDirty)

continue;

string persistenceData = pp.Property.Attributes["CustomProviderData"] as string;

// If we can't find the table/column info we will ignore this data

if (String.IsNullOrEmpty(persistenceData)) {

// REVIEW: Perhaps we should throw instead?

continue;

}

string[] chunk = persistenceData.Split(new char[] { ';' });

if (chunk.Length != 2) {

// REVIEW: Perhaps we should throw instead?

continue;

}

string columnName = chunk[0];

// REVIEW: Should we ignore case?

SqlDbType datatype = (SqlDbType)Enum.Parse(typeof(SqlDbType), chunk[1], true);

object value = null;

// REVIEW: Is this handling null case correctly?

if (pp.Deserialized && pp.PropertyValue == null) { // is value null?

value = DBNull.Value;

}

else {

value = pp.PropertyValue;

}

// REVIEW: Might be able to ditch datatype

columnData.Add(new ProfileColumnData(columnName, pp, value, datatype));

}

// Figure out userid, if we don't find a userid, go ahead and create a user in the aspnetUsers table

Guid userId = Guid.Empty;

cmd = new SqlCommand("SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '" + AppId + "' AND u.UserName = LOWER(@Username)", conn);

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@Username",username);

try {

reader = cmd.ExecuteReader();

if (reader.Read()) {

userId = reader.GetGuid(0);

}

else {

reader.Close();

cmd.Dispose();

reader = null;

cmd = new SqlCommand("dbo.aspnet_Users_CreateUser", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ApplicationId", AppId);

cmd.Parameters.AddWithValue("@UserName", username);

cmd.Parameters.AddWithValue("@IsUserAnonymous", !userIsAuthenticated);

cmd.Parameters.AddWithValue("@LastActivityDate", DateTime.UtcNow);

cmd.Parameters.Add(CreateOutputParam("@UserId", SqlDbType.UniqueIdentifier, 16));

cmd.ExecuteNonQuery();

userId = (Guid)cmd.Parameters["@userid"].Value;

}

}

finally {

if (reader != null) {

reader.Close();

reader = null;

}

cmd.Dispose();

}

// Figure out if the row already exists in the table and use appropriate SELECT/UPDATE

cmd = new SqlCommand(String.Empty, conn);

StringBuilder sqlCommand = new StringBuilder("IF EXISTS (SELECT 1 FROM ").Append(_table);

sqlCommand.Append(" WHERE UserId = @UserId) ");

cmd.Parameters.AddWithValue("@UserId", userId);

// Build up strings used in the query

StringBuilder columnStr = new StringBuilder();

StringBuilder valueStr = new StringBuilder();

StringBuilder setStr = new StringBuilder();

int count = 0;

foreach (ProfileColumnData data in columnData) {

columnStr.Append(", ");

valueStr.Append(", ");

columnStr.Append(data.ColumnName);

string valueParam = "@Value" + count;

valueStr.Append(valueParam);

cmd.Parameters.AddWithValue(valueParam, data.Value);

// REVIEW: Can't update Timestamps?

if (data.DataType != SqlDbType.Timestamp) {

if (count > 0) {

setStr.Append(",");

}

setStr.Append(data.ColumnName);

setStr.Append("=");

setStr.Append(valueParam);

}

++count;

}

columnStr.Append(",LastUpdatedDate ");

valueStr.Append(",@LastUpdatedDate");

setStr.Append(",LastUpdatedDate=@LastUpdatedDate");

cmd.Parameters.AddWithValue("@LastUpdatedDate", DateTime.UtcNow);

sqlCommand.Append("BEGIN UPDATE ").Append(_table).Append(" SET ").Append(setStr.ToString());

sqlCommand.Append(" WHERE UserId = '").Append(userId).Append("'");

sqlCommand.Append("END ELSE BEGIN INSERT ").Append(_table).Append(" (UserId").Append(columnStr.ToString());

sqlCommand.Append(") VALUES ('").Append(userId).Append("'").Append(valueStr.ToString()).Append(") END");

cmd.CommandText = sqlCommand.ToString();

cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery(); //THIS cmd.ExecuteNonQuery Produces the Error

// Need to close reader before we try to update

if (reader != null) {

reader.Close();

reader = null;

}

UpdateLastActivityDate(conn, userId);

}

finally {

if (reader != null)

reader.Close();

if (cmd != null)

cmd.Dispose();

if (conn != null)

conn.Close();

}

}