SP用param选择 [英] SP select with param
本文介绍了SP用param选择的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我使用vs2010和ms sql与(linq to sql)SP传递p1 p2 p3并将所选数据检索到转发器问题是转发器忽略p1,p2,p3并且只显示电影表中的所有数据我有3个表:(电影,导演,电影导演)
ALTER PROCEDURE [dbo]。[SelectMovie]
@ P1 int ,
@ P2 NVARCHAR ( 255 )= NULL ,
@ P3 NVARCHAR ( 255 )= NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL 阅读委托
SELECT Movie.Movie_ID
,Movie.title
,电影。 IMAGE
,Movie.actors
,Movie.description
,Director.NAME
FROM 电影
INNER JOIN 电影 - 导演 ON Movie.Movie_ID =电影 - director.Movie_ID
INNER JOIN Director ON 电影 - director.Director_ID = Director.Director_ID
WHERE (title LIKE ' %' +( @ p1 )+ ' < span class =code-string>%') - title必须匹配
AND (
(
actors LIKE ' %' +( @ p2 )+ ' %' AND @ p2 IS NOT NULL ) - 匹配时@ p2是不为空
或(
cat_ID = @ p3 或 @ p3 IS NULL - if @ p3 IS NULL然后不过滤@ p3
)
)
ASPX:
< asp:Repeater ID = Repeater1 < span class =code-attribute> runat = server >
< HeaderTemplate > < / HeaderTemplate >
< ItemTemplate >
< div style = width:100%; >
< div class = 摘录 >
< a href = movie_details.aspx?id = <%#DataBinder.Eval(Container.DataItem , Movie_ID)%> class = thumb title = 图片 >
< img src = <% #DataBinder.Eval(Container.DataItem, image )%> alt = 发布 style < span class =code-keyword> = opacity:1;向左飘浮;宽度:80px;高度:100像素; border:3px solid #fff;保证金:5px; >
< < span class =code-leadattribute> / a >
< ; a href < span class =code-keyword> = movie_details.aspx?id = <%#DataBinder .Eval(Container.DataItem, Movie_ID)%> class = 标题 >
< h6 >
<%#DataBinder.Eval(Container.DataItem, 标题跨度>)%>
(<%#DataBinder.Eval(Container.DataItem, actors)%>)
< / h6 >
< / a >
< div style = 填充:5px; >
<%#DataBinder.Eval(Container.DataItem, description)%>
< / div >
< div 样式 = 填充:5px; >
<% #DataBinder.Eval(Container.DataItem, name)%>
< / div >
< / div >
< / div >
< br / >
< hr / >
< / ItemTemplate >
< / asp:Repeater >
C#代码
CDTDataContext dc = new CDTDataContext();
受保护 void Page_Load(对象发件人,EventArgs e)
{
如果(!IsPostBack){}
}
受保护 void Button1_Click( object sender,EventArgs e)
{
string Cat_ID = DropDownList1.SelectedValue;
string keyword1 = TextBox2.Text;
string keyword2 = TextBox3.Text;
int ? cid = int .Parse(cat_id);
Repeater1.DataSource = dc.SelectMovie(keyword1,keyword2,cid);
Repeater1.DataBind();
}
解决方案
我的意思是你必须在查询中编辑参数
@ P1 int ,
@ P2 NVARCHAR ( 255 )= NULL ,
@ P3 NVARCHAR ( 255 )= NULL< br $>
请勿在参数中放入
和NULL
P2@ P3
喜欢这个
@ P1 int ,
@ P2 NVARCHAR ( 255 ),
@ P3 NVARCHAR ( 255 )
I use vs2010 and ms sql with (linq to sql) SP to pass p1 p2 p3 and retrieve selected data to repeater The problem is the repeater ignore p1,p2,p3 and just display all data in the movie table I have 3 tables : (Movie , Director, Movie-director)
ALTER PROCEDURE [dbo].[SelectMovie]
@P1 int ,
@P2 NVARCHAR(255) = NULL,
@P3 NVARCHAR(255) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT Movie.Movie_ID
,Movie.title
,Movie.IMAGE
,Movie.actors
,Movie.description
,Director.NAME
FROM Movie
INNER JOIN Movie - director ON Movie.Movie_ID = Movie - director.Movie_ID
INNER JOIN Director ON Movie - director.Director_ID = Director.Director_ID
WHERE (title LIKE '%' + (@p1) + '%') --title has to be matched
AND (
(
actors LIKE '%' + (@p2) + '%' AND @p2 IS NOT NULL) --match only then when @p2 is not null
OR (
cat_ID = @p3 OR @p3 IS NULL --if @p3 IS NULL then do not filter by @p3
)
)
ASPX:
<asp:Repeater ID="Repeater1" runat="server" >
<HeaderTemplate> </HeaderTemplate>
<ItemTemplate>
<div style="width:100%;">
<div class="excerpt">
<a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "Movie_ID")%>" class="thumb" title="An image">
<img src="<%# DataBinder.Eval(Container.DataItem, "image")%>" alt="Post" style="opacity: 1; float:left; width:80px ; height:100px; border:3px solid #fff ; margin:5px;">
</a>
<a href="movie_details.aspx?id=<%# DataBinder.Eval(Container.DataItem, "Movie_ID")%>" class="header">
<h6>
<%# DataBinder.Eval(Container.DataItem, "title")%>
( <%# DataBinder.Eval(Container.DataItem, "actors")%>)
</h6>
</a>
<div style="padding:5px;">
<%# DataBinder.Eval(Container.DataItem, "description")%>
</div>
<div style="padding:5px;">
<%# DataBinder.Eval(Container.DataItem, "name")%>
</div>
</div>
</div>
<br />
<hr />
</ItemTemplate>
</asp:Repeater>
C# code
CDTDataContext dc = new CDTDataContext();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack){ }
}
protected void Button1_Click(object sender, EventArgs e)
{
string Cat_ID = DropDownList1.SelectedValue;
string keyword1 = TextBox2.Text;
string keyword2 = TextBox3.Text;
int? cid = int.Parse(cat_id);
Repeater1.DataSource = dc.SelectMovie(keyword1, keyword2, cid);
Repeater1.DataBind();
}
解决方案
I mean you must edit parameter in query
@P1 int , @P2 NVARCHAR(255) = NULL, @P3 NVARCHAR(255) = NULL
Don't putNULL
in Parameter@P2
and@P3
Like this
@P1 int , @P2 NVARCHAR(255), @P3 NVARCHAR(255)
这篇关于SP用param选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文