asked on

where and how to run update query

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

synx

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.

ASKER CERTIFIED SOLUTION

yatin_81

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

synx

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Real_coffee

ASKER

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

Real_coffee

ASKER

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?

synx

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.

Real_coffee

ASKER

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

Real_coffee

ASKER

yatin_81 - any chance you could talk me through creating that trigger?

synx

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

Real_coffee

ASKER

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>

Real_coffee

ASKER

Thanks guys - I really beat my head against this.

synx

Excellent! Thanks for the update.

yatin_81

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