locked
Deleting joined table row using GridView and AccessDataSource RRS feed

  • Question

  • User1013419587 posted

    Hi there,

    I have three tables in my database which like this:

    Table1: Student(ID, StudentName)

    Table2: Class(ID, ClassName)

    Table3: ClassStudent (ClassID, StudentID)

    I joined Student and Class tables to ClassStudent to implement many-to-many relation and to achieve names of class and student. I can see joined table in the gridview. But my problem is When i try to delete record in ClassStudent nothings happens . Sometime i get error like : No value given for one or more required parameters. My delete command is :


    DeleteCommand="DELETE FROM [ClassStudent] WHERE ([ClassID] = ?)  AND ([StudentID] = ?)" 

    and my asp:parameter is :

    <DeleteParameters>
              <asp:Parameter Name="ClassID" Type="Int32" />
              <asp:Parameter Name="StudentID" Type="Int32" />
    </DeleteParameters>
    

    It is worth to mention that when i do not use joined table and i have only ClassStudent table, Delete command works.

    Friday, December 16, 2011 8:39 AM

Answers

  • User3866881 posted

    Sometime i get error like : No value given for one or more required parameters. My delete command is :

    From this situation,I think you haven't assigned enough parameters for the specified sql statement with parameters。So Would you mind offering us your detailed codes of GridView?

    PS:Here's my suggestion——

    1)You can set another column (suppose let's call it "ID" for the Student_Class table——So your table looks like this:ID,ClassID,StudentID)

    2)Your GridView should do something like this:

    <asp:GridView ……DataKeyNames="ID"………… DeleteCommand="DELETE FROM xxx where [ID]=?>
        <DeleteParameters>
             
    <asp:Parameter Name="ClassID" Type="Int32" />
             
    <asp:Parameter Name="StudentID" Type="Int32" />
         </DeleteParameters>
    </asp:GridView>

    PS:Don't forget to include "ID" in the Select statement。

    Best reguard!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 17, 2011 8:38 PM
  • User-1199946673 posted

    1)You can set another column (suppose let's call it "ID" for the Student_Class table——So your table looks like this:ID,ClassID,StudentID)

    2)Your GridView should do something like this:

    <asp:GridView ……DataKeyNames="ID"………… DeleteCommand="DELETE FROM [ClassStudent] WHERE ([ClassID] = ?)  AND ([StudentID] = ?)" >

    Adding an ID column is a good idea. But why would this solve the problem, if you don't use it? So when adding an ID column (and setting the DataKeyNames property to ID), change the DeleteCommand to:

    DeleteCommand="DELETE FROM [ClassStudent] WHERE [ID] = ?"

    And the DeleteParameters collection to

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

    But another option would be to change the DataKeyNames Property to:

    <asp:GridView ……DataKeyNames="ClassID, StudentID"…………

    By the way. Another reason for this error to occur is when you mispelled one or more fieldnames in the command. They this filedname is threaded as it were a parameter, which isn't specified in the parameter collection off course....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 18, 2011 7:08 AM

All replies

  • User3866881 posted

    Sometime i get error like : No value given for one or more required parameters. My delete command is :

    From this situation,I think you haven't assigned enough parameters for the specified sql statement with parameters。So Would you mind offering us your detailed codes of GridView?

    PS:Here's my suggestion——

    1)You can set another column (suppose let's call it "ID" for the Student_Class table——So your table looks like this:ID,ClassID,StudentID)

    2)Your GridView should do something like this:

    <asp:GridView ……DataKeyNames="ID"………… DeleteCommand="DELETE FROM xxx where [ID]=?>
        <DeleteParameters>
             
    <asp:Parameter Name="ClassID" Type="Int32" />
             
    <asp:Parameter Name="StudentID" Type="Int32" />
         </DeleteParameters>
    </asp:GridView>

    PS:Don't forget to include "ID" in the Select statement。

    Best reguard!

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, December 17, 2011 8:38 PM
  • User-1199946673 posted

    1)You can set another column (suppose let's call it "ID" for the Student_Class table——So your table looks like this:ID,ClassID,StudentID)

    2)Your GridView should do something like this:

    <asp:GridView ……DataKeyNames="ID"………… DeleteCommand="DELETE FROM [ClassStudent] WHERE ([ClassID] = ?)  AND ([StudentID] = ?)" >

    Adding an ID column is a good idea. But why would this solve the problem, if you don't use it? So when adding an ID column (and setting the DataKeyNames property to ID), change the DeleteCommand to:

    DeleteCommand="DELETE FROM [ClassStudent] WHERE [ID] = ?"

    And the DeleteParameters collection to

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

    But another option would be to change the DataKeyNames Property to:

    <asp:GridView ……DataKeyNames="ClassID, StudentID"…………

    By the way. Another reason for this error to occur is when you mispelled one or more fieldnames in the command. They this filedname is threaded as it were a parameter, which isn't specified in the parameter collection off course....

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, December 18, 2011 7:08 AM
  • User1013419587 posted

    Thanks, Both of answers are correct.

    Tuesday, December 20, 2011 10:42 PM