locked
Can't get News_Edit to insert rows via stored procedure RRS feed

  • Question

  • User1603574069 posted

    I'm trying to modify the News_Edit.aspx page to use inserts via stored procedures when a person attempts to insert new announcements.

    I created a stored procedure called Insert_News:

     

    1    ALTER PROCEDURE dbo.Insert_News
    2    	@itemdate datetime,
    3    	@title varchar(500),
    4    	@description varchar(800),
    5    	@staticURL varchar(500),
    6    	@photo int,
    7 @sport int 8 AS 9 INSERT INTO Announcements 10 (itemdate, title, description, staticURL, photo, sport) 11 VALUES(@itemdate, @title, @description, @staticURL, @photo, @sport);  
    sport is a new column I've added to the Announcements tables to group each announcement into sports categories (basketball, baseball, etc).

    Next, I modified the SQLDatasource1 as follows:

     
    1        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"
    2            SelectCommand="SELECT id, itemdate, title, description, staticURL, photo, albumid from announcements where Announcements.id=@id"
    3            InsertCommand="Insert_News" InsertCommandType="StoredProcedure"
    4            UpdateCommand="UPDATE Announcements SET itemdate = @itemdate, title = @title, description = @description, staticURL = @staticURL, photo = @photo, albumid = @albumid WHERE (id = @id)"
    5            DeleteCommand="Delete from Announcements where id=@id" OldValuesParameterFormatString="{0}">
    6            <SelectParameters>
    7                <asp:QueryStringParameter Name="id" QueryStringField="ArticleID" />
    8            </SelectParameters>
    9            <InsertParameters>
    10               <asp:Parameter Name="itemdate" Type="DateTime" />
    11               <asp:Parameter Name="title" />
    12               <asp:Parameter Name="description" />
    13               <asp:Parameter Name="staticURL" />
    14               <asp:Parameter Name="photo" Type="Int32" />
    15               <asp:ControlParameter Name="sport" ControlID="ddlInsertSport" PropertyName="SelectedValue" />
    16           </InsertParameters>
    17           <UpdateParameters>
    18               <asp:Parameter Name="itemdate" Type="DateTime" />
    19               <asp:Parameter Name="title" />
    20               <asp:Parameter Name="description" />
    21               <asp:Parameter Name="staticURL" />
    22               <asp:Parameter Name="location" />
    23               <asp:Parameter Name="photo" />
    24               <asp:Parameter Name="id" />
    25               <asp:Parameter Name="albumid" />
    26           </UpdateParameters>
    27           <DeleteParameters>
    28               <asp:QueryStringParameter Name="id" QueryStringField="ArticleID" />
    29           </DeleteParameters>
    30       </asp:SqlDataSource>
    31   
    
    I've added a drop-down listbox into the FormView1 object in the Insert template. The name of the ddl is ddlInsertSport. It is linked to a table called Sports that contains two columns: sport_id and sport_name. The sport_id column links back to the sport column in the announcements table.

    When I go and attempt to add a new announcement and I click the Save Article button (which has a command of Insert assigned to it), nothing happens. I've tested the stored procedure by calling it directly and that works. It's only when I attempt to call it within the application that it doesn't work.

    Any ideas? This is very frustrating since I'm learning ASP.NET 2.0 and no books have given me any clear guidance on this.

    Thanks!

    Monday, February 12, 2007 10:12 PM

All replies

  • User1603574069 posted

    Okay, I figured it out. I went ahead and created a Inserted event handler for the data source object and read the e event args object to see what exception (if any) was be raised. It turns out that I forgot to remove some of the bound items in the .aspx page because I kept getting an exception that too many arguments were being passed into my stored procedure.

    Also, the other problem was the ddlInsertSport was not "bound" to anything, but since it was in a FormView object, I could not directly access it. I changed the parameter declaration to:

     

    <asp:ControlParameter Name="sport" ControlID="FormView1$ddlInsertSport" DefaultValue="SelectedValue" />

    Now it all works!

     

    Thursday, February 15, 2007 7:45 AM