Solved

Bind gridview to stored procedure in code behind

Posted on 2010-11-12
7
1,232 Views
Last Modified: 2012-05-10
I have a form with 4 dropdownlists and a gridview.  I also have a find button
Each dropdown is bound to it's own linqdatasource.
The gridview is not bound yet.
I need to filter by none to all of the dropdownlist selections, so I created a stored procedure with conditional where clause.
Now I need to know how to bind the grid to the stored procedure.
Everything I have tried returns an error on Bind.
How do I bind a grid to a stored procedure?
Thanks
###########ASPX#####################



<tr>

                    <td>Category</td>

                    <td>State</td>

                    <td>City</td>

                    <td>Salon Name</td>

                    <td>

                         &nbsp;

                    </td>

              </tr>

              <tr>

                    <td>   

                         <asp:DropDownList ID="ddCategory" Width="95%" runat="server" 

                              AutoPostBack="False" DataSourceID="lnqProfCategory" 

                             DataTextField="ProfCategory" DataValueField="ProfCategoryID" AppendDataBoundItems="True"  >

                             <asp:ListItem Value="-1">

                                 -- Select Category --

                            </asp:ListItem> 

                         </asp:DropDownList>                                

                         <asp:LinqDataSource ID="lnqProfCategory" runat="server" 

                             ContextTypeName="SalonDataClassesDataContext" OrderBy="ProfCategory" 

                             Select="new (ProfCategoryID, ProfCategory)" TableName="ProfCategories">

                         </asp:LinqDataSource>

                    </td>

                    <td>

                         <asp:DropDownList ID="ddState"  Width="95%" runat="server" AutoPostBack="True" 

                             DataSourceID="lnqStates" DataTextField="StateName" DataValueField="StateID" 

                            AppendDataBoundItems="True"  >

                             <asp:ListItem Value="-1">

                                 -- Select State --

                            </asp:ListItem> 

                         </asp:DropDownList>                                



                         <asp:LinqDataSource ID="lnqStates" runat="server"

                             ContextTypeName="SalonDataClassesDataContext" OrderBy="StateName" 

                             Select="new (StateID, StateName)" TableName="StatesLUs">

                         </asp:LinqDataSource>



                    </td>

                    <td>    

                         <asp:DropDownList ID="ddCity" Width="95%"  runat="server" AutoPostBack="True" 

                             DataSourceID="lnqCitys" DataTextField="CityName" DataValueField="CityID" 

                             AppendDataBoundItems="True"  >

                             <asp:ListItem Value="-1">

                                 -- Select City --

                            </asp:ListItem> 

                         </asp:DropDownList>                                

                         <asp:LinqDataSource ID="lnqCitys" runat="server" 

                             ContextTypeName="SalonDataClassesDataContext" OrderBy="CityName" 

                             Select="new (CityID, CityName)" TableName="CitysLUs" 

                             Where="StateID == @StateID">

                             <WhereParameters>

                                 <asp:ControlParameter ControlID="ddState" Name="StateID" 

                                     PropertyName="SelectedValue" Type="Int32" />

                             </WhereParameters>

                         </asp:LinqDataSource>

                    </td>

                    <td>

                         <asp:DropDownList ID="DDSalon" Width="95%" runat="server" DataSourceID="lnqStylist" AutoPostBack="False"

                             DataTextField="SalonName" DataValueField="StylistID" AppendDataBoundItems="True"  >

                             <asp:ListItem Value="-1">

                                 -- Select Salon --

                            </asp:ListItem> 

                         </asp:DropDownList>

                         

                         

                         <asp:LinqDataSource ID="lnqStylist" runat="server" 

                             ContextTypeName="SalonDataClassesDataContext" OrderBy="SalonName, FullName" 

                             Select="new (StylistID, SalonName, FullName)" TableName="Stylists" 

                             Where="ProfCategoryID == @ProfCategoryID &amp;&amp; StateID == @StateID &amp;&amp; CityID == @CityID">

                             <WhereParameters>

                                 <asp:ControlParameter ControlID="ddCategory" Name="ProfCategoryID" 

                                     PropertyName="SelectedValue" Type="Int32" />

                                 <asp:ControlParameter ControlID="ddState" Name="StateID" 

                                     PropertyName="SelectedValue" Type="Int32" />

                                 <asp:ControlParameter ControlID="ddCity" Name="CityID" 

                                     PropertyName="SelectedValue" Type="Int32" />

                             </WhereParameters>

                         </asp:LinqDataSource>

                         

                         

                    </td>      

                    <td>

                         <asp:Button ID="buttFind" runat="server" Text="Find" />

                    </td>                        

              </tr>

              

              <tr>

                    <td colspan="5">

                         &nbsp;



                         </td>                              

              

              </tr>

              <tr>

                    <td colspan="5">

                         

                         <asp:GridView ID="GridView1" Width="100%" runat="server" 

                              AutoGenerateColumns="False" DataKeyNames="StylistID" 

                              HeaderStyle-BackColor="DarkBlue" ShowFooter="True" 

                              HeaderStyle-ForeColor="White" AllowPaging="True" 

                              AllowSorting="True" BorderColor="LightGray" EmptyDataText="There are no providers in the area selected" 

                              >

                              <Columns>

                                   <asp:TemplateField HeaderText="Name">

                                  <ItemTemplate>

                                      <asp:Label ID="FullName" runat="server" 

                                          Text='<%# Bind("FullName") %>' />

                                  </ItemTemplate>

                                  </asp:TemplateField>

                                   <asp:TemplateField HeaderText="Prof. Category">

                                   <ItemTemplate >

                                   <asp:Label ID="Prof" runat="server" 

                                          Text='<%# Bind("ProfCategory") %>' />

                                   </ItemTemplate>

                                   </asp:TemplateField>

                                  

                                   <asp:TemplateField HeaderText="Salon">

                                   <ItemTemplate >

                                   <asp:Label ID="Label2" runat="server" 

                                          Text='<%# Bind("SalonName") %>' />

                                   </ItemTemplate>

                                   </asp:TemplateField>

                                   <asp:TemplateField HeaderText="Phone">

                                   <ItemTemplate >

                                   <asp:Label ID="Label1" runat="server" 

                                          Text='<%# Bind("PrimaryPhone") %>' />

                                   </ItemTemplate>

                                   </asp:TemplateField>

                                   <asp:HyperLinkField ControlStyle-CssClass="hyper" HeaderText="Email" 

                                       DataTextField="EmailAdd"  >

                                    <ControlStyle CssClass="hyper"></ControlStyle>

                                   </asp:HyperLinkField>

                                   <asp:ButtonField Text="View Bio" CommandName="buttViewBio" 

                                       HeaderText="View Bio" />

                              </Columns>



                                <HeaderStyle BackColor="DarkBlue" ForeColor="White"></HeaderStyle>

                              <AlternatingRowStyle BackColor="#FFFFCC" />

                         </asp:GridView>                       

                        <asp:LinqDataSource ID="LnqFindStylist" runat="server"

                        ContextTypeName="SalonDataClasses"



                        >

                        </asp:LinqDataSource>

                    </td>                  



