locked
problem using NOW() in DetailsView RRS feed

  • Question

  • User642668503 posted

     Hi, I'm new to this so I appologise if this is something simple that I've made an error on.

    I am using DetailsView to display data from an AccessDB and it can be edited and updated etc.
     - 1 field is "LastUpdated" and the idea being that this field will fill in the current date / time automatically rather than typing it in the text box.
     - I was presuming I can use   NOW() instead of the ? in the UpdateCommand

    - It works fine in the InsertCommand but not in the UpdateCommand

    - Any ideas why this does not work ?

     

    <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/App_Data/taoc_db.mdb" 
            SelectCommand="SELECT ID, CompanyName, ShopNumber, ContactName, Address1, Address2, Suburb, State, Postcode, Country, Phone1, Phone2, Fax, Website, Email1, Email2, Comments, Type, CurrentBuyer, RepName, Media, PaymentTerms, PaymentMethod, LastContacted, LastUpdated FROM contacts WHERE (ID = ?)"
            UpdateCommand="UPDATE contacts SET CompanyName = ?, ShopNumber = ?, ContactName = ?, Address1 = ?, Address2 = ?, Suburb = ?, State = ?, Postcode = ?, Country = ?, Phone1 = ?, Phone2 = ?, Fax = ?, Website = ?, Email1 = ?, Email2 = ?, Comments = ?, Type = ?, CurrentBuyer = ?, RepName = ?, Media = ?, PaymentTerms = ?, 
            PaymentMethod = ?, LastContacted = ?, LastUpdated = NOW() WHERE (ID = ?)" 
            InsertCommand="INSERT INTO contacts(CompanyName, ShopNumber, ContactName, Address1, Address2, Suburb, State, Postcode, Country, Phone1, Phone2, Fax, Website, Email1, Email2, Comments, Type, CurrentBuyer, RepName, Media, PaymentTerms, PaymentMethod, LastContacted, LastUpdated) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())">
            <SelectParameters>
                <asp:QueryStringParameter Name="?" QueryStringField="ID" />
            </SelectParameters>
            <UpdateParameters>
                <asp:Parameter Name="CompanyName" Type="String" />
                <asp:Parameter Name="ShopNumber" Type="String" />
                <asp:Parameter Name="ContactName" Type="String" />
                <asp:Parameter Name="Address1" Type="String" />
                <asp:Parameter Name="Address2" Type="String" />
                <asp:Parameter Name="Suburb" Type="String" />
                <asp:Parameter Name="State" Type="String" />
                <asp:Parameter Name="Postcode" Type="String" />
                <asp:Parameter Name="Country" Type="String" />
                <asp:Parameter Name="Phone1" Type="String" />
                <asp:Parameter Name="Phone2" Type="String" />
                <asp:Parameter Name="Fax" Type="String" />
                <asp:Parameter Name="Website" Type="String" />
                <asp:Parameter Name="Email1" Type="String" />
                <asp:Parameter Name="Email2" Type="String" />
                <asp:Parameter Name="Comments" Type="String" />
                <asp:Parameter Name="Type" Type="String" />
                <asp:Parameter Name="CurrentBuyer" Type="String" />
                <asp:Parameter Name="RepName" Type="String" />
                <asp:Parameter Name="Media" Type="String" />
                <asp:Parameter Name="PaymentTerms" Type="String" />
                <asp:Parameter Name="PaymentMethod" Type="String" />
                <asp:Parameter Name="LastContacted" Type="String" />
                <asp:Parameter Name="LastUpdated" Type="String" />
            </UpdateParameters>
        </asp:AccessDataSource>


     

    Wednesday, November 4, 2009 10:29 PM

Answers

  • User-1199946673 posted

    OleDb Parameters are recognized by their position, not by their name. That's the reason you can us ? as a paramater. But because the parameters are not recognized by their name, you must specify them in the update parameter collection in the same order they appear in the command. So ID should be the last paramater, not the first!

    By the way, Is LastContacted really a string of is it a DateTime Field?

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 9, 2009 12:55 PM
  • User642668503 posted

    Hi Hans

    I know this was a long time ago but I actually worked it out about a week ago. I actually needed to make the field "Read Only" for it to work.
    So to set the date as NOW() the field is included but it's read only, and thus it saves correctly.

    Thanks

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 6, 2010 7:15 AM

