怎样根据两个文本框textbox的值,读出相应的记录。谢谢!
shopName fareName fare remark
0001 促销费 200 11111
0001 进店费 300 121212
0001 海报费 100 121211
0002 促销费 100 121212
0002 进店费 200
0002 其它费 1000
0003
0004
0005
.........
现在有一个textbox1,里面的值是用逗号隔开的,比如:0001,0002
textbox2里面的值也是用逗号隔开的:比如:促销费,进店费
现在怎么样查询,能根据两个文本框的值,读取数据库,把对应的记录读出绑定到datagrid上。
就是把shopname为0001和0002的记录,farename为促销费,进店费的记录读出。
[解决办法]
按照你的表来看
每个shopName 对应的是三个fareName
不懂 你要是需要将值绑定到 DataGrid TextBox是做什么的?
[解决办法]
存储过程
declare @sql varchar(500)
set @sql = 'select * from table where shopname in ('+@idvalues'+)' and farename in ('+ farenameValues+')'
exec @sql
[解决办法]
select * from table where shopname in (textbox1.text.trim) and farename in (textbox2.text.trim)
[解决办法]
select * from tableName where [shopname] in (0001,0002) and [farename] in ('促销费','进店费')select * from tableName where [shopname] in (0001,0002) or [farename] in ('促销费','进店费')
[解决办法]
string[] shopArr= textbox1.split(',');
string[] fareArr = textbox2.split(',');
string shops="";
string fares="";
for(int i=0;i< shopArr.Length;i++)
{
if(i==0) shops+="'"+ shopArr [i]+"'";
else shops+=",'"+ shopArr [i]+"'";
}
for(int i=0;i< fareArr .Length;i++)
{
if(i==0) fares +="'"+ fareArr [i]+"'";
else fares +=",'"+ fareArr [i]+"'";
}
拼Sql语句
"select * from 表 where shopName in("+ shops+") and farename in("+ fares +")"
[解决办法]
string[] array1=textbox1.text.trim.tostring().split(",")string[] array2=textbox1.text.trim.tostring().split(",")select * from table where shopname ='"+array1[0].tostring+"' and farename ='"+array2[0].tostring+"'unionselect * from table where shopname ='"+array1[1].tostring+"' and farename ='"+array2[1].tostring+"'不过这种方式不是很灵活,当array1的长度与array1的长度不一样时
[解决办法]
select * from tableName where [shopname] in (0001,0002) and [farename] in ('促销费','进店费')select * from tableName where [shopname] in (0001,0002) or [farename] in ('促销费','进店费')
[解决办法]
drop function Split goCREATE FUNCTION [Split]( @sText varchar(8000), --分割目标串 @sDelim varchar(20) = ' '--分割字符串)RETURNS @retArray TABLE --返回分割后的串数据表( idx int Primary Key, --主键 Evalue varchar(8000)--值)AS BEGIN declare @idx int declare @value varchar(8000) declare @bcontinue bit declare @iStrike int declare @iDelimlength tinyint if @sDelim = 'Space' BEGIN SET @sDelim = ' ' END SET @idx = 0 SET @sText = LTrim(RTrim(@sText)) SET @iDelimlength = len(@sDelim) SET @bcontinue = 1 IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty')) --如果分割串不为空时 BEGIN WHILE @bcontinue = 1 BEGIN IF CHARINDEX(@sDelim, @sText)>0 --如果在@sText是找到分割字符@sDelim的位置则将第一个元素插入表 BEGIN SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1) INSERT @retArray (idx, Evalue) VALUES (@idx, @value) SET @iStrike = len(@value) + @iDelimlength--增加@idex值并 取回下一个目标串 SET @idx = @idx + 1 SET @sText = LTrim(Right(@sText,len(@sText) - @iStrike)) END ELSE --如果在@sText里找不到@sDelim时,说明@sDelim已经不能再分割了,将@sText插入返回表@retArray BEGIN SET @value = @sText INSERT @retArray (idx, Evalue) VALUES (@idx, @value) SET @bcontinue = 0--设置退出循环标识 END END END ELSE BEGIN WHILE @bcontinue=1 BEGIN IF len(@sText)>1 --如果分割字符为空串时,将字符串中每个字符插入@retArray BEGIN SET @value = SUBSTRING(@sText,1,1) INSERT @retArray (idx, Evalue) VALUES (@idx, @value) SET @idx = @idx+1 SET @sText = SUBSTRING(@sText,2,len(@sText)-1) END ELSE BEGIN --插入字符并设置退出while标识 INSERT @retArray (idx, Evalue) VALUES (@idx, @sText) SET @bcontinue = 0 END END END RETURN END go --你的存储过程if exists (select * from sysobjects where id = object_id(N'Search_Pro') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure Search_Progocreate procedure Search_Pro@idvalues varchar(500),@farenameValues varchar(500)as begindeclare @SQL varchar(1000) set @SQL = 'select * from yourTableName where 1=1 'set @SQL = @SQL + ' and (1<>1 '-----------------------------------------declare @idvalues_id intdeclare @ShopName_Text nvarchar(100)declare cursor_ShopName_Text cursor for select idx from Split(@idvalues,',') order by idx ASCopen cursor_ShopName_Text fetch cursor_ShopName_Text into @idvalues_idwhile @@fetch_status=0 begin if exists(select * from Split(@idvalues,',') where idx=@idvalues_id) begin set @ShopName_Text=(select Evalue from Split(@idvalues,',') where idx=@idvalues_id)--去掉","后的号码 set @SQL = @SQL + ' or shopname ='''+@ShopName_Text+'''' end fetch cursor_ShopName_Text into @idvalues_id end close cursor_ShopName_Textdeallocate cursor_ShopName_Textset @SQL = @SQL + ' ) and ( 1<>1 '------------------------------------------declare @farenameValues_id intdeclare @farename_Text nvarchar(100)declare cursor_farename_Text cursor for select idx from Split(@farenameValues,',') order by idx ASCopen cursor_farename_Text fetch cursor_farename_Text into @farenameValues_idwhile @@fetch_status=0 begin if exists(select * from Split(@farenameValues,',') where idx=@farenameValues_id) begin set @farename_Text=(select Evalue from Split(@farenameValues,',') where idx=@farenameValues_id)--去掉","后的号码 set @SQL = @SQL + ' or farename ='''+@farename_Text+'''' end fetch cursor_farename_Text into @farenameValues_id end close cursor_farename_Textdeallocate cursor_farename_Textset @SQL = @SQL + ' ) '------------------------------------------exec(@SQL)print @SQLendgo
[解决办法]
纠正一个
加上双引号和加号和去掉空格,不要单引号string sql="select * from table where shopname in ("+ textbox1.text.trim.replace(" ","") +") and farename in (" +textbox2.text.trim.replace(" ","") +") ";
[解决办法]
string shopName = "00001,00002,00003"; string fareName = "促销费,进店费,海报费";fareName = "'"+fareName .Replace(",", "','")+"'";string sql = "select * from table where shopname in (" + shopName + ") and fareName in (" + fareName + ")";
[解决办法]
in的时候不要加单引号string feebuySp=feebuyerbhFX.Text.Trim().Replace(" ",""); STRING sql="" sql=" select * from feelist where [listSHOWSTATE] is null and feebuyerSp in ("+ feebuySp +") "sql+=" and feeNAME like '%"+ feenameFX.SelectedItem.Text.Trim() +"%' " sql+=" and feebuyerYW like '%"+ feeywyFX.SelectedItem.Text.Trim() +"%' " sql+=" and feebuyerND like '%"+ feendFX.SelectedItem.Text.Trim() +"%' " SqlDataAdapter da=new SqlDataAdapter(sql,feefxconn);