Regarding Jqgrid And Sql Helpaer Class . I Want To Display Search Employee Details Using A Jqgrid

See more:

C#3.0

C#

Hi I'm, working in jqGrid.
I'm getting an Exception "Failed to convert parameter value from a SqlParameter to a String."


please could you please help me on this


CS Page

private User SrchUser = new User();
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {

        if ((!string.IsNullOrEmpty(Request.QueryString["empid"])))// && (!string.IsNullOrEmpty(Request.QueryString["UM_FirstName"])))
        {
            SrchUser.EmployeeNo = Request.QueryString["empid"].ToString();
            SrchUser.FirstName = " ";//Request.QueryString["UM_FirstName"].ToString();
            TCSRR.DMTController.Users SrchContrlrObj = new TCSRR.DMTController.Users();
            Response.Write(SrchContrlrObj.SearchEmp(SrchUser));
        }
    }

CONTROLER Code

public string SearchEmp(User SrchUser)
{
    try
    {
        TCSRR.DMTModel.UserData SrchUserData = new TCSRR.DMTModel.UserData();
        return SrchUserData.SearchEmpData(SrchUser);
    }
    catch (Exception ex)
    {
        // lstUser.Label = ex.Message;

    }
    return string.Empty;
}

MODEL Code

public String SearchEmpData(User SearchData)
       {
           DataSet ds;
           string retVal = string.Empty;
           try
           {
               SqlParameter[] arParms = new SqlParameter[2];
               arParms[0] = new SqlParameter("@UM_Employee_Number", (SearchData.EmployeeNo));
               arParms[1] = new SqlParameter("@UM_FirstName", (SearchData.FirstName));
               //arParms[1].Direction = ParameterDirection.Output;

               retVal = SqlHelper.ExecuteJSONDataSet(FileManager.GetSettingsValue("DBConnection"), "spmt_SearchBoxEmpDetails", arParms);
               //ds = SqlHelper.ExecuteDataset(FileManager.GetSettingsValue("DBConnection"), "spmt_SearchEmployee", arParms);

               if (!string.IsNullOrEmpty(retVal.Trim()) && retVal.Trim().IndexOf("Error")<=0 )
               {
                   retVal.ToJSON();
               }
           }
           catch (Exception ex)
           {
               ex.CustomException("USRSR_001", "MODEL : User Search Result failed.");
           }
           return retVal;
       }

SQL QRUERY

ALTER PROCEDURE [dbo].[spmt_SearchEmployee]
@UM_Employee_Number NVARCHAR(15),
@UM_FirstName NVARCHAR(30)=''
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Declare @SQL AS NVARCHAR(MAX)
	SET @SQL = ''
    -- Insert statements for procedure here
	SET @SQL = 'SELECT UM_Employee_Number , UM_FirstName'
	SET @SQL = @SQL + ' FROM User_Master'
	SET @SQL = @SQL + ' WHERE UM_Employee_Number LIKE ''%' + @UM_Employee_Number + '%'''
	
	IF RTRIM(LTRIM(@UM_FirstName)) != ''
		BEGIN
			SET @SQL = @SQL + ' OR UM_FirstName LIKE ''%' + @UM_FirstName + '%'''
		END

	EXEC(@SQL);
	
	END

JSON code

$("#jqgEmpSearch").jqGrid({
   url:"Servicelibrary/employeesearch.aspx?empid="+$("[id*=txtSrchEmpID]").val()+"",
   datatype:"json",
   colNames:['EmpNum','EmpName'], // UM_Employee_Number UM_FirstName
   colModel:[{name:'UM_Employee_Number',width:'150'},{name:'UM_FirstName',width:'150'}],
   rowNum:5,
   sortname:'EmpNum',
   viewrecords:true,
   caption:"Employee Search Result"
});