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"])))
{
SrchUser.EmployeeNo = Request.QueryString["empid"].ToString();
SrchUser.FirstName = " ";
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)
{
}
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));
retVal = SqlHelper.ExecuteJSONDataSet(FileManager.GetSettingsValue("DBConnection"), "spmt_SearchBoxEmpDetails", 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;
Declare @SQL AS NVARCHAR(MAX)
SET @SQL = ''
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'],
colModel:[{name:'UM_Employee_Number',width:'150'},{name:'UM_FirstName',width:'150'}],
rowNum:5,
sortname:'EmpNum',
viewrecords:true,
caption:"Employee Search Result"
});