I have a error with my code, when i start test with my web api and it shows object cannot be cast from dbnull change to other types and i have ticked the null box in sql server database. i don't want to change anything in database. The Mobile and datetime columns are null in sql server. I used asp.net web api 2 to do this project.
My Questions: How to solve the error without doing anything in the database?
here is my code:
public IHttpActionResult Get()
{
List<TestClass> draft = new List<TestClass>();
string mainconn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
string sqlquery = "Select * From tblTest";
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
SqlDataReader sdr = sqlcomm.ExecuteReader();
while (sdr.Read())
{
draft.Add(new TestClass()
{
UserId = Convert.ToInt32(sdr.GetValue(0)),
Name = sdr.GetValue(1).ToString(),
Mobile = sdr.GetValue(2).ToString(),
Access = Convert.ToInt32(sdr.GetValue(3)),
Date= Convert.ToDateTime(sdr.GetValue(4))
});
}
return Ok(draft);
}
My database in below:
UserId Name Mobile Access Date
11 John NULL 2 2012-01-02 00:00:00.000
24 Fred 34786584 5 NULL
56 Emily 18375555 0 2014-03-04 00:00:00.000
76 Lydia NULL 4 2015-09-08 00:00:00.000
87 Anna 12313147 5 2020-11-21 00:00:00.000
90 Mimi 27184641 1 NULL
CodePudding user response:
you are trying to convert a null value into DateTime which causes an error. Make your date nullable by adding a ? sign after the DateTime property.
CodePudding user response:
Check by DateTime.TryParseExact
DateTime.TryParseExact("YOURVALUE","yyyyMMdd",CultureInfo.InvariantCulture,DateTimeStyles.None, out resultvalue)?resultvalue.toString():"";
CodePudding user response:
You can check using IsDBNull:
Date = sdr.IsDBNull(4) ? null : Convert.ToDateTime(sdr.GetValue(4))
if Date column has type 'datetime' or 'datetime2' you can use GetDateTime:
Date = sdr.IsDBNull(4) ? null : sdr.GetDateTime(4)