Coalesce is not working and Update is no updating

My code worked a few weeks ago and has since stop working, reasons are totally not clear to me as to what happended.

However, I need to get this thing up and running. It will not longer Coalesce data entry. Iran the debugger and the correct values are in the specified objects as if it is the first time I run the page for a person it will input data, but not of subsequent data entry attempts.

My code: ( I trully appreciate your help)

Ayo

'Using "With/End With" pass content to columns from text objects and datatime variables (see above)

With cmdCommentUpdate

.Parameters.Add(New SqlClient.SqlParameter("@.UserID", ddlEmployeeSuperCmt.SelectedValue))

.Parameters.Add(New SqlClient.SqlParameter("@.Today", bDate))

.Parameters.Add(New SqlClient.SqlParameter("@.Comments", dtToday &" " & UCase(userNamedbInsert) &" " & txtComment.Text &" " & vbCrLf))

.Parameters.Add(New SqlClient.SqlParameter("@.CommenterLogon", UCase(userNamedbInsert)))

.Parameters.Add(New SqlClient.SqlParameter("@.CommentDate", dtNow))

'Establish the type of commandy object

.CommandType = CommandType.Text

'Pass the Update nonquery statement to the commandText object previously instantiated

.CommandText ="UPDATE ATTTble" & _

" SET Comments = COALESCE(Comments, '') + @.Comments, CommenterLogon = @.CommenterLogon, CommentDate = @.CommentDate" & _

" WHERE (UserID = @.UserID) AND (Today = '" & lblDate.Text &"') "

EndWith

Looking at your code if there is an existing comment field for the record the comment field will never get updated. You might want to try,

set Comments =coalesce(@.comments, comments)

This will first see if the value passed in is null, if so it just keeps the existing value otherwise it updates.

Let me know if this helps, or if i am not understanding your question.

|||

set Comments =coalesce(@.comments, comments)

The code continues to over write what is in the database instead of appending the comments.

Ayo

|||

Possibly a date conversion problem:

With cmdCommentUpdate

.Parameters.Add(New SqlClient.SqlParameter("@.UserID", ddlEmployeeSuperCmt.SelectedValue))

.Parameters.Add(New SqlClient.SqlParameter("@.Today", bDate))

.Parameters.Add(New SqlClient.SqlParameter("@.Comments", dtToday &" " & UCase(userNamedbInsert) &" " & txtComment.Text &" " & vbCrLf))

.Parameters.Add(New SqlClient.SqlParameter("@.CommenterLogon", UCase(userNamedbInsert)))

.Parameters.Add(New SqlClient.SqlParameter("@.CommentDate", dtNow))

.Parameters.Add("@.Today",SqlDbType.DateTime).Value=lblToday.Text

'Establish the type of commandy object

.CommandType = CommandType.Text

'Pass the Update nonquery statement to the commandText object previously instantiated

.CommandText ="UPDATE ATTTble" & _

" SET Comments = COALESCE(Comments, '') + @.Comments, CommenterLogon = @.CommenterLogon, CommentDate = @.CommentDate" & _

" WHERE (UserID = @.UserID) AND (Today = @.Today) "

EndWith

|||

If that does not work, then you can try (Although, this should have absolutely no effect):

SET Comments = ISNULL(Comments,'') + @.Comments

|||

For some strange reason, unknown to me, the oroginal code started working.

Thanks for all your suggestions.

Ayomide

CmdExec and Environment

Unfortunately several of my sql users need tot run cmdexe jobs
(dts-packages). To keep them from totally destroying my server I
configured a proxy account with not to much rights on the files system.
For each virtual server I created a separate temp dir for the proxy user.
when a cmdexec job is run the %temp% %tmp% point to the tempdir which
belong to the cluster service account. Now I noticed a Environment
REG_MULTI_SZ key for the sql and sqlagent services (\hkey local
machine\...\/services\mssql$instance). This key contains a full set of
environment vars, but is not wel documented (no hits on google).
Is this, a standard reg key for services, or something special for sql
and sqlagent or something special for a clustered sql and sqlagent
service? I want to temper with the %temp% and %tmp%.
Hans
Hans de Bruin wrote:
> Unfortunately several of my sql users need tot run cmdexe jobs
> (dts-packages). To keep them from totally destroying my server I
> configured a proxy account with not to much rights on the files system.
> For each virtual server I created a separate temp dir for the proxy user.
> when a cmdexec job is run the %temp% %tmp% point to the tempdir which
> belong to the cluster service account. Now I noticed a Environment
> REG_MULTI_SZ key for the sql and sqlagent services (\hkey local
> machine\...\/services\mssql$instance). This key contains a full set of
> environment vars, but is not wel documented (no hits on google).
> Is this, a standard reg key for services, or something special for sql
> and sqlagent or something special for a clustered sql and sqlagent
> service? I want to temper with the %temp% and %tmp%.
>
This is something special for clustering. Just before the sqlserver or
sqlserver agent service starts ResrcMon writes the envrionment to this
key. Any manual made changes are overwriten.
When DTSRun can't write to the \documents and
setting\clusterserviceaccount\local\temp it tries to write in
c:\winows\temp.
Hans
|||When using the Proxy Account both CmdExec and xp_cmdshell call the Win32 API
LogonUser
http://msdn.microsoft.com/library/de.../logonuser.asp
As dwLogonType it uses LOGON32_LOGON_BATCH
As dwLogonProvider is uses LOGON32_PROVIDER_DEFAULT
After which they call the Win32 API CreateProcessAsUser
http://msdn.microsoft.com/library/de...cessasuser.asp
However CreateProcessAsUser does not load the specified user's profile into
the HKEY_USERS registry key. Therefore, to access the information in the
HKEY_CURRENT_USER registry key and if the lpEnvironment parameter is NULL,
the new process inherits the environment of the calling process.
CreateProcessAsUser does not automatically modify the environment block to
include environment variables specific to the user represented by hToken.
For example, the USERNAME and USERDOMAIN variables are inherited from the
calling process if lpEnvironment is NULL. It is your responsibility to
prepare the environment block for the new process and specify it in
lpEnvironment.
So what you get is the enviroment of the service account.
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Hans de Bruin" <invalid@.invalid> wrote in message
news:1120486047.358052@.aquila.amc.uva.nl...
> Hans de Bruin wrote:
> This is something special for clustering. Just before the sqlserver or
> sqlserver agent service starts ResrcMon writes the envrionment to this
> key. Any manual made changes are overwriten.
> When DTSRun can't write to the \documents and
> setting\clusterserviceaccount\local\temp it tries to write in
> c:\winows\temp.
> --
> Hans