Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

where and how to run update query

Posted on 2009-02-17
13
Medium Priority
?
859 Views
Last Modified: 2013-11-26
Hi,

Im using VWD 2008 (VB) / sql2008 express.

I have a formview that allows me to add add a small customer activity record each time we talk to our customers.

fields are: Client_Id, Activity, Start_Date, End_Date

Now my question is this - when I create a new activity record I would like to 'close' the previous record by automatically entering an End_Date = Now() on the previous record for this customer.

I guess the sql syntax would be as follows -

UpdateCommand="UPDATE [Client_Activity] SET  [end_date] = now() WHERE [client_id] = @client_id AND [end_date] IS NULL"

But where would I put this? In a function? and where would I run it? Because it needs to run just before the new record gets inserted. I can supply the code if it helps but this form really is as simple as it sounds.

Thanks folks,

R_C

0
Comment
Question by:Real_coffee
  • 6
  • 5
  • 2
13 Comments
 
LVL 5

Expert Comment

by:synx
ID: 23662576
When you create a new activity record, I assume that you are pressing a button?  You could add the the code to that button's click event.  So before it creates a new record, it runs the update statement for the old record.

Do give more detail, I think I'd need to see your code.
0
 
LVL 2

Accepted Solution

by:
yatin_81 earned 375 total points
ID: 23662635
You can go for SQL TRIGGER on insert event and write appropriate code in trigger. This trigger will fire every time a new record is inserted. So there will no need of writing code in your front end application.
0
 
LVL 5

Assisted Solution

by:synx
synx earned 375 total points
ID: 23664972
That's true about a SQL trigger, but personally I wouldn't go that route.  Doing it with code will be more maintainable since you (or somebody else in the future) can look at the code and know everything that's happening against the database.  If you use a trigger, which is stored in SQL, you won't get the whole picture just by looking in the code since there will be extra processing happening directly in SQL that isn't readily apparent by looking at the source code.

But that's just a personal preference.

By the way, in my last post at the end, I meant to say "TO give more detail, I'd need to see your code."
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Real_coffee
ID: 23667953
I liked the -  "Do give more detail". It was kind of classy.

Code attached. Its largely generated.

A gridview to let me look at all the records in the table.
A formview that lets me insert / edit / delete.

You can see in the INSERT that Im populating the start_date with Now().
When the INSERT link is hit I want to run an update query to replace end_date with now() for all records with a matching client_id that have a NULL end_date.

Yatin_81 - I wouldnt know where to start with a trigger. So if you have a link to a tutorial it would be good for my reference.
<%@ Page Language="VB" %>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 
<script runat="server">
    'Im guessing I put an update function in here
</script>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Customer Activity Example</title>
</head>
<body style="font-family: Tahoma">
    <form id="form1" runat="server">
    <div>
    
        The Formview where I can add/edit/delete records<br />
    
        <asp:FormView ID="FormView1" runat="server" DataKeyNames="client_id" 
            DataSourceID="SqlCase_Activity">
            <EditItemTemplate>
                record_id:
                <asp:Label ID="record_idLabel1" runat="server" 
                    Text='<%# Eval("record_id") %>' />
                <br />
                client_id:
                <asp:Label ID="client_idLabel1" runat="server" 
                    Text='<%# Eval("client_id") %>' />
                <br />
                activity_code:
                <asp:TextBox ID="activity_codeTextBox" runat="server" 
                    Text='<%# Bind("activity_code") %>' />
                <br />
                Activity:
                <asp:TextBox ID="ActivityTextBox" runat="server" 
                    Text='<%# Bind("Activity") %>' />
                <br />
                case_worker:
                <asp:TextBox ID="case_workerTextBox" runat="server" 
                    Text='<%# Bind("case_worker") %>' />
                <br />
                start_date:
                <asp:TextBox ID="start_dateTextBox" runat="server" 
                    Text='<%# Bind("start_date") %>' />
                <br />
                <br />
                <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" 
                    CommandName="Update" Text="Update" />
&nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" 
                    CommandName="Cancel" Text="Cancel" />
            </EditItemTemplate>
            <InsertItemTemplate>
                client_id:
                <asp:TextBox ID="client_idTextBox" runat="server" 
                    Text='<%# Bind("client_id") %>' />
                <br />
                activity_code:
                <asp:TextBox ID="activity_codeTextBox" runat="server" 
                    Text='<%# Bind("activity_code") %>' />
                <br />
                Activity:
                <asp:TextBox ID="ActivityTextBox" runat="server" 
                    Text='<%# Bind("Activity") %>' />
                <br />
                case_worker:
                <asp:TextBox ID="case_workerTextBox" runat="server" 
                    Text='<%# Bind("case_worker") %>' />
                <br />
                start_date:
                <asp:TextBox ID="start_dateTextBox" runat="server" 
                Text='<%# now() %>'/>
                <br />
                <br />
                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" 
                    CommandName="Insert" Text="Insert" />
&nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" 
                    CommandName="Cancel" Text="Cancel" />
            </InsertItemTemplate>
            <ItemTemplate>
                record_id:
                <asp:Label ID="record_idLabel" runat="server" Text='<%# Eval("record_id") %>' />
                <br />
                client_id:
                <asp:Label ID="client_idLabel" runat="server" Text='<%# Eval("client_id") %>' />
                <br />
                activity_code:
                <asp:Label ID="activity_codeLabel" runat="server" 
                    Text='<%# Bind("activity_code") %>' />
                <br />
                Activity:
                <asp:Label ID="ActivityLabel" runat="server" Text='<%# Bind("Activity") %>' />
                <br />
                case_worker:
                <asp:Label ID="case_workerLabel" runat="server" 
                    Text='<%# Bind("case_worker") %>' />
                <br />
                start_date:
                <asp:Label ID="start_dateLabel" runat="server" 
                    Text='<%# Bind("start_date") %>' />
                <br />
                end_date:
                <asp:Label ID="end_dateLabel" runat="server" Text='<%# Bind("end_date") %>' />
                <br />
                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" 
                    CommandName="Edit" Text="Edit" />
                &nbsp;<asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" 
                    CommandName="Delete" Text="Delete" />
                &nbsp;<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" 
                    CommandName="New" Text="New" />
            </ItemTemplate>
        </asp:FormView>
        <br />
        <asp:SqlDataSource ID="SqlCase_Activity" runat="server" 
            ConflictDetection="CompareAllValues" 
            ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
            DeleteCommand="DELETE FROM [CaseActivity] WHERE [client_id] = @original_client_id AND [record_id] = @original_record_id AND (([activity_code] = @original_activity_code) OR ([activity_code] IS NULL AND @original_activity_code IS NULL)) AND (([Activity] = @original_Activity) OR ([Activity] IS NULL AND @original_Activity IS NULL)) AND (([case_worker] = @original_case_worker) OR ([case_worker] IS NULL AND @original_case_worker IS NULL)) AND (([start_date] = @original_start_date) OR ([start_date] IS NULL AND @original_start_date IS NULL)) AND (([end_date] = @original_end_date) OR ([end_date] IS NULL AND @original_end_date IS NULL))" 
            InsertCommand="INSERT INTO [CaseActivity] ([client_id], [activity_code], [Activity], [case_worker], [start_date]) VALUES (@client_id, @activity_code, @Activity, @case_worker, @start_date)" 
            OldValuesParameterFormatString="original_{0}" 
            SelectCommand="SELECT * FROM [CaseActivity]" 
            UpdateCommand="UPDATE [CaseActivity] SET [activity_code] = @activity_code, [Activity] = @Activity, [case_worker] = @case_worker, [start_date] = @start_date WHERE [client_id] = @original_client_id AND (([activity_code] = @original_activity_code) OR ([activity_code] IS NULL AND @original_activity_code IS NULL)) AND (([Activity] = @original_Activity) OR ([Activity] IS NULL AND @original_Activity IS NULL)) AND (([case_worker] = @original_case_worker) OR ([case_worker] IS NULL AND @original_case_worker IS NULL)) AND (([start_date] = @original_start_date) OR ([start_date] IS NULL AND @original_start_date IS NULL)) AND (([end_date] = @original_end_date) OR ([end_date] IS NULL AND @original_end_date IS NULL))">
            <DeleteParameters>
                <asp:Parameter Name="original_client_id" Type="String" />
                <asp:Parameter Name="original_record_id" Type="Int32" />
                <asp:Parameter Name="original_activity_code" Type="String" />
                <asp:Parameter Name="original_Activity" Type="String" />
                <asp:Parameter Name="original_case_worker" Type="String" />
                <asp:Parameter Name="original_start_date" Type="DateTime" />
                <asp:Parameter Name="original_end_date" Type="DateTime" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="record_id" Type="Int32" />
                <asp:Parameter Name="activity_code" Type="String" />
                <asp:Parameter Name="Activity" Type="String" />
                <asp:Parameter Name="case_worker" Type="String" />
                <asp:Parameter Name="start_date" Type="DateTime" />
                <asp:Parameter Name="end_date" Type="DateTime" />
                <asp:Parameter Name="original_client_id" Type="String" />
                <asp:Parameter Name="original_record_id" Type="Int32" />
                <asp:Parameter Name="original_activity_code" Type="String" />
                <asp:Parameter Name="original_Activity" Type="String" />
                <asp:Parameter Name="original_case_worker" Type="String" />
                <asp:Parameter Name="original_start_date" Type="DateTime" />
                <asp:Parameter Name="original_end_date" Type="DateTime" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="client_id" Type="String" />
                <asp:Parameter Name="activity_code" Type="String" />
                <asp:Parameter Name="Activity" Type="String" />
                <asp:Parameter Name="case_worker" Type="String" />
                <asp:Parameter Name="start_date" Type="DateTime" />
              <asp:Parameter Name="end_date" Type="DateTime" />
            </InsertParameters>
        </asp:SqlDataSource>
    
        <br />
        <br />
        The Grid lets me see whats going on with all records</div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="client_id" DataSourceID="SqlCase_Activity">
        <Columns>
            <asp:BoundField DataField="record_id" HeaderText="record_id" 
                InsertVisible="False" ReadOnly="True" SortExpression="record_id" />
            <asp:BoundField DataField="client_id" HeaderText="client_id" ReadOnly="True" 
                SortExpression="client_id" />
            <asp:BoundField DataField="activity_code" HeaderText="activity_code" 
                SortExpression="activity_code" />
            <asp:BoundField DataField="Activity" HeaderText="Activity" 
                SortExpression="Activity" />
            <asp:BoundField DataField="case_worker" HeaderText="case_worker" 
                SortExpression="case_worker" />
            <asp:BoundField DataField="start_date" HeaderText="start_date" 
                SortExpression="start_date" />
            <asp:BoundField DataField="end_date" HeaderText="end_date" 
                SortExpression="end_date" />
        </Columns>
    </asp:GridView>
    </form>
</body>
</html>

Open in new window

0
 

Author Comment

by:Real_coffee
ID: 23684947
Guys?

I can change the Update Link in the formview to a button synx. Within its onclick I could run the code, but can you give me an example of how that code would look?
0
 
LVL 5

Expert Comment

by:synx
ID: 23685260
Sorry - I think there has been something wrong with the site.  I've been trying a few times to post an update.  Here's what I was trying to post the other day:

===================================================================================
Can you create another UpdateCommand - like UpdateActivity that contains the update statement for Client_Activity, and then call it from your InsertButton?  So.. pressing the InsertButton would call two commands, first UpdateActivity (or whatever you name it), then the Insert command.

Apologies - I have never worked with VWD and am used to just writing my own VB from scratch!  But essentially you are wanting to do the same thing you'd do if you were writing from scratch - in the code that is fired as a result of pushing the Insert button, you want to first run the Update statement for Client_Activity, then the Insert statement for CaseActivity.
===================================================================================

Running the code in the onclick like you mentioned is what I would do.  Unfortunately, I haven't used VWD, so I'm not sure what the exact syntax would look like to execute a SQL statement.  Sorry.  Do you have access to any other applications that have a line of code that executes a SQL statement in any event (onclick or other)?  Perhaps you can steal the syntax.  Or check the manual.  Or hope that somebody that knows more than I do about VWD sees this post!

There should be a set of methods available for a SQL Data Source, in your case - "SqlCase_Activity".  Something like SqlCase_Activity.ExecuteSQL("your sql here").  But again, I'm not sure what exact methods are available in VWD.

