locked
still problem with deleting using Gridview control RRS feed

  • Question

  • User73307320 posted

    I'm trying to use the gridview.


    My select stored proc uses a union statement to combine two tables and a table like the following


    NPA, NXX, Line, City, State

    201, 201, 1111, Jersey City, NJ

    201, 202, NULL, Jersey City, NJ

    333, 333, NULL, Houston, TX

    444, 444, 5555, Dallas, TX


    I use the first three columns (NPA, NXX, Line) as parameters for my Delete storedproc.


    The stored proc works fine.  It receives the 3 params and if the 3rd param (Line) is 0 then we delete a record from one table, if it is NOT 0 we delete a record from another table.


    The gridview works when I click the delete link on a row that has a Line value but I get the DBNull error when I click the delete link of a row with a NULL line value.


    What should the code look like to process that NULL and send it a value of  0 to the stored proc ?


    Here's my code


            <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 

                AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" 

                DataSourceID="SqlDataSource2" 

                EmptyDataText="There are no data records to display." ForeColor="#333333" 

                GridLines="None" Width="411px" DataKeyNames="NPA,NXX,Line">

                <AlternatingRowStyle BackColor="White" />

                <Columns>

                    <asp:CommandField ShowDeleteButton="True" />

                    <asp:BoundField DataField="NPA" HeaderText="NPA" ReadOnly="True" 

                        SortExpression="NPA" />

                    <asp:BoundField DataField="NXX" HeaderText="NXX" ReadOnly="True" 

                        SortExpression="NXX" />

                    <asp:BoundField DataField="Line" HeaderText="Line" ReadOnly="True" 

                        SortExpression="Line" />

                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />

                    <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />

                </Columns>

                <EditRowStyle BackColor="#7C6F57" />

                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />

                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />

                <RowStyle BackColor="#E3EAEB" />

                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />

                <SortedAscendingCellStyle BackColor="#F8FAFA" />

                <SortedAscendingHeaderStyle BackColor="#246B61" />

                <SortedDescendingCellStyle BackColor="#D4DFE1" />

                <SortedDescendingHeaderStyle BackColor="#15524A" />

            </asp:GridView>

            <asp:SqlDataSource ID="SqlDataSource2" runat="server" 

                ConnectionString="<%$ ConnectionStrings:InterviewConnectionString1 %>" 

                DeleteCommand="spDeleteAllowedNPANXXRecord" DeleteCommandType="StoredProcedure"

                ProviderName="<%$ ConnectionStrings:InterviewConnectionString1.ProviderName %>" 

                SelectCommand="spSelectCombinedAllowedList" SelectCommandType="StoredProcedure"

                OnDeleting="catchIt">

                <DeleteParameters>

                    <asp:Parameter Name="NPA" Type="Int16" />

                    <asp:Parameter Name="NXX" Type="Int16" />

                    <asp:Parameter Name="Line" DefaultValue="0" Type="Int16" />

                </DeleteParameters>

            </asp:SqlDataSource>


    and the codebehind page


        protected void catchIt(object sender, SqlDataSourceCommandEventArgs e)

        {

            //This doesn't work and I don't know how to do what I'm trying to do

            if(e.Command.Parameters[2].Value.ToString() == DBNull.Value.ToString())

            {

                e.Command.Parameters[2].Value = 0;


            }



    Friday, January 7, 2011 1:30 PM

Answers

  • User73307320 posted

    I got my answer.  I need to delete the parameter and then recreate it with the correct DBType.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 9, 2011 2:08 PM

All replies

  • User1843221445 posted

    well one pretty easy way to sove this would be to use IsNull function in your select

    Select Isnull(Line,0) would give 0 for all records which get null for LINE field

    Friday, January 7, 2011 2:48 PM
  • User-1412735316 posted

    Please change your code inside catchIt function as following  to have it worked properly. When "Line" parameter is null then only two parameter "NPA" and "NXX" is created to supply the stored procedure while it is expecting three parameters, so that the error occures. So I jst added an extra parameter to the command with the name of "@Line" and value "0". Hope you got your answer.

    protected void catchIt(object sender, SqlDataSourceCommandEventArgs e)    

    { if (e.Command.Parameters.Count == 2)

       e.Command.Parameters.Add(new SqlParameter("@Line", "0"));

    }


    Friday, January 7, 2011 3:02 PM
  • User73307320 posted

    Shuvo
    that syntax didn't work it underlined SqlParameter saying "the type or namespace couldn't be found)
    dr magk
    that would probably work but doesn't seem to be a "best practices" solution

    Friday, January 7, 2011 4:40 PM
  • User73307320 posted

    okay so I needed to add using System.Data.SqlClient; to my code page
    but now I get "object cannot be cast from DBNull to other types"
    So I guess that param is THERE its just a type DBNull. SOOO what is the command to change the DBType of that param????

    Friday, January 7, 2011 5:02 PM
  • User-1412735316 posted

    Ok then review my codes.

    <%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" 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>Untitled Page</title>

    </head>

    <body>

        <form id="form1" runat="server">

        <div>

               <asp:GridView ID="GridView1" runat="server" AllowPaging="True" 


                AllowSorting="True" AutoGenerateColumns="False" CellPadding="4" 


                DataSourceID="SqlDataSource2" 


                EmptyDataText="There are no data records to display." ForeColor="#333333" 


                GridLines="None" Width="411px" DataKeyNames="NPA,NXX">


                <AlternatingRowStyle BackColor="White" />


                <Columns>


                    <asp:CommandField ShowDeleteButton="True" />


                    <asp:BoundField DataField="NPA" HeaderText="NPA" ReadOnly="True" 


                        SortExpression="NPA" />


                    <asp:BoundField DataField="NXX" HeaderText="NXX" ReadOnly="True" 


                        SortExpression="NXX" />


                    <asp:BoundField DataField="Line" HeaderText="Line" ReadOnly="True" 


                        SortExpression="Line" />


                    <asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />


                    <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />


                </Columns>


                <EditRowStyle BackColor="#7C6F57" />


                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />


                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />


                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />


                <RowStyle BackColor="#E3EAEB" />


                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />


               <%-- <SortedAscendingCellStyle BackColor="#F8FAFA" />


                <SortedAscendingHeaderStyle BackColor="#246B61" />


                <SortedDescendingCellStyle BackColor="#D4DFE1" />


                <SortedDescendingHeaderStyle BackColor="#15524A" />--%>


            </asp:GridView>


            <asp:SqlDataSource ID="SqlDataSource2" runat="server" 


                ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 


                DeleteCommand="spDeleteAllowedNPANXXRecord" DeleteCommandType="StoredProcedure"


                ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 


                SelectCommand="spSelectCombinedAllowedList" SelectCommandType="StoredProcedure" OnDeleting="CatchIT">


                <DeleteParameters>


                    <asp:Parameter Name="NPA" Type="Int16" />


                    <asp:Parameter Name="NXX" Type="Int16" />

                    

                    <asp:Parameter Name="Line" Type="Int16" />


                </DeleteParameters>


            </asp:SqlDataSource>

        </div>

        </form>

    </body>

    </html>



    Code Behind Page :

    using System;
    using System.Data;
    using System.Configuration;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using System.Data.SqlClient;

    public partial class _Default : System.Web.UI.Page 
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void CatchIT(object sender, SqlDataSourceCommandEventArgs e)
        {
            if (e.Command.Parameters.Count == 2)
                e.Command.Parameters.Add(new SqlParameter("@Line", "0"));
        }
    }


    Stored Procedure:

    Crete PROCEDURE [dbo].[spDeleteAllowedNPANXXRecord]
    @NPA int, @NXX int, @Line int
    AS
    BEGIN

    if(@Line=0) delete from test where NPA=@NPA and NXX=@NXX 
    else
    delete from test where NPA=@NPA and NXX=@NXX and Line=@Line

    END

    Hope from the above you got your solution. if you still facing probs please send me your procedures and page.

    Saturday, January 8, 2011 5:26 AM
  • User73307320 posted

    That doesn't work.

    I get the error:  Object cannot be cast from DBNull to other types. 


    I think I get that because Parameter @Line is already created as a type DBNull.   That's the precise problem I'm having.  I have to find a way to change the type of parameter @Line.  If I could figure that out (if it's possible) then I would be golden.



    Saturday, January 8, 2011 12:47 PM
  • User-1412735316 posted

    please provide me your sps, table script , sample data and aspx page with code behind page. I have tested your code with slight changes and it's working here at my end i may able to solve your current problem too. trust me.

    Saturday, January 8, 2011 1:40 PM
  • User1843221445 posted

    have you tried using IsNull() in select , i think that would be better aproach since you already wan to treat NULL a 0 

    What doyou say ? 

    Sunday, January 9, 2011 4:03 AM
  • User-1412735316 posted

    Hello, Plese provides me the information i have asked before. I m here to help you find the answer.

    Sunday, January 9, 2011 11:41 AM
  • User73307320 posted

    I got my answer.  I need to delete the parameter and then recreate it with the correct DBType.


    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 9, 2011 2:08 PM
  • User-1412735316 posted

    Nice to hear. Don't I deserve "mark as ANSWER" ??? Thanks in advance.


    Sunday, January 9, 2011 2:17 PM
  • User73307320 posted
    Sorry. No. I got the answer from a different forum. I got 3 or 4 answers from everybody but only one was correct.
    Sunday, January 9, 2011 2:27 PM
  • User-1412735316 posted

    Ok. no objection. but i have given you codes which are fully workable. Since you haven't share your code and my codes (totally written for you) are doing well don't i deserve it?

    You had a question and i answer it. may be there could be possibly better answer but it's still an answer within this post.

    Thanks in advance for whatever you decide to do (mark as answer or not). have a nice day.


    Sunday, January 9, 2011 2:58 PM