Filter ASP.Net GridView using SqlDataSource Error: Specified cast is not valid

Last Reply 4 months ago By dharmendr

Posted 4 months ago

I have a gridview whit some data. and I want to show another data instead of the RoomId field. I use the EditTemplate for do that. and this is my girdview code:

                <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px"
                    CellPadding="3" DataSourceID="EditCourseDB" Width="917px" DataKeyNames="LessonTitle" CssClass="Hedear-center" OnSelectedIndexChanged="GridView2_SelectedIndexChanged"
                    HorizontalAlign="Center" Font-Names="Tahoma" Visible="False" OnRowUpdated="GridView2_RowUpdated" OnRowDataBound="GridView2_RowDataBound" Font-Size="Smaller" Style="margin-right: 0px">
                    <Columns>
                        <asp:BoundField DataField="SubjectStudyId" HeaderText="SubjectStudyId" SortExpression="SubjectStudyId" HeaderStyle-CssClass="Hedear-center">
                        </asp:BoundField>
                        <asp:BoundField DataField="LessonTitle" HeaderText=" عنوان درس" SortExpression="LessonTitle" HeaderStyle-CssClass="Hedear-center">
                            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
                        </asp:BoundField>
                        <asp:BoundField DataField="CourseId" HeaderText="آی دی" SortExpression="CourseId" HeaderStyle-CssClass="Hedear-center">
                            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
                        </asp:BoundField>
                        <asp:BoundField DataField="ExamDate" HeaderText=" تاریخ آزمون " SortExpression="ExamDate" HeaderStyle-CssClass="Hedear-center">
                            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
                        </asp:BoundField>
                        <asp:BoundField DataField="ExamTime" HeaderText=" زمان آزمون" SortExpression="ExamTime" HeaderStyle-CssClass="Hedear-center">
                            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
                        </asp:BoundField>
                        <asp:TemplateField HeaderText=" محل آزمون" SortExpression="RoomId">
                            <ItemTemplate>
                                <asp:Literal ID="Literal1" runat="server" Text='<%# GetRoom(Eval("RoomId")) %>'></asp:Literal>
                            </ItemTemplate>
                            <HeaderStyle CssClass="Hedear-center" />
                        </asp:TemplateField>
                        <asp:CommandField SelectText="انتخاب" ShowSelectButton="True" />
                    </Columns>
                    <EmptyDataTemplate>
                        <span class="auto-style14"><strong>هیچ واحد درسی تعریف نشده است</strong></span>
                    </EmptyDataTemplate>                   
                </asp:GridView>

and I use this code for show another value for RoomId field:

        protected string GetRoom(Object o)
        {
            if ((byte)o == 1)
            {
                return " اتاق 101";
            }
            else if ((byte)o == 2)
            {
                return " اتاق 201";
            }
            else if ((byte)o == 3)
            {
                return " اتاق 202";
            }
            else if ((byte)o == 4)
            {
                return " سایت کامپیوتر ";
            }
            
            return "-1";
        }

the data type of RoomId field is tinyint.

but after that I want to run my code I faced to this error:

Is this error due to the use of the procedure?

this is my SqlDataSource:

<asp:SqlDataSource ID="EditCourseDB" runat="server" ConnectionString="<%$ ConnectionStrings:KDUIS-v1ConnectionString %>" 
    SelectCommand="EditCourse" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="LblSubjectStudyId" Name="SubjectStudyId" PropertyName="Text" Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

and this is my Procedure:

ALTER PROCEDURE [dbo].[EditCourse] @SubjectStudyId int
As
select *
from View_SubjectStudyCourse
where SubjectStudyId=@SubjectStudyId

 

Posted 4 months ago Modified on 4 months ago

Hi dorsa,

Check this example. Now please take its reference and correct your code.

SQL

CREATE TABLE EDU_SubjectStudyLesson_Course
(
	RoomId tinyint,
	SubjectStudyId int,
	CourseId int,
	LessonTitle VARCHAR(50),
	ExamDate DateTime,
	ExamTime DateTime
)
GO
INSERT INTO EDU_SubjectStudyLesson_Course VALUES(1, 1, 2, 'Lession 1', '2019/12/12', '10:00')
INSERT INTO EDU_SubjectStudyLesson_Course VALUES(2, 2, 5, 'Lession 2', '2019/12/11', '10:00')
INSERT INTO EDU_SubjectStudyLesson_Course VALUES(3, 6, 3, 'Lession 3', '2019/12/10', '10:00')
INSERT INTO EDU_SubjectStudyLesson_Course VALUES(4, 8, 6, 'Lession 4', '2019/12/15', '10:00')
INSERT INTO EDU_SubjectStudyLesson_Course VALUES(5, 8, 6, 'Lession 5', '2019/12/18', '10:00')
GO
CREATE PROCEDURE EditCourse
    @SubjectStudyId int
