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 Programs\Xtremesystems\Xtremesystems\xs\App_Code\SqlTableProfileProvider.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();
}
}
YOu have a syntax error in your code, please post the CommandText of the Execution before the actual command is executed. With this command we can see what is wrong with your TSQL code.
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
I suggest that immediately before the cmd.ExecuteNonQuery() statement, that perhaps it would be useful to add a messagebox.show, and display your command. Obviously, there is a syntax error and it needs to be examined.
If the error is not obvious to you, post it here and perhaps we can help.
|||I posted the Code but its not showing up in here ..lol but anyways here is the Modified version where i put in a Try ,Catch block..I also tried the MessageBox like you mentioned but it tells me the same thing as every other method..
But here is the Preceding Code::
Code Snippet
public class SqlTableProfileProvider : ProfileProvider {
private string _appName;
private Guid _appId;
private bool _appIdSet;
private string _sqlConnectionString;
private int _commandTimeout;
private string _table;
public override void Initialize(string name, NameValueCollection config) {
if (config == null)
throw new ArgumentNullException("config");
if (String.IsNullOrEmpty(name))
name = "SqlTableProfileProvider";
if (string.IsNullOrEmpty(config["description"])) {
config.Remove("description");
config.Add("description", "SqlTableProfileProvider");
}
base.Initialize(name, config);
string temp = config["connectionStringName"];
if (String.IsNullOrEmpty(temp))
throw new ProviderException("connectionStringName not specified");
_sqlConnectionString = SqlStoredProcedureProfileProvider.GetConnectionString(temp);
if (String.IsNullOrEmpty(_sqlConnectionString)) {
throw new ProviderException("connectionStringName not specified");
}
_appName = config["applicationName"];
if (string.IsNullOrEmpty(_appName))
_appName = SqlStoredProcedureProfileProvider.GetDefaultAppName();
if (_appName.Length > 256) {
throw new ProviderException("Application name too long");
}
_table = config["table"];
if (string.IsNullOrEmpty(_table)) {
throw new ProviderException("No table specified");
}
EnsureValidTableOrColumnName(_table);
string timeout = config["commandTimeout"];
if (string.IsNullOrEmpty(timeout) || !Int32.TryParse(timeout, out _commandTimeout)) {
_commandTimeout = 30;
}
config.Remove("commandTimeout");
config.Remove("connectionStringName");
config.Remove("applicationName");
config.Remove("table");
if (config.Count > 0) {
string attribUnrecognized = config.GetKey(0);
if (!String.IsNullOrEmpty(attribUnrecognized))
throw new ProviderException("Unrecognized config attribute:" + attribUnrecognized);
}
}
public override string ApplicationName {
get { return _appName; }
set {
if (value == null)
throw new ArgumentNullException("ApplicationName");
if (value.Length > 256) {
throw new ProviderException("Application name too long");
}
_appName = value;
_appIdSet = false;
}
}
private Guid AppId {
get {
if (!_appIdSet) {
SqlConnection conn = null;
try {
conn = new SqlConnection(_sqlConnectionString);
conn.Open();
SqlCommand cmd = new SqlCommand("dbo.aspnet_Applications_CreateApplication", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@.applicationname", ApplicationName);
cmd.Parameters.Add(CreateOutputParam("@.ApplicationId", SqlDbType.UniqueIdentifier, 0));
cmd.ExecuteNonQuery();
_appId = (Guid)cmd.Parameters["@.ApplicationId"].Value;
_appIdSet = true;
}
finally {
if (conn != null) {
conn.Close();
}
}
}
return _appId;
}
}
private int CommandTimeout {
get { return _commandTimeout; }
}
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
private static string s_legalChars = "_@.#$";
private static void EnsureValidTableOrColumnName(string name) {
for (int i = 0; i < name.Length; ++i) {
if (!Char.IsLetterOrDigit(name[i]) && s_legalChars.IndexOf(name[i]) == -1)
throw new ProviderException("Table and column names cannot contain: "+name[i]);
}
}
private void GetProfileDataFromTable(SettingsPropertyCollection properties, SettingsPropertyValueCollection svc, string username, SqlConnection conn) {
List<ProfileColumnData> columnData = new List<ProfileColumnData>(properties.Count);
StringBuilder commandText = new StringBuilder("SELECT u.UserID");
SqlCommand cmd = new SqlCommand(String.Empty, conn);
int columnCount = 0;
foreach (SettingsProperty prop in properties) {
SettingsPropertyValue value = new SettingsPropertyValue(prop);
svc.Add(value);
string persistenceData = prop.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);
columnData.Add(new ProfileColumnData(columnName, value, null /* not needed for get */, datatype));
commandText.Append(", ");
commandText.Append("t."+columnName);
++columnCount;
}
commandText.Append(" FROM "+_table+" t, vw_aspnet_Users u WHERE u.ApplicationId = '").Append(AppId);
commandText.Append("' AND u.UserName = LOWER(@.Username) AND t.UserID = u.UserID");
cmd.CommandText = commandText.ToString();
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@.Username", username);
SqlDataReader reader = null;
try {
reader = cmd.ExecuteReader();
//If no row exists in the database, then the default Profile values
//from configuration are used.
if (reader.Read()) {
Guid userId = reader.GetGuid(0);
for (int i = 0; i < columnData.Count; ++i) {
object val = reader.GetValue(i+1);
ProfileColumnData colData = columnData[i];
SettingsPropertyValue propValue = colData.PropertyValue;
//Only initialize a SettingsPropertyValue for non-null values
if (!(val is DBNull || val == null))
{
propValue.PropertyValue = val;
propValue.IsDirty = false;
propValue.Deserialized = true;
}
}
// need to close reader before we try to update the user
if (reader != null) {
reader.Close();
reader = null;
}
UpdateLastActivityDate(conn, userId);
}
}
finally {
if (reader != null) {
reader.Close();
}
}
}
private static void UpdateLastActivityDate(SqlConnection conn, Guid userId) {
SqlCommand cmd = new SqlCommand("UPDATE aspnet_Users SET LastActivityDate = @.LastUpdatedDate WHERE UserId = '" + userId + "'", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@.LastUpdatedDate", DateTime.UtcNow);
try {
cmd.ExecuteNonQuery();
}
finally {
cmd.Dispose();
}
}
public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext context, SettingsPropertyCollection collection) {
SettingsPropertyValueCollection svc = new SettingsPropertyValueCollection();
if (collection == null || collection.Count < 1 || context == null)
return svc;
string username = (string)context["UserName"];
if (String.IsNullOrEmpty(username))
return svc;
SqlConnection conn = null;
try {
conn = new SqlConnection(_sqlConnectionString);
conn.Open();
GetProfileDataFromTable(collection, svc, username, conn);
}
finally {
if (conn != null) {
conn.Close();
}
}
return svc;
}
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////
// Container struct for use in aggregating columns for queries
private struct ProfileColumnData {
public string ColumnName;
public SettingsPropertyValue PropertyValue;
public object Value;
public SqlDbType DataType;
public ProfileColumnData(string col, SettingsPropertyValue pv, object val, SqlDbType type) {
EnsureValidTableOrColumnName(col);
ColumnName = col;
PropertyValue = pv;
Value = val;
DataType = type;
}
}
Here i put in a Try & Catch block but it tells me nothing new, i even tried your Method of the MessageBox before the cmd.ExecuteNonQuery but it tells me nothing new
either..Tells me the same error as in my 1st post..
MessageBox.Show(cmd.ExecuteNonQuery().ToString());
|||Comment out the
cmd.ExecuteNonQuery
line, and add
Messagebox.show ( SQLCommand ).
You need to 'SEE' the command in order to see and correct the syntax problem!
|||
Error 102 'System.Data.SqlClient.SqlCommand' is a 'type' but is used like a 'variable'
The following line produces the error above..
MessageBox.Show(SqlCommand);
//cmd.ExecuteNonQuery();
|||
My regrets, I thought you would understand that we need to see the contents of the command string in the command object -in this situation, the variable 'cmd'.
|||
SqlCommand cmd = new SqlCommand("UPDATE aspnet_Users SET LastActivityDate = @.LastUpdatedDate WHERE UserId = '" + userId + "'", conn);
Sorry for any confusion but this is what i put cause it wouldnt let me define a new cmd in the current scope, so i put this in their.I'm kinda frustrated on this so, If this isn't what you need to see please show me what i need to put in their..
This also has to do with my post in the T-Sql Section for the sample Schema script for creating the ProfileTable_1 ( http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1536893&SiteID=1). That you helped me with Arnie where i put::
grant EXECUTE on dbo.aspnet_Applications_CreateApplication to [WYATT-PC\Wyatt]
It will run the script saying "Query Executed Successfully" but it also says::
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Thxs Rattlerr
MessageBox.Show(cmd.CommandText.ToString() );
But this is the Results of the MessageBox::
IF EXISTS (SELECT 1 FROM ProfileTable_1 WHERE UserId = @.UserId) BEGIN
UPDATE ProfileTable_1 SET, LastUpdatedDate = @.LastUpdatedDate WHERE UserId =
long encrypted string ' END ELSE BEGIN INSERT
ProfileTable_1 (UserId, LastUpdatedDate) VALUES
('Another encrypted string', @.LastUpdatedDate) END
Schema Script related to my post in the T-Sql Area link is provided above::
Code Snippet
--
--Set this to the name of your database
--
use xtremesystems
go
--
--grants on ASP.NET stored procedures and tables used by the custom providers
--
grant EXECUTE on dbo.aspnet_Applications_CreateApplication to [WYATT-PC\Wyatt]
grant EXECUTE on dbo.aspnet_Users_CreateUser to [WYATT-PC\Wyatt]
grant SELECT on dbo.aspnet_Users to [WYATT-PC\Wyatt]
grant UPDATE on dbo.aspnet_Users(LastActivityDate) to [WYATT-PC\Wyatt]
go
--drop table dbo.ProfileTable_1
--go
create table dbo.ProfileTable_1 (
UserId uniqueidentifier not null Primary Key,
FirstName nvarchar(50) null,
LastName nvarchar(50) null,
Age int null,
LastUpdatedDate datetime not null)
go
grant SELECT,INSERT,UPDATE,DELETE on dbo.ProfileTable_1 to [WYATT-PC\Wyatt]
go
--
--Get stored procedure
--
--drop procedure getCustomProfileData
create procedure getCustomProfileData
@.ApplicationName nvarchar(256),
@.UserName nvarchar(256),
@.FirstName nvarchar(50) OUTPUT,
@.LastName nvarchar(50) OUTPUT,
@.Age int OUTPUT
as
declare @.ApplicationId uniqueidentifier
set @.ApplicationId = NULL
--Get the appid
exec dbo.aspnet_Applications_CreateApplication @.ApplicationName, @.ApplicationId OUTPUT
--Return data for the requested user in the application
select @.FirstName = FirstName,
@.LastName = LastName,
@.Age = Age
from dbo.ProfileTable_1 pt,
dbo.vw_aspnet_Users u
where u.ApplicationId = @.ApplicationId
and u.UserName = @.UserName
and u.UserId = pt.UserId
go
grant EXECUTE on dbo.getCustomProfileData to [WYATT-PC\Wyatt]
go
--
--Set stored procedure
--
--drop procedure setCustomProfileData
create procedure setCustomProfileData
@.ApplicationName nvarchar(256),
@.UserName nvarchar(256),
@.IsUserAnonymous bit,
@.FirstName nvarchar(50),
@.LastName nvarchar(50),
@.Age int
as
declare @.ApplicationId uniqueidentifier
set @.ApplicationId = NULL
declare @.CurrentUtcDate datetime
set @.CurrentUtcDate = getutcdate()
--Get the appid
exec dbo.aspnet_Applications_CreateApplication @.ApplicationName, @.ApplicationId OUTPUT
--Create user if needed
declare @.UserId uniqueidentifier
select @.UserId = UserId
from dbo.vw_aspnet_Users
where ApplicationId = @.ApplicationId
and LoweredUserName = LOWER(@.UserName)
if(@.UserId IS NULL)
exec dbo.aspnet_Users_CreateUser @.ApplicationId, @.UserName, @.IsUserAnonymous, @.CurrentUtcDate, @.UserId OUTPUT
--Either insert a new row of data, or update a pre-existing row
if exists (select 1 from dbo.ProfileTable_1 where UserId = @.UserId)
BEGIN
update dbo.ProfileTable_1
set FirstName = @.FirstName,
LastName = @.LastName,
Age = @.Age,
LastUpdatedDate = @.CurrentUtcDate
where UserId = @.UserId
END
else
BEGIN
insert dbo.ProfileTable_1 (UserId, FirstName, LastName, Age, LastUpdatedDate)
values (@.UserId, @.FirstName, @.LastName, @.Age, @.CurrentUtcDate)
END
go
grant EXECUTE on dbo.setCustomProfileData to [WYATT-PC\Wyatt]
go
|||
Rattlerr wrote:
grant EXECUTE on dbo.aspnet_Applications_CreateApplication to [WYATT-PC\Wyatt]
It will run the script saying "Query Executed Successfully" but it also says::
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
Yes, that is correct. You cannot GRANT permissions to yourself. If I recall correctly, you wanted to GRANT permission to ASPNET and you were having difficulty with the value used for [YOURMACHINENAME]. 'Somehow' it was coming up as 'WYATT-PC\Wyatt' rather than just 'WYATT-PC'.
I have take the liberty to reformat the code below so that it is more readible...
IF EXISTS (SELECT 1 FROM ProfileTable_1 WHERE UserId = @.UserId)
BEGIN
UPDATE ProfileTable_1
SET, LastUpdatedDate = @.LastUpdatedDate
WHERE UserId =long encrypted string '
END
ELSE
BEGIN
INSERTProfileTable_1 (UserId, LastUpdatedDate)
VALUES ('Another encrypted string', @.LastUpdatedDate)
END
It appears that when you are putting the query together, there are a couple of errors.
1. In the [yellow] section, there is a unneeded comma that causes a syntax error.
2. In the ]green] section, the is a missing quote after the equals sign and before the string value.
3. In the [blue] section, there 'should' be a space between INSERT and ProfileTable_1
Correct these errors and let us know if you are still experiencing the same problems -or, as I suspect, other problems will show up.
|||
lol sorry i put long encyrpted string in their cause it was a long encrypted string and alot to type out..But i will type it all out this is what shows up in the MessageBox::
IF EXISTS (SELECT 1 FROM ProfileTable_1 WHERE userId = @.UserId)
BEGIN
UPDATE ProfileTable_1
SET, LastUpdatedDate = @.LastUpdatedDate
WHERE UserId = 'cf76b1dc-691f-4987-8b2b-579d21bbcab'
END
BEGIN
INSERT ProfileTable_1 (UserId, LastUpdatedDate)
VALUES ('cf76b1dc-691f-4987-8b2b-579d21bbcab', @.LastUpdatedDate)
END
Here is the section of the script that matchs that MessageBox..
Code Snippet
private static void UpdateLastActivityDate(SqlConnection conn, Guid userId) {
SqlCommand cmd = new SqlCommand("UPDATE aspnet_Users SET LastActivityDate = @.LastUpdatedDate WHERE UserId = '" + userId + "'", conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@.LastUpdatedDate", DateTime.UtcNow);
try {
cmd.ExecuteNonQuery();
}
finally {
cmd.Dispose();
}
}
& This Section:
Code Snippet
// 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;
try
{
MessageBox.Show(cmd.CommandText.ToString() );
//cmd.ExecuteNonQuery();
Testing it now I removed the comma from in front of the setStr.Append(",LastUpdatedDate=@.LastUpdatedDate"); see what happens now..
|||
By removing the comma where i mentioned in my last post the Syntax Error has gone away, but know i have a new error thats being caught by the try ,catch block .. Any idea's on this one?
Would i be correct to assume that has something to do with the Stored Procedures?
And again Thxs for all the help and being patient with me..lol 
Cheers
Must declare the scalar varible "@.Value1LastUpdatedDate".
Code Snippet
--
--Set stored procedure
--
--drop procedure setCustomProfileData
ALTER procedure setCustomProfileData
@.ApplicationName nvarchar(256),
@.UserName nvarchar(256),
@.IsUserAnonymous bit,
@.FirstName nvarchar(50),
@.LastName nvarchar(50),
@.Age int
as
declare @.ApplicationId uniqueidentifier
set @.ApplicationId = NULL
declare @.CurrentUtcDate datetime
set @.CurrentUtcDate = getutcdate()
--Get the appid
exec dbo.aspnet_Applications_CreateApplication @.ApplicationName, @.ApplicationId OUTPUT
--Create user if needed
declare @.UserId uniqueidentifier
select @.UserId = UserId
from dbo.vw_aspnet_Users
where ApplicationId = @.ApplicationId
and LoweredUserName = LOWER(@.UserName)
if(@.UserId IS NULL)
exec dbo.aspnet_Users_CreateUser @.ApplicationId, @.UserName, @.IsUserAnonymous, @.CurrentUtcDate, @.UserId OUTPUT
--Either insert a new row of data, or update a pre-existing row
if exists (select 1 from dbo.ProfileTable_1 where UserId = @.UserId)
BEGIN
update dbo.ProfileTable_1
set FirstName = @.FirstName,
LastName = @.LastName,
Age = @.Age,
LastUpdatedDate = @.CurrentUtcDate
where UserId = @.UserId
END
else
BEGIN
insert dbo.ProfileTable_1 (UserId, FirstName, LastName, Age, LastUpdatedDate)
values (@.UserId, @.FirstName, @.LastName, @.Age, @.CurrentUtcDate)
END
Thats related to this section of code::
Code Snippet
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");
//Removed Comma from infront of the LastUpdatedDate=@.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;
try
{
cmd.ExecuteNonQuery();
//MessageBox.Show(cmd.Parameters.ToString());
|||This is my last attempt to help you. It appears that in your attempt to create separate stringbuilder objects for columnstr, setstr, and valuestr, you may have created a level of complexity that is apparently beyound your competence.
It is not clear if you are using a stored procedure or a query string. You have presented both.
The error has NOTHING to do with the included stored procedure, and everything to do with how you have lost sight of creating your query string.
If you would ONLY take some positive actions to find out what is in the query string, such as my suggestion to use a messagebox.show to display the query string for view, you would be helping yourself. You're wasting a lot of your time (as well as ours) by not using your tools to give you information to help yourself.
If you did so, you would see that your latest error
Must declare the scalar varible "@.Value1LastUpdatedDate".
is most likely because the variable '@.Value1' is being concatenated to the field name 'LastUpdatedDate' without a space and/or comma between.
Check out how you are handling 'valueParam' in the stringbuilder objects.
I hate to come on so 'strongly', but, come on, you have to start using your head and your tools.
|||I appreciate the help but again in my posts " I did not write that code" it was a SandBox project from the site Asp.net. I'm only trying to get it to work while " learning" something in the process.
http://www.asp.net/sandbox/samp_profiles.aspx?tabid=62
Like your comment says:: Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
People like myself don't have all the experience as others have thats why its a learning process and some like myself A: cant afford schooling and buy books when the money is aviable. So remember you wasn't born with the knowledge for programming you learned it over time, so cut me some slack
|||I've suggested repeatedly:
Comment out the ExecuteNoQuery statement, Add a MessageBox.Show statement to display the entire Query String You will then be able to 'see' the Query, and you can help yourself -instead of repeatedly asking for others to read through all this code for you.|||Another simpler way to debug this is turn on SQL Profiler on the server side and it will show you the incoming SQL statement along with the error reported (potentially pointing to the line of tsql that is in error).
No comments:
Post a Comment