Appending While Updating

I have a textbox and the user wants to append each data entry to the
previous data entry such that at the end of the day the db field will
have, 8:00 am UserID Breakfast 12:00 PM UserID Lunch 5:00 PM
UserID Dinner. With each time period being entered at separate
times of the day. Currently my code over writes the previous data
entry.

This is code:
The portion of the code requires IIS Windows Authentication to be set
Dim authUserName As String
Dim userNamedbInsert As String

authUserName = Request.ServerVariables("AUTH_USER")
Session("userNamedbInsert") = authUserName

If authUserName <> "&quot; Then
userNamedbInsert = Replace
(authUserName, "newDomain", "&quot;)
Else
userNamedbInsert = Replace
(authUserName, "oldDomain", "&quot;)
End If

'Declare DateTime variables, for date and parsed date,
i.e. Today

Dim bDate As DateTime
bDate = lblDate.Text

Dim newCookie As HttpCookie = New HttpCookie
("SupervisorComment";)
newCookie.Values.Add("Comments", bDate & UCase
(userNamedbInsert) & txtComment.Text)
newCookie.Expires = #12/31/2010#
Response.Cookies.Add(newCookie)

'Instantiate the command object
Using cmdCommentUpdate As New
System.Data.SqlClient.SqlCommand

'Establish connection to the database connection
Dim sqlcon As New SqlClient.SqlConnection
(ConfigurationManager.ConnectionStrings
(&quot;AttendanceConnectionString&quot;).ToString)

'Open connection
sqlcon.Open()

'Pass opened connection (see above) to the command
object
cmdCommentUpdate.Connection = sqlcon

'Using "With/End With" pass content to columns from
text objects and datatime variables (see above)
With cmdCommentUpdate
.Parameters.Add(New SqlClient.SqlParameter
(";Comments&quot;, bDate & UCase(userNamedbInsert)
& txtComment.Text))

'Establish the type of commandy object
.CommandType = CommandType.Text

'Pass the Update nonquery statement to the
commandText object previously instantiated
.CommandText = "UPDATE ATTTblAttendance"; & _
" SET Comments = Comments & _
" WHERE (UserID = UserID) AND (Today = '"
& lblDate.Text & "') "

End With

'Execute the nonquerry via the command object
cmdCommentUpdate.ExecuteNonQuery()

'Close the sql connection
sqlcon.Close()
Response.Redirect(&quot;~/Maintenance.aspx")
End Using

Thank you for your help.

Gail

Messages in this topic (1) Reply (via web post) | Start a new topic

.

Stuffing repeated entries into the same field isn't really the best use of a
relational database. You'd be better off creating a child comment table
that held each entry. I'd also break out the date and user ID into their
own columns, so you could do searches and joins. Much easier to work with
and use later on. Your current design gets you a blob of data that is useful
only for display.

However if you really want to do it, you can concatenate in your UPDATE
statement.

.CommandText = "UPDATE ATTTblAttendance"; & _
" SET Comments = COALESCE(Comments, '') +
Comments & _
" WHERE (UserID = UserID) AND (Today = '"
& lblDate.Text & "') "

On 10/24/07, gail15322 < GCG%40miamidade.gov">GCGmiamidade.gov> wrote:
&gt;
> I have a textbox and the user wants to append each data entry to the
> previous data entry such that at the end of the day the db field will
>; have, 8:00 am UserID Breakfast 12:00 PM UserID Lunch 5:00 PM
> UserID Dinner. With each time period being entered at separate
> times of the day. Currently my code over writes the previous data
>; entry.
&gt;
> This is code:
&gt; The portion of the code requires IIS Windows Authentication to be set
> Dim authUserName As String
&gt; Dim userNamedbInsert As String
&gt;
> authUserName = Request.ServerVariables("AUTH_USER&quot;)
>; Session(&quot;userNamedbInsert&quot;) = authUserName
>
> If authUserName <> "&quot; Then
>; userNamedbInsert = Replace
> (authUserName, "newDomain", "&quot;)
> Else
>; userNamedbInsert = Replace
> (authUserName, "oldDomain", "&quot;)
> End If
>
> 'Declare DateTime variables, for date and parsed date,
&gt; i.e. Today
&gt;
> Dim bDate As DateTime
> bDate = lblDate.Text
>
> Dim newCookie As HttpCookie = New HttpCookie
> ("SupervisorComment")
> newCookie.Values.Add("Comments", bDate & UCase
&gt; (userNamedbInsert) & txtComment.Text)
&gt; newCookie.Expires = #12/31/2010#
> Response.Cookies.Add(newCookie)
>
&gt;
> 'Instantiate the command object
&gt; Using cmdCommentUpdate As New
> System.Data.SqlClient.SqlCommand
>
> 'Establish connection to the database connection
> Dim sqlcon As New SqlClient.SqlConnection
> (ConfigurationManager.ConnectionStrings
> ("AttendanceConnectionString").ToString)
>
>; 'Open connection
> sqlcon.Open()
>
> 'Pass opened connection (see above) to the command
> object
&gt; cmdCommentUpdate.Connection = sqlcon
&gt;
> 'Using "With/End With" pass content to columns from
>; text objects and datatime variables (see above)
&gt; With cmdCommentUpdate
&gt; .Parameters.Add(New SqlClient.SqlParameter
> ("Comments&quot;, bDate & UCase(userNamedbInsert)
> & txtComment.Text))
>
> 'Establish the type of commandy object
&gt; .CommandType = CommandType.Text
&gt;
> 'Pass the Update nonquery statement to the
> commandText object previously instantiated
> .CommandText = "UPDATE ATTTblAttendance"; & _
> " SET Comments = Comments & _
> " WHERE (UserID = UserID) AND (Today = '"
> & lblDate.Text & "') "
&gt;
> End With
>;
> 'Execute the nonquerry via the command object
&gt; cmdCommentUpdate.ExecuteNonQuery()
>
> 'Close the sql connection
> sqlcon.Close()
>; Response.Redirect(&quot;~/Maintenance.aspx")
> End Using
&gt;
> Thank you for your help.
&gt;
> Gail
>;
>
>
>
> Yahoo! Groups Links
&gt;
>
>
>;

--
Dean Fiala
Very Practical Software, Inc
Now with Blogging...
http://www.vpsw.com/blogbaby
Microsoft MVP

[Non-text portions of this message have been removed]

Messages in this topic (0) Reply (via web post) | Start a new topic

.