You will need to use the SQL String Split function to get the result.
----------------------------------CREATE THIS FUNCTION----------------------------------
CREATE FUNCTION [dbo].[SplitString]
(
@String varchar(8000), @Delimiter char(1)
)
returns @temptable TABLE (items varchar(8000),itemno int)
as
begin
declare @idx int
declare @i int=1
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
BEGIN
insert into @temptable(Items,itemno) values(@slice,@i)
SET @i+=1
END
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
----------------------------------CREATE THIS FUNCTION----------------------------------
----------------------------RUN THIS QUERY TO FETCH DATA----------------------------
SELECT *
FROM YourTable
WHERE RefNo IN
(
SELECT items
FROM dbo.SplitString('R1,R2',',')
)
Yogesh Bhadauriya
Sr Technical Lead
Ahmedabad, India
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebForm
{
public partial class GetDataFromSQLDatabase : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
using (
SqlConnection con =
new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-IdentitySQL-20190204011940;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"))
{
string[] tags = TextBox1.Text.Split(',');
string cmdText = "SELECT * FROM [table] where RefNo IN ({0})";
string[] paramNames = tags.Select(
(s, i) => "@tag" + i.ToString()
).ToArray();
string inClause = string.Join(", ", paramNames);
using (SqlCommand cmd = new SqlCommand(string.Format(cmdText, inClause),con))
{
con.Open();
for (int i = 0; i < paramNames.Length; i++)
{
cmd.Parameters.AddWithValue(paramNames[i], tags[i]);
}
GridView1.DataSource = cmd.ExecuteReader();
GridView1.DataBind();
con.Close();
}
}
}
}
}
Result:
Bert Regards,
Brando
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
293 Points
676 Posts
Get SQl Data Using Comma Value
Feb 01, 2019 07:31 AM|Gopi.MCA|LINK
Hello
This Is my table Data
In c# asp.net textbox user will enter for example like this format R1,R3
I want resultset to be show like this
Star
10303 Points
2360 Posts
Re: Get SQl Data Using Comma Value
Feb 02, 2019 06:05 AM|yrb.yogi|LINK
You will need to use the SQL String Split function to get the result.
Sr Technical Lead
Ahmedabad, India
Star
9831 Points
3120 Posts
Re: Get SQl Data Using Comma Value
Feb 04, 2019 08:58 AM|Brando ZWZ|LINK
Hi Gopi.MCA,
According to your description, I suggest you could try to use
select [table] where RefNo
in to achieve your requirement.You could parameterize each value according to the textbox value.
More details, you could refer to below codes:
ASPX:
Code-behind:
Result:
Bert Regards,
Brando
Star
10303 Points
2360 Posts
Re: Get SQl Data Using Comma Value
Feb 04, 2019 12:21 PM|yrb.yogi|LINK
Don't use the inline SQL Query in Application Side, this leads to SQL Injection.
Check more details here
Sr Technical Lead
Ahmedabad, India