如何在前端的sql查询中将值传递给IN关键字 [英] how to pass values to IN keyword in sql query from front end
问题描述
大家好,
我想在前端的sql查询中将值传递给IN关键字。当我直接在查询中给出值时它会得到。但从前端传递值却没有得到。收到错误消息
将varchar值'2,3'转换为数据类型int时转换失败。
< pre lang =c#> CREATE TABLE TestIn(名称VARCHAR( 15 ),id int identity)
将插入 TestIn(名称)值(' Ammu'),(' unni'),(' geethu'),(' tintu'),(' joby'),(' jinu')
声明@id varchar( 300 )
set @ id = ' 2 ,3'
SELECT * FROM TestIn 其中 id in (@id)
DROP TABLE TestIn
怎么做。谢谢提前,
Amritha
这不是那么简单......:笑:
看看这里:在SQL IN子句中使用逗号分隔值参数字符串 [ ^ ]
使用系统;
使用 System.Data;
使用 System.Configuration;
使用 System.Collections;
使用 System.Web;
使用 System.Web.Security;
使用 System.Web.UI;
使用 System.Web.UI.WebControls;
使用 System.Web.UI.WebControls.WebParts;
使用 System.Web.UI.HtmlControls;
使用 System.Data.Sql;
使用 System.Data.SqlClient;
使用 System.Data;
使用 System.Data.Common;
public partial class test:System.Web.UI.Page
{
protected void Page_Load( object sender,EventArgs e)
{
using (SqlConnection sqlCon = new SqlConnection( @ 数据源= 127.0.0.1 ; Database = DatabaseName; User id = uid; Password = pwd; Integrated security = true;))
{
sqlCon.Open();
SqlCommand sqlCmd = new SqlCommand( usp_data,sqlCon);
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Add( new SqlParameter( @ vals, 2,3,4,5));
// 或
sqlCmd.Parameters.Add( new SqlParameter( @ vals, A,B,C,D));
int recAffected = sqlCmd.ExecuteNonQuery();
sqlCon.Close();
}
}
}
使用 [DatabaseName]
go
创建 程序 [dbo]。[usp_data]
@ vals varchar (max)= null
as
BEGIN
声明 @str varchar (max)
声明 @ vals varchar (max)
- 用于数值
set @ str = ' select * from tbl_data where id in(' + @ vals + ' )'
print @str
exec ( @ str )
- 用于文本值
set @ vals = replace( @ vals , ' ,', char ( 39 )+ ' ,' + char( 39 ))
set @ str = ' select * from tbl_data where id in(' + char ( 39 )+ @ vals + char ( 39 )+ ' )'
print @ str
exec ( @ str )
END
DineshT:91 + 9829104666
Hi all,
I want to pass values to IN keyword in sql query from front end .when i give values directly in query its getting. but pass values from front end its not getting. getting error message
Conversion failed when converting the varchar value '2,3' to data type int.
CREATE TABLE TestIn (name VARCHAR(15), id int identity)
insert into TestIn(name) values('Ammu'),('unni'),('geethu'),('tintu'),('joby'),('jinu')
declare @id varchar(300)
set @id='2,3'
SELECT * FROM TestIn where id in (@id)
DROP TABLE TestIn
How to do this.Thanks in Advance,
Amritha
It's not that simple...:laugh:
Have a look here: Using comma separated value parameter strings in SQL IN clauses[^]
using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.Sql; using System.Data.SqlClient; using System.Data; using System.Data.Common; public partial class test : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { using (SqlConnection sqlCon = new SqlConnection(@"Data Source=127.0.0.1; Database=DatabaseName; User id=uid; Password=pwd; Integrated security=true;")) { sqlCon.Open(); SqlCommand sqlCmd = new SqlCommand("usp_data", sqlCon); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.Parameters.Add(new SqlParameter("@vals", "2,3,4,5")); //OR sqlCmd.Parameters.Add(new SqlParameter("@vals", "A,B,C,D")); int recAffected = sqlCmd.ExecuteNonQuery(); sqlCon.Close(); } } }
use [DatabaseName] go create procedure [dbo].[usp_data] @vals varchar(max)=null as BEGIN declare @str varchar(max) declare @vals varchar(max) -- for numeric values set @str = 'select * from tbl_data where id in(' + @vals + ')' print @str exec (@str) -- for text values set @vals = replace(@vals, ',', char(39)+','+char(39)) set @str = 'select * from tbl_data where id in(' + char(39) + @vals + char(39) + ')' print @str exec (@str) END
DineshT : 91+9829104666
这篇关于如何在前端的sql查询中将值传递给IN关键字的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!