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