VB.NET Programming Corner!

[knives645]

sir pano po gawin sa .net yung ma-view mo yung mga records galing sa ms access 2007 sa listview? pa help po ako. salamat =)

Find the samples on the first page of this thread.

 

[UtakVerde]

boss galing s thread na to patambay

 

[Rajino]

Mga Sir, San kaya ako makakakuha ng isang Accounting System Source code vb.net, kailangan lang...Thanks!

 

[iknowhtml]

Find the samples on the first page of this thread.

cge po sir, slmat,

 

[Joussyd]

tanong ko lng sa mga developers na gumamit n ng mysql as database sa vb.net, my problema kase nung inilipat namen ung database sa ibang PC tas gnmit ko sa vb... ndi kase magconnect, anyone who can help?

 

[attackshakz]

gusto ko talaga matuto mag connect ng vb.net to mysql sqlserver

 

[lesterluma]

ask lang po ... (QUERY TO mga ka SB)


etong code(Query) na ito ay kaya nyang idisplay yung image1 see attached pix.

SELECT DISTINCT site_id,date_survey,(a.hour_from + " - " + a.hour_to) AS hour, volume as cnt
FROM survinfo_1dir a,survraw_1dir b , vehicle_type e
WHERE site_id =  "SV01001LZ_AC" AND a.header_no=b.header_no  AND e.vehicle_type = b.vehicle_type
AND date_survey = "5/10/2006"

UNION ALL

SELECT DISTINCT  site_id,date_survey,(c.hour_from + " - " + c.hour_to) AS hour, (isnull(volume_inc_post,0 ) + isnull(volume_dec_post,0))  as cnt
FROM survinfo_2dir c,survraw_2dir d , vehicle_type f
WHERE site_id=  "SV01001LZ_AC" AND c.header_no=d.header_no  AND f.vehicle_type = d.vehicle_type
AND date_survey = "5/10/2006"

ORDER BY hour

View attachment 785984




bale yan po dapat yung output ko..


GOAL:

dapat ma group by yung column "HOUR" tapos mag suSUM yung column "CNT"..



ano po ba dapat kong idagdag sa code ko ? dapat po ba akong gumamit ng SUB-query, paano po ba?

o pa modify nlang po yung code na binigay ko para magawa ko na yun thanks..

 

[ellimacZ]

pa help po. paano po mag update ng ms sql database with just textboxes and not using datagrid? yung para po kasi yan sa atm program namin po . thank you

 

[lesterluma]

di ko pa din makuha k'SB pa help ulit..


pwede ko ba i'PLUS yung mga columns dyan sa image?

SQL padin po ito.. paano po.? thnx


bale ganito dapat

yung COLUMN 1 HANGGANG 12 dapat makuha ko yung SUM nila..

then ilagay sa another column ung result ganun din sa ibang column.

mali kasi yung result ko eh.. patulong po ..



View attachment 786317

 

[maskio]

di ko pa din makuha k'SB pa help ulit..


pwede ko ba i'PLUS yung mga columns dyan sa image?

SQL padin po ito.. paano po.? thnx


bale ganito dapat

yung COLUMN 1 HANGGANG 12 dapat makuha ko yung SUM nila..

then ilagay sa another column ung result ganun din sa ibang column.

mali kasi yung result ko eh.. patulong po ..



View attachment 786317

normalize the datatable para di ka mahirapan,

example...

table_survey
/* dito mo ilagay ang survey date - hour */
1 | May 10, 2006 12:00:00 AM | 1000 - 1100


table_answer
/* you have here surveyId(from table_survey), questionId and answerValue */

surveyId | questionId | answerValue
1 | 1 | 214
1 | 2 | 89
1 | 3 | 16

para pag nag query ka, sum(answervalue) ...

anyway, on your current structure,, here's how i do it.

create a stored proc (cursor) or you can do a loop on your front end to create the select statement. the loop will go through all the columns. the output of your query string should look like this:

select (a.1 + a.2 + a.3) `total` from table a;

pero kung static yung column mo at hindi mo na dadagdagan, hardcode mo nalang. (i would still recommend normalizing your data tables before you denormalize it). you could either use star schema or snowflake schema structure.

 

