如何使用存储过程和查询字符串解决此问题
本文关键字：解决 问题 字符串 查询 何使用 存储过程 | 更新日期: 2023-09-27 18:32:43
我在同一页面中有多个数据列表..用户可以单击记录，这会将他重定向到查看页面以查看信息。我使用查询字符串发送 ID 以便单击的记录查看页面...问题是当我选择ID EX：17的记录时。有时信息是正确的，但并不总是因为如果其他表中有其他记录，ID 为 ex：17 它会替换它而不是点击的记录！
Im 使用 c#/Asp.net 4.0 框架/sql Server 2008
这是我的代码
起始页.aspx （ 数据列表页 ） ：
public partial class MainMasterStartPage : System.Web.UI.Page
{
protected SqlConnection _connection;
protected SqlCommand _command;
protected SqlDataAdapter _adp;
protected System.Data.DataTable _tbl;
protected void Page_Load(object sender, EventArgs e)
{
if (!(this.IsPostBack))
{
// For News DataList
prepareConnection();
_command.CommandText = "select top 5 * from News ORDER BY id DESC";
_adp = new SqlDataAdapter();
_tbl = new System.Data.DataTable();
_adp.SelectCommand = _command;
_adp.Fill(_tbl);
dlNews.DataSource = _tbl;
dlNews.DataBind();
// For Sports DataList
prepareConnection();
_command.CommandText = "select top 5 * from Sports ORDER BY id DESC";
_adp = new SqlDataAdapter();
_tbl = new System.Data.DataTable();
_adp.SelectCommand = _command;
_adp.Fill(_tbl);
dlSports.DataSource = _tbl;
dlSports.DataBind();
// For Technology DataList
prepareConnection();
_command.CommandText = "select top 5 * from Technology ORDER BY id DESC";
_adp = new SqlDataAdapter();
_tbl = new System.Data.DataTable();
_adp.SelectCommand = _command;
_adp.Fill(_tbl);
dlTechnology.DataSource = _tbl;
dlTechnology.DataBind();
// For Articles DataList
prepareConnection();
_command.CommandText = "select top 5 * from Articles ORDER BY id DESC";
_adp = new SqlDataAdapter();
_tbl = new System.Data.DataTable();
_adp.SelectCommand = _command;
_adp.Fill(_tbl);
dlArticles.DataSource = _tbl;
dlArticles.DataBind();
// For Islamics DataList
prepareConnection();
_command.CommandText = "select top 5 * from Islamics ORDER BY id DESC";
_adp = new SqlDataAdapter();
_tbl = new System.Data.DataTable();
_adp.SelectCommand = _command;
_adp.Fill(_tbl);
dlIslamics.DataSource = _tbl;
dlIslamics.DataBind();
}
}
protected void prepareConnection()
{
_connection = new SqlConnection(@"Data Source=SONIC-PC'SQLEXPRESS;Initial Catalog=BrainStorms;User ID=sa;Password=gg123");
_connection.Open();
_command = new SqlCommand();
_command.Connection = _connection;
}
和视图页面以显示已单击的记录：
public partial class View : System.Web.UI.Page
{
protected SqlCommand News_command;
protected SqlCommand Sports_command;
protected SqlCommand Technology_command;
protected SqlCommand Articles_command;
protected SqlCommand Islamics_command;
protected SqlDataAdapter News_adp;
protected SqlDataAdapter Sports_adp;
protected SqlDataAdapter Technology_adp;
protected SqlDataAdapter Articles_adp;
protected SqlDataAdapter Islamics_adp;
protected System.Data.DataTable News_tbl;
protected System.Data.DataTable Sports_tbl;
protected System.Data.DataTable Technology_tbl;
protected System.Data.DataTable Articles_tbl;
protected System.Data.DataTable Islamics_tbl;
protected SqlConnection _connection;
protected string _ID;
protected void Page_Load(object sender, EventArgs e)
{
if ((Request.QueryString["ID"] != null))
{
_ID = Request.QueryString["ID"].ToString();
}
//for The News dataList
prepareConnection();
News_command.CommandText = "select * from News where ID=@ID";
News_command.Parameters.AddWithValue("ID", _ID);
News_adp = new SqlDataAdapter();
News_tbl = new System.Data.DataTable();
News_adp.SelectCommand = News_command;
News_adp.Fill(News_tbl);
if (News_tbl.Rows.Count > 0)
{
lblID.Text = News_tbl.Rows[0]["ID"].ToString();
lblTitle.Text = News_tbl.Rows[0]["Title"].ToString();
lblContent.Text = News_tbl.Rows[0]["Contect"].ToString();
}
//For The Sports DataList
prepareConnection();
Sports_command.CommandText = "select * from Sports where ID=@ID";
Sports_command.Parameters.AddWithValue("ID", _ID);
Sports_adp = new SqlDataAdapter();
Sports_tbl = new System.Data.DataTable();
Sports_adp.SelectCommand = Sports_command;
Sports_adp.Fill(Sports_tbl);
if (Sports_tbl.Rows.Count > 0)
{
lblID.Text = Sports_tbl.Rows[0]["ID"].ToString();
lblTitle.Text = Sports_tbl.Rows[0]["Title"].ToString();
lblContent.Text = Sports_tbl.Rows[0]["Contect"].ToString();
}
//for The Technology DataList
prepareConnection();
Technology_command.CommandText = "select * from Technology where ID=@ID";
Technology_command.Parameters.AddWithValue("ID", _ID);
Technology_adp = new SqlDataAdapter();
Technology_tbl = new System.Data.DataTable();
Technology_adp.SelectCommand = Technology_command;
Technology_adp.Fill(Technology_tbl);
if (Technology_tbl.Rows.Count > 0)
{
lblID.Text = Technology_tbl.Rows[0]["ID"].ToString();
lblTitle.Text = Technology_tbl.Rows[0]["Title"].ToString();
lblContent.Text = Technology_tbl.Rows[0]["Contect"].ToString();
}
//For The Articles DataList
prepareConnection();
Articles_command.CommandText = "select * from Articles where ID=@ID";
Articles_command.Parameters.AddWithValue("ID", _ID);
Articles_adp = new SqlDataAdapter();
Articles_tbl = new System.Data.DataTable();
Articles_adp.SelectCommand = Articles_command;
Articles_adp.Fill(Articles_tbl);
if (Articles_tbl.Rows.Count > 0)
{
lblID.Text = Articles_tbl.Rows[0]["ID"].ToString();
lblTitle.Text = Articles_tbl.Rows[0]["Title"].ToString();
lblContent.Text = Articles_tbl.Rows[0]["Contect"].ToString();
}
//For The Islamics DataList
prepareConnection();
Islamics_command.CommandText = "select * from Islamics where ID=@ID";
Islamics_command.Parameters.AddWithValue("ID", _ID);
Islamics_adp = new SqlDataAdapter();
Islamics_tbl = new System.Data.DataTable();
Islamics_adp.SelectCommand = Islamics_command;
Islamics_adp.Fill(Islamics_tbl);
if (Islamics_tbl.Rows.Count > 0)
{
lblID.Text = Islamics_tbl.Rows[0]["ID"].ToString();
lblTitle.Text = Islamics_tbl.Rows[0]["Title"].ToString();
lblContent.Text = Islamics_tbl.Rows[0]["Contect"].ToString();
}
}
protected void prepareConnection()
{
_connection = new SqlConnection(@"Data Source=SONIC-PC'SQLEXPRESS;Initial Catalog=BrainStorms;User ID=sa;Password=gg123");
_connection.Open();
News_command = new SqlCommand();
News_command.Connection = _connection;
Sports_command = new SqlCommand();
Sports_command.Connection = _connection;
Technology_command = new SqlCommand();
Technology_command.Connection = _connection;
Articles_command = new SqlCommand();
Articles_command.Connection = _connection;
Islamics_command = new SqlCommand();
Islamics_command.Connection = _connection;
}
}
这是起始页.aspx来源：
<%@ Page Title="" Language="C#" MasterPageFile="MainMaster.master" AutoEventWireup="true"
CodeFile="StartPage.aspx.cs" Inherits="MainMasterStartPage" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
<div class="MainLatest">
<div id="mainNews" style="clear: both; text-decoration: none; color: Black; text-align: right;
direction: rtl; width: auto; height: auto; margin-right: 0px; float: right; margin-top: 5px;
border-bottom: 1px solid #7F2423;">
<div id="news" style="border: 1px solid black; margin-bottom: 15px;background-color: #EAE9E4;">
<h3 style="background-color: #35496A; color: white; text-align: center; width: 650px;
float: right; border-bottom: 4px solid #7F2423; margin-bottom: 8px; padding-right: 5px;">
آخر الأخبار</h3>
<asp:DataList ID="dlNews" runat="server">
<ItemTemplate>
<a href='./NewsView.aspx?ID=<%#Eval("ID") %>' style="text-decoration: none;">
<div id="123">
<div id="image" style="clear: both; float: right; margin: 0 5px 10px 10px;">
<asp:Image ID="Image1" runat="server" ImageUrl="~/images/epica.jpg" Style="height: 70px;
width: 130px; border: 1px solid black;" />
</div>
<div id="title" style="position: relative; float: right; top: -14px; right: 0px;
height: 69px;">
<asp:Label ID="lblTitle" runat="server" Text='<%#Eval("Title") %>' Style="font-size: 15px;
font-weight: bold; line-height: 110px; "></asp:Label>
</div>
</div>
</a>
</ItemTemplate>
</asp:DataList>
</div>
<div id="sports" style="border: 1px solid black; margin-bottom: 15px;background-color: #EAE9E4;">
<h3 style="background-color: #03C0F8; text-align: center; color: white; width: 650px;
float: right; border-bottom: 4px solid #7F2423; margin-bottom: 8px; padding-right: 5px;">
الرياضة</h3>
<asp:DataList ID="dlSports" runat="server">
<ItemTemplate>
<a href='./SportsView.aspx?ID=<%#Eval("ID") %>' style="text-decoration: none;">
<div id="123">
<div id="image" style="clear: both; float: right; margin: 0 5px 10px 10px;">
<asp:Image ID="Image1" runat="server" ImageUrl="~/images/epica.jpg" Style="height: 70px;
width: 130px; border: 1px solid black;" />
</div>
<div id="title" style="position: relative; float: right; top: -18px; right: -2px;
height: 76px;">
<asp:Label ID="lblTitle" runat="server" Text='<%#Eval("Title") %>' Style="font-size: 15px;
font-weight: bold; line-height: 110px;"></asp:Label>
</div>
</div>
</a>
</ItemTemplate>
</asp:DataList>
</div>
<div id="technology"style="border: 1px solid black; margin-bottom: 15px;background-color: #EAE9E4;">
<h3 style="background-color: #FF9900; text-align: center; color: #7F2423; width: 650px;
float: right; border-bottom: 4px solid #7F2423; margin-bottom: 8px; padding-right: 5px;">
أخبار التكنولوجيا</h3>
<asp:DataList ID="dlTechnology" runat="server">
<ItemTemplate>
<a href='./TechnologyView.aspx?ID=<%#Eval("ID") %>' style="text-decoration: none;">
<div id="123">
<div id="image" style="clear: both; float: right; margin: 0 5px 10px 10px;">
<asp:Image ID="Image1" runat="server" ImageUrl="~/images/epica.jpg" Style="height: 70px;
width: 130px; border: 1px solid black;" />
</div>
<div id="title" style="position: relative; float: right; top: -18px; right: -2px;
height: 76px;">
<asp:Label ID="lblTitle" runat="server" Text='<%#Eval("Title") %>' Style="font-size: 15px;
font-weight: bold; line-height: 110px;"></asp:Label>
</div>
</div>
</a>
</ItemTemplate>
</asp:DataList>
</div>
<div id="articles"style="border: 1px solid black; margin-bottom: 15px;background-color: #EAE9E4;">
<h3 style="background-color: #7F2423; text-align: center; color: white; width: 650px;
float: right; border-bottom: 4px solid #7F2423; margin-bottom: 8px; padding-right: 5px;">
مقالات</h3>
<asp:DataList ID="dlArticles" runat="server">
<ItemTemplate>
<a href='./ArticlesView.aspx?ID=<%#Eval("ID") %>' style="text-decoration: none;">
<div id="123">
<div id="image" style="clear: both; float: right; margin: 0 5px 10px 10px;">
<asp:Image ID="Image1" runat="server" ImageUrl="~/images/epica.jpg" Style="height: 70px;
width: 130px; border: 1px solid black;" />
</div>
<div id="title" style="position: relative; float: right; top: -18px; right: -2px;
height: 76px;">
<asp:Label ID="lblTitle" runat="server" Text='<%#Eval("Title") %>' Style="font-size: 15px;
font-weight: bold; line-height: 110px;"></asp:Label>
</div>
</div>
</a>
</ItemTemplate>
</asp:DataList>
</div>
<div id="islamics"style="border: 1px solid black; margin-bottom: 15px;background-color: #EAE9E4;">
<h3 style="background-color: #FF9900; text-align: center; color: #7F2423; width: 650px;
float: right; border-bottom: 4px solid #7F2423; margin-bottom: 8px; padding-right: 5px;">
إسلاميات</h3>
<asp:DataList ID="dlIslamics" runat="server">
<ItemTemplate>
<a href='./IslamicsView.aspx?ID=<%#Eval("ID") %>' style="text-decoration: none;">
<div id="123">
<div id="image" style="clear: both; float: right; margin: 0 5px 10px 10px;">
<asp:Image ID="Image1" runat="server" ImageUrl="~/images/epica.jpg" Style="height: 70px;
width: 130px; border: 1px solid black;" />
</div>
<div id="title" style="position: relative; float: right; top: -18px; right: -2px;
height: 76px;">
<asp:Label ID="lblTitle" runat="server" Text='<%#Eval("Title") %>' Style="font-size: 15px;
font-weight: bold; line-height: 110px;"></asp:Label>
</div>
</div>
</a>
</ItemTemplate>
</asp:DataList>
</div>
</div>
</div>
</asp:Content>
这是视图页面.aspx来源：
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<p dir="rtl">
<asp:Label ID="lblTitle" runat="server" Text="Label" ></asp:Label>
"</p>
<p dir="rtl">
(<asp:Label ID="lblID" runat="server" Text="Label" ></asp:Label>
)</p>
<p dir="rtl">
:</p>
<p dir="rtl">
<asp:Label ID="lblContent" runat="server" Text="Label" ></asp:Label>
</p>
</asp:Content>
如果您的ID中没有唯一性，那总是会有问题的。我能建议的最好的事情是传递更多参数，这将有助于唯一定义您真正想要显示的表行。
更根本的是，我会问你是否应该更改你的数据库，以便你的ID列是唯一的，以避免黑客攻击，比如给你的queryString增加不必要的复杂性。
附言我宁愿将其添加为评论，但我认为我太新了（声誉太低），无法这样做。
您应该只从包含ID的表中提取记录。
您应该能够通过提供主题（新闻、体育、技术等）来做到这一点。 在HyperLink的查询字符串中。
在View页面上使用此选项来选择要查询的相应表。 不要查询所有内容，请查询正确的一个。