从数据库筛选到ASP.NET SQL Server的gridview [英] filtering from a database to a gridview asp.net sql server

问题描述

我的项目有问题.我有一些下拉列表，用户可以过滤掉gridview中的数据.因此，在筛选器按钮上，单击我希望它访问数据库存储过程并带回所需的数据".

到目前为止，这是我的代码.和存储过程，以及GridView的html，以防万一它也出错.

I am having problems with my project. I have a few dropdownlists which a user can filter the data that is in the gridview. So on filter button click I want it to access the database stored procedure and bring back the required data.

Here is my code so far. And Stored Procedure, and html for the gridview just in case its wrong too.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Text;
using System.Configuration;

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

    }
   public string FormatURL(object strArgument)
    {
        return ("readrealimage.aspx?id=" + strArgument);
    }
   public void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
   {

   }
   protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
   {
   }
   protected void btnFilter_Click(object sender, EventArgs e)
   {
       SqlConnection MyConnection = new
    SqlConnection(ConfigurationManager.ConnectionStrings
     ["MyConnectionString"].
     ConnectionString);

   SqlCommand command = new SqlCommand("sp_SearchAnimals", MyConnection);

   if (AnimalTypeDDL.Text != "Dont Mind")
   {
       AnimalTypeDDL.Text = command.Parameters["@Type_of_Animal"].Value.ToString();
   }
   if (CrossBreedDDL.Text != "Dont Mind")
   {
       CrossBreedDDL.Text = command.Parameters["@CrossBreed"].Value.ToString();
   }
   if (SexDDL.Text != "Dont Mind")
   {
       SexDDL.Text = command.Parameters["@Sex"].Value.ToString();
   }
   if (SizeDDL.Text != "Dont Mind")
   {
       SizeDDL.Text = command.Parameters["@Size"].Value.ToString();
   }
   if (AgeDDL.Text != "Dont Mind")
   {
       AgeDDL.Text = command.Parameters["@Age"].Value.ToString();
   }
   if (LocationDDL.Text != "Dont Mind")
   {
       LocationDDL.Text = command.Parameters["@Location"].Value.ToString();
   }
   if (RescueDDL.Text != "Dont Mind")
   {
       RescueDDL.Text = command.Parameters["@Name"].Value.ToString();
   }
   if (ChildrenDDL.Text != "Select one")
   {
       ChildrenDDL.Text = command.Parameters["@Children"].Value.ToString();
   }
   if (OtherCatsDDL.Text != "Select one")
   {
       OtherCatsDDL.Text = command.Parameters["@OtherCats"].Value.ToString();
   }
   if (OtherDogsDDL.Text != "Select one")
   {
       OtherDogsDDL.Text = command.Parameters["@OtherDogs"].Value.ToString();
   }
   GridView1.DataSource = ds;
   GridView1.DataBind();

   if (GridView1.Rows.Count >= 1)
   {
       Panel1.Visible = true;
       GridView1.Visible = true;
       lblMsg.Visible = false;

   }
   else if (GridView1.Rows.Count < 1)
   {
       GridView1.Visible = false;
       Panel1.Visible = false;
       lblMsg.Text = "Your search criteria returned no results.";
       lblMsg.Visible = true;
   }
   command.CommandType = CommandType.Text;
   command.Connection.Open();
   SqlDataReader MyDataReader = command.ExecuteReader();
   MyConnection.Close();
   }
   protected void buttonClear_Click(object sender, EventArgs e)
  {
   AnimalTypeDDL.Text = "Don''t Mind";
   CrossBreedDDL.Text = "Don''t Mind";
   SexDDL.Text = "Don''t Mind";
   SizeDDL.Text = "Don''t Mind";
   AgeDDL.Text = "Don''t Mind";
   LocationDDL.Text = "Don''t Mind";
   RescueDDL.Text = "Don''t Mind";

   }
   }

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 ALTER PROC [dbo].[sp_SearchAnimals]
 (
 @Type_of_Animal nvarchar(50),
 @CrossBreed nvarchar(50),
 @Sex nvarchar(50),
 @Size nvarchar(50),
 @Age nvarchar(50),
 @Location nvarchar(50),
 @Name nvarchar(50),
 @Children nvarchar(50),
 @OtherCats nvarchar(50),
 @OtherDogs nvarchar(50),
 @Details nvarchar(300)
 )
 AS
BEGIn

SELECT
           AD.Location,AD.Details, R.Name,
           I.Content
