MS SQL RSS MS SQL RSS

conversion of datetime when data is NULL

Please support our MS SQL advertiser: Intel Parallel Studio Home
Reply

•
•
Join Date: Jul 2009
Posts: 13
Reputation: vytla is an unknown quantity at this point 
Solved Threads: 0
vytla vytla is offline Offline
Newbie Poster

conversion of datetime when data is NULL

 
0
  #1
Jul 11th, 2009
hi friends fetching datetime values from database, when data is null in code side it is displying the some garbage value instead that i want to show 'date is not found' the result is showind in grid view control

service written for fetching datetime


C# Syntax (Toggle Plain Text) 
  1.  
  2.     public List<Topics> GetAllTopicNames(int communityId)
  3.     {
  4.         List<Topics> communityTopiclist = new List<Topics>();
  5.  
  6.         try
  7.         {
  8.             string connectionString = ConfigurationSettings.AppSettings["connectionString"];
  9.             SqlConnection con = new SqlConnection(connectionString);
  10.             con.Open();
  11.             SqlCommand cmd = new SqlCommand("SELECT * from (select cd.Community_id,ct.Topic_id ,ct.Name,ct.Posts from Community_Details cd,Community_Topic_Details ct where ct.Community_Id = cd.Community_Id ) as C left join (select cr.topic_id , max(cr.reply_date) as RecentPost from Community_Topic_Details ctr,Community_Replys cr where ctr.topic_id=cr.topic_id group by cr.topic_id) as rep on rep .topic_Id=c.Topic_Id where C.Community_id=" + communityId.ToString(), con);
  12.  
  13.             SqlDataReader dr = cmd.ExecuteReader();
  14.             if (dr.HasRows != false)
  15.             {
  16.                 while (dr.Read())
  17.                 {
  18.                     Topics communityTopics = new Topics();
  19.                     if (dr["Topic_Id"] != System.DBNull.Value)      
  20.                     communityTopics.TopicId = int.Parse(dr["Topic_Id"].ToString());
  21.                     if (dr["Name"] != System.DBNull.Value)      
  22.                     communityTopics.TopicName = dr["Name"].ToString();
  23.                     if (dr["Posts"] != System.DBNull.Value)      
  24.                     communityTopics.TopicPosts = int.Parse(dr["Posts"].ToString());                                
  25.                     if (dr["RecentPost"] != System.DBNull.Value)
  26.                     communityTopics.LastPost = DateTime.Parse(dr["RecentPost"].ToString());
  27.  
  28.  
  29.                    communityTopiclist.Add(communityTopics);
  30.  
  31.                 }
  32.             }
  33.             else
  34.             {
  35.                 return null;
  36.             }
  37.  
  38.         }
  39.         catch (Exception ex)
  40.         {
  41.             return null;
  42.         }
  43.         return communityTopiclist;
  44.  
  45.  
  46.     }
  47.  
  48.  
  49.  
  50.   public void GetAllTopics()
  51.     {
  52.         CommunityManagement.CommunityManagementSoap topicService = new CommunityManagement.CommunityManagementSoapClient();
  53.         GetAllTopicNamesRequest topicRequest = new GetAllTopicNamesRequest();
  54.         topicRequest.Body = new GetAllTopicNamesRequestBody();
  55.         topicRequest.Body.communityId = int.Parse(Request.QueryString["communityId"]);
  56.         GetAllTopicNamesResponse topicResponse = topicService.GetAllTopicNames(topicRequest);
  57.         if (topicResponse != null && topicResponse.Body != null && topicResponse.Body.GetAllTopicNamesResult != null)
  58.         {
  59.  
  60.                 GridViewCommunityTopics.DataSource = topicResponse.Body.GetAllTopicNamesResult;
  61.                 GridViewCommunityTopics.DataBind();
  62.                 PanelMiddle.Controls.Add(GridViewCommunityTopics);
  63.  
  64.                 //.Controls.Add(GridViewCommunityTopics);
  65.  
  66.         }
  67.         else
  68.             GridViewCommunityTopics.DataSource = null;
  69.  
  70.     }
Last edited by peter_budo; Jul 11th, 2009 at 2:45 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
Reply With Quote Quick reply to this message  
•
•
Join Date: Dec 2004
Posts: 4,190
Reputation: peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of peter_budo has much to be proud of 
Solved Threads: 483
Moderator
Featured Poster
peter_budo's Avatar
peter_budo peter_budo is offline Offline
Code tags enforcer

Re: conversion of datetime when data is NULL

 
-1
  #2
