concat函数需要2到254个参数 [英] The concat function requires 2 to 254 arguments
问题描述
我的系统出了问题。我试图在我的datagridview中搜索特定数据并过滤它并点击它显示到texboxes ..它有一个错误说
concat函数需要2到254个参数
我该怎么办?谢谢
我尝试过:
< pre> Imports System.Data.SqlClient
Public 类 TreatmentHistory
Dim connection As < span class =code-keyword>新 SqlConnection( Server = DESKTOP-C6IEOUN \ SQLEXPRESS; database = NEWCMO; integrated security = True;)
私有 Sub TreatmentHistory_Load (发件人作为 对象,e 作为 EventArgs )句柄 MyBase .Load
FilterData( )
< span class =code-keyword> End Sub
Private Sub txtPID_TextChanged(发件人作为 对象 ,e As EventArgs)句柄 txtPID.TextChanged
结束 Sub
公共 Sub FilterData(valueToSearch As String )
Dim searchQuery As String = SELECT * FROM tblTreatment WHERE CONCAT(patientID,Surname,Firstname,Age,
Clm11,
Clm12,
Clm13,
Cl m14,
Clm15,
Clm16,
Clm17,
Clm18,
Clm21,
Clm22,
Clm23,
Clm24,
Clm25,
Clm26,
Clm27,
Clm28,
Clm31,
Clm32,
Clm33,
Clm34,
Clm35,
Clm36,
Clm37,
Clm38,
Clm41,
Clm42,
Clm43,
Clm44,
Clm45,
Clm46,
Clm47,
Clm48,
Clm51,
Clm52,
Clm53,
Clm54,
Clm55,
Clm61,
Clm62,
Clm63,
Clm64,
Clm65,
Clm71,
Clm72,
Clm73,
Clm74,
Clm75,
Clm81,
Clm82,
Clm83,
Clm84,
Clm85,
Button1,
Button2,
Button3,
Button4,
Button5,
Button7,
Button6,
Button8,
Button9,
Button10,
Button11,
Button12,
Button13,
Button15,
Button14,
Button16,
Button17,
Button18,
Button19,
Button20,
Button21,
Button22,
Button23,
Button25,
Button24,
Button26,
Button27,
Button28,
但是ton29,
Button30,
Button31,
Button32,
Button33,
Button35,
Button34,
Button36,
Button37,
Button38,
Button39,
Button40,
Button41,
Button42,
Button43,
Button44,
Button46,
Button45,
Button47,
Button48,
Button49,
Button50,
Button51,
Button52,
Button53,
Button54,
Button55,
Button56,
Button57,
Button58,
Button59,
Button60,
Button61,
Button62,
Button63,
Button64,
Button65,
Button66,
Button67,
Button68,
Button69,
Button70,
Button71,
Button72,
Button73,
Button74,
Button75,
Button76,
Button77,
Button78,
Button79,
Button80,
Button81,
Button82,
Button83,
Button84,
Button85,
Button86,
Button87,
Button88,
Button89,
Button90,
Button91,
Button92,
Button93,
Button94,
Button95,
Button96,
Button97,
Button98,
Button99,
Button100,
Button101,
Button102,
Button103,
Button104 ,
Button105,
Button106,
Button107,
Button108,
Button109,
Button110,
Button111,
Button112,
Button113,
Button114,
Button115,
Button116,
Button117,
Button118,
Button119,
Button120,
Button121,
Button122,
Button123,
Button124,
Button125,
Button126,
Button127,
Button128,
Button129 ,
Button130,
Button131,
Button132,
Button133,
Button134,
Button135,
Button136,
Button137,
Button138,
Button139,
Button140,
Button141,
Button142,
Button143,
Button144,
Button145,
Button146,
Button147,
Button148,
Button149,
Button150,
Button151,
Button152,
Button153,
Button154 ,
Button155,
Button156,
Button157,
Button158,
Button159,
Button160,
Button161,
Button162,
Button163,
Button164,
Button165,
Button166,
Button167,
Button168,
Button169,
Button170,
Button171,
Button172,
Button173,
Button174,
Button175,
Button176,
Button177,
Button178,
Button179,
Button180,
Button181,
Button182,
Button183,
Button184,
Button185,
Button186,
Button187,
Button188,
Button189,
Button190,
Button191,
Button192,
Button193,
Button194,
Button195,
Button196,
Button197,
Button198,
Button199,
Button200,
Button201,
Button202,
Button203,
Button204,
Button205,
Button206,
Button207,
Button208,
Button209,
Button210,
Button211,
Button212,
Button213,
Button214,
Button215,
Button216,
Button217,
Button218,
Button219,
Button220,
Bu tton221,
Button222,
Button223,
Button224,
Button225,
Button226,
Button227,
Button228,
Button229,
Button230,
Button231,
Button232,
Button233,
Button234,
Button235,
Button236,
Button237,
Button238,
Button239,
Button240,
Button241,
Button242,
Button243,
Button244,
Button245,
Button246,
Button247,
Button248,
Button249,
Button250,
Button251,
Button252,
Button253,
Button254,
Button255,
Button256,
Button257,
Button258,
Button259,
Button260,
备注,
投诉)喜欢' %& valueToSearch& %'
Dim 命令 As 新 SqlCommand(searchQuery,connection)
Dim adapter As 新 SqlDataAdapter(命令)
Dim 表作为 新 DataTable()
adapter.Fill(table)
dgvTreatmentHistory.DataSource = table
End < span class =code-keyword> Sub
私有 Sub PictureBox1_Click(发件人作为 对象,e As EventArgs )句柄 PictureBox1.Click
FilterData(txtPID.Text)
结束 Sub
结束 类
嗯,错误很明显:你提供了许多参数concat
功能。您可以逐步执行连接。例如,您可以连接参数序列的前半部分,然后连接后半部分,最后连接结果字符串。
<小> [更新]
引用:我怎样才能连接halfs?
这个想法很简单,例如,假设你有{'a','b','c','d'}
,然后:
concat(concat(' a ',' b'),concat(' c',' d' ))
[/ update]
Concat [ ^ ]函数将两个或多个字符串连接成单个字符串,但您希望在特定字段中搜索特定值。至于你的代码,你试图连接字段的名称,而不是字符串。使用CONCAT函数的正确方法是:
SELECT CONCAT(' Hello',' , ',' ',' World',' !' )
我建议创建存储过程 [ ^ ]可以接受几个参数。如果用户可以选择要搜索的字段(使用单选按钮),则sp可以如下所示:
使用 YourDatabaseName;
CREATE 已存储程序 usp_SearchDb
@ SearchedField NVARCHAR ( 30 ),
@ FindValue NVARCHAR ( 30 )
< span class =code-keyword> AS
SET NOCOUNT ON 跨度>;
DECLARE @ qry NVARCHAR (MAX)= N ' SELECT * FROM YourTable WHERE' + @ SearchedField + ' 喜欢%' + @ FindValue + ' %'
EXEC ( @ qry )
END
注意:未经测试,但它应该有效!
如果您需要要一次查询多个列,您必须阅读查询多列(完整 - 文本搜索) [ ^ ]
祝你好运!
I have a problem in my system. Im trying to search specific data in my datagridview and filter it and click it the show to the texboxes.. It has an error saying
The concat function requires 2 to 254 arguments
What should I do? Thanks
What I have tried:
<pre>Imports System.Data.SqlClient
Public Class TreatmentHistory
Dim connection As New SqlConnection("Server=DESKTOP-C6IEOUN\SQLEXPRESS;database =NEWCMO; integrated security=True;")
Private Sub TreatmentHistory_Load(sender As Object, e As EventArgs) Handles MyBase.Load
FilterData("")
End Sub
Private Sub txtPID_TextChanged(sender As Object, e As EventArgs) Handles txtPID.TextChanged
End Sub
Public Sub FilterData(valueToSearch As String)
Dim searchQuery As String = "SELECT * FROM tblTreatment WHERE CONCAT(patientID,Surname,Firstname,Age,
Clm11,
Clm12,
Clm13,
Clm14,
Clm15,
Clm16,
Clm17,
Clm18,
Clm21,
Clm22,
Clm23,
Clm24,
Clm25,
Clm26,
Clm27,
Clm28,
Clm31,
Clm32,
Clm33,
Clm34,
Clm35,
Clm36,
Clm37,
Clm38,
Clm41,
Clm42,
Clm43,
Clm44,
Clm45,
Clm46,
Clm47,
Clm48,
Clm51,
Clm52,
Clm53,
Clm54,
Clm55,
Clm61,
Clm62,
Clm63,
Clm64,
Clm65,
Clm71,
Clm72,
Clm73,
Clm74,
Clm75,
Clm81,
Clm82,
Clm83,
Clm84,
Clm85,
Button1,
Button2,
Button3,
Button4,
Button5,
Button7,
Button6,
Button8,
Button9,
Button10,
Button11,
Button12,
Button13,
Button15,
Button14,
Button16,
Button17,
Button18,
Button19,
Button20,
Button21,
Button22,
Button23,
Button25,
Button24,
Button26,
Button27,
Button28,
Button29,
Button30,
Button31,
Button32,
Button33,
Button35,
Button34,
Button36,
Button37,
Button38,
Button39,
Button40,
Button41,
Button42,
Button43,
Button44,
Button46,
Button45,
Button47,
Button48,
Button49,
Button50,
Button51,
Button52,
Button53,
Button54,
Button55,
Button56,
Button57,
Button58,
Button59,
Button60,
Button61,
Button62,
Button63,
Button64,
Button65,
Button66,
Button67,
Button68,
Button69,
Button70,
Button71,
Button72,
Button73,
Button74,
Button75,
Button76,
Button77,
Button78,
Button79,
Button80,
Button81,
Button82,
Button83,
Button84,
Button85,
Button86,
Button87,
Button88,
Button89,
Button90,
Button91,
Button92,
Button93,
Button94,
Button95,
Button96,
Button97,
Button98,
Button99,
Button100,
Button101,
Button102,
Button103,
Button104,
Button105,
Button106,
Button107,
Button108,
Button109,
Button110,
Button111,
Button112,
Button113,
Button114,
Button115,
Button116,
Button117,
Button118,
Button119,
Button120,
Button121,
Button122,
Button123,
Button124,
Button125,
Button126,
Button127,
Button128,
Button129,
Button130,
Button131,
Button132,
Button133,
Button134,
Button135,
Button136,
Button137,
Button138,
Button139,
Button140,
Button141,
Button142,
Button143,
Button144,
Button145,
Button146,
Button147,
Button148,
Button149,
Button150,
Button151,
Button152,
Button153,
Button154,
Button155,
Button156,
Button157,
Button158,
Button159,
Button160,
Button161,
Button162,
Button163,
Button164,
Button165,
Button166,
Button167,
Button168,
Button169,
Button170,
Button171,
Button172,
Button173,
Button174,
Button175,
Button176,
Button177,
Button178,
Button179,
Button180,
Button181,
Button182,
Button183,
Button184,
Button185,
Button186,
Button187,
Button188,
Button189,
Button190,
Button191,
Button192,
Button193,
Button194,
Button195,
Button196,
Button197,
Button198,
Button199,
Button200,
Button201,
Button202,
Button203,
Button204,
Button205,
Button206,
Button207,
Button208,
Button209,
Button210,
Button211,
Button212,
Button213,
Button214,
Button215,
Button216,
Button217,
Button218,
Button219,
Button220,
Button221,
Button222,
Button223,
Button224,
Button225,
Button226,
Button227,
Button228,
Button229,
Button230,
Button231,
Button232,
Button233,
Button234,
Button235,
Button236,
Button237,
Button238,
Button239,
Button240,
Button241,
Button242,
Button243,
Button244,
Button245,
Button246,
Button247,
Button248,
Button249,
Button250,
Button251,
Button252,
Button253,
Button254,
Button255,
Button256,
Button257,
Button258,
Button259,
Button260,
Remarks,
Complain) like '%" & valueToSearch & "%'"
Dim command As New SqlCommand(searchQuery, connection)
Dim adapter As New SqlDataAdapter(command)
Dim table As New DataTable()
adapter.Fill(table)
dgvTreatmentHistory.DataSource = table
End Sub
Private Sub PictureBox1_Click(sender As Object, e As EventArgs) Handles PictureBox1.Click
FilterData(txtPID.Text)
End Sub
End Class
Well, the error is clear enough: you are providing to many argument to theconcat
function. You could possibly perform the concatenation stepwise. For instance you could concatenate the first half of the argument sequence, then the second half, and finally concatenate the resulting strings.
[update]
Quote:How could I concatenate the halfs??
The idea is simple, suppose, for instance, you have{'a','b','c','d'}
, then:
concat( concat('a', 'b'), concat('c', 'd'))
[/update]
Concat[^] function concatenates two or more strings into single one, but you want to search for specific value in specific field. As to your code, you're trying to concatenate name of fields, instead of strings. A proper way to use CONCAT function is:
SELECT CONCAT('Hello', ',', ' ', 'World', '!')
I'd suggest to create Stored procedure[^] which can accept several arguments. If user can select field to search (by using radiobutton), your sp can look like this:
Use YourDatabaseName; CREATE STORED PROCEDURE usp_SearchDb @SearchedField NVARCHAR(30), @FindValue NVARCHAR(30) AS SET NOCOUNT ON; DECLARE @qry NVARCHAR(MAX) = N'SELECT * FROM YourTable WHERE ' + @SearchedField + ' Like %' + @FindValue + '%' EXEC(@qry) END
Note: Not tested, but it should works!
In case, when you need to query several columns at once, you have to read about Querying Multiple Columns (Full-Text Search)[^]
Good luck!
这篇关于concat函数需要2到254个参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!