我创建了一个存储过程,根据ado发送的字符串在数据库中搜索多个表
Vb代码
Shared ReadOnly Property Connection() As String
Get
Return ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
End Get
End Property
Function GetData(ByVal SearchKey As String) As DataTable
Dim sqlConn As New SqlConnection(Connection)
Dim ds As New DataSet
Dim sqlCmd As New SqlCommand("Search_List")
Dim sqlAdapter As New SqlDataAdapter
sqlCmd.CommandType = CommandType.StoredProcedure
sqlCmd.Connection = sqlConn
sqlAdapter.SelectCommand = sqlCmd
sqlCmd.Parameters.Add(New SqlParameter("@SearchKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, True, CByte(0), CByte(0), "SearchKey", DataRowVersion.Default, SearchKey))
sqlCmd.Parameters.Add("RerurnValue", SqlDbType.Int)
sqlCmd.Parameters("RerurnValue").Direction = ParameterDirection.ReturnValue
Try
sqlConn.Open()
sqlAdapter.Fill(ds, "Result")
sqlConn.Close()
Catch ex As Exception
Exit Function
End Try
Return ds.Tables("Result")
End Function
而且SQL SP是:
ALTER PROCEDURE [dbo].[Search_List](
@SearchKey NVARCHAR(200)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Rc bigint
DECLARE @Err bigint
set @SearchKey = '%'+@SearchKey+'%'
CREATE
TABLE
#Temp
(
ID BIGINT,
elementType NVARCHAR(10),
NameAr NVARCHAR(255),
NAmeEn NVARCHAR(255),
DescAr NVARCHAR(MAX),
DescEn NVARCHAR(MAX),
URL NVARCHAR(MAX)
)
INSERT INTO #Temp
SELECT
Id
,'C'
,NameAr
,NameEn
,DescAr
,DescEn
,'Counsel.aspx'
FROM
CMS_Councils
Where
(NameAr like @SearchKey
OR
NameEn Like @SearchKey
OR
DescAr Like @SearchKey
OR
DescEn Like @SearchKey)
AND
isnull(Status,0) = 1
select * from #Temp
end
正如您所看到的,我在VB中声明了参数为Nvarchr,并将SQL参数@SearchKey也声明为Nvarchar。如果我在@SearchKey中发送英文数据,则搜索会返回正确的数据,但如果我尝试在@SearchKey中发送阿拉伯字符串,则没有结果出现,尽管表格中存在阿拉伯数据。
我错过了什么吗?
我该怎么做才能允许阿拉伯语搜索?