locked
Problem with update query RRS feed

  • Question

  • User-334971697 posted

    Hi all,

    I am having a problem getting an update query to work on an access database. It is based on information that a user enters into a gridview. The select query that populates the gridview works, but the update query, not. I have a similar update query on a different page that updates the same database with different fields that works. I am using VWD 2008 and have used the query builder, which works when I enter values manually. The code for the whole page is below, with the code for the relevent gridview emboldened.

    Any help on this would be much appreciated, as it is driving me crazy.

    Kind regards
    Richard

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <title></title>
    <style type="text/css">
    .style1
    {
    font-family: Arial, Helvetica, sans-serif;
    font-size: xx-large;
    font-weight: bold;
    }
    .style2
    {
    font-family: Arial, Helvetica, sans-serif;
    font-size: medium;
    font-weight: bold;
    }
    .style3
    {
    text-align: center;
    }
    .style4
    {
    font-family: Arial, Helvetica, sans-serif;
    font-size: small;
    font-weight: bold;
    }
    </style>
    </head>
    <body>
    <form id="form1" runat="server">
    <div>

    <span class="style1">
    <table>
    <tr>
    <td class="style3">
    <span class="style1">
    <img alt="SIS" src="images/sislogo.jpg" style="width: 204px; height: 84px" /></span></td>
    <td>
    &nbsp;&nbsp; &nbsp;</td>
    <td>
    <span class="style1">Report data entry&nbsp;&nbsp;&nbsp;&nbsp; <br />
    </span>

    <span class="style2">February 2012</span></td>
    </tr>
    </table>
    </span>

    <span class="style2">Choose options &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Grade:&nbsp;
    <asp:DropDownList ID="ComboGrade" runat="server" AutoPostBack="True"
    DataSourceID="DSGrade" DataTextField="Grade" DataValueField="Grade"
    Width="50px">
    </asp:DropDownList>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Subject: </span><asp:DropDownList ID="ComboSubject" runat="server" AutoPostBack="True"
    DataSourceID="DSSubjectCombo" DataTextField="Subject" DataValueField="Subject"
    Width="160px">
    </asp:DropDownList>
    &nbsp;&nbsp;&nbsp;&nbsp;
    <asp:AccessDataSource ID="DSSubjectCombo" runat="server"
    DataFile="App_Data\Reports.mdb"


    SelectCommand="SELECT DISTINCT Students.Grade, StudentSubject.Subject FROM (Students INNER JOIN StudentSubject ON Students.StudentID = StudentSubject.StudentID) WHERE (Students.Grade = ?)">
    <SelectParameters>
    <asp:ControlParameter ControlID="ComboGrade" Name="?"
    PropertyName="SelectedValue" />
    </SelectParameters>
    </asp:AccessDataSource>
    <asp:Button ID="Button1" runat="server" PostBackUrl="~/GSFeb2012Overview.aspx"
    Text="Go to student overview" />
    &nbsp;<asp:AccessDataSource ID="DSGrade" runat="server"
    DataFile="App_Data\Reports.mdb"
    SelectCommand="SELECT [Grade] FROM [Grades] ORDER BY [Grade]">
    </asp:AccessDataSource>
    <hr />
    <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False"
    DataKeyNames="Grade,Subject" DataSourceID="DSCriteria" Height="4px"
    style="font-family: Arial, Helvetica, sans-serif; font-size: small"
    Width="100%" BackColor="White" BorderColor="#CC9966" BorderStyle="None"
    BorderWidth="1px" CellPadding="2">
    <RowStyle BackColor="White" ForeColor="#330099" Height="4px"
    HorizontalAlign="Left" />
    <Columns>
    <asp:BoundField DataField="Grade" HeaderText="Grade" ReadOnly="True"
    SortExpression="Grade" />
    <asp:BoundField DataField="Criteria1" HeaderText="Criteria1"
    SortExpression="Criteria1" />
    <asp:BoundField DataField="Criteria2" HeaderText="Criteria2"
    SortExpression="Criteria2" />
    <asp:BoundField DataField="Criteria3" HeaderText="Criteria3"
    SortExpression="Criteria3" />
    <asp:BoundField DataField="Criteria4" HeaderText="Criteria4"
    SortExpression="Criteria4" />
    <asp:BoundField DataField="Criteria5" HeaderText="Criteria5"
    SortExpression="Criteria5" />
    <asp:BoundField DataField="Criteria6" HeaderText="Criteria6"
    SortExpression="Criteria6" />
    <asp:BoundField DataField="Criteria7" HeaderText="Criteria7"
    SortExpression="Criteria7" />
    <asp:BoundField DataField="Criteria8" HeaderText="Criteria8"
    SortExpression="Criteria8" />
    <asp:BoundField DataField="Criteria9" HeaderText="Criteria9"
    SortExpression="Criteria9" />
    <asp:BoundField DataField="Criteria10" HeaderText="Criteria10"
    SortExpression="Criteria10" />
    <asp:BoundField DataField="criteria11" HeaderText="criteria11"
    SortExpression="criteria11" />
    </Columns>
    <FooterStyle BackColor="#FFFFCC" ForeColor="#330099" />
    <PagerStyle BackColor="#FFFFCC" ForeColor="#330099" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="#663399" />
    <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="#FFFFCC"
    Height="4px" HorizontalAlign="Left" />
    <EditRowStyle Height="4px" />
    </asp:GridView>
    <asp:AccessDataSource ID="DSCriteria" runat="server"
    DataFile="App_Data\Reports.mdb"



    SelectCommand="SELECT Grade, Subject, Criteria1, Criteria2, Criteria3, Criteria4, Criteria5, Criteria6, Criteria7, Criteria8, Criteria9, Criteria10, Criteria11 FROM GradeSubjects WHERE (Subject = ?) AND (Grade = ?)">
    <SelectParameters>
    <asp:ControlParameter ControlID="ComboSubject" DefaultValue="German"
    Name="Subject" PropertyName="SelectedValue" Type="String" />
    <asp:ControlParameter ControlID="ComboGrade" DefaultValue="1a" Name="?"
    PropertyName="SelectedValue" />
    </SelectParameters>
    </asp:AccessDataSource>
    <div class="style3">
    <br />
    <span class="style4">Evaluation key:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NA - Not Applicable&nbsp;&nbsp;&nbsp;&nbsp; NS - Needs
    Support&nbsp;&nbsp;&nbsp;&nbsp; EM - EMerging&nbsp;&nbsp;&nbsp;&nbsp; CO - COnsolidating&nbsp;&nbsp;&nbsp;&nbsp; EX - EXtending</span><br />

    </div>

    </div>
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
    BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px"
    CellPadding="1" DataSourceID="DSGrid" GridLines="Vertical" Height="158px"
    style="font-family: Arial, Helvetica, sans-serif; font-size: small">
    <PagerSettings Position="TopAndBottom" />
    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
    <Columns>
    <asp:CommandField ButtonType="Button" ShowEditButton="True" />
    <asp:BoundField DataField="1stName" HeaderText="1st Name"
    SortExpression="1stName" >
    <ControlStyle Width="60px" />
    <ItemStyle Width="0px" />
    </asp:BoundField>
    <asp:BoundField DataField="Surname" HeaderText="Surname"
    SortExpression="Surname" >
    <ControlStyle Width="60px" />
    </asp:BoundField>
    <asp:BoundField DataField="GradeOverall" HeaderText="Overall Grade"
    SortExpression="GradeOverall" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:TemplateField HeaderText="Comment" SortExpression="Comment">
    <EditItemTemplate>
    <asp:TextBox ID="TextBox1" runat="server"
    CssClass="style5" Rows="4" Text='<%# Bind("Comment") %>' TextMode="MultiLine"
    Width="250px"></asp:TextBox>
    </EditItemTemplate>
    <ItemTemplate>
    <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Comment") %>'
    TextMode="MultiLine" Width="251px"></asp:TextBox>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="Criteria1Grade" HeaderText="Criteria 1 Grade"
    SortExpression="Criteria1Grade">
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria2Grade" HeaderText="Criteria 2 Grade"
    SortExpression="Criteria2Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria3Grade" HeaderText="Criteria 3 Grade"
    SortExpression="Criteria3Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria4Grade" HeaderText="Criteria 4 Grade"
    SortExpression="Criteria4Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria5Grade" HeaderText="Criteria 5 Grade"
    SortExpression="Criteria5Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria6Grade"
    SortExpression="Criteria6Grade" HeaderText="Criteria 6 Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria7Grade"
    SortExpression="Criteria7Grade" HeaderText="Criteria 7 Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria8Grade"
    SortExpression="Criteria8Grade" HeaderText="Criteria 8 Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria9Grade"
    SortExpression="Criteria9Grade" HeaderText="Criteria 9 Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="Criteria10Grade"
    SortExpression="Criteria10Grade" HeaderText="Criteria 10 Grade" >
    <ControlStyle Width="30px" />
    </asp:BoundField>
    <asp:BoundField DataField="StudentID" HeaderText="ID"
    SortExpression="StudentID" >
    <ControlStyle Width="1px" />
    <HeaderStyle Font-Size="1pt" />
    <ItemStyle Font-Size="1pt" Width="1px" />
    </asp:BoundField>
    <asp:BoundField DataField="Grade" HeaderText="G"
    SortExpression="Grade" >
    <ControlStyle Width="1px" />
    <HeaderStyle Font-Size="1pt" />
    <ItemStyle Font-Size="1pt" Width="1px" />
    </asp:BoundField>
    <asp:BoundField DataField="Subject" HeaderText="S"
    SortExpression="Subject" >
    <ControlStyle Width="1px" />
    <HeaderStyle Font-Size="1pt" />
    <ItemStyle Font-Size="1pt" Width="1px" />
    </asp:BoundField>
    <asp:BoundField DataField="ReportYear" HeaderText="R"
    SortExpression="ReportYear" >
    <ControlStyle Width="1px" />
    <HeaderStyle Font-Size="1pt" />
    <ItemStyle Font-Size="1pt" Width="1px" />
    </asp:BoundField>
    <asp:BoundField DataField="ReportSession" HeaderText="RS"
    SortExpression="ReportSession" >
    <ControlStyle Font-Size="1pt" Width="1px" />
    <HeaderStyle Font-Size="1pt" />
    <ItemStyle Font-Size="1pt" Width="1px" />
    </asp:BoundField>
    </Columns>
    <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
    <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
    <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
    <AlternatingRowStyle BackColor="#DCDCDC" />
    </asp:GridView>

    <asp:AccessDataSource ID="DSGrid" runat="server"
    DataFile="App_Data\Reports.mdb"
    SelectCommand="SELECT StudentSubject.StudentID, Students.[1stName], Students.Surname, Students.Grade, Students.Left, StudentSubject.Subject, StudentSubject.GradeOverall, StudentSubject.ReportYear, StudentSubject.ReportSession, StudentSubject.Criteria1Grade, StudentSubject.Criteria2Grade, StudentSubject.Criteria3Grade, StudentSubject.Criteria4Grade, StudentSubject.Criteria5Grade, StudentSubject.Criteria6Grade, StudentSubject.Criteria7Grade, StudentSubject.Criteria8Grade, StudentSubject.Criteria9Grade, StudentSubject.Criteria10Grade, StudentSubject.Comment FROM (Students INNER JOIN StudentSubject ON Students.StudentID = StudentSubject.StudentID) WHERE (StudentSubject.Subject = ?) AND (StudentSubject.ReportYear = '2011 - 2012') AND (Students.Grade = ?) AND (StudentSubject.ReportSession = 'February') AND (Students.left is null) ORDER BY Students.Surname"
    UpdateCommand="UPDATE StudentSubject SET GradeOverall = ?, Criteria1Grade = ?, Criteria2Grade = ?, Criteria3Grade = ?, Criteria4Grade = ?, Criteria5Grade = ?, Criteria6Grade = ?, Criteria7Grade = ?, Criteria8Grade = ?, Criteria9Grade = ?, Criteria10Grade = ?, Comment = ? WHERE (StudentID = ?) AND (Subject = ?) AND (ReportYear = ?) AND (ReportSession = ?)">
    <SelectParameters>
    <asp:ControlParameter ControlID="ComboSubject" DefaultValue="German" Name="Subject" PropertyName="SelectedValue" Type="String" />
    <asp:ControlParameter ControlID="ComboGrade" DefaultValue="1a"
    Name="Grade" PropertyName="SelectedValue" Type="String" />
    </SelectParameters>
    <UpdateParameters>
    <asp:Parameter Name="GradeOverall" Type="String" />
    <asp:Parameter Name="Criteria1Grade" Type="String" />
    <asp:Parameter Name="Criteria2Grade" Type="String" />
    <asp:Parameter Name="Criteria3Grade" Type="String" />
    <asp:Parameter Name="Criteria4Grade" Type="String" />
    <asp:Parameter Name="Criteria5Grade" Type="String" />
    <asp:Parameter Name="Criteria6Grade" Type="String" />
    <asp:Parameter Name="Criteria7Grade" Type="String" />
    <asp:Parameter Name="Criteria8Grade" Type="String" />
    <asp:Parameter Name="Criteria9Grade" Type="String" />
    <asp:Parameter Name="Criteria10Grade" Type="String" />
    <asp:Parameter Name="Comment" Type="String" />
    <asp:Parameter Name="StudentID" Type="Int32" />
    <asp:Parameter Name="Subject" Type="String" />
    <asp:Parameter Name="ReportYear" Type="String" />
    <asp:Parameter Name="ReportSession" Type="String" />
    </UpdateParameters>
    </asp:AccessDataSource>
    <p>
    &nbsp;</p>
    <p>
    &nbsp;</p>
    </form>
    </body>
    </html>
    Monday, January 2, 2012 3:55 PM

