从sql数据库到表单C＃显示时间 [英] displaying time from sql database to your form C#

问题描述

我有一个表格可以将数据保存到mssql数据库中并且工作正常。

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

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希望将此表中存储的数据显示在各种文本框中并且

datetimepickers但我一直在设置错误s pecified cast无效在

此行activitystime =（DateTime）dr [Activity_Stime];请帮帮我

用于搜索的代码

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

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();
        }

推荐答案

SQL Time的等效CLR类型是TimeSpan，而不是DateTime。因此，activitystime变量应该是C＃中的TimeSpan类型，并且以后很容易将其转换为日期时间。



以下是SQL-CLR类型映射列表供您参考： https://msdn.microsoft.com/en-us/library/ms131092.aspx [ ^ ]

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[^]

我怀疑 dr [Activity_Sdate] 返回 null 值，因此可以将其转换为 DateTime 。



您可以检查 dr [Activity_Sdate] 返回 DbNull.Value [ ^ ]然后返回默认值，或者你可能想要使用 ?? 返回默认值（推荐）。

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).

activitysdate = dr["Activity_Sdate"] ?? DefaultDate

请参阅： ...运营商（C＃参考） [ ^ ]

您需要TimeSpan，将您的时间字段定义为TimeSpan并将其转换为如下所示

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

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

这篇关于从sql数据库到表单C＃显示时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！