Clustered DB Server: asp

Showing posts with label asp. Show all posts

Sunday, March 11, 2012

Code Samples for external linking to Reports

Hello All,
Are there any code samples which would let me post a URL
link from a classic ASP app to specified Reports on SRS
via URL querystring parameters?
Any thoughts/insight would be greatly appreciated...
TIA,
--Karthiki found ample examples of this in the samples that was an option on installtion
"Karthik" wrote:
> Hello All,
> Are there any code samples which would let me post a URL
> link from a classic ASP app to specified Reports on SRS
> via URL querystring parameters?
> Any thoughts/insight would be greatly appreciated...
> TIA,
> --Karthik
>

Code Samples

I am hoping that someone can direct me to a VB.Net code sample (preferably
ASP.Net) which is a good example of programatically setting ADO.Net
connection properties, calling a stored procedure, supplying user defined
parameters for the stored procedure and outputing the report to a PDF format.
I really need a sample that does all this programatically.
Any examples appreciated.
David Johnsonif you look through msdn, i'm sure you'll find enough examples.
almost for every method / function they have a code snippet
"David" wrote:
> I am hoping that someone can direct me to a VB.Net code sample (preferably
> ASP.Net) which is a good example of programatically setting ADO.Net
> connection properties, calling a stored procedure, supplying user defined
> parameters for the stored procedure and outputing the report to a PDF format.
> I really need a sample that does all this programatically.
> Any examples appreciated.
> David Johnson

Thursday, March 8, 2012

Code Page

I read text files in ASP on server side, then try to enter data into a
database in SQL Server. The files are in ibm852 (1250) coding - at least
that's the code page with which they are shown properly when putting the
lines read on the output.
However, when I enter them into the SQL Server (currently simply by setting
a string to an "INSERT INTO T1(6, 'hello')"-like statement and then execute
it through the connection such as oConn.Execute sSQL), the special
characters (Hungarian) are all changed to meaningless characters, such as
'hell:' for 'helló' etc.
The texty columns are of type varchar(n). I also tried nvarchar, but nothing
has changed.
So, how can I set the appropriate code page in SQL Server or transform the
strings so that the special characters don't get messed up when entered?Agoston,
Try changing the column type to nvarchar(n) and executing this query:
INSERT INTO T1(6, N'helló')
Perhaps you simply forgot to type the N required to signify a Unicode
string.
Steve Kass
Drew University
Agoston Bejo wrote:
>I read text files in ASP on server side, then try to enter data into a
>database in SQL Server. The files are in ibm852 (1250) coding - at least
>that's the code page with which they are shown properly when putting the
>lines read on the output.
>However, when I enter them into the SQL Server (currently simply by setting
>a string to an "INSERT INTO T1(6, 'hello')"-like statement and then execute
>it through the connection such as oConn.Execute sSQL), the special
>characters (Hungarian) are all changed to meaningless characters, such as
>'hell:' for 'helló' etc.
>The texty columns are of type varchar(n). I also tried nvarchar, but nothing
>has changed.
>So, how can I set the appropriate code page in SQL Server or transform the
>strings so that the special characters don't get messed up when entered?
>
>|||"Steve Kass" <skass@.drew.edu> wrote in message
news:%23xM75ETpEHA.1712@.tk2msftngp13.phx.gbl...
> Agoston,
> Try changing the column type to nvarchar(n) and executing this query:
> INSERT INTO T1(6, N'helló')
> Perhaps you simply forgot to type the N required to signify a Unicode
> string.
It doesn't change a thing. The same messy characters are in the db. Any
other ideas?
> Steve Kass
> Drew University
> Agoston Bejo wrote:
> >I read text files in ASP on server side, then try to enter data into a
> >database in SQL Server. The files are in ibm852 (1250) coding - at least
> >that's the code page with which they are shown properly when putting the
> >lines read on the output.
> >
> >However, when I enter them into the SQL Server (currently simply by
setting
> >a string to an "INSERT INTO T1(6, 'hello')"-like statement and then
execute
> >it through the connection such as oConn.Execute sSQL), the special
> >characters (Hungarian) are all changed to meaningless characters, such as
> >'hell:' for 'helló' etc.
> >The texty columns are of type varchar(n). I also tried nvarchar, but
nothing
> >has changed.
> >
> >So, how can I set the appropriate code page in SQL Server or transform
the
> >strings so that the special characters don't get messed up when entered?
> >
> >
> >
> >|||I don't do ASP programming, but is the string "INSERT INTO T1 ..." a
Unicode string? If not, it will not preserve the accented characters.
There ought to be some way to specify that it be Unicode, similar to the
way you do in SQL Server with the N prefix. If that fails to produce
the right result, I'm not sure what could be happening, since Unicode
strings shouldn't be affected by code page settings, but I'd probably
try specifying the 1250 code page somewhere on the ASP page - maybe the
ASP programmers have a better idea.
SK
Agoston Bejo wrote:
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:%23xM75ETpEHA.1712@.tk2msftngp13.phx.gbl...
>
>>Agoston,
>> Try changing the column type to nvarchar(n) and executing this query:
>>INSERT INTO T1(6, N'helló')
>>Perhaps you simply forgot to type the N required to signify a Unicode
>>string.
>>
>
>It doesn't change a thing. The same messy characters are in the db. Any
>other ideas?
>
>
>>Steve Kass
>>Drew University
>>Agoston Bejo wrote:
>>
>>I read text files in ASP on server side, then try to enter data into a
>>database in SQL Server. The files are in ibm852 (1250) coding - at least
>>that's the code page with which they are shown properly when putting the
>>lines read on the output.
>>However, when I enter them into the SQL Server (currently simply by
>>
>setting
>
>>a string to an "INSERT INTO T1(6, 'hello')"-like statement and then
>>
>execute
>
>>it through the connection such as oConn.Execute sSQL), the special
>>characters (Hungarian) are all changed to meaningless characters, such as
>>'hell:' for 'helló' etc.
>>The texty columns are of type varchar(n). I also tried nvarchar, but
>>
>nothing
>
>>has changed.
>>So, how can I set the appropriate code page in SQL Server or transform
>>
>the
>
>>strings so that the special characters don't get messed up when entered?
>>
>>
>>
>
>|||Steve Kass wrote:
> I don't do ASP programming, but is the string "INSERT INTO T1 ..." a
> Unicode string? If not, it will not preserve the accented characters.
> There ought to be some way to specify that it be Unicode, similar to
> the way you do in SQL Server with the N prefix. If that fails to
> produce the right result, I'm not sure what could be happening, since
> Unicode
> strings shouldn't be affected by code page settings, but I'd probably
> try specifying the 1250 code page somewhere on the ASP page - maybe
> the ASP programmers have a better idea.
>
Good thought, but vbscript is unicode by default.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Code Page

Code inside! --> How to return the @@identity parameter without using stored procedures

Hi.
here is my code with my problem described in the syntax.
I am using asp.net 1.1 and VB.NET
Thanks in advance for your help.
I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row.

PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName); SELECT @.NewID = @.@.identity"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

'Here is my problem.
'What do I have to do in order to add the parameter @.NewID and
'how do I read and return the value of @.NewID within that function howToReturnID
'any help is greatly appreciated!
'I cannot use SPs in this application - have to do it this way! :-(

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
dbCommand.ExecuteNonQuery()

'here i want to return the new ID!
Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

Why don't you put your SQL statement something like this;
Insert Into table (col1, col2) Values (1, 2); Select @.@.IDENTITY
And from there, to retrieve the @.@.IDENTITY, you will execute a scalar return. For example:
Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
|||

Hi,

thank you very much for your help. Smile [:)]
I tried your suggestion but got always two rows inserted.

Obviously the command object executed the insert statement two times?