FROM
   AnimalDetails As AD INNER JOIN
   Images As I ON AD.ImageId = I.ImageId,
   AnimalDetails As DA INNER JOIN RescueDetails As R ON DA.RescueId = R.RescueId
    WHERE
   (@Type_of_Animal is NUll OR AD.Type_of_Animal = @Type_of_Animal) AND
   (@CrossBreed is null OR AD.CrossBreed = @CrossBreed) AND
   (@Sex is null or AD.Sex = @Sex) AND
   (@Size is null or AD.Size = @Size) AND
   (@Age is null or AD.Age = @Age) AND
   (@Location is null or AD.Location = @Location) AND
   (@Name is null or R.Name = @Name) AND
   (@Children is null or AD.Children = @Children) AND
   (@OtherCats is null or AD.OtherCats = @OtherCats) AND
   (@OtherDogs is null or AD.OtherDogs = @OtherDogs) AND
   (@Details is null or AD.Details = @Details)

    END

<asp:GridView ID="GridView1"  runat="server" AutoGenerateColumns="False"
                       DataSourceID="SqlDataSource1"
                       OnRowDataBound="GridView1_RowDataBound"
                        önSelectedIndexChanged="GridView1_SelectedIndexChanged"
                       AllowPaging="True" AllowSorting="True">

                       <rowstyle cssclass="gridViewRowStyle" wrap="True" />
                       <HeaderStyle CssClass="gridViewHeaderStyle" Wrap="False" />
                       <SelectedRowStyle Wrap="true"  CssClass="gridViewSelectedRowStyle"/>
                       <alternatingrowstyle cssclass="gridViewAltRowStyle" />
                       <columns>
                           <asp:BoundField DataField="Type_of_Animal" headerText="Type_of_Animal"  />
                           <asp:BoundField DataField="Sex" HeaderText="Sex" SortExpression="Sex" />
                           <asp:BoundField DataField="Age" HeaderText="Age" SortExpression="Age" />
                           <asp:BoundField DataField="Location" HeaderText="Location"
                                />
                       </columns>
                       <footerstyle cssclass="gridViewHeaderStyle" wrap="False" />
                       <pagersettings position="Bottom" />
                       <pagerstyle cssclass="gridViewHeaderStyle" />

                   <asp:SqlDataSource ID="SqlDataSource1" runat="server"
                       ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"

                       SelectCommand="SELECT [Type_of_Animal], [Sex], [Age], [Location]         From[AnimalDetails] ">

                   <div style="font-family: Arial; font-size: small;">
                         You are viewing page <%=GridView1.PageIndex + 1%>of <%=GridView1.PageCount%>
                   </div>

               <asp:Label ID="lblMsg" runat="server" Text="Your search criteria did not return any Logs"
               Visible="False" CssClass="label">

推荐答案

ConnectionStrings:MyConnectionString％> SelectCommand ="SELECT [Type_of_Animal]，[Sex]，[Age]，[Location] From [AnimalDetails]"> < div style ="font-family:Arial; font-size:small;">   您正在查看<％= GridView1.PageCount％>的页面<％= GridView1.PageIndex + 1％> </div> < asp:标签ID ="lblMsg" runat =服务器" Text =您的搜索条件未返回任何日志" Visible ="False" CssClass ="label">

ConnectionStrings:MyConnectionString %>" SelectCommand="SELECT [Type_of_Animal], [Sex], [Age], [Location] From[AnimalDetails] "> <div style="font-family: Arial; font-size: small;">   You are viewing page <%=GridView1.PageIndex + 1%>of <%=GridView1.PageCount%> </div> <asp:Label ID="lblMsg" runat="server" Text="Your search criteria did not return any Logs" Visible="False" CssClass="label">

您的逻辑完全不起作用，因为您是在不执行命令的情况下将命令参数分配给控件的.这是缺少基本的 ADO.NET [ ^ ]框架.请看以下链接

1. 配置参数和参数数据类型(ADO.NET) [ Parameters.AddWithValue ADO.NET中的输出参数2 [ ^ ]
3. 为初学者使用ADO.NET [我学到的新东西 [ ^ ]

祝你好运.

Your logic totally doesn''t work, since you''re assigning the command parameters to the controls without executing the command.This is a lack of knowing the basic ADO.NET[^] framework. Look the following links

1. Configuring Parameters and Parameter Data Types (ADO.NET)[^]
2. Parameters.AddWithValue Output parameter in ADO.NET 2[^]
3. Using ADO.NET for beginners[^]
4. One more New Things I Learned[^]

Good luck.

这篇关于从数据库筛选到ASP.NET SQL Server的gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！