AS
SELECT *
FROM EDU_SubjectStudyLesson_Course
WHERE SubjectStudyId = @SubjectStudyId

HTML

Subject StudyId :
<asp:Label Text="8" ID="LblSubjectStudyId" runat="server" />
<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" BackColor="White"
    BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="3" Width="100%"
    DataKeyNames="LessonTitle" CssClass="Hedear-center" HorizontalAlign="Center"
    Font-Names="Tahoma" Font-Size="Smaller" Style="margin-right: 0px" DataSourceID="EditCourseDB">
    <Columns>
        <asp:BoundField DataField="SubjectStudyId" HeaderText="SubjectStudyId" SortExpression="SubjectStudyId"
            HeaderStyle-CssClass="Hedear-center">
            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
        </asp:BoundField>
        <asp:BoundField DataField="LessonTitle" HeaderText=" عنوان درس" SortExpression="LessonTitle"
            HeaderStyle-CssClass="Hedear-center">
            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
        </asp:BoundField>
        <asp:BoundField DataField="CourseId" HeaderText="آی دی" SortExpression="CourseId"
            HeaderStyle-CssClass="Hedear-center">
            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
        </asp:BoundField>
        <asp:BoundField DataField="ExamDate" HeaderText=" تاریخ آزمون " SortExpression="ExamDate"
            DataFormatString="{0:yyyy/MM/dd}" HeaderStyle-CssClass="Hedear-center">
            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
        </asp:BoundField>
        <asp:BoundField DataField="ExamTime" HeaderText=" زمان آزمون" SortExpression="ExamTime"
            DataFormatString="{0:HH:mm}" HeaderStyle-CssClass="Hedear-center">
            <HeaderStyle CssClass="Hedear-center"></HeaderStyle>
        </asp:BoundField>
        <asp:TemplateField HeaderText=" محل آزمون" SortExpression="RoomId">
            <ItemTemplate>
                <asp:Literal ID="Literal1" runat="server" Text='<%# GetRoom(Eval("RoomId")) %>'></asp:Literal>
            </ItemTemplate>
            <HeaderStyle CssClass="Hedear-center" />
        </asp:TemplateField>
        <asp:CommandField SelectText="انتخاب" ShowSelectButton="True" />
    </Columns>
    <EmptyDataTemplate>
        <span class="auto-style14"><strong>هیچ واحد درسی تعریف نشده است</strong></span>
    </EmptyDataTemplate>
    <FooterStyle BackColor="White" ForeColor="#000066" />
    <HeaderStyle BackColor="#006699" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="White" ForeColor="#000066" HorizontalAlign="Left" />
    <RowStyle ForeColor="#000066" />
    <SelectedRowStyle BackColor="#669999" Font-Bold="True" ForeColor="White" />
    <SortedAscendingCellStyle BackColor="#F1F1F1" />
    <SortedAscendingHeaderStyle BackColor="#007DBB" />
    <SortedDescendingCellStyle BackColor="#CAC9C9" />
    <SortedDescendingHeaderStyle BackColor="#00547E" />
</asp:GridView>
<asp:SqlDataSource ID="EditCourseDB" runat="server" ConnectionString="<%$ ConnectionStrings:TestConnectionString %>"
    SelectCommand="EditCourse" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="LblSubjectStudyId" Name="SubjectStudyId" PropertyName="Text"
            Type="Int32" />
    </SelectParameters>
</asp:SqlDataSource>

Code

C#

protected string GetRoom(Object o)
{
    if ((byte)o == 1)
    {
        return " اتاق 101";
    }
    else if ((byte)o == 2)
    {
        return " اتاق 201";
    }
    else if ((byte)o == 3)
    {
        return " اتاق 202";
    }
    else if ((byte)o == 4)
    {
        return " سایت کامپیوتر ";
    }
    return "-1";
}

VB.Net

Protected Function GetRoom(ByVal o As Object) As String
    If CByte(o) = 1 Then
        Return " اتاق 101"
    ElseIf CByte(o) = 2 Then
        Return " اتاق 201"
    ElseIf CByte(o) = 3 Then
        Return " اتاق 202"
    ElseIf CByte(o) = 4 Then
        Return " سایت کامپیوتر "
    End If
    Return "-1"
End Function

Screenshot