无法从SQL表中获取记录到使用存储过程的C#中的DataGridView [英] Unable To Fetch Records From SQL Table To DataGridView In C# Using Stored Procedure
问题描述
CREATE PROCEDURE USP_SELECT_CUSTOMERS(
@fullName VARCHAR(100)='',
@Address VARCHAR(100)='',
@ContactNumber VARCHAR(15)=''
)
AS BEGIN
SELECT * FROM Customers
WHERE ((ContactName LIKE @fullName +'%')OR(@fullName =''))
OR((地址LIKE @Address +'%')OR(@Address =''))
OR((电话LIKE @ContactNumber +'%')OR(@ContactNumber =''))
END
以下是我在程序中调用存储过程的方式:=>
private void btnSubmit_Click(object sender,EventArgs e)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand(USP_SELECT_CUSTOMERS,con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(@ fullName,txtFullName.Text);
cmd.Parameters.AddWithValue(@ Address,txtAddress.Text);
cmd.Parameters.AddWithValue(@ ContactNumber,txtContactNumber.Text);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if(ds.Tables [0] .Rows.Count> 0)
{
dataGridView1.Visible = true;
dataGridView1.DataSource = ds.Tables [0];
}
}
catch(Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
con.Close();
}
}
我想要的是即使1个文本框如果填充,存储过程应该给出所需的输出。
谢谢。
你应该 AND
而不是 OR
。尝试这样:
CREATE PROCEDURE USP_SELECT_CUSTOMERS(
@fullName VARCHAR(100),
@Address VARCHAR 100),
@ContactNumber VARCHAR(15)
)
AS BEGIN
SELECT * FROM Customers
WHERE((ContactName LIKE @fullName +'%')OR(@ (@ Address =''))
AND((Address LIKE @Address +'%')OR(@Address =''))
AND((Phone LIKE @ContactNumber +'%')OR(@ContactNumber = ''))
END
它会给你所需的结果。
请参阅 SQL Fiddle
编辑:
根据您的要求,使用此查询:
pre>
CREATE PROCEDURE USP_SELECT_CUSTOMERS
(
@fullName VARCHAR(100),
@Address VARCHAR(100),
@ContactNumber VARCHAR (15)
)
AS
BEGIN
如果LEN(@fullName)= 0
SET @fullName ='$$$'
IF LEN(@Address)= 0
SET @Address ='$$$'
IF LEN(@ContactNumber)= 0
SET @ContactNumber ='$$$'
SELECT * FROM Customers
WHERE((ContactName LIKE @fullName +'%'))
OR((Address LIKE @Address +'%'))
OR((Phone LIKE @ContactNumber +'%'))
END
添加了一个简单的技巧。请参阅 SQL Fiddle 。
替换 $$$
,其中不会包含在 ContactName
,地址
和电话
字段。
I have a windows form where the user can input multiple values in multiple textboxes for faster search results. But when running, it takes only 1st parameter i.e., the fullname and ignores the other. Don't know the reason why is it so. Am getting the full table in the result which I don't want. I have created a stored procedure.
CREATE PROCEDURE USP_SELECT_CUSTOMERS (
@fullName VARCHAR(100) = '',
@Address VARCHAR(100) = '',
@ContactNumber VARCHAR(15) = ''
)
AS BEGIN
SELECT * FROM Customers
WHERE ((ContactName LIKE @fullName+'%') OR (@fullName = ''))
OR ((Address LIKE @Address+'%') OR (@Address = ''))
OR ((Phone LIKE @ContactNumber+'%') OR (@ContactNumber = ''))
END
Here's how am i calling the stored procedure in my program :=>
private void btnSubmit_Click(object sender, EventArgs e)
{
try
{
con.Open();
SqlCommand cmd = new SqlCommand("USP_SELECT_CUSTOMERS", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@fullName", txtFullName.Text);
cmd.Parameters.AddWithValue("@Address", txtAddress.Text);
cmd.Parameters.AddWithValue("@ContactNumber", txtContactNumber.Text);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count > 0)
{
dataGridView1.Visible = true;
dataGridView1.DataSource = ds.Tables[0];
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
con.Close();
}
}
What I want is even if the 1 textbox if filled, the stored procedure should give the desired output. Kindly help me out in this.
Thanks.
You should AND
instead of OR
. Try this:
CREATE PROCEDURE USP_SELECT_CUSTOMERS (
@fullName VARCHAR(100),
@Address VARCHAR(100),
@ContactNumber VARCHAR(15)
)
AS BEGIN
SELECT * FROM Customers
WHERE ((ContactName LIKE @fullName+'%') OR (@fullName = ''))
AND ((Address LIKE @Address+'%') OR (@Address = ''))
AND ((Phone LIKE @ContactNumber+'%') OR (@ContactNumber = ''))
END
It will give you the desired result.
See SQL Fiddle
EDIT: As per your requirement, use this query:
CREATE PROCEDURE USP_SELECT_CUSTOMERS
(
@fullName VARCHAR(100),
@Address VARCHAR(100),
@ContactNumber VARCHAR(15)
)
AS
BEGIN
IF LEN(@fullName)=0
SET @fullName='$$$'
IF LEN(@Address)=0
SET @Address='$$$'
IF LEN(@ContactNumber)=0
SET @ContactNumber='$$$'
SELECT * FROM Customers
WHERE ((ContactName LIKE @fullName+'%'))
OR ((Address LIKE @Address+'%'))
OR ((Phone LIKE @ContactNumber+'%'))
END
Added a simple trick. See SQL Fiddle.
Replace $$$
with anything which will not be included in ContactName
,Address
and Phone
fields.
这篇关于无法从SQL表中获取记录到使用存储过程的C#中的DataGridView的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!