SQL Server 2008 R2存储过程不起作用(可视化c#)
问题描述:
我已经搜索并尝试过去一周左右不同的事情,我的问题可能是特定的通过谷歌找到答案。SQL Server 2008 R2存储过程不起作用(可视化c#)
如果我在SQL Server Management Studio中执行此查询并将参数@zoekterm
替换为'%something%'
,它工作正常并返回我想要的结果。但是当我从C#调用相同的过程时,它什么都不返回。
这是一个错误还是我只是愚蠢?
下面是在C#中的存储过程和函数的代码,(我知道我应该用开关的情况下...)
存储过程:
-- =============================================
-- Author: Daan
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[quick_bedrijf]
-- Add the parameters for the stored procedure here
@zoekterm varchar(100) = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT bedrijf.bedrijf_nr, bedrijf.zoeknaam, bedrijf.plaats
FROM bedrijf
WHERE zoeknaam LIKE @zoekterm AND NIETactief = 0
ORDER BY bedrijf.zoeknaam, bedrijf.plaats
END
C#:
private void snel_zoek2()
{
listView1.Items.Clear();
con.Open();
if (type == 1)
{
command1 = new SqlCommand("quick_project", con);
colnum = 5;
}
else if (type == 2)
{
command1 = new SqlCommand("quick_bedrijf", con);
colnum = 3;
}
else if (type == 3)
{
command1 = new SqlCommand("quick_persoon", con);
colnum = 4;
}
command1.CommandType = CommandType.StoredProcedure;
SqlParameter zoekterm = command1.Parameters.Add("@zoekterm", SqlDbType.VarChar, 100);
zoekterm.Direction = ParameterDirection.Input;
//command1.Parameters.Add(new SqlParameter("@zoekterm", SqlDbType.VarChar)).Value = " '%zee%'";// + textBox2.Text.ToString()+
zoekterm.Value = "'%"+textBox2.Text.ToString()+"%'";
// MessageBox.Show(zoekterm.Value.ToString());
SqlDataAdapter adapt = new SqlDataAdapter();
DataTable dt = new DataTable();
adapt.SelectCommand = command1;
adapt.Fill(dt);
dataGridView1.BindingContext = new BindingContext();
dataGridView1.DataSource = dt;
con.Close();
}
答
你不把这些引号放入参数中(这些是表示文字);它应该是:
zoekterm.Value = "%"+textBox2.Text+"%";
这是目前失败,因为如果文字是“ABC”,它正在寻找启动,并以一个单引号结束,包括“ABC”的字符串。在SQL方面,你问它:
LIKE '''%abc%'''
Whow,让我测试这一点,所以我是傻吧..:P 编辑:证实!感谢名单! – Daanvl 2011-02-10 08:07:00