Solved

DetailsView is not updating the database (Multiple tables)

Posted on 2008-06-15
12
887 Views
Last Modified: 2012-06-27
Hi Guys,
The DetailsView is not updating the databse. There is no error message, but after I clicked update, it retures to detailsview with all old data. Anyone please help me. Thanks
<asp:DetailsView ID="DetailsView3" runat="server" AutoGenerateRows="False" BackColor="White"

        BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1"

        DataSourceID="AccessDataSourceStudentContact" GridLines="None" Height="50px"

        Width="288px" DataKeyNames="Username">

        <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />

        <EditRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />

        <RowStyle BackColor="#DEDFDE" ForeColor="Black" />

        <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />

        <Fields>

            <asp:BoundField DataField="Username" HeaderText="Username" ReadOnly="True" SortExpression="Username" />

            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />

            <asp:BoundField DataField="LocalPhone" HeaderText="LocalPhone" SortExpression="LocalPhone" />

            <asp:BoundField DataField="LocalWorkPhone" HeaderText="LocalWorkPhone" SortExpression="LocalWorkPhone" />

            <asp:BoundField DataField="PerStreet" HeaderText="PerStreet" SortExpression="PerStreet" />

            <asp:BoundField DataField="PerCity" HeaderText="PerCity" SortExpression="PerCity" />

            <asp:BoundField DataField="PerState" HeaderText="PerState" SortExpression="PerState" />

            <asp:BoundField DataField="PerZip" HeaderText="PerZip" SortExpression="PerZip" />

            <asp:BoundField DataField="PerCountry" HeaderText="PerCountry" SortExpression="PerCountry" />

            <asp:BoundField DataField="LocalStreet" HeaderText="LocalStreet" SortExpression="LocalStreet" />

            <asp:BoundField DataField="LocalCity" HeaderText="LocalCity" SortExpression="LocalCity" />

            <asp:BoundField DataField="LocalState" HeaderText="LocalState" SortExpression="LocalState" />

            <asp:BoundField DataField="LocalZip" HeaderText="LocalZip" SortExpression="LocalZip" />

            <asp:BoundField DataField="AddressUntil" HeaderText="AddressUntil" SortExpression="AddressUntil" />

            <asp:BoundField DataField="CountryName" HeaderText="CountryName" SortExpression="CountryName" />

            <asp:BoundField DataField="CountryCode" HeaderText="CountryCode" SortExpression="CountryCode" />

            <asp:CommandField ShowEditButton="True" />

        </Fields>

        <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />

        <HeaderTemplate>

            Contact Information

        </HeaderTemplate>

    </asp:DetailsView>

    <asp:AccessDataSource ID="AccessDataSourceStudentContact" runat="server" DataFile="~/App_Data/GLC.mdb"

        SelectCommand="SELECT Students.Username, Students.Email, Students.LocalPhone, Students.LocalWorkPhone, Address.PerStreet, Address.PerCity, Address.PerState, Address.PerZip, Address.PerCountry, Address.LocalStreet, Address.LocalCity, Address.LocalState, Address.LocalZip, Address.AddressUntil, Countries.CountryName, Countries.CountryCode FROM ((Students INNER JOIN Address ON Students.Username = Address.UserName) INNER JOIN Countries ON Address.PerCountry = Countries.ID) WHERE (Students.Username = @Username)" 

        UpdateCommand="UPDATE Students, Address SET Students.Email = [@Email], Students.LocalPhone = [@LocalPhone], Students.LocalWorkPhone = [@LocalWorkPhone], Address.PerStreet = [@PerStreet], Address.PerCity = [@PerCity], Address.PerState = [@PerState], Address.PerZip = [@PerZip], Address.PerCountry = [@PerCountry], Address.LocalStreet = [@LocalStreet], Address.LocalCity = [@LocalCity], Address.LocalState = [@LocalState], Address.LocalZip = [@LocalZip], Address.AddressUntil = [@AddressUntil]

