Collation name mismatch - .NET SqlException

I've been doing some minor mods for my client and he's reported a
weird error. Weird, because until recently all was going swimmingly.
It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
2000 database.
I couldn't reproduce the problem at my office so I asked them to
provide me with a backup, which I duly restored to a new test
database. Lo and behold, it failed with the same error:
[SqlException: Error converting data type varchar to datetime.]
Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn)
Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack)
However, looking at the tables and stored procedures involved I could
see no differences between my development database and their test
version (table defs and SPROCs all identical).
However, I did note that looking at the properties of the two
databases:
Development DB (no problems) - Collation Name =
SQL_Latin1_General_CP1_Cl_AS
Test DB (SqlException) - Collation Name =
There was no Collation Name.
I realise that there's no way of changing the Collation Name of an
existing database, but if I were to get the client to create a new
blank database with the same Collation Name as the development DB and
then restore the test db over it, would that work, do you think?
Thanks
Edward
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
You can change the database default collation with ALTER DATABASE:
ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS;
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name =
> SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name =
> There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward
|||> [SqlException: Error converting data type varchar to datetime.]
I forgot to add that this error is not related to collation. It looks like
varchar data is somewhere being converted to a datetime and the data isn't a
valid datetime string. You can identify problem data with ISDATE:
SELECT *
FROM dbo.MyTable
WHERE ISDATE(MyColumn) = 0
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name =
> SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name =
> There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward
sqlsql

Collation name mismatch - .NET SqlException

I've been doing some minor mods for my client and he's reported a
weird error. Weird, because until recently all was going swimmingly.
It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
2000 database.
I couldn't reproduce the problem at my office so I asked them to
provide me with a backup, which I duly restored to a new test
database. Lo and behold, it failed with the same error:
[SqlException: Error converting data type varchar to datetime.]
Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack, Boolean IgnoreReturn)
Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames,
Boolean[] CopyBack)
However, looking at the tables and stored procedures involved I could
see no differences between my development database and their test
version (table defs and SPROCs all identical).
However, I did note that looking at the properties of the two
databases:
Development DB (no problems) - Collation Name = SQL_Latin1_General_CP1_Cl_AS
Test DB (SqlException) - Collation Name =
There was no Collation Name.
I realise that there's no way of changing the Collation Name of an
existing database, but if I were to get the client to create a new
blank database with the same Collation Name as the development DB and
then restore the test db over it, would that work, do you think?
Thanks
Edward> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
You can change the database default collation with ALTER DATABASE:
ALTER DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CI_AS;
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name => SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name => There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward|||> [SqlException: Error converting data type varchar to datetime.]
I forgot to add that this error is not related to collation. It looks like
varchar data is somewhere being converted to a datetime and the data isn't a
valid datetime string. You can identify problem data with ISDATE:
SELECT *
FROM dbo.MyTable
WHERE ISDATE(MyColumn) = 0
--
Hope this helps.
Dan Guzman
SQL Server MVP
<teddysnips@.hotmail.com> wrote in message
news:2ad498ef-fd23-4f18-9c07-81ce8bdd40ac@.s19g2000prg.googlegroups.com...
> I've been doing some minor mods for my client and he's reported a
> weird error. Weird, because until recently all was going swimmingly.
> It's a Visual Studio .NET 2003 ASP.NET/VB.NET application with a SQL
> 2000 database.
> I couldn't reproduce the problem at my office so I asked them to
> provide me with a backup, which I duly restored to a new test
> database. Lo and behold, it failed with the same error:
> [SqlException: Error converting data type varchar to datetime.]
> Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack, Boolean IgnoreReturn)
> Microsoft.VisualBasic.CompilerServices.LateBinding.LateCall(Object
> o, Type objType, String name, Object[] args, String[] paramnames,
> Boolean[] CopyBack)
> However, looking at the tables and stored procedures involved I could
> see no differences between my development database and their test
> version (table defs and SPROCs all identical).
> However, I did note that looking at the properties of the two
> databases:
> Development DB (no problems) - Collation Name => SQL_Latin1_General_CP1_Cl_AS
> Test DB (SqlException) - Collation Name => There was no Collation Name.
> I realise that there's no way of changing the Collation Name of an
> existing database, but if I were to get the client to create a new
> blank database with the same Collation Name as the development DB and
> then restore the test db over it, would that work, do you think?
> Thanks
> Edward

CodeGen.net

hi, i cant use codeGen.net with SQL SERVER 6.5 this cause SQl SERVER 6.5 dose not support sqlclient is there is way to connect SQL SERVER 6.5. thanxYou can still use OleDb (System.Data.OleDb) to connect SQL 6.5

