computed columns in gridview / sql server

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

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)

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

Select allOpen in new window

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

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

Select allOpen in new window

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

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

Select allOpen in new window

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

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

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

I am suspecting that is what is going on,

whats the function to tear the ss part?

Failed to convert parameter value from a TimeSpan to a String.