All replies

  • User-814327568 posted

    You need to change "NOW" in your query with "GetDate()". When query executes on your SQL Server (I assume you are using SQL Server), GetDate() will get you DateTime on your SQL Server. While "NOW" is not recognised as T-SQL Command on SQL Server.

    I hope this helped

    Wednesday, November 4, 2009 11:09 PM
  • User642668503 posted

    Hi MadhavRao

    GetDate() doesn;t work either - I'm using an Access DB, not SQL Server.

    Thanks anyway.  

    Wednesday, November 4, 2009 11:23 PM
  • User-1199946673 posted

    I was presuming I can use   NOW() instead of the ? in the UpdateCommand
     

    Off course you can

    Any ideas why this does not work ?
     

     

    Yes, the problem is your UpdateParameter collection. LastUpdated isn't a parameter, since you assign it a value in the command, so it should not be in your collection. However, ID is a parameter, so it should be in the collection

    Change 

    <asp:Parameter Name="LastUpdated" Type="String" />

    In

    <asp:Parameter Name="ID" Type="Int32" />

    Make sure that the DataKeyNames Property of the detailsview is set to "ID"

    Friday, November 6, 2009 2:52 AM
  • User642668503 posted

    Hi hans_v

    Thanks for the advice but it still doesn't work. When I change one of the other fields and then update nothing really occurs. The page returns to the normal state, but the items are not updated. The Date NOW() is also not updated ?

    Sorry, but any other advice ?

    Thanks for your time.

    Chris  

    Friday, November 6, 2009 7:28 AM
  • User-1199946673 posted

    Can you catch an error?

    if none of the fields in the records are updated, your command is probably wrong/ Are you sure you didn't misppelled obne of the fieldnames? Did you try this query in Access?

    Another option you can do is to single outthe problem. Start wth the following command:

  •   UpdateCommand="UPDATE contacts SET     LastUpdated = NOW() WHERE (ID = ?)"

And clear all UpdateParanmeters. except ID

