Comments by akhil.krish

akhil.krish 26-Jan-16 5:48am View

hello krishna rao,

iam using this code working fine...

Private Sub BindGrid()
Using ds As New DataSet()
ds.ReadXml(Server.MapPath("~/SiteInfo1.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo2.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo3.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo4.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo5.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo6.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo7.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo8.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo9.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo10.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo11.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo12.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo13.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo14.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo15.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo16.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo17.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo18.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo19.xml"))
ds.ReadXml(Server.MapPath("~/SiteInfo20.xml"))
GridView1.DataSource = ds
GridView1.DataBind()
End Using
End Sub

output file:
http://postimg.org/image/s1xtuzun7/

XML File....

<schema><sitename>
Al-Kharj
<devicename>
Netmate
<currdate>
29/06/2013
<currtime>
08:45
<temperature>
50.77
<humidity>
44.31
<waterleakage>
0
<smokedetect>
0
<ipaddress>
172.16.66.99
<contactname>
lijibin
<contactemail>
lijibin@139.com
<contactmobile>
13416424136
<contactphone>
02087344237

<generalinfo>
normal

<ipcam>
http://172.30.1.104

sir here i need any xml file error loop will continue .. leave to error XML file..

Environment Monitor device give every 5mins give to the XML file.. total 20 device is there.. some time any data center device problem.. leave to the that device remaining 19 device ping take the data..

errorfile:

http://postimg.org/image/pkvb2q745/

how to write this loop code.. here.

Advance Thanks

akhil.krish 26-Jan-16 2:31am View

Private Sub BindGrid()
Using ds As New DataSet()
ds.ReadXml(Server.MapPath("~/Customers.xml"))
GridView1.DataSource = ds
GridView1.DataBind()
End Using
End Sub

where to add your code.. i have 20xml file there..

20xml data display on grid view sir..

please help me..

thanks for reply...

akhil.krish 2-Jun-14 5:14am View

damith sl
checkbox also same code?

akhil.krish 2-Jun-14 4:48am View

DamithSL Thanks for reply and solution....

akhil.krish 29-May-14 5:36am View

outer join not working....

akhil.krish 22-May-14 6:11am View

Hi seeshil thanks for reply...
without user id data how to come.... eventlog table.. somebody absent not save the data...

so iam using right join

OutLog Query

SELECT
U.USER_ID, U.User_FirstName+ ' ' + U.User_LastName as [Employee Name] ,LOCALTIMESTAMP,EVENTID,
Convert(Date ,LOCALTIMESTAMP) as ATTDate FROM access_event_logs E Right JOIN access_user U ON E.USERID=U.User_ID
and (Convert(Date,LOCALTIMESTAMP) BETWEEN '20/may/2014' And '20/may/2014')
WHERE User_autoMatch='1' and (EVENTID ='out' or EVENTID is null) order by USER_ID

this is inlog query

SELECT distinct
U.USER_ID, U.User_FirstName+ ' ' + U.User_LastName ,LOCALTIMESTAMP,EVENTID,
Convert(Time ,LOCALTIMESTAMP) as ATTDate FROM access_event_logs E RIGHT JOIN access_user U ON E.USERID=U.User_ID
and (Convert(date,LOCALTIMESTAMP) BETWEEN '20/may/2014' And '20/may/2014')
WHERE U.User_ID is not null and User_autoMatch='1' and (EVENTID ='in' or EVENTID is null) order by USER_ID

result different showing...

