• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1876
  • Last Modified:

multiple parameters to gridview

hi there; i am trying to populate a gridview with one of two parameters passed from this page:

Protected Sub RedirectUser(ByVal sender As System.Object, ByVal e As System.EventArgs)
           
            Dim strCompany = Trim(txtCompany.Text)
            Dim intCityID = ddlCity.SelectedItem.Value
           
            If strCompany = "" Then                
                 Session("CityID") = intCityID
            Else
                Session("Company") = strCompany
            End If
           
            Response.Redirect("results.aspx")
           
        End Sub

i have posted my aspx code below as well as the stored procedure.

when i run this stored procedure in visual studio express, and am prompted for a value, i get the desired results (i.e. i am prompted for an @cityID and a @company.  if i insert just a company and leave the cityID=NULL, then the company is displayed.  if i insert just a cityID and leave the company=NULL then a list of cities is displayed).  however, when i run my page in visual studio and enter a value in one of the two controls, then the result page (the one with the gridview and uses the stored procedure) displays nothing.

here is my stored procedure:

ALTER PROCEDURE sp_Select_Company
(@company varchar(50),
@cityID int)
AS

SELECT        tblCompany.fldCompanyName, tblCompany.fldCompanyDescription, tblCompanyPhoto.fldCompanyPhotoName, tblCompanyPhoto.fldCompanyPhotoTag,
                         tblCompanyPhoto.fldCompanyPhotoDefault, tblCompany.fldCompanyAlias, tblCity.fldCity, tblProvince.fldProvinceAbb,
                         tblCompany.fldCompanyID AS fldCompanyID
FROM            tblCity INNER JOIN
                         tblProvince ON tblCity.fldProvinceID = tblProvince.fldProvinceID RIGHT OUTER JOIN
                         tblCompany ON tblCity.fldCityID = tblCompany.fldCityID LEFT OUTER JOIN
                         tblCompanyPhoto ON tblCompany.fldCompanyID = tblCompanyPhoto.fldCompanyID
WHERE        ((tblCompanyPhoto.fldCompanyPhotoDefault = 1 OR
                         tblCompanyPhoto.fldCompanyPhotoDefault IS NULL) AND (tblCompany.fldCompanyName LIKE N'%' + @company + '%')) OR
                         (tblCity.fldCityID = @cityID AND (tblCompanyPhoto.fldCompanyPhotoDefault = 1 OR
                         tblCompanyPhoto.fldCompanyPhotoDefault IS NULL) AND tblCompany.fldIsVisible = 1)
ORDER BY tblCompany.fldCompanyName

and my gridview:

<asp:GridView
    Width="100%"
    id="gvCompanyResults"
    onrowdatabound="gvCompanyResults_RowDataBound"
    OnRowCommand="gvCompanyResults_OnRowCommand"
    runat="server"
    AutoGenerateColumns="False"
    EmptyDataText="There are no companies that match your search."
    DataSourceID="SqlDataSource1"
    ForeColor="#333333"
    CellPadding="4"
    GridLines="None"
    allowpaging="True"
    allowsorting="True">
<FooterStyle BackColor="#990000" ForeColor="White" Font-Bold="True"></FooterStyle>

<RowStyle BackColor="#FFFBD6" ForeColor="#333333"></RowStyle>

<SelectedRowStyle BackColor="#FFCC66" ForeColor="Navy" Font-Bold="True"></SelectedRowStyle>

<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center"></PagerStyle>

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

<AlternatingRowStyle BackColor="White"></AlternatingRowStyle>

<Columns>
<asp:TemplateField HeaderText="Photo" SortExpression="fldCompanyPhotoName"><ItemTemplate>
<div class="thumbnail">
<asp:ImageButton id="ibCompanyPhoto" CommandArgument='<%# Eval("fldCompanyID") %>'  Height="100" CommandName="thumbnailLink" runat="server" ImageUrl='<%# "images/company/" & Eval("fldCompanyID") & "/" & Eval("fldCompanyPhotoName") %>'></asp:ImageButton>
<span style="width:254px;"><asp:Image ID="imgCompanyPhotoPopUp" Width="250" runat="server" /><br /><asp:Label ID="lblCompanyPhotoTag" CssClass="tag" runat="server" /></span>
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Company" SortExpression="fldCompanyName"><ItemTemplate>
<asp:Label id="lblCompany" runat="server" Text='<%# Bind("fldCompanyName") %>'></asp:Label>&nbsp;(aka <asp:Label id="lblCompanyAlias" runat="server" Text='<%# Eval("fldCompanyAlias") %>'></asp:Label>)
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Location" SortExpression="fldCity"><ItemTemplate>
<asp:Label id="lblCity" runat="server" Text='<%# Bind("fldCity") %>'/>, <asp:Label id="lblProvinceAbb" runat="server" Text='<%# Bind("fldProvinceAbb") %>'/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

<asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myCS %>" SelectCommand="sp_Select_Company" selectcommandtype="StoredProcedure">
<SelectParameters>
<asp:SessionParameter Name="company" SessionField="Company" Size="10" Type="String" />
<asp:SessionParameter DefaultValue="" Name="cityID" SessionField="CityID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>

any suggestions?  thanks all.
0
vbnewbie01
Asked:
vbnewbie01
  • 9
  • 5
1 Solution
 
Ashutosh VyasFounder, InitQubeCommented:
I have not seen the code but from the explanation you gave out in the text, I presume that you leave the textbox blank for the field that you want to leave blank. This would never assign the null value to the parameters. You can rather use

System.DBNull.Value

in place of the blank textbox and see if you get the desired results.
0
 
vbnewbie01Author Commented:
hi, ashutosh9910; thanks for your response.  

if you take a look at the code, technically, if the textbox is empty, then no value is passed anyway.

this considered, in my stored procedure, i have set my default values to NULL:

ALTER PROCEDURE sp_Select_Company
(@company varchar(50) = Null,
@cityID int = Null)
AS ...

this, unfortunately, does not work either.

any other suggestions?
0
 
Bob LearnedCommented:
Try this:

       Protected Sub RedirectUser(ByVal sender As System.Object, ByVal e As System.EventArgs)
           
            Dim strCompany = Trim(txtCompany.Text)
            Dim intCityID = ddlCity.SelectedItem.Value
           
            If strCompany.Trim().Length = 0 Then                
                 Session("CityID") = intCityID
                 Session.Remove("Company")
            Else
                Session("Company") = strCompany
                 Session.Remove("CityID")
            End If
           
            Response.Redirect("results.aspx")
           
        End Sub

Bob
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
vbnewbie01Author Commented:
hi, TheLearnedOne; i actually already have this (except i had removed the sessions first, rather than within the conditions) but chose not to include it in my original code above since i don't think it has anything to do with the solution :

            Session.Remove("CityID")
            Session.Remove("Company")
           
            Dim strCompany = Trim(txtCompany.Text)
            Dim intCityID = ddlCity.SelectedItem.Value
           
           
            If strCompany = "" Then
                Session("CityID") = intCityID
            Else
                Session("Company") = strCompany            
            End If
           
            Response.Redirect("results.aspx")

the reason i'm actually removing the sessions, is so that neither session will retain their value if the user returns to the search page and chooses to do another search.

any other suggestions?  thanks again.
0
 
Bob LearnedCommented:
I don't understand what you just said.  Are you saying that removing session values didn't help your problem, or are you saying that it won't help your problem (without trying it)?

Bob
0
 
vbnewbie01Author Commented:
hi, TheLearnedOne; i was saying that i'm already removing the sessions (see my last post) and it doesn't work.  and, yes, i tried removing the sessions within the conditions (as you suggested) and it doesn't work.

maybe i'm not explaining the issue clearly.

index.aspx has a textbox and a dropdownlist.  the user chooses one or the other to search.  the value of the particular search is stored in one of the session variables and redirected to results.aspx where the session variable is retrieved by the gridview and passed to a stored procedure (see original post code).

this isn't displaying any results when run as an aspx page yet displays the desired results just fine when a query is run within visual studio express.

hope that helps clear it up.  thanks again.
0
 
Bob LearnedCommented:
With the SessionParameter, there is the ConvertEmptyStringToNull property:

                <asp:SessionParameter Name="company" SessionField="Company" Size="10" Type="String" ConvertEmptyStringToNull="true"  />
                <asp:SessionParameter DefaultValue="" Name="cityID" SessionField="CityID" Type="Int32" ConvertEmptyStringToNull="true" />

Bob
0
 
vbnewbie01Author Commented:
hi, bob; thank you but, unfortunately, that doesn't work either.

0
 
Bob LearnedCommented:
Are you still removing the Session variables, or setting them to empty string?

Bob
0
 
vbnewbie01Author Commented:
hi, bob; i am initially removing the session variables then resetting them based on the uses search method .... here is my current code:

index.aspx:

Protected Sub RedirectUser(ByVal sender As System.Object, ByVal e As System.EventArgs)
           
            Session.Remove("CityID")
            Session.Remove("Company")
           
            Dim strCompany = Trim(txtCompany.Text)
            Dim intCityID = ddlCity.SelectedItem.Value
           
           
            If strCompany = "" Then
                Session("CityID") = intCityID
            Else
                Session("Company") = strCompany
            End If
           
            Response.Redirect("results.aspx")
           
           
End Sub


results.aspx:

<asp:GridView
    Width="100%"
    id="gvCompanyResults"
    onrowdatabound="gvCompanyResults_RowDataBound"
    OnRowCommand="gvCompanyResults_OnRowCommand"
    runat="server"
    AutoGenerateColumns="False"
    EmptyDataText="There are no companies that match your search."
    DataSourceID="SqlDataSource1"
    ForeColor="#333333"
    CellPadding="4"
    GridLines="None"
    allowpaging="True"
    allowsorting="True">
<FooterStyle BackColor="#990000" ForeColor="White" Font-Bold="True"></FooterStyle>

<RowStyle BackColor="#FFFBD6" ForeColor="#333333"></RowStyle>

<SelectedRowStyle BackColor="#FFCC66" ForeColor="Navy" Font-Bold="True"></SelectedRowStyle>

<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center"></PagerStyle>

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

<AlternatingRowStyle BackColor="White"></AlternatingRowStyle>

<Columns>
<asp:TemplateField HeaderText="Photo" SortExpression="fldCompanyPhotoName">
<ItemTemplate>
<div class="thumbnail">
<asp:ImageButton id="ibCompanyPhoto" CommandArgument='<%# Eval("fldCompanyID") %>'  Height="100" CommandName="thumbnailLink" runat="server" ImageUrl='<%# "images/company/" & Eval("fldCompanyID") & "/" & Eval("fldCompanyPhotoName") %>'></asp:ImageButton>
<span style="width:254px;"><asp:Image ID="imgCompanyPhotoPopUp" Width="250" runat="server" /><br /><asp:Label ID="lblCompanyPhotoTag" CssClass="tag" runat="server" /></span>
</div>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Company" SortExpression="fldCompanyName"><ItemTemplate>
<asp:Label id="lblCompany" runat="server" Text='<%# Bind("fldCompanyName") %>'></asp:Label>&nbsp;(aka <asp:Label id="lblCompanyAlias" runat="server" Text='<%# Eval("fldCompanyAlias") %>'></asp:Label>)
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Location" SortExpression="fldCity"><ItemTemplate>
<asp:Label id="lblCity" runat="server" Text='<%# Bind("fldCity") %>'/>, <asp:Label id="lblProvinceAbb" runat="server" Text='<%# Bind("fldProvinceAbb") %>'/>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

<asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:chowdownCS %>" SelectCommand="sp_Select_Company" selectcommandtype="StoredProcedure">
<SelectParameters>
    <asp:SessionParameter Name="company" SessionField="Company" Size="10" Type="String" ConvertEmptyStringToNull="true"  />
    <asp:SessionParameter DefaultValue="" Name="cityID" SessionField="CityID" Type="Int32" ConvertEmptyStringToNull="true" />
</SelectParameters>

</asp:SqlDataSource>


stored procedure:

ALTER PROCEDURE sp_Select_Company
(@company varchar(50) = Null,
@cityID int = Null)
AS

SELECT        tblCompany.fldCompanyName, tblCompany.fldCompanyDescription, tblCompanyPhoto.fldCompanyPhotoName, tblCompanyPhoto.fldCompanyPhotoTag,
                         tblCompanyPhoto.fldCompanyPhotoDefault, tblCompany.fldCompanyAlias, tblCity.fldCity, tblProvince.fldProvinceAbb,
                         tblCompany.fldCompanyID AS fldCompanyID
FROM            tblCity INNER JOIN
                         tblProvince ON tblCity.fldProvinceID = tblProvince.fldProvinceID RIGHT OUTER JOIN
                         tblCompany ON tblCity.fldCityID = tblCompany.fldCityID LEFT OUTER JOIN
                         tblCompanyPhoto ON tblCompany.fldCompanyID = tblCompanyPhoto.fldCompanyID
WHERE        ((tblCompanyPhoto.fldCompanyPhotoDefault = 1 OR
                         tblCompanyPhoto.fldCompanyPhotoDefault IS NULL) AND (tblCompany.fldCompanyName LIKE N'%' + @company + '%')) OR
                         (tblCity.fldCityID = @cityID AND (tblCompanyPhoto.fldCompanyPhotoDefault = 1 OR
                         tblCompanyPhoto.fldCompanyPhotoDefault IS NULL) AND tblCompany.fldIsVisible = 1)
ORDER BY tblCompany.fldCompanyName
0
 
Bob LearnedCommented:
I think you need this with the ConvertEmptyStringToNull:

            If strCompany = "" Then
                Session("CityID") = intCityID
                Session("Company") = String.Empty
            Else
                Session("CityID") = String.Empty
                Session("Company") = strCompany
            End If

Bob
0
 
vbnewbie01Author Commented:
nope.  same result.  no display for each.
0
 
vbnewbie01Author Commented:
just to take it back to basics, i created a datagrid (asp.net 1.x) and called/displayed it from the page load event.