[lesterluma]

normalize the datatable para di ka mahirapan,

example...

table_survey
/* dito mo ilagay ang survey date - hour */
1 | May 10, 2006 12:00:00 AM | 1000 - 1100


table_answer
/* you have here surveyId(from table_survey), questionId and answerValue */

surveyId | questionId | answerValue
1 | 1 | 214
1 | 2 | 89
1 | 3 | 16

para pag nag query ka, sum(answervalue) ...

anyway, on your current structure,, here's how i do it.

create a stored proc (cursor) or you can do a loop on your front end to create the select statement. the loop will go through all the columns. the output of your query string should look like this:

select (a.1 + a.2 + a.3) `total` from table a;

pero kung static yung column mo at hindi mo na dadagdagan, hardcode mo nalang. (i would still recommend normalizing your data tables before you denormalize it). you could either use star schema or snowflake schema structure.

-thanks sir. pero di naman po siya actually table eh. result po siya ng query ko. kaya paano ko po yun inonormalize.



,eto nlang po ... paano ko ba iseseperate yung column CNT sa code ko .. naka distinct kasi siya sa code ko, dapat di po siya kasama sa DISTINCT..


NOTE: na grup by ko na yung 3 columns tapos di ko sinama si column CNT pero mali yung RESULT..

idea po pls....

SELECT DISTINCT a.site_id,a.date_survey,(a.hour_from + " - " + a.hour_to) AS hour, b.volume as cnt
FROM survinfo_1dir a,survraw_1dir b 
WHERE a.site_id =  "SV01001LZ_AC" AND a.header_no=b.header_no 
AND a.date_survey = "5/10/2006"

UNION ALL

SELECT DISTINCT  c.site_id,c.date_survey,(c.hour_from + " - " + c.hour_to) AS hour, (isnull(d.volume_inc_post,0 ) + isnull(d.volume_dec_post,0))  as cnt
FROM survinfo_2dir c,survraw_2dir d
WHERE c.site_id=  "SV01001LZ_AC" AND c.header_no=d.header_no 
AND c.date_survey = "5/10/2006"

 

[lesterluma]

normalize the datatable para di ka mahirapan,

example...

table_survey
/* dito mo ilagay ang survey date - hour */
1 | May 10, 2006 12:00:00 AM | 1000 - 1100


table_answer
/* you have here surveyId(from table_survey), questionId and answerValue */

surveyId | questionId | answerValue
1 | 1 | 214
1 | 2 | 89
1 | 3 | 16

para pag nag query ka, sum(answervalue) ...

anyway, on your current structure,, here's how i do it.

create a stored proc (cursor) or you can do a loop on your front end to create the select statement. the loop will go through all the columns. the output of your query string should look like this:

select (a.1 + a.2 + a.3) `total` from table a;

pero kung static yung column mo at hindi mo na dadagdagan, hardcode mo nalang. (i would still recommend normalizing your data tables before you denormalize it). you could either use star schema or snowflake schema structure.

ganito nlang sir maskio. paano mo po iququery yung result..
View attachment 134081
see image...


balak ko kasi I susum yung rows. per row. tapos sa dulo ng row yung result. thanks.

thanks..

 

[maskio]

ganito nlang sir maskio. paano mo po iququery yung result..
View attachment 786433
see image...


balak ko kasi I susum yung rows. per row. tapos sa dulo ng row yung result. thanks.

thanks..

considering yung column1 is the surveyId and column2 is the questionId;

SELECT SUM(result) `result`, column1 FROM table GROUP BY column2;

** EDIT **
teka mali pagka intindi ko... kulang ang info mo sa picture... you are only adding column1 and column2; please paki specify ang unique identifier ng row. (primary key nila)

 

[saels]

nice one

 

[lesterluma]

considering yung column1 is the surveyId and column2 is the questionId;

SELECT SUM(result) `result`, column1 FROM table GROUP BY column2;

** EDIT **
teka mali pagka intindi ko... kulang ang info mo sa picture... you are only adding column1 and column2; please paki specify ang unique identifier ng row. (primary key nila)

--ganito po.. wala pa po kasi yung column na RESULT dun ..

bale ang laman ng table COLUMN1 at COLUMN 2 lang,,

ang goal ko is gumawa ng query na magsusum yung column 1 + column 2 = result para po siyang ganyan .







bytheway, up ko lang to sir. baka alam nyo po ito ?


ayaw ko kasing I DISTINCT yung column CNT ko eh.. paano ko ba siya ihihiwalay sa CODE ko ? thanks sir maskio..

SELECT DISTINCT a.site_id,a.date_survey,(a.hour_from + " - " + a.hour_to) AS hour, b.volume as cnt
FROM survinfo_1dir a,survraw_1dir b 
WHERE a.site_id =  "SV01001LZ_AC" AND a.header_no=b.header_no 
AND a.date_survey = "5/10/2006"

UNION ALL

SELECT DISTINCT  c.site_id,c.date_survey,(c.hour_from + " - " + c.hour_to) AS hour, (isnull(d.volume_inc_post,0 ) + isnull(d.volume_dec_post,0))  as cnt
FROM survinfo_2dir c,survraw_2dir d
WHERE c.site_id=  "SV01001LZ_AC" AND c.header_no=d.header_no 
AND c.date_survey = "5/10/2006"

 

[maskio]

ganito nlang sir maskio. paano mo po iququery yung result..
View attachment 786433
see image...


balak ko kasi I susum yung rows. per row. tapos sa dulo ng row yung result. thanks.

thanks..

consider this structure

 

[maskio]

--ganito po.. wala pa po kasi yung column na RESULT dun ..

bale ang laman ng table COLUMN1 at COLUMN 2 lang,,

ang goal ko is gumawa ng query na magsusum yung column 1 + column 2 = result para po siyang ganyan .







bytheway, up ko lang to sir. baka alam nyo po ito ?


ayaw ko kasing I DISTINCT yung column CNT ko eh.. paano ko ba siya ihihiwalay sa CODE ko ? thanks sir maskio..

SELECT DISTINCT a.site_id,a.date_survey,(a.hour_from + " - " + a.hour_to) AS hour, b.volume as cnt
FROM survinfo_1dir a,survraw_1dir b 
WHERE a.site_id =  "SV01001LZ_AC" AND a.header_no=b.header_no 
AND a.date_survey = "5/10/2006"

UNION ALL

SELECT DISTINCT  c.site_id,c.date_survey,(c.hour_from + " - " + c.hour_to) AS hour, (isnull(d.volume_inc_post,0 ) + isnull(d.volume_dec_post,0))  as cnt
FROM survinfo_2dir c,survraw_2dir d
WHERE c.site_id=  "SV01001LZ_AC" AND c.header_no=d.header_no 
AND c.date_survey = "5/10/2006"

pakita mo columns ng table, mas madali ko magawan ng query... lagyan mo nalang ng dummy data

 

[lesterluma]

consider this structure

astig yun sir ah.. hehe.. kaso ang problema wala naman po primary key yun eh.. kasi di naman siya table eh. query lang po siya. bale wla siyang primary key o anumang unique identifier. kaya ganun..

sir yung isang problem ko po baka po alam nyo. ?

 

[lesterluma]

pakita mo columns ng table, mas madali ko magawan ng query... lagyan mo nalang ng dummy data

sir. eto po yung pics.

View attachment 134083

yung column "CNT" na didistinct din kasi siya. kaya mali result ko ..

ayaw ko siya isama sa DISTINCT..


parang ganito gusto kong manyari..


iDISTINCT mo lahat ng COLUMN instead kay COLUMN CNT ..




yan thx po..

 

[maskio]

sir. eto po yung pics.

View attachment 786453

yung column "CNT" na didistinct din kasi siya. kaya mali result ko ..

ayaw ko siya isama sa DISTINCT..


parang ganito gusto kong manyari..


iDISTINCT mo lahat ng COLUMN instead kay COLUMN CNT ..




yan thx po..

bigyan mo ako ng table structure at ano hitsura ng desired output. yung attached pic mo is output na...

i cannot query without seeing the tables...