computed columns in gridview / sql server

TonyReba
TonyReba used Ask the Experts™
on
Hi EE,

I am wondering if someone can help on the following issue, I have a sql database in table containing three nvarchar variables,  the reason i am using nvarchar is because the server instance i am using is sql server 2000 which has no separate field for date and time, so I stick to use nvarchar to input the:

Sign in Time
Registration Start
Registration End

Now I need two more columns that need to calculate

Wait Time  = Reg Start - Sign In Time
Total Registration time  = Reg End - Re Start

As you can see the issue relies on the data types, is there a workaround to this?
Can I Convert the data types on the asp.net code page to a time column and perform the substractions?
Meabe in Sql server?

Ultimate the goal is to self auto-compute this columns so I can represent in a asp.net GridView

Please advise, thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DateTime.parse("nvarchardatetimestring")

the above call will parse your nvarchar datetime string to DateTime .net type.
Provided the datetime is in correct format.

http://msdn.microsoft.com/en-us/library/1k1skd40.aspx

Author

Commented:
Ok, if I understand you correc, I am going to parse the varchar time to datetime and then substract in the code behind?
well i think that would be the way out.
Then use DateTime.Subtract(DateTime)
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
Hmmm, I dont think that would work, please see my current snippet,


       /* Calculate the WaitTime , RegStart and SigInTime are nvarchar fileds from the db inputed as 8:00 format*/

        TimeSpan RegStartConv =  TimeSpan.Parse("RegStart");
        TimeSpan SignInTimeConv = TimeSpan.Parse("SignInTime");
                       
        TimeSpan WaitTime = RegStartConv - SignInTimeConv ;
         
         /*******************************************************************/
well this one worked perfectly for me:-
            var st = TimeSpan.Parse("8:00");
            var ed = TimeSpan.Parse("5:30");

            st.Subtract(ed); //02:30:00 was the result...

Open in new window

Author

Commented:
Ok, so how do I replace the sample times with the input values?

Author

Commented:
var RegStartConv =  TimeSpan.Parse("RegStart");
        var SignInTimeConv = TimeSpan.Parse("SignInTime");
                       
        RegStartConv.Subtract(SignInTime);

invalid expression term
use executereader to get data from the database, lets suppose your reader variable is rd, then this is what you should do:-
if(rd.HasRows && rd.Read())
{
string stTime = rd["stTime"].ToString();
string edTime = rd["edTime"].ToString();

var st = TimeSpan.Parse(stTime);
var ed = TimeSpan.Parse(edTime);

var diff = st.Subtract(ed); //02:30:00 was the result..
}

Open in new window

Author

Commented:
var SignInTime = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSignInTime")).Text;

         var RegStart = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRegStart")).Text;

         var RegEnd= ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRegEnd")).Text;

       /* Calculate the WaitTime , RegStart and SigInTime are nvarchar fileds from the db inputed as 8:00 format*/

        var RegStartConv =  TimeSpan.Parse("RegStart");
        var SignInTimeConv = TimeSpan.Parse("SignInTime");
                       
        RegStartConv.Subtract(SignInTime);

Author

Commented:
This is my data retrieval code:

 private DataTable GetData(SqlCommand cmd)
     {
         DataTable dt = new DataTable();
         SqlConnection con = new SqlConnection(strConnString);
         SqlDataAdapter sda = new SqlDataAdapter();
         cmd.CommandType = CommandType.Text;
         cmd.Connection = con;
         con.Open();
         sda.SelectCommand = cmd;
         sda.Fill(dt);
         return dt;
     }
see this:-

var SignInTime = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSignInTime")).Text;

         var RegStart = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRegStart")).Text;

         var RegEnd= ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRegEnd")).Text;

       /* Calculate the WaitTime , RegStart and SigInTime are nvarchar fileds from the db inputed as 8:00 format*/

        var RegStartConv =  TimeSpan.Parse(RegStart);
        var SignInTimeConv = TimeSpan.Parse(SignInTime);
                       
        RegStartConv.Subtract(SignInTimeConv ); 

Open in new window

Author

Commented:
this is the error on the page

Error: Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in a correct format.
this means your time format is not correct, get it validated by using Regular expressions and only test your code on valid time expression....

you can also use TimeSpan.TryParse if you don't want your asp.net code to throw an exception in case the format is not correct...

Another thing if you are fetching it from database, then ensure that users enter right format and then only you store the same in DB....

Author

