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>

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

Hi MadhavRao

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

Thanks anyway.  

cjhent:

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

Off course you can

cjhent:

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"

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  

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:

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

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

cjhent:

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

cjhent:

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

cjhent:

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

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>

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?