############CODE BEHIND###############



    Protected Sub FillGrid()

        Dim sSalon As String = "Empty"

        Dim iProfCat As Integer = -1

        Dim iStateID As Integer = -1

        Dim iCityID As Integer = -1



        If Me.ddCategory.SelectedIndex > 0 Then

            iProfCat = Me.ddCategory.SelectedValue

        End If

        If Me.ddCity.SelectedIndex > 0 Then

            iCityID = Me.ddCity.SelectedValue

        End If

        If Me.ddState.SelectedIndex > 0 Then

            iStateID = Me.ddState.SelectedValue

        End If

        If Me.DDSalon.SelectedIndex > 0 Then

            sSalon = Me.DDSalon.SelectedItem.Text

        End If



     

        Using dc As New SalonDataClassesDataContext

            Me.LnqFindStylist.ContextTypeName = dc.stp_FindStylist(iProfCat, iStateID, iCityID, sSalon)

            Me.GridView1.DataSource = Me.LnqFindStylist

            Me.GridView1.DataBind()

        End Using





    End Sub





#############STORED PROCEDURE#################



ALTER PROCEDURE  [dbo].[stp_FindStylist]

  @profcategoryid int,

  @stateid int,

  @CityID int,

  @SalonName varchar(50)

as

begin







declare @sql varchar(8000)

