displaying time from sql database to your form C#

Question

0.00/5 (No votes)

See more:

i have a form thats save data into mssql database and its work alright.

SQL

CREATE TABLE tblCeActivity
(
Ce_No char(4),
Ce_Name char(30),
Activity_Code int,
Prod_Job_Code int,
Activity_Sdate Datetime,
Activity_Stime time,
Comments Char(30),
Activity_Edate Datetime,
Activity_Etime time)

i want to display the data stored in this table unto the various textboxes and
datetimepickers but i keep geting the error "specified cast is not valid" at
this line activitystime = (DateTime)dr["Activity_Stime"]; please help me out
code used for the search

C#

public bool searchmember(string personid)
        {

            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

            conn.ConnectionString = "Data Source=USER-PC;Initial Catalog=CSIS;User ID=sa;Password=mike";
            conn.Open();

            SqlCommand cmd = new SqlCommand();
            string sqlQueryy = null;

            sqlQueryy = "select * from tblCeActivity where Ce_No='" + personid + "'";

            cmd.Connection = conn;
            cmd.CommandText = sqlQueryy;
            cmd.CommandType = System.Data.CommandType.Text;

            SqlDataReader dr = null;
            dr = cmd.ExecuteReader();

            if (dr.Read())
            {
                engineerno = dr["Ce_No"].ToString();
                engineername = dr["Ce_Name"].ToString();

                activitycode = dr["Activity_Code"].ToString();
                jobcode = dr["Prod_Job_Code"].ToString();
                activitysdate = (DateTime)dr["Activity_Sdate"];
                activitystime = (DateTime)dr["Activity_Stime"];
                comments = dr["Comments"].ToString();
                activityedate = (DateTime)dr["Activity_Edate"];
                activityetime = (DateTime)dr["Activity_Etime"];
                return true;
            }

            else
            {
                return false;
            }

            conn.Close();
        }

Posted 26-Aug-15 0:48am

mikeoabban

Updated 26-Aug-15 0:55am

Andy Lanng

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

nikhil-vartak · Answer 1 · 2015-08-26T01:08:00

The equivalent CLR type for SQL Time is TimeSpan, not DateTime. So activitystime variable should be of TimeSpan type in C# and it is easy to convert it to datetime later.

Here's the list of SQL-CLR type mappings for your reference: https://msdn.microsoft.com/en-us/library/ms131092.aspx[^]

Maciej Los · Answer 2 · 2015-08-26T01:03:00

I suspect that dr["Activity_Sdate"] returns null value, so it can be cast as DateTime.

You can check if dr["Activity_Sdate"] return DbNull.Value[^] then return default value or you may want to use ?? to return default value (recomended).

C#

activitysdate = dr["Activity_Sdate"] ?? DefaultDate

See: ?? Operator (C# Reference)[^]

DamithSL · Answer 3 · 2015-08-26T01:08:00

Solution 3

you need TimeSpan, define your time fields as TimeSpan and cast them as below

C#

TimeSpan activitystime = (TimeSpan)dr["Activity_Stime"];

Posted 26-Aug-15 1:08am

DamithSL

Schatak · Answer 4 · 2015-08-26T01:11:00

You can do another thing apart from Solution 1.
You can create a custom Function where you can pass your datatime and Format in which you want to convert your date.
along with custom function you can check DbNull as well.

C#

public static String DateToString(DateTime date, Int32 dateFormat)
  {
      String result = "";
      if (dateFormat == 1)
          result = date.ToString("MM/dd/yyyy");
      else
          result = date.ToString("dd/MM/yyyy");

      return result;

  }

C#

if (!DBNull.Value.Equals(dr["Date"])
                txtDate.Text = DateToString(Convert.ToDateTime(dr["Date"].ToString()), DateFormat);
            else
  txtDate.Text = "";

displaying time from sql database to your form C#

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0