Commented:
I am using the ajax maskededitextender as follows in the Sign In Time, Reg Start, and Red End fields, so the input goes always as hh:mm,,, the columns are varchars,




 <asp:MaskedEditExtender ID="txtRegEnd_MaskedEditExtender" runat="server"
                                    CultureAMPMPlaceholder="" CultureCurrencySymbolPlaceholder=""
                                    CultureDateFormat="" CultureDatePlaceholder="" CultureDecimalPlaceholder=""
                                    CultureThousandsPlaceholder="" CultureTimePlaceholder="" Enabled="True"
                                     MaskType="Time" Mask="99:99" TargetControlID="txtRegEnd">



I would have gone with computed columns on sql, server  but I didnt know how..


Rest of the code->

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
     {
         string author = authUserName;
         string SignInId = ((Label)GridView1.Rows[e.RowIndex].FindControl("lblSignInId")).Text;

         string Dept = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtDept")).Text;
         string LName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtLName")).Text;
         string FName = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtFName")).Text;
         string Doctor = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtDoctor")).Text;
         string DateProc = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtDateProc")).Text;

         var SignInTime = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtSignInTime")).Text;

         var RegStart = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRegStart")).Text;

         var RegEnd= ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtRegEnd")).Text;

       /* Calculate the WaitTime , RegStart and SigInTime are nvarchar fileds from the db inputed as 8:00 format*/

        var RegStartConv =  TimeSpan.Parse("RegStart");
        var SignInTimeConv = TimeSpan.Parse("SignInTime");

        var diff = RegStartConv.Subtract(SignInTimeConv);
       
     

         /*******************************************************************/



         //string TotalRegTime= ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtTotalRegTime")).Text;
         
         string Comment = ((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtComment")).Text;

         string RegRepInt = ((DropDownList)GridView1.Rows[e.RowIndex].FindControl("txtRegRepInt")).SelectedValue;

                 
                 
         SqlConnection con = new SqlConnection(strConnString);

         SqlCommand cmd = new SqlCommand();

         cmd.CommandType = CommandType.Text;

         cmd.CommandText = "update [tbl_SignIns] set Dept=@Dept,LName=@LName, FName=@FName " +
         ",DoctorName=@Doctor, SignInTime=@SignInTime, RegStart=@RegStart, RegEnd=@RegEnd, RegWaitTime=@RegWaitTime, RegRepInt=@RegRepInt, Comment=@Comments, DateProc=@DateProc " +
         "    where SignInId=@SignInId;" +

          "select * from  [tbl_SignIns]";

         cmd.Parameters.Add("@SignInId", SqlDbType.VarChar).Value = SignInId;

         cmd.Parameters.Add("@Dept", SqlDbType.VarChar).Value = Dept;
         cmd.Parameters.Add("@LName", SqlDbType.VarChar).Value = LName;
         cmd.Parameters.Add("@FName", SqlDbType.VarChar).Value = FName;
         cmd.Parameters.Add("@Doctor", SqlDbType.VarChar).Value = Doctor;
         cmd.Parameters.Add("@DateProc", SqlDbType.DateTime).Value = DateProc;
         cmd.Parameters.Add("@SignInTime", SqlDbType.VarChar).Value = SignInTime;
         cmd.Parameters.Add("@RegStart", SqlDbType.VarChar).Value = RegStart;
         cmd.Parameters.Add("@RegEnd", SqlDbType.VarChar).Value = RegEnd;
         cmd.Parameters.Add("@RegWaitTime", SqlDbType.VarChar).Value =diff;
        // cmd.Parameters.Add("@TotalRegTime", SqlDbType.NVarChar).Value = TotalRegTime;
         cmd.Parameters.Add("@RegRepInt", SqlDbType.VarChar).Value = RegRepInt;
         cmd.Parameters.Add("@Comments", SqlDbType.VarChar).Value = Comment;
       

         GridView1.EditIndex = -1;

         GridView1.DataSource = GetData(cmd);

         GridView1.DataBind();

     }
testScreenShot.doc
you have to validate your textboxes before you even run your server side code, see here:-

http://igorgladkov.com/resources/demos/jquery.validator/demos/datetime_validation.html

Author

Commented:
hi, the input is going as hh:mm , I have entered manually on the db, anmd is fine,

the calculated column is the one is error

how would I use try parse to

hh:mm??
try to debug and see what is the result in diff variable of yours.
if it is in hh:mm:ss format, simply tear off the ss part and store it in db...

Author

Commented:
I am suspecting that is what is going on,

whats the function to tear the ss part?

Author

Commented:
Failed to convert parameter value from a TimeSpan to a String.
var ts = diff.ToString().Substring(0, diff.ToString().LastIndexOf(':'));

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial