Create procedure USP_GetDetails
@RequirementID int
as
begin
select distinct ('QQ' + cast(YEAR(GETDATE()) as varchar) + cast(MONTH(GETDATE()) as varchar)+ REPLICATE('0', 4 - LEN(RequirementID)) + cast(RequirementID as varchar)) as RequirementID,
r.PositionTitle,r.JobDescription,Convert(varchar(20),r.OpenDate,1),c.City_Name,r.Experience from Tbl_RequirementModule r,Tbl_Cities c where r.JobLocation=c.City_ID and RequirementID=@RequirementID
end
PageDAL.cs:
public DataTable GetDdata(string RequirementID)
{
SqlConnection con = dbcon.OpenCoonection();
SqlCommand cmd = new SqlCommand("USP_GetDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.Parameters.AddWithValue("@RequirementID", RequirementID);
// Session["RequirementID"] = RequirementID;
DataSet ds = new DataSet();
DataTable dt = new DataTable();
da.Fill(dt);
var Req = dt.Rows[0][0];
var tittle = dt.Rows[0][1];
var jobDes = dt.Rows[0][2];
var OpnDate = dt.Rows[0][3];
var CName = dt.Rows[0][4];
var Exp = dt.Rows[0][5];
Session["ID"] = Req.ToString();
Session["PositionTitle"] = tittle.ToString();
Session["OpenDate"] = OpnDate.ToString();
Session["City_Name"] = CName.ToString();
Session["Experience"] = Exp.ToString();
Session["JobDescription"] = jobDes;
da.Fill(ds);
dbcon.CloseConnection();
return dt;
}
Am getting the error like "Error converting nvarchar to int" .Actually in database am storing the Requirement ID like int but while fetching the data from database ,am concatenating with string ,and datetime. For example ,am storing RequirementId=1 in db but while fetching am writing the query to get like "QQ20150001". So can any one help me how to solve this error?