Code works without transaction but not with transaction?

C#
VS 2003
.Net Framework V1.1 SP1
SQL Server 2000 SP3
Enterprise Library June 2005
I'm working with some code {not of my creation} that performs the
following sequence of actions:
- Open SqlConnection using SQL authentication with UID & PWD in connect
string
- BeginTransaction
- Call stored procedure that updates 1 table {OK}
- Call stored procedure that updates 1 table {FAIL}
- Call stored procedure that updates 1 table
- Call stored procedure that updates 1 table
- Commit
- Dispose
All calls to the stored procedures are coded like this:
SqlHelper.ExecuteNonQuery(transaction,
CommandType.StoredProcedure,
"StoredProcedure",
param, param ...)
SYMPTOMS:
-If I take away the transaction the code works Ok
-If I use the transaction the code always fails on the second stored
procedure call with a "Login failed for user xxx" exception bubbled up
from ADO.NET's ConnectionPool.GetConnection() method
-If I shuffle the order of procedure calls the second call still always
fails
-If I put a breakpoint before the second procedure call the
transaction's connection is Open and the Isolation level is the default
{ReadCommitted}
As the natives were gathering outside my cube with pitchforks and
torches I hacked out the transaction and put something that works back
online. But, what the heck is happening - any ideas?Crash,
Well, you obviously have an error with the second stored procedure. You
need to track that down.
As for why everything fails, once there is a fail vote in the
transaction, SQL Server will not let you back it out. You have to redo the
whole thing.
Hope this helps.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Crash" <sourcenexus@.san.rr.com> wrote in message
news:1138833840.836442.96230@.z14g2000cwz.googlegroups.com...
> C#
> VS 2003
> .Net Framework V1.1 SP1
> SQL Server 2000 SP3
> Enterprise Library June 2005
> I'm working with some code {not of my creation} that performs the
> following sequence of actions:
> - Open SqlConnection using SQL authentication with UID & PWD in connect
> string
> - BeginTransaction
> - Call stored procedure that updates 1 table {OK}
> - Call stored procedure that updates 1 table {FAIL}
> - Call stored procedure that updates 1 table
> - Call stored procedure that updates 1 table
> - Commit
> - Dispose
> All calls to the stored procedures are coded like this:
> SqlHelper.ExecuteNonQuery(transaction,
> CommandType.StoredProcedure,
> "StoredProcedure",
> param, param ...)
> SYMPTOMS:
> -If I take away the transaction the code works Ok
> -If I use the transaction the code always fails on the second stored
> procedure call with a "Login failed for user xxx" exception bubbled up
> from ADO.NET's ConnectionPool.GetConnection() method
> -If I shuffle the order of procedure calls the second call still always
> fails
> -If I put a breakpoint before the second procedure call the
> transaction's connection is Open and the Isolation level is the default
> {ReadCommitted}
> As the natives were gathering outside my cube with pitchforks and
> torches I hacked out the transaction and put something that works back
> online. But, what the heck is happening - any ideas?
>|||I switched the order of the procedure calls around - it doesn't matter
what order I call them in the second one always fails. If I remove the
transaction all of the procedure calls succeed...
What disturbs me is that I am in the middle of performing steps in an
active transaction on an open connection - and the exception is
bubbling up from the connection pool' Why would it try to fetch a
connection from the pool while I'm in the middle of a transaction'|||I forgot to add:
The transaction is NOT distributed - all stored procedures are
executing in the same database...|||On 1 Feb 2006 14:44:00 -0800, Crash wrote:

>C#
>VS 2003
>.Net Framework V1.1 SP1
>SQL Server 2000 SP3
>Enterprise Library June 2005
>I'm working with some code {not of my creation} that performs the
>following sequence of actions:
>- Open SqlConnection using SQL authentication with UID & PWD in connect
>string
>- BeginTransaction
>- Call stored procedure that updates 1 table {OK}
>- Call stored procedure that updates 1 table {FAIL}
>- Call stored procedure that updates 1 table
>- Call stored procedure that updates 1 table
>- Commit
>- Dispose
>All calls to the stored procedures are coded like this:
>SqlHelper.ExecuteNonQuery(transaction,
> CommandType.StoredProcedure,
> "StoredProcedure",
> param, param ...)
>SYMPTOMS:
>-If I take away the transaction the code works Ok
>-If I use the transaction the code always fails on the second stored
>procedure call with a "Login failed for user xxx" exception bubbled up
>from ADO.NET's ConnectionPool.GetConnection() method
>-If I shuffle the order of procedure calls the second call still always
>fails
>-If I put a breakpoint before the second procedure call the
>transaction's connection is Open and the Isolation level is the default
>{ReadCommitted}
>As the natives were gathering outside my cube with pitchforks and
>torches I hacked out the transaction and put something that works back
>online. But, what the heck is happening - any ideas?
Hi Crash,
Since it's always the second procedure that fails, regardless of the
order of execution, I suspect that the stored procedures somehow mess up
the transaction nesting level. Maybe they start a transaction that they
don't end, or maybe they end (commit/rollback) the open transaction that
the next procedure expects to be still open?
What happens if you execute a script like the one below from Query
Analyzer:
BEGIN TRANSACTION
EXEC Proc1 param, param, ...
EXEC Proc2 param, param, ...
EXEC Proc3 param, param, ...
EXEC Proc4 param, param, ...
COMMIT TRANSACTION
Do all procedures execute or do you get errors? If the latter, please
copy and paste the error messages.
It might also help to post the code of the stored procedures themselves.
Hugo Kornelis, SQL Server MVP|||- FYI this code is running under ASP, but it is logging into the
database as a specific user. The user that ASP logs in as is not dbo
but does have rights on all of the tables & procedures involved {I
checked}...
- None of the tables involved have any triggers on them {I checked}...
- The entire database has no indexes, no constraints, or any other of
those pesky referrential integrity nuisances {not the way I'd do it
either but I got called in to help on this one}...
- Forgot to mention it earlier but had already tried it - yes - within
query analyzer I can succesfully execute any combination of sproc calls
within a transaction just as you recommended... No errors from Query
analyzer within a transaction no matter what order of execution...
- None of the stored procedures does anything with transactions in and
of themselves - they each insert or update 1 row in a single table per
call... The stored procedures do different things but here's a look at
one of them:
CREATE PROCEDURE dbo.WT_UserInfo_Update
(
@.UserID int =Null,
@.Password varchar(64) = NULL,
@.FirstName varchar(30) = NULL,
@.LastName varchar(30) = NULL,
@.JobTitle varchar(100) = NULL,
@.PhoneNumber varchar(50) = NULL,
@.EmailAddress varchar(100) = NULL,
@.AdminUserID int = NULL
)
AS
BEGIN
--Update Existing User
Update WT_User
set Password=@.Password,
PasswordChangeDate=getdate(),
FirstName=@.FirstName,
LastName=@.LastName,
JobTitle=@.JobTitle,
PhoneNumber=@.PhoneNumber,
EmailAddress=@.EmailAddress,
ModifiedBy= @.AdminUserID
Where UserID=@.UserID
IF @.@.ERROR <> 0 GOTO ExitProcError
return @.UserID
END
ExitProcError:
Return(-1)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXECUTE ON [dbo].[WT_UserInfo_Update] TO [<<same user as used
in connect string>>]
GO
The other procedures do not do anything fancier; 1 deletes from a
{different} table where @.UserID=xxx, and a third procedure is called x
times to insert 1 row into the other table per call.
I'm thinking this is more of an ADO issue of some sort, but it is
bizarre. Flawlessly reproducable, but very bizarre...|||On 3 Feb 2006 11:50:06 -0800, Crash wrote:

>- FYI this code is running under ASP, but it is logging into the
>database as a specific user. The user that ASP logs in as is not dbo
>but does have rights on all of the tables & procedures involved {I
>checked}...
>- None of the tables involved have any triggers on them {I checked}...
>- The entire database has no indexes, no constraints, or any other of
>those pesky referrential integrity nuisances {not the way I'd do it
>either but I got called in to help on this one}...
>- Forgot to mention it earlier but had already tried it - yes - within
>query analyzer I can succesfully execute any combination of sproc calls
>within a transaction just as you recommended... No errors from Query
>analyzer within a transaction no matter what order of execution...
>- None of the stored procedures does anything with transactions in and
>of themselves - they each insert or update 1 row in a single table per
>call... The stored procedures do different things but here's a look at
>one of them:
>
(snip)
>The other procedures do not do anything fancier; 1 deletes from a
>{different} table where @.UserID=xxx, and a third procedure is called x
>times to insert 1 row into the other table per call.
>I'm thinking this is more of an ADO issue of some sort, but it is
>bizarre. Flawlessly reproducable, but very bizarre...
Hi Crash,
Yeah, I have to agree. Since a logically identical series of statements
from Query Analyzer executes flawlessly, it's probably not a SQL Server
issue.
I have no ADO experience, so I can't help you further. Maybe you should
take this to an ADO group. (I think they'll want to see more of the
front--end code than you posted here, though).
Hugo Kornelis, SQL Server MVP

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

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.
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
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.
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!|||You are right. I did overlook that. Silly me. Anyway, it was a pleasure.

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

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

Clustered Sql Server(Again)

I should take offline backup at midnight after stopping our sql server 2000.
But it's prohibited to stop by writing a task like "net stop MSSQLSERVER"
(written in MSDN site), also the passive server brings sql server online and
cancels offline backup.
How can I stop my clustered Sql Server and take backup at midnights? How can
I schedule such a job?
(Before clustering we used to write a batch file ("net stop sqlserver") and
in the sceduled tasks we run them whenever needed)
What should I do from now on?
Thanks,
Abdulkadir
First off, offline backups are unnecessary for a SQL Server. You never have
to take a SQL Server offline to back it up. You use the built in backup
features within the product.
As far as interacting with the cluster, you can't do it directly against the
service since that is seen as a failure. You have to stop the SQL Server
through the cluster. I don't know the command, but you issue the equivalent
thing to the cluster and I'm fairly certain the command string can be found
in online help.
But, again, you don't need to shut down a SQL Server to backup the data.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||Every night we're taking an online backup, somedays incremental - others
full.
We have another system that backups our SqlServer (Hp Omniback) and this
tool restores DB only to servers which have same collation, same drive name,
same directory,bla bla bla...
We can't restore online backup to any other server we have. We may just want
to restore the files and attach it to any server. At the same time we're
trying that.
Thanks for your answer, I will try to find the command string.
I just wanted to learn how this process is done in other firms...
Thanks again,
"Michael Hotek" <mhotek@.nomail.com>, iletide unu yazd
news:eTD4FLfLEHA.1312@.TK2MSFTNGP12.phx.gbl...
> First off, offline backups are unnecessary for a SQL Server. You never
have
> to take a SQL Server offline to back it up. You use the built in backup
> features within the product.
> As far as interacting with the cluster, you can't do it directly against
the
> service since that is seen as a failure. You have to stop the SQL Server
> through the cluster. I don't know the command, but you issue the
equivalent
> thing to the cluster and I'm fairly certain the command string can be
found
> in online help.
> But, again, you don't need to shut down a SQL Server to backup the data.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>
|||You can use the built-in tools to back up to a local or remote file share.
Then your backup tool can archive those files for disaster recovery. That
way, you get the best of both worlds.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"aculfa" <aaa@.aa.com> wrote in message
news:uVccjcfLEHA.2624@.TK2MSFTNGP09.phx.gbl...
> Every night we're taking an online backup, somedays incremental - others
> full.
> We have another system that backups our SqlServer (Hp Omniback) and this
> tool restores DB only to servers which have same collation, same drive
name,
> same directory,bla bla bla...
> We can't restore online backup to any other server we have. We may just
want[vbcol=seagreen]
> to restore the files and attach it to any server. At the same time we're
> trying that.
> Thanks for your answer, I will try to find the command string.
> I just wanted to learn how this process is done in other firms...
> Thanks again,
> "Michael Hotek" <mhotek@.nomail.com>, iletide unu yazd
> news:eTD4FLfLEHA.1312@.TK2MSFTNGP12.phx.gbl...
> have
> the
Server
> equivalent
> found
>
|||Here is the order in which you would perform your tasks(, from cluster
prespective):
1. Run command "Cluster group <Group Hosting SQL> /offline".
2. Then you are free to perform your SQL related tasks. After you are done
with your tasks make sure that SQL is in the same state as it was after
doing Step1, above.
3. RunCommand "Cluster group <Group Hosting SQL> /online".
Hope this helps.
Sandeep Sutari
Microsoft Corp.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Michael Hotek" <mhotek@.nomail.com> wrote in message
news:eTD4FLfLEHA.1312@.TK2MSFTNGP12.phx.gbl...
> First off, offline backups are unnecessary for a SQL Server. You never
have
> to take a SQL Server offline to back it up. You use the built in backup
> features within the product.
> As far as interacting with the cluster, you can't do it directly against
the
> service since that is seen as a failure. You have to stop the SQL Server
> through the cluster. I don't know the command, but you issue the
equivalent
> thing to the cluster and I'm fairly certain the command string can be
found
> in online help.
> But, again, you don't need to shut down a SQL Server to backup the data.
> --
> Mike
> Principal Mentor
> Solid Quality Learning
> "More than just Training"
> SQL Server MVP
> http://www.solidqualitylearning.com
> http://www.mssqlserver.com
>