SQL server Nvarchar parameters

I Have created an SP to search against many tables in the db based on string sent form ado

the Vb code

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

and the SQL SP is :

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

As you can see I have declared argument in VB as Nvarchr and SQL parameter @SearchKey as Nvarchar also if I send english data in @SearchKey search returns correct data, but if I tried to send arabic string in @SearchKey no results appeared knowing that there is arabic data inside the table

Am I missing something?

What Should I do further than that to allow arabic search?

Answer

You can try specifying a collation using the COLLATE keyword:

You’d need to specify arabic afterwards, for instance: COLLATE arabic_ci_as

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    
COLLATE arabic_ci_as

Advertisement

Answer