inlog show 23rows between the date( 4members absent..so taltal present [in] 19

outlog show 21rows between the date ( 2 ids forget out not showing.. and 4members day total absent.. this 4 showing "null" but 2 ids not showing.. i can't understand)

Inlog:

http://tinypic.com/view.php?pic=2m5gm50&s=8

Outlog:

http://tinypic.com/view.php?pic=hs13jp&s=8#.U33M3vmSyYM

if u know pls tell me... whats is the problem.. and here one more problem also there Event[in] 22 user id times showing... but i wan't 1st IN only

akhil.krish 22-May-14 5:24am View

Hi, Thavan rajan.. Thanks for rply....

Same result .........

akhil.krish 22-May-14 1:57am View

Hi Thava Rajan... thanks for rply...

EventLog Table like this

http://tinypic.com/view.php?pic=2nau0aq&s=8

iam using crystal report.. for daily attendace...

device only save present attendace for eventlog table.... my problem crystal report.. some body absent show the absent that day.....

http://tinypic.com/view.php?pic=jze3nr&s=8#.U3x1p_mSyYM

so need like this report.... if u know please help me.....

akhil.krish 21-May-14 5:46am View

EventLog Table like this

http://tinypic.com/view.php?pic=2nau0aq&s=8

iam using crystal report.. for daily attendace...

device only save present attendace for eventlog table.... my problem crystal report.. some body absent show the absent that day.....

http://tinypic.com/view.php?pic=jze3nr&s=8#.U3x1p_mSyYM

so need like this report.... if u know please help me.....

akhil.krish 21-May-14 1:06am View

HI RDBurmon.... Thanks for rply..

This my table structure...

,[USERID]nvarchar(50)
,[DEPARTMENT]nvarchar(50)
,[TIMESTAMPS]datetime
,[EVENTID]nvarchar(50)
,[TERMINALSN]nvarchar(50)
,[ACCESSMETHOD]nvarchar(255)
,[REMARKS]nvarchar(255)
,[TERMINALIP]nvarchar(50)
,[PHOTO]image
,[PHOTOSIZE]int
,[RECLOGDTM]datetime
,[RECLOGFROMIP]nvarchar(50)
,[LOCALTIMESTAMP]datetime
,[PhotoPath]nvarchar(50)

and iam using another table also.. just for name purpose This table (access_user )

akhil.krish 20-May-14 11:06am View

did u see my image......

akhil.krish 20-May-14 9:57am View

HI CHILL,
update my code.. after update result below like this

http://tinypic.com/view.php?pic=e9yy39&s=8#.U3te7fmSyYM

akhil.krish 20-May-14 9:17am View

hi chill thanks for rply

not working both......

akhil.krish 15-May-14 13:16pm View

thanks for rply damith.....

iam using windows application... using almost 14forms but .. main employee payment form and employee rgistraion form.... if user change any value ... store the log ... admin see the last update... like this i wan't.....

akhil.krish 11-Apr-14 4:02am View

hi,

i want total late time, but +time also add my query..... that is my problem

late time +8 eariy out time -18 total late -10 coming but i want only -time

akhil.krish 10-Apr-14 6:02am View

ok updated..... did u see latest my update.....

akhil.krish 10-Apr-14 5:42am View

http://tinypic.com/view.php?pic=29cndvm&s=8#.U0ZnS_mSwZw

akhil.krish 8-Apr-14 7:30am View

http://postimg.org/image/v0axnzt7x/

akhil.krish 8-Apr-14 6:41am View

it is very urgent sir please help me.....

akhil.krish 8-Apr-14 6:36am View

Deleted

select distinct
CE.USERID,ce2.User_FirstName,
CONVERT(Date,Localtimestamp) as LocalTime,
(select min(TIMESTAMPS)
from access_event_logs As MINCE
where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS)
from access_event_logs As MAXCE
where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE Inner join access_user as ce2 on ce.userid = ce2.User_FirstName
WHERE USERID='012' AND Convert(date,LOCALTIMESTAMP) between '03/2/2014' and '03/16/2014'

i tried this one output no data sir

akhil.krish 8-Apr-14 6:33am View

Deleted

1st query worng data giving ......

37 012 2014-03-04 08:29:43.000 2014-03-03 14:31:58.000

(in) 4th date (out) 03 date

akhil.krish 8-Apr-14 5:58am View

Deleted

select distinct CE.USERID, CONVERT(Date,Localtimestamp) as LocalTime,
(select min(TIMESTAMPS) from access_event_logs As MINCE
where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE
where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime from access_event_logs As CE
WHERE USERID='019' AND Convert(date,LOCALTIMESTAMP) between '3/1/2014' and '3/18/2014'

this code how add another table code sir.....

i want employee-registration table take the employee name....

akhil.krish 8-Apr-14 2:52am View

vijay can u send u r mail id please........

akhil.krish 8-Apr-14 2:32am View

thanks for rply Maciej Los...

i want create another table base on event log table sir....dynamic update the data...abother table based on event_log

akhil.krish 7-Apr-14 16:19pm View

any updates.....

akhil.krish 7-Apr-14 8:31am View

hi Schatak,

my table [access_event_logs ]
,[USERID]nvarchar(50)
,[TIMESTAMPS]datetime
,[EVENTID]nvarchar(50)
,[LOCALTIMESTAMP]datetime

out put like this

[USERID] [TIMESTAMPS] [EVENTID]

1 019 2014-03-06 07:50:48.000 IN
2 019 2014-03-06 17:02:39.000 OUT
3 019 2014-03-09 07:43:37.000 IN
4 019 2014-03-09 14:34:59.000 OUT
5 019 2014-03-10 07:43:34.000 IN
6 019 2014-03-10 14:30:24.000 OUT

eventid (in and out ) only single row...

check the 1st sql query...

akhil.krish 7-Apr-14 5:33am View

Thanks For Reply Schatak.....

s iwant another column late and earily late time

akhil.krish 2-Apr-14 16:15pm View

Hi Maciej Los,

this query working fine

select distinct CE.USERID,
(select min(TIMESTAMPS) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE
where LOCALTIMESTAMP between '03/03/2014' And '03/06/2014' and USERID= '019'

but i need 2more columns also 1) between the date Total time calculate 2)current date

please help me.... i need very urgent

akhil.krish 2-Apr-14 16:14pm View

HI Mike Meinz,

this query working fine

select distinct CE.USERID,
(select min(TIMESTAMPS) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE
where LOCALTIMESTAMP between '03/03/2014' And '03/06/2014' and USERID= '019'

but i need 2more columns also 1) between the date Total time calculate 2)current date

please help me....

akhil.krish 2-Apr-14 16:09pm View

Deleted

HI Maciej Los...
this query working fine but i want 2more columns also..
1) between time calculate the time ( total time )
2) current date