WHERE ((([Student].[Username])=[@Username]))">
 

     

    <SelectParameters>

       <asp:QUERYSTRINGPARAMETER DefaultValue="0" Name="Students.Username" QueryStringField="ID" />

    </SelectParameters>

Open in new window

0
Comment
Question by:Robertyue
  • 8
  • 4
12 Comments
 
LVL 21

Expert Comment

by:naspinski
Comment Utility
First thing is first: Open sql management studio and make sure your query is working in there.
0
 

Author Comment

by:Robertyue
Comment Utility
Thank you. I'm using access database. I changed the SQL update command to
"UPDATE Address INNER JOIN Students ON Address.UserName = Students.Username SET Students.Email = @Email, Students.LocalPhone = @LocalPhone, Students.LocalWorkPhone = @LocalWorkPhone, Address.PerStreet = @PerStreet, Address.PerCity = @PerCity, Address.PerState = @PerState, Address.PerZip = @PerZip, Address.PerCountry = @PerCountry, Address.LocalStreet = @LocalStreet, Address.LocalCity = @LocalCity, Address.LocalState = @LocalState, Address.LocalZip = @LocalZip, Address.AddressUntil = @AddressUntil WHERE Student.Username=@Username"
It works fine in Access, but not in DetailsView....
0
 

Author Comment

by:Robertyue
Comment Utility
I've done other detailviews and they are perfectly updating the database because they have only one table.
0
 
LVL 21

Expert Comment

by:naspinski
Comment Utility
I dont see you declaring any UpdateParameters... all of those need to be declared just like you have your SelectParameters up above.
0
 

Author Comment

by:Robertyue
Comment Utility
Sorry, I forgot the post the rest...I did declare them..Sorry again


    <UpdateParameters>
     <asp:Parameter Name="Email" Type="String" />
     <asp:Parameter Name="LocalPhone" Type="String" />
     <asp:Parameter Name="LocalWorkPhone" Type="String" />
     <asp:Parameter Name="PerStreet" Type="String" />
     <asp:Parameter Name="PerCity" Type="String" />
     <asp:Parameter Name="PerState" Type="String" />
     <asp:Parameter Name="PerZip" Type="String" />
     <asp:Parameter Name="PerCountry" Type="Int32" />
     <asp:Parameter Name="LocalStreet" Type="String" />
     <asp:Parameter Name="LocalCity" Type="String" />
     <asp:Parameter Name="LocalState" Type="String" />
     <asp:Parameter Name="LocalZip" Type="String" />
     <asp:Parameter Name="AddressUntil" Type="DateTime" />
     
     
     
    </UpdateParameters>
0
 

Author Comment