first here: db.command.ExecuteNonQuery()
and here:Dim identity As Integer = Convert.ToInt32(command.ExecuteScalar())
this is what I did:
Is this correct ? -at least it works ;-) - but is it the "right way" to do it?
PublicFunction howToReturnID(ByVal aCompanyAsString,ByVal aNameAsString)AsInteger

'that is the variable for the new id.
Dim intNewIDAsInteger

Dim strSQLAsString ="INSERT INTO tblAnfragen(aCompany, aName)" & _
"VALUES (@.aCompany, @.aName);"

'I separated the SQL query string
Dim strSQL2AsString ="Select @.@.IDENTITY;"

Dim dbConnectionAs SqlConnection =New SqlConnection(connectionString)
Dim dbCommandAs SqlCommand =New SqlCommand()
dbCommand.CommandText = strSQL

dbCommand.Parameters.Add("@.aFirma", aCompany.Trim)
dbCommand.Parameters.Add("@.aAnsprAnrede", aName.Trim)

dbCommand.Connection = dbConnection

Try
dbConnection.Open()
'execute first query
dbCommand.ExecuteNonQuery()

'execute second query - this actually returns the id of the currently inserted row.
'But is this the CORRECT WAY to do it?? Any objections?
'this solution only inserts one row not two - as it did when the SQL-query was in one string
dbCommand.CommandText = strSQL2
newID = Convert.ToInt32(dbCommand.ExecuteScalar)

Return intNewID

Catch exAs Exception

ThrowNew System.Exception("Error: " & ex.Message.ToString())

Finally

dbCommand.Dispose()
dbConnection.Close()
dbConnection.Dispose()

EndTry

EndFunction

|||Don't call the ExecuteNonQuery() method. Every Execute*() method that you call runs it as a completely new query, if you know what I mean.
Regards,
Justin|||Well, not really Embarrassed [:$]

You mean the .ExecuteScalar method of the command object does also run the insert query?
If so why is there a ExecuteNonQuery method at all?
Is the way I did it in my second source code example not recommendable?
But it works for me - or is there a good reason not to do it that way (performance issues, etc.) ?|||Well, with your second code example, you have described what is wrong with it - it inserts the same data twice into the table. So this breaks logics reason. Besides calling the Execute*() twice, I don't see anything else wrong with the second code posting Wink [;)]

.
The reason why we have the three Execute() methods (ExecuteNonQuery, ExecuteReader, and ExecuteScalar) on the commands are that each one tells the 'executer' (loosely saying it here) on what type of result to expect. With ExecuteNonQuery(), it returns how many rows were affected. With ExecuteScalar() method, you are expect a simple value type to be returned. And with the ExecuteReader(), it returns a data reader...
They all 'execute' but you have to decide on what information you need to retrieve from execution of that command. Am I making sense now?|||Hi,
yes, everything works now the way it is supposed to :)
Thank you very much for your help!
But in my second code example that i have posted it does not insert the row twice ;-)
Just take a closer look at it - I have provided the command object with a second query that only selects @.@.identity .
But I did that "workaround" because I didn't know that executeScalar also "executes" insert queries ...!
My problem is solved now! Big Smile [:D]

|||You are right. I did overlook that. Silly me Smile [:)]

. Anyway, it was a pleasure Wink [;)]

Saturday, February 25, 2012

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 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

--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]

--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)

grant SELECT,INSERT,UPDATE,DELETE on dbo.ProfileTable_1 to [WYATT-PC\Wyatt]

--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

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

grant EXECUTE on dbo.getCustomProfileData to [WYATT-PC\Wyatt]

--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

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

grant EXECUTE on dbo.setCustomProfileData to [WYATT-PC\Wyatt]

|||

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

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).

Sunday, March 11, 2012

Code Samples for external linking to Reports

Code Samples

Thursday, March 8, 2012

Code Page

Code Page

Code inside! --> How to return the @@identity parameter without using stored procedures

Saturday, February 25, 2012

cmd.ExecuteNonQuery question..help please

Incorrect syntax near ','.

Clustered DB Server

Blog Archive

About Me