Answers

  • User-334971697 posted

    Hi again,

    just to let you know, I have solved the problem. It stemmed from a small snippet of VB which I had forgotten about. This made certain columns invisible. Unfortunately, I had added a column and forgotten to update the code, so it ended up making part of the compound key invisible. Correcting this solved the problem.

    Thanks for your help

    Richard 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2012 2:20 AM

All replies

  • User-334971697 posted

    ...sorry, I'll post the offending code in a code window,  Oops.

        <asp:AccessDataSource ID="DSGrid" runat="server" 
            DataFile="App_Data\Reports.mdb" 
            SelectCommand="SELECT StudentSubject.StudentID, Students.[1stName], Students.Surname, Students.Grade, Students.Left, StudentSubject.Subject, StudentSubject.GradeOverall, StudentSubject.ReportYear, StudentSubject.ReportSession, StudentSubject.Criteria1Grade, StudentSubject.Criteria2Grade, StudentSubject.Criteria3Grade, StudentSubject.Criteria4Grade, StudentSubject.Criteria5Grade, StudentSubject.Criteria6Grade, StudentSubject.Criteria7Grade, StudentSubject.Criteria8Grade, StudentSubject.Criteria9Grade, StudentSubject.Criteria10Grade, StudentSubject.Comment FROM (Students INNER JOIN StudentSubject ON Students.StudentID = StudentSubject.StudentID) WHERE (StudentSubject.Subject = ?) AND (StudentSubject.ReportYear = '2011 - 2012') AND (Students.Grade = ?) AND (StudentSubject.ReportSession = 'February') AND (Students.left is null) ORDER BY Students.Surname"
            UpdateCommand="UPDATE StudentSubject SET GradeOverall = ?, Criteria1Grade = ?, Criteria2Grade = ?, Criteria3Grade = ?, Criteria4Grade = ?, Criteria5Grade = ?, Criteria6Grade = ?, Criteria7Grade = ?, Criteria8Grade = ?, Criteria9Grade = ?, Criteria10Grade = ?, Comment = ? WHERE (StudentID = ?) AND (Subject = ?) AND (ReportYear = ?) AND (ReportSession = ?)">
            <SelectParameters>
                <asp:ControlParameter ControlID="ComboSubject"  DefaultValue="German"       Name="Subject"          PropertyName="SelectedValue" Type="String" />
                <asp:ControlParameter ControlID="ComboGrade"     DefaultValue="1a"  
                    Name="Grade"       PropertyName="SelectedValue" Type="String" />
            </SelectParameters>
            <UpdateParameters>
                    <asp:Parameter Name="GradeOverall"   Type="String" />
                    <asp:Parameter Name="Criteria1Grade" Type="String" />
                    <asp:Parameter Name="Criteria2Grade" Type="String" />
                    <asp:Parameter Name="Criteria3Grade" Type="String" />
                    <asp:Parameter Name="Criteria4Grade" Type="String" />
                    <asp:Parameter Name="Criteria5Grade" Type="String" />
                    <asp:Parameter Name="Criteria6Grade" Type="String" />
                    <asp:Parameter Name="Criteria7Grade" Type="String" />
                    <asp:Parameter Name="Criteria8Grade" Type="String" />
                    <asp:Parameter Name="Criteria9Grade" Type="String" />
                    <asp:Parameter Name="Criteria10Grade" Type="String" />
                    <asp:Parameter Name="Comment"        Type="String" />
                    <asp:Parameter Name="StudentID"      Type="Int32" />
                    <asp:Parameter Name="Subject"        Type="String" />
                    <asp:Parameter Name="ReportYear"     Type="String" />
                    <asp:Parameter Name="ReportSession"  Type="String" />
                </UpdateParameters>
        </asp:AccessDataSource>
    Monday, January 2, 2012 3:57 PM
  • User-1199946673 posted

    In the SelectCommand, you should include the Primary Key field(s) of the StudenSubject Table (I assume it is called StudentSubjectID). The WHERE clause in your UpdateCommand should be:

    UPDATE StudentSubject ...... WHERE StudentSubjectID = ?

    And don't forget to change the UpdateParameters Collection accordingly

    In the GridView, change the value of DataKeyNames property to "StudenSubjectID"

     

    Monday, January 2, 2012 7:10 PM
  • User-334971697 posted

    Hi and thanks for the response,

    the primary key for the StudentSubject table is a compound key using StudentID, Subject, ReportYear and ReportSession. In the DataKeyNames, I have left this blank, as the primary keys are all visible in the gridview - IIRC this means I don't need them in DataKeyNames.

    I have been experimenting and I found that if I recreated the gridview from scratch, it would work (with nothing in DataKeyNames). However, when I tried moving some of the columns in the gridview, it broke it. More specifically, there is one field (ReportSession) which stops the gridview from updating if it is moved beyond the 14th column. I also tried adding the keys to DataKeyNames, but this also stopped the gridview from updating.

    This has really got me stumped!

    Regards
    Richard  

    Tuesday, January 3, 2012 1:09 AM
  • User-334971697 posted

    Hi again,

    just to let you know, I have solved the problem. It stemmed from a small snippet of VB which I had forgotten about. This made certain columns invisible. Unfortunately, I had added a column and forgotten to update the code, so it ended up making part of the compound key invisible. Correcting this solved the problem.

    Thanks for your help

    Richard 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, January 3, 2012 2:20 AM