please help me

akhil.krish 2-Apr-14 16:06pm View

select distinct CE.USERID,
(select min(TIMESTAMPS) from access_event_logs As MINCE where CAST(MINCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE where CAST(MAXCE.TIMESTAMPS as DATE)=CAST(CE.TIMESTAMPS As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE
where LOCALTIMESTAMP between '03/03/2014' And '03/06/2014' and USERID= '019'

this query working fine but i want 2more columns.. 1) total time calculate between the time and and current date

please help me..

akhil.krish 2-Apr-14 6:57am View

query execute
select distinct
CE.USERID,
(select min(TIMESTAMPS) from access_event_logs As MINCE where MINCE.EVENTID ='In' and MINCE.USERID=CE.USERID) As MinTime,
(select max(TIMESTAMPS) from access_event_logs As MAXCE where MAXCE.EVENTID ='Out' and MAXCE.USERID=CE.USERID) As MaxTime
from access_event_logs As CE

after output like this

001 2014-01-15 08:20:17.000 2014-04-01 11:21:02.000
002 2014-01-15 08:06:19.000 2014-04-02 10:06:46.000
003 2014-01-14 11:00:31.000 2014-03-19 11:20:03.000
004 2014-01-15 07:35:20.000 2014-04-01 11:34:38.000
005 2014-01-15 08:28:32.000 2014-04-01 11:27:15.000
006 2014-01-15 08:03:44.000 2014-04-01 11:18:02.000
007 2014-01-15 09:18:56.000 2014-04-02 10:24:57.000
008 2014-01-16 08:23:06.000 2014-04-02 10:23:56.000
009 2014-01-15 08:53:19.000 2014-04-01 11:18:32.000
010 2014-01-15 08:32:07.000 2014-04-01 11:20:02.000
011 2014-01-15 07:56:49.000 2014-04-02 10:24:51.000
012 2014-01-15 08:28:44.000 2014-04-01 11:22:09.000
013 2014-01-15 08:22:01.000 2014-04-02 10:22:25.000
014 2014-01-15 08:15:36.000 2014-04-02 09:00:09.000

but i want same day IN and OUT, somebody forget the out that value null...

how to solve this problem

akhil.krish 2-Apr-14 6:43am View

Hi vijay me also same problem... please help me...

http://www.codeproject.com/Questions/753217/how-to-create-Pivot-table

akhil.krish 2-Apr-14 2:38am View

see here 19th date in (2014-02-19 08:19:25.000) out(2014-02-18 14:18:22.000) out 18th date .. some body forget the OUT i want null value..

AND SAME DAY ONLY TAKE IN AND OUT REMAINING NOT CALCULATE...first in first out,
my office timings 7:30 am to 2:30 pm

akhil.krish 2-Apr-14 2:30am View

see here........
actually my [access_event_logs] table

id USERID TIMESTAMPS EVENTID
622 012 2014-02-06 09:08:04.000 IN
642 012 2014-02-06 14:27:33.000 OUT
654 012 2014-02-09 08:36:27.000 IN
672 012 2014-02-09 14:24:46.000 OUT
687 012 2014-02-10 08:20:08.000 IN
717 012 2014-02-10 14:44:52.000 OUT
722 012 2014-02-11 08:26:02.000 IN
748 012 2014-02-11 14:31:16.000 OUT
759 012 2014-02-12 08:02:35.000 IN
780 012 2014-02-12 14:28:35.000 OUT
787 012 2014-02-13 08:01:50.000 IN
809 012 2014-02-13 14:27:37.000 OUT
821 012 2014-02-16 08:19:07.000 IN
846 012 2014-02-16 14:33:22.000 OUT
854 012 2014-02-17 08:14:45.000 IN
879 012 2014-02-17 14:28:25.000 OUT
885 012 2014-02-17 18:58:02.000 OUT
886 012 2014-02-17 18:58:41.000 IN
888 012 2014-02-18 00:06:53.000 OUT
894 012 2014-02-18 08:00:12.000 IN
914 012 2014-02-18 14:18:22.000 OUT
936 012 2014-02-19 08:19:25.000 IN
958 012 2014-02-19 14:33:17.000 OUT
966 012 2014-02-20 08:14:40.000 IN
995 012 2014-02-20 14:26:54.000 OUT
1003 012 2014-02-23 08:15:37.000 IN
1036 012 2014-02-23 14:25:49.000 OUT
1049 012 2014-02-24 08:25:44.000 IN
1072 012 2014-02-24 14:12:58.000 OUT
1090 012 2014-02-25 08:26:49.000 IN
1107 012 2014-02-25 13:58:39.000 OUT
1128 012 2014-02-26 08:41:24.000 IN
1149 012 2014-02-26 14:09:11.000 OUT
1164 012 2014-02-27 08:36:38.000 IN
1191 012 2014-02-27 14:17:26.000 OUT
1207 012 2014-03-02 08:47:07.000 IN
1238 012 2014-03-02 14:49:57.000 OUT
1246 012 2014-03-03 08:27:01.000 IN
1275 012 2014-03-03 14:31:58.000 OUT
1285 012 2014-03-04 08:29:43.000 IN
1310 012 2014-03-04 14:32:12.000 OUT
1323 012 2014-03-05 08:34:29.000 IN
1340 012 2014-03-05 14:23:29.000 OUT
1353 012 2014-03-06 08:16:55.000 IN
1371 012 2014-03-06 14:31:12.000 OUT
1386 012 2014-03-09 08:36:04.000 IN
1414 012 2014-03-09 14:36:59.000 OUT
1424 012 2014-03-10 08:20:11.000 IN
1453 012 2014-03-10 19:18:14.000 IN
1467 012 2014-03-11 09:15:55.000 OUT
1468 012 2014-03-11 09:16:00.000 F1
1487 012 2014-03-11 14:31:35.000 OUT
1504 012 2014-03-12 08:55:45.000 IN
1522 012 2014-03-12 14:30:29.000 OUT
1536 012 2014-03-13 08:35:52.000 IN
1567 012 2014-03-13 14:38:58.000 OUT
1582 012 2014-03-16 08:46:06.000 IN
1603 012 2014-03-16 14:27:56.000 OUT
1619 012 2014-03-17 08:45:15.000 IN
1645 012 2014-03-17 14:26:25.000 OUT
1667 012 2014-03-18 10:12:19.000 IN
1682 012 2014-03-18 14:26:33.000 OUT
1698 012 2014-03-19 05:48:03.000 IN
1723 012 2014-03-19 11:11:36.000 OUT
1737 012 2014-03-20 05:50:27.000 IN
1751 012 2014-03-20 11:19:26.000 OUT
1766 012 2014-03-23 05:51:12.000 IN
1784 012 2014-03-23 11:19:10.000 OUT

akhil.krish 2-Apr-14 1:27am View

Hi Maciej Los ,

check u r solution..
same problem... output like this

18 012 2014-02-06 00:00:00.000 2014-02-06 09:08:04.000 2014-02-06 14:27:33.000 5
19 012 2014-02-09 00:00:00.000 2014-02-09 08:36:27.000 2014-02-09 14:24:46.000 6
20 012 2014-02-10 00:00:00.000 2014-02-10 08:20:08.000 2014-02-10 14:44:52.000 6
21 012 2014-02-11 00:00:00.000 2014-02-11 08:26:02.000 2014-02-11 14:31:16.000 6
22 012 2014-02-12 00:00:00.000 2014-02-12 08:02:35.000 2014-02-12 14:28:35.000 6
23 012 2014-02-13 00:00:00.000 2014-02-13 08:01:50.000 2014-02-13 14:27:37.000 6
24 012 2014-02-16 00:00:00.000 2014-02-16 08:19:07.000 2014-02-16 14:33:22.000 6
25 012 2014-02-17 00:00:00.000 2014-02-17 08:14:45.000 2014-02-17 14:28:25.000 6
26 012 2014-02-17 00:00:00.000 2014-02-17 18:58:41.000 2014-02-17 18:58:02.000 0
27 012 2014-02-18 00:00:00.000 2014-02-18 08:00:12.000 2014-02-18 00:06:53.000 -8
28 012 2014-02-19 00:00:00.000 2014-02-19 08:19:25.000 2014-02-18 14:18:22.000 -18
29 012 2014-02-20 00:00:00.000 2014-02-20 08:14:40.000 2014-02-19 14:33:17.000 -18
30 012 2014-02-23 00:00:00.000 2014-02-23 08:15:37.000 2014-02-20 14:26:54.000 -66
31 012 2014-02-24 00:00:00.000 2014-02-24 08:25:44.000 2014-02-23 14:25:49.000 -18
32 012 2014-02-25 00:00:00.000 2014-02-25 08:26:49.000 2014-02-24 14:12:58.000 -18
33 012 2014-02-26 00:00:00.000 2014-02-26 08:41:24.000 2014-02-25 13:58:39.000 -19
34 012 2014-02-27 00:00:00.000 2014-02-27 08:36:38.000 2014-02-26 14:09:11.000 -18
35 012 2014-03-02 00:00:00.000 2014-03-02 08:47:07.000 2014-02-27 14:17:26.000 -66
36 012 2014-03-03 00:00:00.000 2014-03-03 08:27:01.000 2014-03-02 14:49:57.000 -18
37 012 2014-03-04 00:00:00.000 2014-03-04 08:29:43.000 2014-03-03 14:31:58.000 -18
38 012 2014-03-05 00:00:00.000 2014-03-05 08:34:29.000 2014-03-04 14:32:12.000 -18
39 012 2014-03-06 00:00:00.000 2014-03-06 08:16:55.000 2014-03-05 14:23:29.000 -18
40 012 2014-03-09 00:00:00.000 2014-03-09 08:36:04.000 2014-03-06 14:31:12.000 -66
41 012 2014-03-10 00:00:00.000 2014-03-10 08:20:11.000 2014-03-09 14:36:59.000 -18
42 012 2014-03-10 00:00:00.000 2014-03-10 19:18:14.000 2014-03-11 09:15:55.000 14
43 012 2014-03-12 00:00:00.000 2014-03-12 08:55:45.000 2014-03-11 14:31:35.000 -18
44 012 2014-03-13 00:00:00.000 2014-03-13 08:35:52.000 2014-03-12 14:30:29.000 -18
45 012 2014-03-16 00:00:00.000 2014-03-16 08:46:06.000 2014-03-13 14:38:58.000 -66
46 012 2014-03-17 00:00:00.000 2014-03-17 08:45:15.000 2014-03-16 14:27:56.000 -18
47 012 2014-03-18 00:00:00.000 2014-03-18 10:12:19.000 2014-03-17 14:26:25.000 -20
48 012 2014-03-19 00:00:00.000 2014-03-19 05:48:03.000 2014-03-18 14:26:33.000 -15
49 012 2014-03-20 00:00:00.000 2014-03-20 05:50:27.000 2014-03-19 11:11:36.000 -18
50 012 2014-03-23 00:00:00.000 2014-03-23 05:51:12.000 2014-03-20 11:19:26.000 -66

akhil.krish 1-Apr-14 18:00pm View

thanks for rply Maciej Los .....:-)

012 2014-02-17 08:14:45.000 2014-02-17 14:28:25.000 6
012 2014-02-17 18:58:41.000 2014-02-17 18:58:02.000 0
012 2014-02-18 08:00:12.000 2014-02-18 00:06:53.000 -8
012 2014-02-19 08:19:25.000 2014-02-18 14:18:22.000 -18
012 2014-02-20 08:14:40.000 2014-02-19 14:33:17.000 -18
012 2014-02-23 08:15:37.000 2014-02-20 14:26:54.000 -66
012 2014-02-24 08:25:44.000 2014-02-23 14:25:49.000 -18
012 2014-02-25 08:26:49.000 2014-02-24 14:12:58.000 -18
012 2014-02-26 08:41:24.000 2014-02-25 13:58:39.000 -19
012 2014-02-27 08:36:38.000 2014-02-26 14:09:11.000 -18
012 2014-03-02 08:47:07.000 2014-02-27 14:17:26.000 -66
012 2014-03-03 08:27:01.000 2014-03-02 14:49:57.000 -18
012 2014-03-04 08:29:43.000 2014-03-03 14:31:58.000 -18
012 2014-03-05 08:34:29.000 2014-03-04 14:32:12.000 -18
012 2014-03-06 08:16:55.000 2014-03-05 14:23:29.000 -18
012 2014-03-09 08:36:04.000 2014-03-06 14:31:12.000 -66
012 2014-03-10 08:20:11.000 2014-03-09 14:36:59.000 -18
012 2014-03-10 19:18:14.000 2014-03-11 09:15:55.000 14
012 2014-03-12 08:55:45.000 2014-03-11 14:31:35.000 -18
012 2014-03-13 08:35:52.000 2014-03-12 14:30:29.000 -18
012 2014-03-16 08:46:06.000 2014-03-13 14:38:58.000 -66
012 2014-03-17 08:45:15.000 2014-03-16 14:27:56.000 -18
012 2014-03-18 10:12:19.000 2014-03-17 14:26:25.000 -20
012 2014-03-19 05:48:03.000 2014-03-18 14:26:33.000 -15
012 2014-03-20 05:50:27.000 2014-03-19 11:11:36.000 -18
012 2014-03-23 05:51:12.000 2014-03-20 11:19:26.000 -66
012 2014-03-24 05:58:08.000 2014-03-23 11:19:10.000 -18

12 user id attendance coming worng... why

akhil.krish 1-Apr-14 17:37pm View

id (pk,bigint)

akhil.krish 1-Apr-14 17:36pm View

Ok let me start from the beginning. I have a table under ACCESS named as LOGS. Following is the structure of the table:

_______userid_______LOCALTIMESTAMP
======================================
______007_____________2014-01-14 08:00:31.000 AM
______007_____________2014-01-14 15:00:31.000 PM

What i want is, i need a query to run which should appear to me as:

_____________Userid_______IN TIME_____________OUT TIME
================================================== ========
____________007_____________01/02/09 07:00 AM___01/02/09 17:30 PM

Currently logs are dumped into this table each log as a row. There are maximum two logs per date and I want to merge them into one.

Means the query should return me two records in one or in simple way: It should filter the userid (007) on date (01/02/09) and return this emp minimum time (time in) and maximum time (time out) in two fields and this "view merge" should be in one row so I can use in reports.

One more thing is that what can be done if more than two logs exists for same emp on same day

akhil.krish 1-Apr-14 17:32pm View

There are hundreds of records like ID: 1 and 2. but what I need for now is INTIME and OUTTIME record to be linked to one seperate query or view for the records having SAME DATE. I don't want to do this using table dump method.

akhil.krish 1-Apr-14 17:27pm View

,[USERID]nvarchar(50)
,[DEPARTMENT]nvarchar(50)
,[TIMESTAMPS]datetime
,[EVENTID]nvarchar(50)
,[TERMINALSN]nvarchar(50)
,[ACCESSMETHOD]nvarchar(255)
,[REMARKS]nvarchar(255)
,[TERMINALIP]nvarchar(50)
,[PHOTO]image
,[PHOTOSIZE]int
,[RECLOGDTM]datetime
,[RECLOGFROMIP]nvarchar(50)
,[LOCALTIMESTAMP]datetime
,[PhotoPath]nvarchar(50)

akhil.krish 1-Apr-14 17:20pm View

actually my table 23 users ..

after execute this query output like this

001 2014-03-24 09:09:20.000 2014-03-24 14:18:54.000 5
002 2014-03-24 09:59:36.000 2014-03-24 13:43:49.000 4
003 2014-03-20 08:33:05.000 2014-03-19 14:20:03.000 -18
004 2014-03-24 07:40:28.000 2014-03-24 14:30:55.000 7
005 2014-03-24 08:03:06.000 2014-03-24 14:23:21.000 6
006 2014-03-24 09:46:58.000 2014-03-24 14:19:52.000 5
007 2014-03-24 10:59:07.000 2014-03-24 14:13:30.000 4
008 2014-03-20 09:50:23.000 2014-03-20 14:00:34.000 5
009 2014-03-24 08:58:26.000 2014-03-24 14:19:44.000 6
010 2014-03-23 09:24:59.000 2014-03-23 13:10:39.000 4
011 2014-03-23 10:08:19.000 2014-03-23 14:17:35.000 4
012 2014-03-24 08:58:08.000 2014-03-24 14:20:52.000 6
013 2014-03-19 09:12:38.000 2014-03-19 13:47:47.000 4

akhil.krish 1-Apr-14 15:36pm View

Hi Maciej los ,
PIVOT(MAX(timestamps) FOR eventid IN ([IN],[OUT])) AS PT
max means last date giving .. i want between the date.......

akhil.krish 1-Apr-14 14:41pm View

ok thanks for rply...

some time in two time or out time..but i want select the 1st in 1st out select same date and and some forget out , forget the IN that employee abesent another column..

akhil.krish 1-Apr-14 14:25pm View

thanq so much.. Maciej los...this is working fine
i need u r mail id one more doubt is there.. pls send u r mail id.....

akhil.krish 1-Apr-14 14:18pm View

Deleted

query not working macij los...

this error
Msg 488, Level 16, State 1, Line 8
Pivot grouping columns must be comparable. The type of column "PHOTO" is "image", which is not comparable.

akhil.krish 1-Apr-14 14:15pm View

hi maciej los thanks for reply...

total means user particular date in value 1, out value 1, total 2, some time only IN total value 1..

akhil.krish 1-Apr-14 13:32pm View

Deleted

Thank You for Reply ..Schatak

USERID nvarchar(50)
TIMESTAMPS datetime
EVENTID nvarchar(50)

akhil.krish 30-Mar-14 6:59am View

Thanks for rply arathi.....

akhil.krish 30-Mar-14 3:44am View

yes.peter last time iam notice change the query.......

akhil.krish 30-Mar-14 3:36am View

actually my company using actatek thumb machine ...machine give database..

(acees_event_log) table this columns only...
,[USERID]
,[DEPARTMENT]
,[EVENTID]
,[TERMINALSN]
,[ACCESSMETHOD]
,[REMARKS]
,[LOCALTIMESTAMP]

type of this columns only

akhil.krish 30-Mar-14 3:24am View

Thanks For rply Peter Leow..

i tried this query. output no column name.....

012 user id betwwen the date 2days only in for that dates....

u r query execute output is no column name......

akhil.krish 30-Mar-14 2:28am View

Thanks for rply...

same problem.. i want between the date present days.....some employee only (in) sum employee only (out) he forget thumb..so employee (in and out) then 1 present day...

so i want count query between date (in and out) same day...

akhil.krish 11-Mar-14 3:27am View

MY Biofinger machine " Actatek" please help me.......me aslo same project..........

akhil.krish 14-Jan-14 0:36am View

Hi gitanjali singh... if u possible send vb.code......

akhil.krish 8-Jan-14 3:18am View

hi jocodes.... not working......

akhil.krish 3-Dec-13 3:05am View

thanks for rply thatraja....
page load code:
btnPrint.Attributes.Add("onclick", "window.open('Reports.aspx?rptid=13','','location=0,resizable=0,ScrollBars=1,statusbar=1,fullscreen =yes,left=20,top=10,moveable=0') ;return false")

using this code..
report.aspx code:

Case "13"
report.Load(Server.MapPath("Reports/RepPOD.rpt"))
CRptViewer.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None
report.RecordSelectionFormula = Request.QueryString("val")

Page.Title = "POD Reports"

akhil.krish 19-Nov-13 5:28am View

Orcun Thanks for rply..... but i wan't master page Globalization.... localization working but not working globalization .....

akhil.krish 28-Oct-13 6:44am View

Thanks for reply ashok..

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click
BindView()
GridView1.DataBind()
End Sub

Sub BindView()
Try
Dim flgsent As Boolean = False
Dim ObjAMSMaintainSearch As clsAMSRegMaintance = New clsAMSRegMaintance

If rdcACC.Checked = True Then
If ObjAMSMaintainSearch.FLG_TYPE = "True" Then ObjAMSMaintainSearch.FLG_TYPE = "الملحقات"
flgsent = True
ElseIf rdcDevice.Checked = True Then
If ObjAMSMaintainSearch.FLG_TYPE = "False" Then ObjAMSMaintainSearch.FLG_TYPE = "جهاز"

flgsent = True
Else

End If

If ChkRmflg.Checked = True Then
ObjAMSMaintainSearch.RMFLG = True
Else
ObjAMSMaintainSearch.RMFLG = False
End If

ObjAMSMaintainSearch.MTYPE = 0
'If txtRMNO.Text <> "" Then
' ObjAMSMaintainSearch.RMNO = Convert.ToInt64(Trim(txtRMNO.Text))
'End If
If txtRMNO.Text <> "" Then
Dim rmno As Int64 = Int64.Parse(Trim(txtRMNO.Text))
ObjAMSMaintainSearch.RMNO = rmno
End If

If txtOrderNum.Text <> "" Then
ObjAMSMaintainSearch.MORDERNO = Convert.ToInt64(Trim(txtOrderNum.Text))
End If
If txtDevNum.Text <> "" Then
ObjAMSMaintainSearch.DEVICE_NO = Trim(txtDevNum.Text)
End If
ObjAMSMaintainSearch.SERIAL_NO = Trim(txtSerialNum.Text)
ObjAMSMaintainSearch.PONO = Trim(txtPONO.Text)
ObjAMSMaintainSearch.DEPCODE = ddlDept.SelectedValue
ObjAMSMaintainSearch.MODELCODE = ddlModel.SelectedValue
ObjAMSMaintainSearch.DEVCODE = ddlDeviceType.SelectedValue
ObjAMSMaintainSearch.PROBLEMDESC = Trim(txtPrbdesc.Text)
ObjAMSMaintainSearch.INSIDE_EMP = Trim(txtInSideMEmp.Text)
ObjAMSMaintainSearch.FRECHDATE = Trim(txtFRecHDate.Text)
ObjAMSMaintainSearch.TRECHDATE = Trim(txtTRecHDate.Text)
ObjAMSMaintainSearch.FPURCHASEHDATE = Trim(txtFPuchaseHDate.Text)
ObjAMSMaintainSearch.TPURCHASEHDATE = Trim(txtTPuchaseHDate.Text)
ObjAMSMaintainSearch.FSENDOUTHDATE = Trim(txtFSendOutHDate.Text)
ObjAMSMaintainSearch.TSENDOUTHDATE = Trim(txtTSendOutHDate.Text)

Dim ds As DataSet = clsAMSRegMaintance.GetAMSInMainSearchbyQry(ObjAMSMaintainSearch)

GridView1.DataSource = ds
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub

this is my vb.net code

where to write...

akhil.krish 28-Oct-13 1:59am View

Thanks for reply "Rohan Leuva"

Private Sub Draw_Grid()
Dim I As Integer

MsGrid.ColWidth(0) = 800
MsGrid.ColWidth(1) = 1200
MsGrid.ColWidth(2) = 1500
MsGrid.ColWidth(3) = 1500
MsGrid.ColWidth(4) = 1000
MsGrid.ColWidth(5) = 1200
MsGrid.ColWidth(6) = 2500
MsGrid.ColWidth(7) = 3000
MsGrid.ColWidth(8) = 3000

MsGrid.TextMatrix(0, 0) = "رقم البلاغ"
MsGrid.TextMatrix(0, 1) = "رقم الجهاز"
MsGrid.TextMatrix(0, 2) = "الرقم االتسلسلي"
MsGrid.TextMatrix(0, 3) = "نوع الصيانة"
MsGrid.TextMatrix(0, 4) = "نوع الجهاز"
MsGrid.TextMatrix(0, 5) = "التاريخ الصيانة"
MsGrid.TextMatrix(0, 6) = "اسم الفني"
MsGrid.TextMatrix(0, 7) = "ملاحظات"
MsGrid.TextMatrix(0, 8) = "وصف المشكلة"

For I = 0 To MsGrid.Cols - 1
MsGrid.FixedAlignment(I) = 3
MsGrid.ColAlignment(I) = 0
Next I

For I = 1 To RCount
If MsGrid.TextMatrix(I, 4) = "True" Then MsGrid.TextMatrix(I, 4) = "الملحقات"
If MsGrid.TextMatrix(I, 4) = "False" Then MsGrid.TextMatrix(I, 4) = "جهاز"
Next

This is vb code... same application iam developing in asp.net..

grid view data FLG_TYPE value is False... convert to Arabic language "جهاز"

all i change columns name in GridView UI setting...

If MsGrid.TextMatrix(I, 4) = "True" Then MsGrid.TextMatrix(I, 4) = "الملحقات"
If MsGrid.TextMatrix(I, 4) = "False" Then MsGrid.TextMatrix(I, 4) = "جهاز"
both 2 lines how to write on code behind....

akhil.krish 17-Sep-13 1:56am View

ok i accept solution.... thanks for rply...

gprawin@gmail.com this my mail id send u r mail id....

akhil.krish 16-Sep-13 3:39am View

solve my problem... thanks for rply...

solution :

DeviceReportForm.aspx.vb

If rdbPO.Checked = True And ddl_PO_Cod.SelectedIndex <> 0 Then
Dim pono As String = Convert.ToString(ddl_PO_Cod.SelectedValue)
btnPrint.Attributes.Add("onclick", "window.open('Reports.aspx?rptid=11&val=" & pono & "','','location=0,resizable=0,ScrollBars=1,statusbar=1,width=980,height=800,left=20,top=10,moveable=0') ;return false")
End If

Reports.aspx.vb

Case "11"
report.Load(Server.MapPath("Reports/RepOPODevice.rpt"))
report.RecordSelectionFormula = "{TBL_DEVMASTER.PONO}='" & Request.QueryString("val") & "'"
Page.Title = "PODevice Reports"

akhil.krish 16-Sep-13 2:46am View

Jigar Sangoi Thanks it's working fine but empty reports showing....please help it is very urgent...

akhil.krish 16-Sep-13 1:55am View

If rdbPO.Checked = True And ddl_PO_Cod.SelectedIndex <> "-1"

this is also same problem ....

akhil.krish 16-Sep-13 1:53am View

Thanks for reply

iam use same u r code... but error comes like this

A string is required here. Details: errorKind Error in File RepOPODevice {F2C06A73-C223-4BF9-ABFA-391D2B70062B}.rpt: Error in formula Record_Selection: '{TBL_DEVMASTER.PONO}= 6169/801//17' A string is required here. Details: errorKind

akhil.krish 2-Sep-13 2:22am View

i wan't Right Side Navagation .....

akhil.krish 26-Aug-13 3:34am View

Aarti Meswania Thanks for Rply....:-)

iam using one java control.. reportview....

report.aspx page code

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim rptid As Integer = Request.QueryString("rptid")

Select Case (rptid)
Case "1"
report.Load(Server.MapPath("Reports/RepCompanyCod.rpt"))
CRptViewer.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None
CRptViewer.DisplayToolbar = False
Page.Title = "Company Reports"

Case "2"
report.Load(Server.MapPath("Reports/RepDepCod.rpt"))
CRptViewer.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None
Page.Title = "Department Reports"

Case "3"
report.Load(Server.MapPath("Reports/RepDeptDevice.rpt"))
CRptViewer.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None
Page.Title = "Department Device Reports"

akhil.krish 26-Aug-13 3:17am View

where to write this code...

akhil.krish 25-Aug-13 3:47am View

Hi tarun,

Try this steps...

Change the Datasource Connection:
From the Current Data Source list (the top box), click once on the datasource connection that you want to change.
In the Replace with list (the bottom box), click once on the new datasource connection.
Click Update.

akhil.krish 13-Aug-13 2:34am View

where to write this code?

akhil.krish 13-Aug-13 2:33am View

my problem if enter wrong details then go page refresh every time.. so i add the clear button code..but error validation not clear the error msg....?

akhil.krish 8-Jun-13 6:11am View

ex: iam select the ddl value comapany.. i wil select LG... but ddl selected value is samsung... every time select the default value samsung...