by:Robertyue
Comment Utility
Here is the complete version
<asp:DetailsView ID="DetailsView3" runat="server" AutoGenerateRows="False" BackColor="White"

        BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellPadding="3" CellSpacing="1"

        DataSourceID="AccessDataSourceStudentContact" GridLines="None" Height="50px"

        Width="288px" DataKeyNames="Username">

        <FooterStyle BackColor="#C6C3C6" ForeColor="Black" />

        <EditRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />

        <RowStyle BackColor="#DEDFDE" ForeColor="Black" />

        <PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />

        <Fields>

            <asp:BoundField DataField="Username" HeaderText="Username" ReadOnly="True" SortExpression="Username" />

            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />

            <asp:BoundField DataField="LocalPhone" HeaderText="LocalPhone" SortExpression="LocalPhone" />

            <asp:BoundField DataField="LocalWorkPhone" HeaderText="LocalWorkPhone" SortExpression="LocalWorkPhone" />

            <asp:BoundField DataField="PerStreet" HeaderText="PerStreet" SortExpression="PerStreet" />

            <asp:BoundField DataField="PerCity" HeaderText="PerCity" SortExpression="PerCity" />

            <asp:BoundField DataField="PerState" HeaderText="PerState" SortExpression="PerState" />

            <asp:BoundField DataField="PerZip" HeaderText="PerZip" SortExpression="PerZip" />

            <asp:BoundField DataField="PerCountry" HeaderText="PerCountry" SortExpression="PerCountry" />

            <asp:BoundField DataField="LocalStreet" HeaderText="LocalStreet" SortExpression="LocalStreet" />

            <asp:BoundField DataField="LocalCity" HeaderText="LocalCity" SortExpression="LocalCity" />

            <asp:BoundField DataField="LocalState" HeaderText="LocalState" SortExpression="LocalState" />

            <asp:BoundField DataField="LocalZip" HeaderText="LocalZip" SortExpression="LocalZip" />

            <asp:BoundField DataField="AddressUntil" HeaderText="AddressUntil" SortExpression="AddressUntil" />

            <asp:BoundField DataField="CountryName" HeaderText="CountryName" SortExpression="CountryName" />

            <asp:BoundField DataField="CountryCode" HeaderText="CountryCode" SortExpression="CountryCode" />

            <asp:CommandField ShowEditButton="True" />

        </Fields>

        <HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />

        <HeaderTemplate>

            Contact Information

        </HeaderTemplate>

    </asp:DetailsView>

    <asp:AccessDataSource ID="AccessDataSourceStudentContact" runat="server" DataFile="~/App_Data/GLC.mdb"

        SelectCommand="SELECT Students.Username, Students.Email, Students.LocalPhone, Students.LocalWorkPhone, Address.PerStreet, Address.PerCity, Address.PerState, Address.PerZip, Address.PerCountry, Address.LocalStreet, Address.LocalCity, Address.LocalState, Address.LocalZip, Address.AddressUntil, Countries.CountryName, Countries.CountryCode FROM ((Students INNER JOIN Address ON Students.Username = Address.UserName) INNER JOIN Countries ON Address.PerCountry = Countries.ID) WHERE (Students.Username = @Username)" 

        UpdateCommand="UPDATE (Address INNER JOIN Students ON Address.UserName = Students.Username) SET Students.Email = @Email, Students.LocalPhone = @LocalPhone, Students.LocalWorkPhone = @LocalWorkPhone, Address.PerStreet = @PerStreet, Address.PerCity = @PerCity, Address.PerState = @PerState, Address.PerZip = @PerZip, Address.PerCountry = @PerCountry, Address.LocalStreet = @LocalStreet, Address.LocalCity = @LocalCity, Address.LocalState = @LocalState, Address.LocalZip = @LocalZip, Address.AddressUntil = @AddressUntil WHERE (Student.Username=@Username)">
 

     

    <SelectParameters>

       <asp:QUERYSTRINGPARAMETER DefaultValue="0" Name="Students.Username" QueryStringField="ID" />

    </SelectParameters>

    

    <UpdateParameters>

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

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

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

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

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

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

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

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

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

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

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

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

     <asp:Parameter Name="AddressUntil" Type="DateTime" />

     

    </UpdateParameters>

    

    </asp:AccessDataSource>

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 21

Accepted Solution

by:
naspinski earned 500 total points
Comment Utility
I dont see username in there.  I know it is a readonly, but it still needs to be declared.

Try adding that in.
0
 

Author Comment

by:Robertyue
Comment Utility
I did add it in but it's still not working. I didn't declare Username for my another single table detailsview and it works.  Thanks
0
 

Author Comment

by:Robertyue
Comment Utility
Is my SQL Update command wrong? It works in Access. The format is wrong in ASP.Net, may be?
0
 

Author Comment

by:Robertyue
Comment Utility
Please see attachment. This is my database and there is only one query (update). Please enter "hao" as the Uername. Why it's asking me to enter the username for both "Students.Username" and "@Username"? Should it ask me to only enter "@Username", then the database compares the entry with records to find out the data? Thanks
GLC.mdb
0
 
LVL 21

Expert Comment

by:naspinski
Comment Utility
Sorry, I have to get going today, but I can take a look at it in about 12 hours if someone doesn't beat me to it.
0
 

Author Comment

by:Robertyue
Comment Utility
Hello, you back? Could you please take a look my databse? Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now