Update/Insert/Delete with stored procedures and SqlDataSource control

Thread is closed for posting
18 posts, 0 answers
  1. Telerik Admin
    Telerik Admin avatar
    1498 posts
    Member since:
    Oct 2004

    Posted 27 Mar 2007 Link to this post

    Requirements

    RadGrid for ASP .NET version

    RadControls for ASP .NET AJAX version

    4.x and later



    2007.3.1314 and later

    .NET version

    2.0 and later

    Visual Studio version

    2005 and later

    Programming language

    Codeless data-binding

    Browser support

    all supported by RadGrid for ASP .NET


    all browsers supported by RadControls for ASP .NET AJAX


      PROJECT DESCRIPTION
    This demo represents how to perform automatic operations (update/insert/delete) with SqlDataSource control (part of ASP.NET 2.0) and stored procedures. The main points are:
    • enable the automatic operations for the control
    • configure from design-time the update/insert/delete parameters of the SqlDataSource control
    • define the stored procedures in the underlying database passing the proper set of arguments to them

    Below is the code used for the stored prodecured in the sample web site:

    SelectEmployees  
     
    CREATE PROCEDURE SelectEmployees   
     
    AS 
     
    SELECT [EmployeeID], [LastName], [FirstName], [Title], [HireDate], [Address] FROM [Employees]  
    GO  
     
     
    UpdateEmployee  
     
    CREATE PROCEDURE UpdateEmployee   
    (@LastName char(20), @FirstName char(15), @Title char (15),   
    @HireDate datetime, @Address char(40), @EmployeeID int)  
     
    AS 
     
    UPDATE [Employees] SET [LastName] = @LastName, [FirstName] = @FirstName, [Title] = @Title, [HireDate] = @HireDate, [Address] = @Address WHERE [EmployeeID] = @EmployeeID  
    GO  
     
    InsertEmployee  
     
    CREATE PROCEDURE InsertEmployee   
    (@LastName char(20), @FirstName char(15), @Title char (15),  
    @HireDate datetime, @Address char(40))   
     
    AS 
     
    INSERT INTO [Employees] ([LastName], [FirstName], [Title], [HireDate], [Address]) VALUES (@LastName, @FirstName, @Title, @HireDate, @Address)  
    GO  
     
    DeleteEmployee  
     
    CREATE PROCEDURE DeleteEmployee  
    @EmployeeID int 
     
    AS 
     
    DELETE FROM [Employees] WHERE [EmployeeID] = @EmployeeID  
    GO 

    Note that you may need to modify the connection string (NorthwindConnectionString) in the web.config file of the web site to point to the Northwind SQL database. The database is present under the App_Data subfolder.

  2. mike
    mike avatar
    16 posts
    Member since:
    Apr 2007

    Posted 05 Apr 2007 Link to this post

    all the files for the 049321_AutomaticOperationsWithSqlDataSourceAndStoredProcedures.zip download appear to be password protected and so they cannot be extracted

  3. Sebastian
    Admin
    Sebastian avatar
    9802 posts
    Member since:
    Sep 2012

    Posted 06 Apr 2007 Link to this post

    Hi Mike,

    I double-checked on my machine whether the zip extraction is password protected. The files from the archive were extracted without the need to enter a password. Since I am not sure what causes the abnormality on your end, I sent you a mirror version of the project via email.

    Kind regards,
    Stephen
    the telerik team


    Instantly find answers to your questions at the new telerik Support Center

  4. maggi
    maggi avatar
    58 posts
    Member since:
    Mar 2007

    Posted 06 Apr 2007 Link to this post

    Hi Stephen,

        I tried extracting files from the zip file AutomaticOperationsWithSqlDataSourceAndStoredProcedures.zip
    on http://www.telerik.com/community/code-library/submission/b311D-tgcgc.aspx link but am unable to do so it shows error: Cannot open file: it does not appear to be valid archieve file. Could you please mail me the example I need it urgently.

    Thanks,
    maggi 

  5. Vlad
    Admin
    Vlad avatar
    11043 posts
    Member since:
    Sep 2012

    Posted 06 Apr 2007 Link to this post

    Hello Maggi,

    We just tried the link and it works - can you try again to download? I have sent you a version of the project via email as well.

    Best regards,
    Vlad
    the telerik team


    Instantly find answers to your questions at the new telerik Support Center

  6. Eddie Hernandez
    Eddie Hernandez avatar
    1 posts
    Member since:
    Feb 2007

    Posted 12 Apr 2007 Link to this post

    I need send a message information if the value entered is not valid and cancel operation of update.  How?

  7. Sebastian
    Admin
    Sebastian avatar
    9802 posts
    Member since:
    Sep 2012

    Posted 13 Apr 2007 Link to this post

    Hi Eddie,

    You can add a validator of your choice to the grid edit form and take advantage of the advanced server-side validation mechanism RadGrid exposes. Thus the update action will be cancelled when invalid input is entered by the user:

    http://www.telerik.com/demos/aspnet/Grid/Examples/DataEditing/Validation/DefaultCS.aspx

    Greetings,
    Stephen
    the telerik team


    Instantly find answers to your questions at the new telerik Support Center

  8. sgross
    sgross avatar
    1 posts
    Member since:
    Nov 2006

    Posted 29 Apr 2007 Link to this post

    Still a little new to ASP.NET...

    Where can I get 

    <add assembly="Microsoft.mshtml, Version=7.0.3300.0, 
        Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"
    /> 

    For this demo?

  9. Konstantin Petkov
    Admin
    Konstantin Petkov avatar
    1848 posts
    Member since:
    Sep 2012

    Posted 01 May 2007 Link to this post

    Hello sgross,

    Please, review the technical notes of the following Microsoft KB:

    http://support.microsoft.com/kb/311295

    Kind regards,
    Konstantin Petkov
    the telerik team


    Instantly find answers to your questions at the new Telerik Support Center

  10. cusumano
    cusumano avatar
    8 posts
    Member since:
    May 2007

    Posted 16 Sep 2007 Link to this post

    Hi again :)


    Thanx again for being so quick in answering back :)

    I've read your post about radgrid and stored procedures and I've made a small example to play with but unfortunatly something goes wrong..

    Here is my simple webform (sorry for the italian :P ) :

    <radG:RadGrid ID="rg_nazioni" runat="server" DataSourceID="sql_nazioni" AllowSorting="true" EnableAJAX="true"
        AllowAutomaticDeletes="true" AllowAutomaticInserts="true" AllowAutomaticUpdates="true" ShowStatusBar="true">
            <ClientSettings>
                <Scrolling AllowScroll="True" UseStaticHeaders="True" />
            </ClientSettings>
            <MasterTableView AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="sql_nazioni" CommandItemDisplay="TopAndBottom">
                <EditFormSettings>
                    <EditColumn CancelText="Cancella" EditText="Modifica" InsertText="Inserisci" UpdateText="Aggiorna">
                    </EditColumn>
                </EditFormSettings>
                <Columns>
                    <radG:GridEditCommandColumn UniqueName="EditCommandColumn" ButtonType="LinkButton" />
                    <radG:GridBoundColumn DataField="id" HeaderText="id" SortExpression="id" UniqueName="id" ReadOnly="true" />
                    <radG:GridBoundColumn DataField="c_cod" HeaderText="Codice nazione" SortExpression="c_cod" UniqueName="c_cod" />
                    <radG:GridBoundColumn DataField="c_cod_lingua" HeaderText="Traduzione" SortExpression="c_cod_lingua" UniqueName="c_cod_lingua"/>
                    <radG:GridBoundColumn DataField="c_nome" HeaderText="Nazione" SortExpression="c_nome" UniqueName="c_nome"/>
                    <radG:GridBoundColumn DataField="c_descrizione" HeaderText="Descrizione" SortExpression="c_descrizione" UniqueName="c_descrizione"/>
                    <radG:GridButtonColumn UniqueName="ButtonColumn" Text="Elimina" CommandName="Delete" ButtonType="linkButton"/>
                </Columns>
               
                <NoRecordsTemplate>
                    Nessuna nazione presente
                </NoRecordsTemplate>
            </MasterTableView>
        </radG:RadGrid>
            <asp:SqlDataSource ID="sql_nazioni" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommandType="StoredProcedure"
            SelectCommand="SP_RECUPERA_NAZIONI"
            InsertCommandType="StoredProcedure"
            InsertCommand="SP_INSERISCI_NAZIONE"
            UpdateCommandType="StoredProcedure"
            UpdateCommand="SP_MODIFICA_NAZIONE"
            DeleteCommandType="StoredProcedure"
            DeleteCommand="SP_ELIMINA_NAZIONE">
            <InsertParameters>
                <asp:Parameter Name="COD" Type="string" />
                <asp:Parameter Name="COD_LINGUA" Type="string" />
                <asp:Parameter Name="NOME" Type="string" />
                <asp:Parameter Name="DESCRIZIONE" Type="string" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="COD" Type="string" />
                <asp:Parameter Name="COD_LINGUA" Type="string" />
                <asp:Parameter Name="NOME" Type="string" />
                <asp:Parameter Name="DESCRIZIONE" Type="string" />
            </UpdateParameters>
        </asp:SqlDataSource>

    What hapens is that, when I click on Insert in the insert form I get an error saying that I've specified too many arguments for the insert stored procedure. I really can't see the error in this code any how even the stored procedure is simple:

    CREATE  PROCEDURE [dbo].[SP_INSERISCI_NAZIONE]
    @COD AS NVARCHAR(50),
    @COD_LINGUA AS NCHAR(3),
    @NOME AS NVARCHAR(50),
    @DESCRIZIONE AS TEXT
    AS
    INSERT INTO tb_nazioni (c_cod, c_cod_lingua, c_nome, c_descrizione)
    VALUES (@COD, @COD_LINGUA, @NOME, @DESCRIZIONE)
    GO

    Please help me out! :)


  11. Sebastian
    Admin
    Sebastian avatar
    9802 posts
    Member since:
    Sep 2012

    Posted 17 Sep 2007 Link to this post

    Hi cusumano,

    You may agree with me that this is a generic SQL/ASP.NET error which is out of the scope of our web grid. Nevertheless, I suggest you review the following posts in the ASP.NET forums which elaborate on the same subject and explain how to resolve the issue:

    http://forums.asp.net/p/898892/976553.aspx

    http://forums.asp.net/p/1096888/1658080.aspx

    Best regards,
    Stephen
    the Telerik team


    Instantly find answers to your questions at the new Telerik Support Center
     

  12. s_erbentraut
    s_erbentraut avatar
    7 posts
    Member since:
    Sep 2006

    Posted 12 Dec 2007 Link to this post

    How do you get this example to work if the parameter names are different in your Update stored procedure than the columns names that get bound to the grid from the select command.

    Basically, I can't figure out how to map the automatically extracted parameter names and map them to parameter names in the update SP if they are different. Is this possible, or do you have to have the exact same names across the board for this to work with the automatic updating?

  13. Yavor
    Admin
    Yavor avatar
    11 posts
    Member since:
    Dec 2013

    Posted 14 Dec 2007 Link to this post

    Hi s_erbentraut,

    I believe that the names of the paremeters may be different, for as long as you reference properly the database fields in the statement following the stored procedure declaration. You may also check to make sure that all fields in the database are of the proper type and that the fields all allow inserts. As for the select, update and insert commands, I believe that they must match in number and name, as mentioned in one of the previous posts:

    The COLUMNS produced by the select query MUST match in name and quantity with the input parameters of the UpdateCommand.  
    These select column names are used in the grid columns, and subsequently 'bound' to the named parameters in the Update command. So, essentially, 
    this error is resolved by modifying the stored procedures.  
     
    For example:  
     
     
    -----Select Query--------------------  
    sp_GetDealerGuideAssetsByDealerId] @nDealerId bigint, @nReturnCode bigint output, @nReturnId bigint output AS  
     
        SET @nReturnCode = 0 
        SET @nReturnId = 0 
        SELECT   
            ISNULL(tblDealerGuideAssets.[_DealerGuideAssetsId],0) as [nDealerGuideAssetsId],  
            ISNULL(tblDealerGuideAssets.[Model Size],0) as [nModelSize],          
            ISNULL(tblDealerGuideAssets.[Assets],0) as nAsset,  
            ISNULL(tblDealerGuideAssets.[_DealerId],0) as [nDealerId]  
            FROM  
            tblDealerGuideAssets  
            WHERE  
            (tblDealerGuideAssets.[_DealerId] = @nDealerId)  
            ORDER BY   
            tblDealerGuideAssets.[Model Size]  
     
        SET @nReturnCode = 0 
        SET @nReturnId = 0 
    -----Udate --------------------------  
    sp_EditDealerGuideAssetByDealerGuideAssetsId]   
    @nDealerGuideAssetsId bigint,   
    @nDealerId bigint,   
    @nModelSize bigint,   
    @nAsset money,   
    @nReturnCode bigint output,   
    @nReturnId bigint output AS  
     
        SET @nReturnCode = 0 
        SET @nReturnId = 0 
     
        DECLARE @m_nCt bigint  
     
     
        BEGIN  
            UPDATE   
                tblDealerGuideAssets  
                SET   
                [_DealerId] = @nDealerId,            [Model Size] = @nModelSize,            [Assets] = @nAsset  
                WHERE  
                ([_DealerGuideAssetsId] = @nDealerGuideAssetsId)  
                      
            SET @nReturnCode = 0 
            SET @nReturnId = @nDealerGuideAssetsId       
     
     
       
     
    two other points:  running the 'configure' wizard seems to reset the parameter directions.  I found it was necessary to edit the ASP to ensure that all the Direction="Input" and Direction="Output" were explicit  
     
     
       
     
    <%@ Page Language="vb" AutoEventWireup="false" MasterPageFile="~/Dealer.Master" CodeBehind="DealerAssets.aspx.vb" Inherits="hrs2.WebForm16"   
        title="Untitled Page" %> 
    <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">  
        <asp:Label ID="Label1" runat="server" Text="Label" Width="249px"></asp:Label>&nbsp;  
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRS Fast AccessConnectionString %>" 
            SelectCommand="sp_GetDealerAssetsByDealerId" SelectCommandType="StoredProcedure" 
            UpdateCommand="sp_EditDealerAssetByDealerGuideAssetsId" UpdateCommandType="StoredProcedure">  
            <UpdateParameters> 
                <asp:Parameter  Direction="Input" Name="nDealerGuideAssetsId" Type="Int64" /> 
                <asp:Parameter  Direction="Input" Name="nDealerId" Type="Int64" /> 
                <asp:Parameter  Direction="Input" Name="nModelSize" Type="Int64" /> 
                <asp:Parameter  Direction="Input" Name="nAsset" Type="Decimal" /> 
                <asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" /> 
                <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" /> 
            </UpdateParameters> 
            <SelectParameters> 
                <asp:Parameter Direction="Input" DefaultValue="24" Name="nDealerId" Type="Int64" /> 
                <asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" /> 
                <asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" /> 
            </SelectParameters> 
        </asp:SqlDataSource> 
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="nDealerGuideAssetsId,nDealerId">  
            <Columns> 
                <asp:CommandField ShowEditButton="True" /> 
                <asp:BoundField DataField="nDealerGuideAssetsId" HeaderText="nDealerGuideAssetsId"                ReadOnly="True" SortExpression="nDealerGuideAssetsId" /> 
                <asp:BoundField DataField="nDealerId" HeaderText="nDealerId" ReadOnly="True"                SortExpression="nDealerId" /> 
                <asp:BoundField DataField="nModelSize" HeaderText="nModelSize"                SortExpression="nModelSize" /> 
                <asp:BoundField DataField="nAsset" HeaderText="nAsset" ReadOnly="True" SortExpression="nAsset" /> 
            </Columns> 
        </asp:GridView> 
    </asp:Content> 
     
     

    I hope this information helps.

    Greetings,
    Yavor
    the Telerik team

    Instantly find answers to your questions at the new Telerik Support Center

  14. s_erbentraut
    s_erbentraut avatar
    7 posts
    Member since:
    Sep 2006

    Posted 14 Dec 2007 Link to this post

    Let me clarify ... I know that within the actual SP, that the input params can be named different than the fields in the table you are updating. I was saying, that whatever the names are for the fields that pull down with your Select command and are bound to the grid, those will be used automatically to create the parameter names that will be passed to your update SP (and probably an Insert, although I didn't try that). That means, you have to make the input params on your update (or insert) statement match the column names that you are pulling down in the select portion. I did further testing and seemed to confirm this behavior, although I really didn't find it clearly documented or described clearly in any user forms here on Telerik or other places on the net.

    This behavior is rather inconvenient when you have any level of complexity in your SPs where you are pulling from multiple tables in your select statement, or updating multiple tables in your Update command and your column names that you update don't match 1 -> 1 for the columns you need to update.

    The good news is that I did find a way around this! If you change your UpdateCommandType = "Text" instead of "Stored Procedure", you can then make an explicit call to your Update (or Insert) SP like this:

    Exec UpdateEmployee @LastName = @lname,
    @FirstName = @FirstName,
    @Title = @Title,
    @HireDate = @HireDate,
    @Address = @Address,
    @EmployeeID = @EmployeeID

    In the AutomaticOperationsWithSqlDataSourceAndStoredProcedure.zip example project, I simply changed the name on the SelectEmployees SP so that it aliased the ouput of the "LastName" column to "LName". Then note that in the syntax above (the update command), I explicitly assigned the input params that the SP will expect, to the params that the grid will generate and pass to the update command in the SqlDataSource.

    It is very important to note that on the "Command and Parameter Editor" screen, (accessed by right clicking the datasource, then click on "update query"), that all parameters be completely empty on the "Parameters" window.  Certain  operations with the wizard seem to regenerate those, I so I would just stay away from the sqldatasource "Configure Datasource" wizard and all should be well. I have the complete sample project, but don't see how to post it on this thread reply. If you tell me how, I would be glad to email it to you so it can be posted here for everyone's benefit.

    Thanks,
    Steve Erbentraut

  15. Yavor
    Admin
    Yavor avatar
    11 posts
    Member since:
    Dec 2013

    Posted 15 Dec 2007 Link to this post

    Hello Steve,

    Thank you for getting back to us and for the detailed
    It would be great to share your approach for everyone's benefit. You can do so either in this post, or better yet in the forums. If you would like to attach some code, you can use the code library, and open a new code library entry, and furnish any details, pertaining to your implementation, which may also be of interest. Needless to say, I will be happy to update your Telerik points for your involvement.

    Regards,
    Yavor
    the Telerik team


    Instantly find answers to your questions at the new Telerik Support Center

  16. s_erbentraut
    s_erbentraut avatar
    7 posts
    Member since:
    Sep 2006

    Posted 17 Dec 2007 Link to this post

    Ok, I just posted the project in the project in the source library, but I don't see it on that forum yet - does it need to get approved before it's publicly available?

  17. Yavor
    Admin
    Yavor avatar
    11 posts
    Member since:
    Dec 2013

    Posted 17 Dec 2007 Link to this post

    Hello,

    Indeed, it will need to be approved first. I will make sure that it is available shortly.

    Sincerely yours,
    Yavor
    the Telerik team


    Instantly find answers to your questions at the new Telerik Support Center

  18. KlimUser
    KlimUser avatar
    18 posts
    Member since:
    Apr 2008

    Posted 17 Dec 2008 Link to this post

    I just want to thank everyone who wrote on this topic, especially to s_erbentraut for his knowledge acquired. Has been very helpful!!!
    Thanks!!! and greetings From MX!!

Back to Top