Do keep us posted!  If I find anything in Google, I'll post it up here.
0
 

Author Comment

by:Real_coffee
ID: 23685656
Ok, thanks for giving it a shot anyway.

I could do this attachec code in the button but im convinced that

a) the insert would run first and
b) its an old fashioned way of doing it.





<%
    'Lets connect to the database
    Dim Conn
    Dim Connstring
    Dim sql_insert
    
    Connstring = "myconnectionstring
    Conn.Open(ConnString)
 
' check it opens ok
    If Err.Number <> 0 Then
        'Response.Write(Err.Description & "<br>" & vbCrLf)
    Else
        'Response.Write("Connected to DB<br><br>" & vbCrLf)
    End If
 
    sql_update = "This would be my update code"
    
    ' Executing the sql code
    Conn.Execute(sql_update)
 
    ' Done. Now Close the connection
    Conn.Close()
    Conn = Nothing
    
   
 %>

Open in new window

0
 

Author Comment

by:Real_coffee
ID: 23685934
yatin_81 - any chance you could talk me through creating that trigger?
0
 
LVL 5

Expert Comment

by:synx
ID: 23692209
Just a thought - you could use that code to do the update AND the insert.  You have the code written to do the update... couldn't you add a few lines of code after "Conn.Execute(sql_update)" to then do the insert?  That's the manual way of doing it.  I didn't realize that VWD lets you also add manual code.  If that works, in my opinion that would be preferable to using a SQL trigger (again, for maintainability/readability reasons - not because there's anything wrong with triggers).
0
 

Author Comment

by:Real_coffee
ID: 23696067
Done it.

Here's the answer (or one answer anyway) in case you are reading this with the same problem;

(Thanks to synx and yatin_81 and nvanhaaster who showed me the way to get it done.)

I used a stored procedure in the sql database -

ALTER PROCEDURE updateClientActivity
    @client_id varchar(50),
    @activity varchar(50),
    @start_date datetime,
    @end_date datetime
AS
BEGIN
    UPDATE Client_Activity SET end_date=@end_date WHERE client_id=@client_id and end_date IS NULL
    INSERT INTO Client_Activity (client_id,Activity,Start_Date,End_Date) VALUES (@client_id,@activity,@start_date,NULL)
END

****

I then called it from my test asp page along with the 4 parameters

<%@ Page Language="VB" %>

<%@ Import namespace= "System.Data"%>
<%@ Import namespace = "System.Data.SqlClient"%>
<%@ Import Namespace = "System.Configuration"%>

<script runat="server">

    Dim client_id As String
    Dim activity As String
    Dim end_date As Date
    Dim start_date As Date
     
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim _con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Dim _com As SqlCommand = New SqlCommand("UpdateClientActivity", _con)
        _com.CommandType = CommandType.StoredProcedure
        _com.Parameters.AddWithValue("@client_id", "Real_coffee") 'each parameter in order as they appear in the stored procedure
        _com.Parameters.AddWithValue("@activity", "succeed") 'each parameter in order as they appear in the stored procedure
        _com.Parameters.AddWithValue("@start_date", "02/20/2008") 'each parameter in order as they appear in the stored procedure
        _com.Parameters.AddWithValue("@end_date", Now()) 'each parameter in order as they appear in the stored procedure
       
        _con.Open()
        _com.ExecuteNonQuery()
        _con.Close()

    End Sub
</script>

<Head runat="server">
    <title>Untitled Page</title>
</head>
<html xmlns="http://www.w3.org/1999/xhtml">
<body>
    <form id="form1" runat="server">
    <div>
 
         
        <br />
   
        <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
   
    </div>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
        SelectCommand="SELECT * FROM [Client_Activity]"></asp:SqlDataSource>
    </form>
</body>
</html>

0
 

Author Closing Comment

by:Real_coffee
ID: 31547917
Thanks guys - I really beat my head against this.
0
 
LVL 5

Expert Comment

by:synx
ID: 23696121
Excellent!  Thanks for the update.
0
 
LVL 2

Expert Comment

by:yatin_81
ID: 23701586
Hey guys, sorry for not in touch with you. As said by synx "Thanks for the update". Keep questioning real time queries..............
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question