akhil.krish 8-Jun-13 2:33am View

Deleted

HI my self solve this problem.....

vb.net code:

Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
BindGridView()
GridView1.PageIndex = e.NewPageIndex
GridView1.DataBind()
End Sub"

The ASPX page

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" AllowPaging="true" PageSize="10" OnPageIndexChanging="GridView1_PageIndexChanging">

akhil.krish 5-Jun-13 2:57am View

select default one

akhil.krish 5-Jun-13 2:16am View

select the value not store in database... is store table 1st column default value is taking....

akhil.krish 5-Jun-13 2:10am View

Hi Rockstar,

i try this code same problem.... dropdownlist value not clear.. i wan't click the drop then show the value...open form clear textbox and dropdownlist values.....

akhil.krish 3-Jun-13 4:49am View

Dear Tadit dash,

i wil try on u r solution.. but same error occuring,

problem is ddldept value intger but i write single quote i think problem is that, and i wil remove the sigle quote but again error comming on " error invalid column name 'IT'

akhil.krish 3-Jun-13 3:44am View

Insert Command.
select the dddept.selected value
" Error conversion failed when converting the varchar value'IT' to data type

akhil.krish 3-Jun-13 3:44am View

Insert Command.
select the dddept.selected value
" Error conversion failed when converting the varchar value'IT' to data type

akhil.krish 2-Jun-13 3:26am View

thanq...

Comments by akhil.krish (Top 80 by date)