Dim myConnection As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("chowdownCS").ToString)
       
        Dim myCommand As New SqlCommand("sp_Select_Company", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
       
       
        If Session("Company") = String.Empty Then
            myCommand.Parameters.Add("@cityID", SqlDbType.Int).Value = Session("CityID")
        Else
            myCommand.Parameters.Add("@company", SqlDbType.VarChar).Value = Session("Company")
        End If
       
       
        Dim myAdapter As New SqlDataAdapter(myCommand)
        Dim myDataSet As New DataSet()
        myAdapter.Fill(myDataSet)
       
        dgResults.DataSource = myDataSet
        dgResults.DataBind()
       
        myConnection.Close()

i kept the gridview on the page (underneath the datagrid) and when i ran the page, the datagrid displayed both searches fine (cityID and company) but, as usual, the gridview only displayed the company.

anyway, i am not going to use the datagrid but rather posted it for assistance reference.  i'm pretty confused as to why the gridview isn't populating.  
0
 
vbnewbie01Author Commented:
i posted this question on the asp.net website forum and the solution is to add the "CancelSelectOnNullParameter" property set to true in you sqldatasource.

to quote: "Check if you have the "CancelSelectOnNullParameter" property set to true in you sqldatasource. If this property is true (and it is by default), your query will not even run if any of the parameter is null."

here is the final working code below:

index.aspx:

Protected Sub RedirectUser(ByVal sender As System.Object, ByVal e As System.EventArgs)
 

Session.Remove("CityID")Session.Remove("Company")
 

 

Dim strCompany = Trim(txtCompany.Text)Dim intCityID = ddlCity.SelectedItem.Value
 

 

If strCompany = "" Then

Session("CityID") = intCityID
Else

Session("Company") = strCompany
End If

 

Response.Redirect("results.aspx")
 

 

End Sub

 

results.aspx:

<asp:GridView
Width="100%"

id="gvCompanyResults"
onrowdatabound="gvCompanyResults_RowDataBound"

OnRowCommand="gvCompanyResults_OnRowCommand"
runat="server"

AutoGenerateColumns="False"
EmptyDataText="There are no companies that match your search."

DataSourceID="SqlDataSource1"
ForeColor="#333333"

CellPadding="4"
GridLines="None"

allowpaging="True"
allowsorting="True">

<FooterStyle BackColor="#990000" ForeColor="White" Font-Bold="True"></FooterStyle>
<RowStyle BackColor="#FFFBD6" ForeColor="#333333"></RowStyle>

<SelectedRowStyle BackColor="#FFCC66" ForeColor="Navy" Font-Bold="True"></SelectedRowStyle>
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center"></PagerStyle>

<HeaderStyle BackColor="#990000" ForeColor="White" Font-Bold="True"></HeaderStyle>
<AlternatingRowStyle BackColor="White"></AlternatingRowStyle>

<Columns>
<asp:TemplateField HeaderText="Photo" SortExpression="fldCompanyPhotoName">

<ItemTemplate>
<div class="thumbnail">

<asp:ImageButton id="ibCompanyPhoto" CommandArgument='<%# Eval("fldCompanyID") %>' Height="100" CommandName="thumbnailLink" runat="server" ImageUrl='<%# "images/company/" & Eval("fldCompanyID") & "/" & Eval("fldCompanyPhotoName") %>'></asp:ImageButton>

<span style="width:254px;"><asp:Image ID="imgCompanyPhotoPopUp" Width="250" runat="server" /><br /><asp:Label ID="lblCompanyPhotoTag" CssClass="tag" runat="server" /></span>
</div>

</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Company" SortExpression="fldCompanyName"><ItemTemplate>
<asp:Label id="lblCompany" runat="server" Text='<%# Bind("fldCompanyName") %>'></asp:Label>&nbsp;(aka <asp:Label id="lblCompanyAlias" runat="server" Text='<%# Eval("fldCompanyAlias") %>'></asp:Label>)

</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText="Location" SortExpression="fldCity"><ItemTemplate>
<asp:Label id="lblCity" runat="server" Text='<%# Bind("fldCity") %>'/>, <asp:Label id="lblProvinceAbb" runat="server" Text='<%# Bind("fldProvinceAbb") %>'/>

</ItemTemplate>
</asp:TemplateField>

</Columns>
</asp:GridView>

<asp:SqlDataSource CancelSelectOnNullParameter="false" id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myCS %>" SelectCommand="sp_Select_Company" selectcommandtype="StoredProcedure">
<SelectParameters>

<asp:SessionParameter Name="company" SessionField="Company" Size="10" Type="String"/>

<asp:SessionParameter Name="cityID" SessionField="CityID" Type="Int32"/>
</SelectParameters>

</asp:SqlDataSource>


0
 
vbnewbie01Author Commented:
i have requested that this question be closed, thank you very much for your time and great suggestions!!!  much appreciated!!!!
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now