Jul 11th, 2009
Is this ASP.NET or GUI question? (Just so I can move this post to more relevant forum section. MS SQL section is more of queries questions and DB maintenance)
Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, Graduate & Undergraduate Software Development Community, JAVAWUG (Java Web User Group), The London Android Group
Reply With Quote Quick reply to this message  
•
•
Join Date: Nov 2007
Posts: 5
Reputation: rathnakar is an unknown quantity at this point 
Solved Threads: 0
rathnakar rathnakar is offline Offline
Newbie Poster

Re: conversion of datetime when data is NULL

 
0
  #3
Jul 14th, 2009
hi friend ,
use following query in your code(sql command)

select case when date1 is null then 'date is not found' else convert(nvarchar,date1,105) end as date1 from datetest

regards,
rathnakar






•
•
•
•
Originally Posted by vytla View Post
hi friends fetching datetime values from database, when data is null in code side it is displying the some garbage value instead that i want to show 'date is not found' the result is showind in grid view control

service written for fetching datetime


C# Syntax (Toggle Plain Text) 
  1.  
  2.     public List<Topics> GetAllTopicNames(int communityId)
  3.     {
  4.         List<Topics> communityTopiclist = new List<Topics>();
  5.  
  6.         try
  7.         {
  8.             string connectionString = ConfigurationSettings.AppSettings["connectionString"];
  9.             SqlConnection con = new SqlConnection(connectionString);
  10.             con.Open();
  11.             SqlCommand cmd = new SqlCommand("SELECT * from (select cd.Community_id,ct.Topic_id ,ct.Name,ct.Posts from Community_Details cd,Community_Topic_Details ct where ct.Community_Id = cd.Community_Id ) as C left join (select cr.topic_id , max(cr.reply_date) as RecentPost from Community_Topic_Details ctr,Community_Replys cr where ctr.topic_id=cr.topic_id group by cr.topic_id) as rep on rep .topic_Id=c.Topic_Id where C.Community_id=" + communityId.ToString(), con);
  12.  
  13.             SqlDataReader dr = cmd.ExecuteReader();
  14.             if (dr.HasRows != false)
  15.             {
  16.                 while (dr.Read())
  17.                 {
  18.                     Topics communityTopics = new Topics();
  19.                     if (dr["Topic_Id"] != System.DBNull.Value)      
  20.                     communityTopics.TopicId = int.Parse(dr["Topic_Id"].ToString());
  21.                     if (dr["Name"] != System.DBNull.Value)      
  22.                     communityTopics.TopicName = dr["Name"].ToString();
  23.                     if (dr["Posts"] != System.DBNull.Value)      
  24.                     communityTopics.TopicPosts = int.Parse(dr["Posts"].ToString());                                
  25.                     if (dr["RecentPost"] != System.DBNull.Value)
  26.                     communityTopics.LastPost = DateTime.Parse(dr["RecentPost"].ToString());
  27.  
  28.  
  29.                    communityTopiclist.Add(communityTopics);
  30.  
  31.                 }
  32.             }
  33.             else
  34.             {
  35.                 return null;
  36.             }
  37.  
  38.         }
  39.         catch (Exception ex)
  40.         {
  41.             return null;
  42.         }
  43.         return communityTopiclist;
  44.  
  45.  
  46.     }
  47.  
  48.  
  49.  
  50.   public void GetAllTopics()
  51.     {
  52.         CommunityManagement.CommunityManagementSoap topicService = new CommunityManagement.CommunityManagementSoapClient();
  53.         GetAllTopicNamesRequest topicRequest = new GetAllTopicNamesRequest();
  54.         topicRequest.Body = new GetAllTopicNamesRequestBody();
  55.         topicRequest.Body.communityId = int.Parse(Request.QueryString["communityId"]);
  56.         GetAllTopicNamesResponse topicResponse = topicService.GetAllTopicNames(topicRequest);
  57.         if (topicResponse != null && topicResponse.Body != null && topicResponse.Body.GetAllTopicNamesResult != null)
  58.         {
  59.  
  60.                 GridViewCommunityTopics.DataSource = topicResponse.Body.GetAllTopicNamesResult;
  61.                 GridViewCommunityTopics.DataBind();
  62.                 PanelMiddle.Controls.Add(GridViewCommunityTopics);
  63.  
  64.                 //.Controls.Add(GridViewCommunityTopics);
  65.  
  66.         }
  67.         else
  68.             GridViewCommunityTopics.DataSource = null;
  69.  
  70.     }
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the MS SQL Forum
Thread Tools Search this Thread



connectingtodatabaseinuse count cursor database datepart deadlock delete_trigger highperformancecomputing hpc hpcserver2008 ibm iis loop maximum microsoft ms multithreading news number permission query result server sets sql sqlserver sqlserver2005 supercomputing uniqueid update view
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC