Comments by Awoldeselassie (Top 59 by date)

Sorry, I realize that's confusing. It means most examples do slightly different thing, e.g. get list of all users rather than logged in user info. I don't really know a great deal about AD

Thanks for suggestion, I am probably having a brain freeze, but struggling to understand those articles.

Ok, I understand. but I really thought what I provided was enough to understand the question. If I ask you how do I write something on the screen using C#, you don't have to see any code or need any information to answer console.writeline "".

My code query the db and if it returns any rows, save it in a table. create new gridview ...Gridview emailGrid = new GridView.... and bind the table data to it. then send the gridview via email. When I receive the email the gridview has lines, and doesn't look anything like the rest of the email generated by the application. I would like to customise it i.e. change the colour, get rid of grid line, give it a nice header and so on using C#.

hope that is detailed enough for you.

maybe you should read the question before you answer or comment. C# code for customising a gridview, change the colour, get rid of the grid lines, make it more presentable. you don't always have to see my code to understand the question.

I created a webform and added gridview1. On the behind code, on page load, open connection and read data using a stored procedure. The stored procedure gets records (asset bookings) that have 'starting time' within the next 15 min. Then bind the data to gridview1. then count the Rows in gridview1, if count is greater than 0, then render gridview1 in HTML email body and send it. Oooooooshhh. mouth full.

i have a second gridview to perform the same operation for 'end time' too. but i need this to run every 15 min automatically.

I got a feeling that there is a better way of doing this!!!!

thank you Richard, very detailed blog, will have a read through

oo thanks, will try it let you know.

true....the answer to the question SA is ultimately, I would like to send a reminder e-mail automatically. I got that working using webform(i can explain how if you like), but i need it to run automatically without me starting it. if you have a better suggestion, i would appreciate it.

ok, thanks. will look into it.

Also, query is complicated time dependent query and pulls data out of four tables, I see you point, but I cant see an easier way of implementing this in the code behind rather than stored procedure. You answer worked for me CHILL60.

I see,.. it is only for viewing purpose, and the data is not going to be used once it leaves the table. also, when you have to send gridview as confirmation email, you want the data to take as little space as possible. so HH:MM:SS:mm is not good for viewing or space. I wont be using the data in the code behind again, so shouldn't be a problem if format changes to whatever, because that data is not coming back.

using C#, im executing a stored procedure. so I needed to change it on the procedure rather than code behind.

Spot on, although I didn't need to declare, Thanks

That has all the formats except the answer to my question. Thanks for suggesting anyways.

I hope it works, you suggestion is the best I found. I just didn't expect to change most of the code as I expected the foreach to check for each row, and not the table every time it loops.
thanks again man.

Your suggestion is perfect, I will go through my code and change it. thanks for the help

Ok, thanks for all your suggestions and for trying hard to help. Your solution was helpful.

yes you did, had to find out the hard way... every time the foreach loop iterate, it has to check using the IF, but I don't know how to get it to do that for the individual row, any suggestions?
At least I know the problem, half the battle is won..lol

After a week, I finally figured out what the problem is: the IF statement inside the foreach loop is checking the table every time it loops rather than the row. So if there is time overlap or not, its always going to be the same result for all rows. I just need a way of getting the IF statement to check each row rather than the table.

Yes I have, but debugging is not doing anything as the code is working properly. Its just not doing what I want it to do.

ok thanks, I have posted the entire code behind for the event handler. data is checked based on the user input. so I had to use a button and event handler. so you can see the 'while(reader.Read)'. I dont know if I got the foreach in the wrong place, or even if I should be looping through the table rows in the code behind, driving me nuts, lol

what you said earlier makes a lot of sense, its only using the current read to loop through the rows. data is bind using ItemTemplate as is shows in the code. It works but its just looping through

And yes, as you can see, I am using ItemTemplate for the checkbox and label.

I have added the code for GridView and databind. It is one reader, but shouldn't it loop through all rows as long as I use foreach?

Ok, thanks for the tip, My bad.

The problem is, even though it is looping through the rows, it only display one or the other (visible label for all rows, or visible checkbox for all rows). So if I have 10 row, but only one of the equipment is booked for the specified time, all checkbox is set to false and all label is set to true. What it should do is check each row, and set the visibility to true or false for each row independently depending on the specified time.
Thaks

Yes I have debugged, it is working and not throwing any exceptions, but not as its required. And no, as you can see from the code, I don't hold the overlap value, I used an if statement to toggle the visibility of the checkbox and label.

Sinisa Hajnal, I don't know what you mean by put the flag in the query. elaborate please?

The datasource for the gridview is a table callen 'tblAssets' as it display the assets.
Datasource for the reader to check if time overlap is occurring, is a table called tblBooking. It looks like its working, but it displays one or the other. If it find time overlap or clash, all assets are unavailable. otherwise all are available.

Your first sentence is correct. And yes, I have tried. But the RowDataEvent is outside the button event handler, therefore when I refer to the checkbox or label in the IF statement, it keeps saying 'it doesn't exists in this context' that would have been much easier, but I don't know how to reference, for e.g. 'CheckBoxAsset.Visible = false;' and 'LabelUnavailable.Visible = true;' in the IF statement as shown above. Sorry if I sound stupid

spot on mate, thanks for you help, good progress today.

Hi mate,
they are ItemTemplate, hence I cant directly reference to them.

Sorry for a lot of spelling mistakes.

After struggling for a while, I just fixed it. I think the data in the reader does not need be converted.

DateTime startDate = Convert.ToDateTime(reader["StartDate"]);
DateTime endDate = Convert.ToDateTime(reader["EndDate"]);

TimeSpan startTime = (TimeSpan)reader["StartTime"];
TimeSpan endTime = (TimeSpan)reader["EndTime"];

then check for every possibility to avoid time overlap using IF,

if (((startDate <= startDateTime && startDateTime <= endDate)
|| (startDate <= endDateTime && endDateTime <= endDate)
|| (startDate >= startDateTime && endDateTime >= endDate))
&&
((startTime <= starttime && starttime <= endTime)
|| (startTime <= endtime && endtime <= endTime)
|| (startTime >= starttime && endtime >= endTime)))

{

GridView1.Visible = false;

}
else
{
GridView1.Visible = true;
}

Now, I just need to access the checkbox and label within the gridview to make one visible and hind the other depending on weather there is a time overlap or not. At the moment, im hiding the gridview just to see if its checking the time right. So rather than:
GridView1.Visible = false;

I would like to:

checkbox.visible = false;
label.visible = true;

checkbox and label are within the gridview, do you know how to?

with the code above, it wont let me compare it with If statement because one will be 'System.DateTime' and the other 'System.TimeSpan'

with
TimeSpan startTime = Convert.ToDateTime(reader["StartTime"]).TimeOfDay;
TimeSpan endTime = Convert.ToDateTime(reader["EndTime"]).TimeOfDay;

I get a runtime error

Unable to cast object of type 'System.TimeSpan' to type 'System.IConvertible'.

I don't know what I am doing wrong, any suggestion please?

Thank you so much for that mate, it seems to be working, but I cant test it properly because now im getting another error. It is saying "Input string is not in the correct format" on the following line:

TimeSpan starttime = TimeSpan.ParseExact(StartTime.SelectedValue, "hh:mm", CultureInfo.InvariantCulture);
TimeSpan endtime = TimeSpan.ParseExact(FinishTime.SelectedValue, "hh:mm", CultureInfo.InvariantCulture);

I tried a few combination of the hh:mm format, but nothing worked, any idea?

Thanks for your answer mate, but that reads the new time from the textbox in the form. starttime = textbox called StartTime
startTime = needs to come from the reader, converted to TimeSpan.

So, I want to change this:-

DateTime startTime = Convert.ToDateTime(reader["StartTime"]);
DateTime endTime = Convert.ToDateTime(reader["EndTime"]);

which is data from the reader, to TimeSpan

yeah off course,.. thank you so much for your help Andy, I should add a button and see if it works.

Ok awesome thanks, so is that an event handler for a button, or addition to the js postback function?

Thanks Andy, I been trying your code for an hour but still no joy... You are right, all four boxes need to be populated before postback, and even then postback is not the problem as they can postback individually. The problem is checking the date and time against the database.

I am now thinking it might be easier to make the gridview visible = false and use button to check availability. That way I can add event handler to the button. Too much work, and makes the form look ugly, but I am struggling doing it this way. You think that is better way of handling it?

I have a StartDate textbox with the calendar, and a StartTime dropdown menu. Then EndDate textbox with the calendar, and Endtime dropdown menu

The calendar I used is a plugin called zebra_datepicker.

That is correct, the page postBack already for other dropdown. Then databound gridview and another dropdown menu populate base on the choice in the first dropdown menu. postBack is not the problem, it is comparing it to the database. In lame terms, I would like to check if an asset is booked for the time the user choose, and make it unavailable. This should be done as soon as the user choose the date and time. Then page postBack, compare it in the Booking table, and display either label or checkbox accordingly.

<asp:TextBox ID="StartDate" class="dropdownArrow1" runat="server"
Width="200px"
Height="20px" BorderColor="#cccccc" align="left" BackColor="#fffbec" BorderWidth="1px" BorderStyle="solid" OnTextChanged="StartDate_TextChanged">




<asp:Calendar ID="CalendarStartDate" runat="server" Visible="False">



</td>
<td class="style10">
<asp:DropDownList ID="StartTime" class="dropdownArrow1" runat="server"
Width="70px"
Height="20px" BorderColor="#cccccc" align="left" TextMode="MultiLine" BackColor="#fffbec" BorderWidth="1px" BorderStyle="solid">
<asp:ListItem>
<asp:ListItem>09:00
<asp:ListItem>09:15
<asp:ListItem>09:30 .....

The calender and time dropdown menu are there to enable the user to insert the date and time in text format, then that is inserted into a datetime field.

Hope this makes it a bit clearer.

There is a calendar control for date, and time is just a normal dropdown menu populated at runtime. It would be ideal when the user choose a date, if I can compare it with the startDate column of the tblBooking, and if it matches, then compare the StartTime too. If that matches too, or is in between the StartTime and EndTime, then Checkbox.visible = false and lable.visible = true. If not insert booking data into the database.

I have few dropdown. Every time a dropdown changes, the page postBack and populate a gridView and other dropdown depending on the first dropdown option.

Sorry if this sounds complicated.

Thanks for your suggestion Andy, but I have created the application using web form and fully working. I just need to add that functionality using whatever, (C# if possible, rather than creating a new webservice.

Thank you, the MS tutorial was helpful

Solved

Add a new datetime column and set the default to CURRENT_TIMESTAMP


SELECT B.BookingID,
S.StaffName,
A.Asset,
B.StartDate,
B.EndDate,
B.StartTime,
B.EndTime,
A.Campus,
B.Location,
B.Arrengement,
B.Messege
FROM tblBooking as B INNER JOIN tblAssets as A

on B.AssetID = A.AssetID
inner join tblStaff as S
on B.StaffID = S.StaffID
where B.TimeOfBooking >= dateadd(minute, -1, getdate())
and B.TimeOfBooking <= dateadd(minute, -0, getdate())


returns records a minute old.

I copied and entered your solution. Your script deals with minutes, but returns anything that has a StartTime of greater than GETDATE() + 5 minutes or less than GETDATE() - 5 minutes. So that is all records.

Yes you are right, the emails are sent at the time of booking. But I need a trigger that fires looking for new bookings in order to include them in the email. Trigger fires every two minutes looking for records that are only one minute old, then send email to all new records.

three types of email are included in the system:
1, send email to admin every morning with the days booking
WHERE StartDate = CONVERT(DateTime, DATEDIFF(DAY, 0, GETDATE()))
2, send reminder email to whoever responsible to the delivery before 30 min
where B.StartTime < convert (time, dateadd(minute, -30, getdate() ))
and B.StartTime > convert(time, dateadd(minute, -31, getdate()) )
and B.StartDate = CONVERT(DateTime, DATEDIFF(DAY, 0, GETDATE()))
3, send confirmation email to user immediately after booking
????

with your solution, it returns all records with StartDate <= today with < 5, or all records >= today with > 5, which is what I was able to do before.

however, I would like the query to return records that are entered in table within the last 5 minutes only. Or if you know a simpler way of sending booking confirmation email, I would appreciate it.

thanks for your help. I was using the convert because there is incompatible error without it as datediff is an int. I am no expert at this clearly, so please overlook my silly mistakes. I will try your solution now.

I have tried to resolve it using different function.. and tried researching.
for:
WHERE StartDate <= CONVERT(dateTime, DATEDIFF(minute, 5, GETDATE()))
I get error
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

Thanks for your response.
Like you said, it is much easier to use unique constraint. But I don't understand why a lot of people like to avoid triggers all together when triggers are more flexible.

if the SELECT result is NULL, then it returns NULL
AssetID identity (1,1) set on, it automatically assign a unique ID
I don't know how I can achieve the same result with proc. as it cant be triggered in response to an event. if you know another way of achieving the same result, I would appreciate your help.
table such as inserted, deleted are temporarily created by SSMS to manage when INSERT or DELETE is executed. you can temporarily refer to the tables to check for conditions.

Here is what I was hoping to achieve
each asset have a unique AR number. when entering new record, the script should compare the new AR number to the database. if it exists then tell the user it exists and roll back. if not then commit.
However, even if the AR number doesn't exists, I get the message 'The asset already exist in the database' and the transaction ends in the trigger.
when only executing SELECT, it works fine as it doesn't involve the trigger.

Yes I understand, I got it working just now, there was a script the program is calling every time it runs, which undo all the changes by copying the previous version from the server. I just have to compile it into .exe file. Thanks for your help anyways

I explained above that I have already tried that and it didn't work