declare @sqlWhere varchar(8000)



set @sqlWhere = ' WHERE dbo.Stylists.searchable = 1 ' 

if @profcategoryID > 0

begin

  set @sqlWhere = @sqlWhere + ' and dbo.Stylist.profcategoryid = ' + cast(@profcategoryID as char(10))

end 

if @stateID > 0

begin

  set @sqlWhere = @sqlWhere + ' and dbo.stylist.stateID = ' + cast(@stateID as char(10))	

end

if @CityID > 0

begin

  set @sqlWhere = @sqlWhere + ' and dbo.stylist.CityID = ' + cast(@CityID as char(10))

end



if @SalonName <> 'Empty'

begin

  set @sqlWhere = @sqlWhere + ' and dbo.stylist.SalonName = ' + @SalonName	

end





set @sql = 'SELECT dbo.Stylists.StylistID, dbo.ProfCategories.ProfCategory, dbo.Stylists.SalonName, 

			dbo.Stylists.FullName, dbo.Stylists.PrimaryPhone, dbo.Stylists.EmailAdd, dbo.Stylists.searchable

			FROM  dbo.ProfCategories INNER JOIN

            dbo.Stylists ON dbo.ProfCategories.ProfCategoryID = dbo.Stylists.ProfCategoryID '



set @sql = @sql + @sqlWhere + 

    ' ORDER BY 	dbo.Stylists.FullName, dbo.Stylists.SalonName'



--select @sql



exec( @sql )

Open in new window

0
Comment
Question by:Sheritlw
  • 5
  • 2
7 Comments
 
LVL 41

Assisted Solution

by:guru_sami
guru_sami earned 500 total points
ID: 34125869
What did you try and what is the error you are getting?
Can you try this:
Me.GridView1.DataSource = dc.stp_FindStylist(iProfCat, iStateID, iCityID, sSalon)
OR
Me.GridView1.DataSource = dc.stp_FindStylist(iProfCat, iStateID, iCityID, sSalon).ToList()

Open in new window

0
 

Author Comment

by:Sheritlw
ID: 34125960
I tried both.  My latest error is
DataBinding: 'stp_FindStylistResult' does not contain a property with the name 'StylistID'.

Stylist ID is included in the sp and I changed the exec( @sql ) to Select @sql in the stored procedure.

Thanks
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 34125967
can you adjust your select like this:
dbo.Stylists.StylistID as StylistID
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Sheritlw
ID: 34125977
sure, I will try now
0
 

Author Comment

by:Sheritlw
ID: 34125985
I now get the error
Data source is an invalid type.  It must be either an IListSource, IEnumerable, or IDataSource.

I don't have to tolist() option, so it is...
Me.GridView1.DataSource = dc.stp_FindStylist(iProfCat, iStateID, iCityID, sSalon)

Thanks
0
 

Accepted Solution

by:
Sheritlw earned 0 total points
ID: 34126096
I got it!!!
I had to set SET FMTONLY OFF; in my stored procedure.

Thanks for your help
0
 

Author Closing Comment

by:Sheritlw
ID: 34153397
I found solution and posted it
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Exceptions 3 40
C#.NET and microsoft certification. 3 34
VB.Net - Windows UserName index and length error 4 24
Code works but it's slow 24 47
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now