locked
asp:SqlDataSource delete command needs to have two sql statements to delete from two tables, but it only executes the first statement. RRS feed

  • Question

  • User1100324560 posted

    In the following code, you will see that the delete command has two statements.

    delete FROM gdsvalue.usersitesqueue where userid = (select iduserprofile from gdsvalue.userprofile where iduserprofile = ?);

    delete FROM gdsvalue.userprofile where iduserprofile = ?

    Only the first statement is executed and it does not execute the 2nd statement. No error is thrown.

    Please guide me, how to make this work ?

    The complete sqldatasource code is shown below.

    <asp:SqlDataSource ID="SqlDataSourceEditUsers" runat="server" ConflictDetection="CompareAllValues"

    ConnectionString="<%$ ConnectionStrings:SVMySQLConnectionString %>"
    DeleteCommand="delete FROM gdsvalue.usersitesqueue where userid = (select iduserprofile from gdsvalue.userprofile where iduserprofile = ?);
    delete FROM gdsvalue.userprofile where iduserprofile = ?"
    InsertCommand="INSERT INTO [userprofile] ([iduserprofile], [ldapusername], [worklocationdir], [group], [domain]) VALUES (?, ?, ?, ?, ?)"
    OldValuesParameterFormatString="original_{0}" ProviderName="<%$ ConnectionStrings:SVMySQLConnectionString.ProviderName %>"
    SelectCommand="SELECT * FROM [userprofile]" UpdateCommand="UPDATE [userprofile] SET [ldapusername] = ?, [worklocationdir] = ?, [group] = ? WHERE [iduserprofile] = ? AND (([ldapusername] = ?) OR ([ldapusername] IS NULL AND ? IS NULL)) AND (([worklocationdir] = ?) OR ([worklocationdir] IS NULL AND ? IS NULL)) AND (([group] = ?) OR ([group] IS NULL AND ? IS NULL))">
    <DeleteParameters>
    <asp:Parameter Name="original_iduserprofile" Type="Int32" />
    <asp:Parameter Name="original_ldapusername" Type="String" />
    <asp:Parameter Name="original_ldapusername1" Type="String" />
    <asp:Parameter Name="original_worklocationdir" Type="String" />
    <asp:Parameter Name="original_worklocationdir1" Type="String" />
    <asp:Parameter Name="original_group" Type="String" />
    <asp:Parameter Name="original_group1" Type="String" />
    </DeleteParameters>
    <InsertParameters>
    <asp:Parameter Name="iduserprofile" Type="Int32" />
    <asp:Parameter Name="ldapusername" Type="String" />
    <asp:Parameter Name="worklocationdir" Type="String" />
    <asp:Parameter Name="group" Type="String" />
    <asp:Parameter Name="domain" Type="String" />
    </InsertParameters>
    <UpdateParameters>
    <asp:Parameter Name="ldapusername" Type="String" />
    <asp:Parameter Name="worklocationdir" Type="String" />
    <asp:Parameter Name="group" Type="String" />
    <asp:Parameter Name="original_iduserprofile" Type="Int32" />
    <asp:Parameter Name="original_ldapusername" Type="String" />
    <asp:Parameter Name="original_ldapusername1" Type="String" />
    <asp:Parameter Name="original_worklocationdir" Type="String" />
    <asp:Parameter Name="original_worklocationdir1" Type="String" />
    <asp:Parameter Name="original_group" Type="String" />
    <asp:Parameter Name="original_group1" Type="String" />
    </UpdateParameters>
    </asp:SqlDataSource>

    Monday, October 12, 2015 10:19 AM

Answers

  • User614698185 posted

    Hi shriramkarpur,

    DeleteCommand="delete FROM gdsvalue.usersitesqueue where userid = (select iduserprofile from gdsvalue.userprofile where iduserprofile = ?);
    delete FROM gdsvalue.userprofile where iduserprofile = ?"

    You should delete semicolon that between two queries. And write the two queries in one line.

    Like below:

    DeleteCommand="delete FROM gdsvalue.usersitesqueue where userid = (select iduserprofile from gdsvalue.userprofile where iduserprofile = ?) delete FROM gdsvalue.userprofile where iduserprofile = ?"

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 12, 2015 11:32 PM
  • User614698185 posted

    Hi shriramkarpur,

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete FROM gdsvalue.userprofile where iduserprofile = 'haerr.ec'' at line 1

    Firstly, check out your schema name is "gdsvalue".

    Secondly, make sure your where clause is correct.

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 14, 2015 6:01 AM

All replies

  • User614698185 posted

    Hi shriramkarpur,

    DeleteCommand="delete FROM gdsvalue.usersitesqueue where userid = (select iduserprofile from gdsvalue.userprofile where iduserprofile = ?);
    delete FROM gdsvalue.userprofile where iduserprofile = ?"

    You should delete semicolon that between two queries. And write the two queries in one line.

    Like below:

    DeleteCommand="delete FROM gdsvalue.usersitesqueue where userid = (select iduserprofile from gdsvalue.userprofile where iduserprofile = ?) delete FROM gdsvalue.userprofile where iduserprofile = ?"

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 12, 2015 11:32 PM
  • User1100324560 posted

    Candice,

    Thanks for your response. But I tried by removing the semicolon and making it one line. It does not work. Gives me the following error.

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete FROM gdsvalue.userprofile where iduserprofile = 'haerr.ec'' at line 1

    Tuesday, October 13, 2015 8:41 PM
  • User614698185 posted

    Hi shriramkarpur,

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete FROM gdsvalue.userprofile where iduserprofile = 'haerr.ec'' at line 1

    Firstly, check out your schema name is "gdsvalue".

    Secondly, make sure your where clause is correct.

    Best Regards,

    Candice Zhou

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 14, 2015 6:01 AM