Avatar of TonyReba
TonyReba
Flag for United States of America asked on

computed columns in gridview / sql server

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
Microsoft SQL ServerASP.NETC#

Avatar of undefined
Last Comment
BuggyCoder

8/22/2022 - Mon
BuggyCoder

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
TonyReba

ASKER
Ok, if I understand you correc, I am going to parse the varchar time to datetime and then substract in the code behind?
BuggyCoder

well i think that would be the way out.
Then use DateTime.Subtract(DateTime)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
TonyReba

ASKER
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 ;
         
         /*******************************************************************/
BuggyCoder

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

TonyReba

ASKER
Ok, so how do I replace the sample times with the input values?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
TonyReba

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

invalid expression term
BuggyCoder

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

TonyReba

ASKER
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);
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
TonyReba

ASKER
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;
     }
BuggyCoder

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

TonyReba

ASKER
this is the error on the page

Error: Sys.WebForms.PageRequestManagerServerErrorException: Input string was not in a correct format.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
BuggyCoder

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

ASKER
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
BuggyCoder

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
TonyReba

ASKER
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??
BuggyCoder

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

ASKER
I am suspecting that is what is going on,

whats the function to tear the ss part?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
TonyReba

ASKER
Failed to convert parameter value from a TimeSpan to a String.
ASKER CERTIFIED SOLUTION
BuggyCoder

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question