If that one works, add all parameters one by one in both the command and command and try is it will update to find out which field(s) are caausing the problem...

  • Friday, November 6, 2009 3:00 PM
  • User642668503 posted

    Hi hans_v 

    Thanks for the info but still no luck. I've done all as mentioned above but still no change.

    This is no error shown, the page looks like it saves as expected but the changes are not saved and the date is not correct.
     - I stripped it back to 3 fields - ID, CompanyName and LastUpdated but still no luck.

    Is it something to do with (ID = ?) at the end and maybe not knowing what record to update. I did a different detailsview on a different part of my website and it works with (ID= ID) but then that updated every record rather than just the 1 I was trying to update.

    Is there a different way to do it with a "TemplateField" rather than a databound field ?

    As an aside - is there and difference in having the square brakets next to each item ?

    UpdateCommand="UPDATE [contacts] SET [CompanyName] = ?, [LastUpdated] = NOW() WHERE [ID] = ?"

     

    Thanks, Chris

    Sunday, November 8, 2009 5:17 AM
  • User-1199946673 posted

    Can you please post not only the update command, but the complete datasource control and detailsview?

     

    I did a different detailsview on a different part of my website and it works with (ID= ID) but then that updated every record rather than just the 1 I was trying to update.

    That's what I would expect, because ID = ID will be true for every record Wink

    Is there a different way to do it with a "TemplateField" rather than a databound field ?

     

    Not really, only you'll need to Bind the control(s) in the Template field manually

    As an aside - is there and difference in having the square brakets next to each item ?

    No, the brackets can be ommited, unless you've a fieldname that contains spaces or a fieldname which is a reserved word

    Sunday, November 8, 2009 12:23 PM
  • User642668503 posted

    Hi hans_v 

    Here is the full code.

       <asp:DetailsView ID="DetailsView1" runat="server" 
            DataSourceID="AccessDataSource1" Height="50px" Width="389px" 
            AutoGenerateRows="False" DataKeyNames="ID">
            <Fields>
                <asp:CommandField ButtonType="Button" ShowEditButton="True" />
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" 
                    ReadOnly="True" SortExpression="ID" />
                <asp:BoundField DataField="CompanyName" HeaderText="CompanyName" 
                    SortExpression="CompanyName" />
                <asp:BoundField DataField="ShopNumber" HeaderText="ShopNumber" 
                    SortExpression="ShopNumber" />
                <asp:BoundField DataField="ContactName" HeaderText="ContactName" 
                    SortExpression="ContactName" />
                <asp:BoundField DataField="Address1" HeaderText="Address1" 
                    SortExpression="Address1" />
                <asp:BoundField DataField="Address2" HeaderText="Address2" 
                    SortExpression="Address2" />
                <asp:BoundField DataField="Suburb" HeaderText="Suburb" 
                    SortExpression="Suburb" />
                <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
                <asp:BoundField DataField="Postcode" HeaderText="Postcode" 
                    SortExpression="Postcode" />
                <asp:BoundField DataField="Country" HeaderText="Country" 
                    SortExpression="Country" />
                <asp:BoundField DataField="Phone1" HeaderText="Phone1" 
                    SortExpression="Phone1" />
                <asp:BoundField DataField="Phone2" HeaderText="Phone2" 
                    SortExpression="Phone2" />
                <asp:BoundField DataField="Fax" HeaderText="Fax" SortExpression="Fax" />
                <asp:BoundField DataField="Website" HeaderText="Website" 
                    SortExpression="Website" />
                <asp:BoundField DataField="Email1" HeaderText="Email1" 
                    SortExpression="Email1" />
                <asp:BoundField DataField="Email2" HeaderText="Email2" 
                    SortExpression="Email2" />
                <asp:BoundField DataField="Comments" HeaderText="Comments" 
                    SortExpression="Comments" />
                <asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" />
                <asp:BoundField DataField="CurrentBuyer" HeaderText="CurrentBuyer" 
                    SortExpression="CurrentBuyer" />
                <asp:BoundField DataField="RepName" HeaderText="RepName" 
                    SortExpression="RepName" />
                <asp:BoundField DataField="Media" HeaderText="Media" 
                    SortExpression="Media" /> 
                <asp:BoundField DataField="PaymentTerms" HeaderText="PaymentTerms" 
                    SortExpression="PaymentTerms" />
                <asp:BoundField DataField="PaymentMethod" HeaderText="PaymentMethod" 
                    SortExpression="PaymentMethod" />
                <asp:BoundField DataField="LastContacted" HeaderText="LastContacted" 
                    SortExpression="LastContacted" />
                <asp:BoundField DataField="LastUpdated" HeaderText="LastUpdated" 
                    SortExpression="LastUpdated" />
            </Fields>
        </asp:DetailsView>
        <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            DataFile="~/App_Data/taoc_db.mdb" 
            InsertCommand="INSERT INTO contacts(ID, CompanyName, ShopNumber, ContactName, Address1, Address2, Suburb, State, Postcode, Country, Phone1, Phone2, Fax, Website, Email1, Email2, Comments, Type, CurrentBuyer, RepName, Media, PaymentTerms, PaymentMethod, LastContacted, LastUpdated) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())" 
            SelectCommand="SELECT ID, CompanyName, ShopNumber, ContactName, Address1, Address2, Suburb, State, Postcode, Country, Phone1, Phone2, Fax, Website, Email1, Email2, Comments, Type, CurrentBuyer, RepName, Media, PaymentTerms, PaymentMethod, LastContacted, LastUpdated FROM contacts WHERE (ID = ?)" 
            UpdateCommand="UPDATE contacts SET CompanyName = ?, ShopNumber = ?, ContactName = ?, Address1 = ?, Address2 = ?, Suburb = ?, State = ?, Postcode = ?, Country = ?, Phone1 = ?, Phone2 = ?, Fax = ?, Website = ?, Email1 = ?, Email2 = ?, Comments = ?, Type = ?, CurrentBuyer = ?, RepName = ?, Media = ?, PaymentTerms = ?, PaymentMethod = ?, LastContacted = ?, LastUpdated = NOW() WHERE (ID = ?)">
            <SelectParameters>
                 <asp:QueryStringParameter Name="?" QueryStringField="ID" />   
            </SelectParameters>
            <UpdateParameters>
                <asp:Parameter Name="ID" Type="Int32" />
                <asp:Parameter Name="CompanyName" Type="String" />
                <asp:Parameter Name="ShopNumber" Type="String" />
                <asp:Parameter Name="ContactName" Type="String" />
                <asp:Parameter Name="Address1" Type="String" />
                <asp:Parameter Name="Address2" Type="String" />
                <asp:Parameter Name="Suburb" Type="String" />
                <asp:Parameter Name="State" Type="String" />
                <asp:Parameter Name="Postcode" Type="String" />
                <asp:Parameter Name="Country" Type="String" />
                <asp:Parameter Name="Phone1" Type="String" />
                <asp:Parameter Name="Phone2" Type="String" />
                <asp:Parameter Name="Fax" Type="String" />
                <asp:Parameter Name="Website" Type="String" />
                <asp:Parameter Name="Email1" Type="String" />
                <asp:Parameter Name="Email2" Type="String" />
                <asp:Parameter Name="Comments" Type="String" />
                <asp:Parameter Name="Type" Type="String" />
                <asp:Parameter Name="CurrentBuyer" Type="String" />
                <asp:Parameter Name="RepName" Type="String" />
                <asp:Parameter Name="Media" Type="String" />
                <asp:Parameter Name="PaymentTerms" Type="String" />
                <asp:Parameter Name="PaymentMethod" Type="String" />
                <asp:Parameter Name="LastContacted" Type="String" />
            </UpdateParameters>
        </asp:AccessDataSource>


     

    Sunday, November 8, 2009 8:28 PM
  • User-1199946673 posted

    OleDb Parameters are recognized by their position, not by their name. That's the reason you can us ? as a paramater. But because the parameters are not recognized by their name, you must specify them in the update parameter collection in the same order they appear in the command. So ID should be the last paramater, not the first!

    By the way, Is LastContacted really a string of is it a DateTime Field?

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 9, 2009 12:55 PM
  • User642668503 posted

    Hi Hans

    I know this was a long time ago but I actually worked it out about a week ago. I actually needed to make the field "Read Only" for it to work.
    So to set the date as NOW() the field is included but it's read only, and thus it saves correctly.

    Thanks

    Chris

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 6, 2010 7:15 AM