Solved

Bind gridview to stored procedure in code behind

Posted on 2010-11-12
7
1,236 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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 DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Read oracle BLOB in vb.net 2 24
SSIS Column mapping 5 40
Whice version VisualStudio for new Dev Laptop? 10 41
Datatable / Dates ? 4 31
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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