Stored Procedure:
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?
4 Replies
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Keertti Raj Thangavelu BabuPosted Apr 3, 2015, 4:53 AM
use Stored Procedure
Pls Check below link :
https://ask.sqlservercentral.com/questions/104602/auto-increment-for-a-varchar-column-1.html
--------------------------------------------------------------------------------------------
Mark it as "Accepted Answer", if its useful
Usha RajPosted Apr 3, 2015, 3:29 AM
Usha RajPosted Apr 3, 2015, 3:25 AM
Keertti Raj Thangavelu BabuPosted Apr 3, 2015, 3:02 AM
use this code instead of cmd.Parameters.AddWithValue("@RequirementID", RequirementID);
Command.Parameters.Add("@RequirementID", SqlDbType.Int).Value = Convert.ToInt32(RequirementID);
----------------------------------------------------------------------------
Mark it as "Accepted Answer", if its useful