|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
populating comboboxHi to populate a combobox with one of the field from the database we
have to run a while loop which will add items in combobox like this While Not rst.EOF = True combo1.AddItem rst.Fields("name") rst.MoveNext Wend can this be done in other way to increase the speed. jack wrote:
> Hi to populate a combobox with one of the field from the database we How long does it take at the moment? Is this the slowest part of your> have to run a while loop which will add items in combobox like this > > While Not rst.EOF = True > combo1.AddItem rst.Fields("name") > rst.MoveNext > Wend > > > can this be done in other way to increase the speed. application? -- Larry Lard Replies to group please "jack" <gautams.m***@gmail.com>'s wild thoughts were released on 11 Oct 2005 01:40:31 -0700 bearing the followingfruit: >Hi to populate a combobox with one of the field from the database we If this is causing a noticable delay then you must be adding>have to run a while loop which will add items in combobox like this > > While Not rst.EOF = True > combo1.AddItem rst.Fields("name") > rst.MoveNext > Wend > > >can this be done in other way to increase the speed. a significant number of items to the combo box, surely far more than is practical. Jan Hyde (VB MVP) -- I saw a sign today that read "LOTS FOR SALE" but when I stopped I saw absolutely nothing. (Jim E. Snibbler) [Abolish the TV Licence - http://www.tvlicensing.biz/] I have found the following loop to be somewhat faster
Dim i As Long For i = 1 to rst.RecordCount > combo1.AddItem rst.Fields("name") Probably because it eliminates the EOF checking> rst.MoveNext Next Show quoteHide quote "jack" <gautams.m***@gmail.com> wrote in message news:1129020031.297067.33760@z14g2000cwz.googlegroups.com... > Hi to populate a combobox with one of the field from the database we > have to run a while loop which will add items in combobox like this > > While Not rst.EOF = True > combo1.AddItem rst.Fields("name") > rst.MoveNext > Wend > > > can this be done in other way to increase the speed. > "Norm Cook" <normcookNOSPAM@cableone.net> wrote in message But of course this assumes that you have opened your recordset in such a way news:11knefica2f2la9@corp.supernews.com... > I have found the following loop to be somewhat faster > Dim i As Long > For i = 1 to rst.RecordCount >> combo1.AddItem rst.Fields("name") >> rst.MoveNext > Next > > Probably because it eliminates the EOF checking that the RecordCount property has been filled with the true number of records. Try to create custom function like this
on textbox1_change query = "select top 1 from table1 where field1 like ' " & me.textbox1.text & " %' " execute query lenght = len(textbox1.text) textbox1 = result from query textbox1.selstart = lenght texbox1.sellenght = 0 syntax is not correct but I think u see what I menthe Hope it helps john "_john_" <j***@microsoft.com>'s wild thoughts were released on Tue, 11 Oct 2005 15:14:34 +0200 bearing the followingfruit: Show quoteHide quote >Try to create custom function like this I'm not sure how that would help the OP, but firing off an> >on textbox1_change > query = "select top 1 from table1 where field1 like ' " & >me.textbox1.text & " %' " > execute query > lenght = len(textbox1.text) > textbox1 = result from query > textbox1.selstart = lenght > texbox1.sellenght = 0 > > >syntax is not correct but I think u see what I menthe >Hope it helps SQL on a textbox change event is certainly a good way to slow things down further ;-) Jan Hyde (VB MVP) -- I saw this bloke chatting up a cheetah, I thought "he's trying to pull a fast one". [Abolish the TV Licence - http://www.tvlicensing.biz/] I use it and works just fine. Can you explain, please.
regards, John Show quoteHide quote "Jan Hyde" <StellaDrin***@REMOVE.ME.uboot.com> wrote in message news:i4gnk1110hgu014b7m4hekbe2mal9pg7dm@4ax.com... > "_john_" <j***@microsoft.com>'s wild thoughts were released > on Tue, 11 Oct 2005 15:14:34 +0200 bearing the following > fruit: > >>Try to create custom function like this >> >>on textbox1_change >> query = "select top 1 from table1 where field1 like ' " & >>me.textbox1.text & " %' " >> execute query >> lenght = len(textbox1.text) >> textbox1 = result from query >> textbox1.selstart = lenght >> texbox1.sellenght = 0 >> >> >>syntax is not correct but I think u see what I menthe >>Hope it helps > > I'm not sure how that would help the OP, but firing off an > SQL on a textbox change event is certainly a good way to > slow things down further ;-) > > > > Jan Hyde (VB MVP) > > -- > I saw this bloke chatting up a cheetah, I thought "he's trying to > pull a fast one". > > [Abolish the TV Licence - http://www.tvlicensing.biz/] > "_john_" <j***@microsoft.com>'s wild thoughts were released on Wed, 12 Oct 2005 10:37:40 +0200 bearing the followingfruit: >I use it and works just fine. Can you explain, please. Sure, your psudo code doesn't seem to have anything to dowith populating a combo box, the OP's question was how to speed up populationg the combo box. As far as firing SQLs off on a change event goes, it may well work fine for you for now, but the slowest part of any database application is the database access. You might not notice it now, but throw multiple users, remote databases, busy networks, large tables etc into the mix and suddenly you can find an app becomes unusable. Generally firing off several sqls that returns only 1 record will take more time overall than firing 1 SQL to return many records and it would also increase database activity which could impact other users. J Show quoteHide quote >regards, Jan Hyde (VB MVP)>John > >"Jan Hyde" <StellaDrin***@REMOVE.ME.uboot.com> wrote in message >news:i4gnk1110hgu014b7m4hekbe2mal9pg7dm@4ax.com... >> "_john_" <j***@microsoft.com>'s wild thoughts were released >> on Tue, 11 Oct 2005 15:14:34 +0200 bearing the following >> fruit: >> >>>Try to create custom function like this >>> >>>on textbox1_change >>> query = "select top 1 from table1 where field1 like ' " & >>>me.textbox1.text & " %' " >>> execute query >>> lenght = len(textbox1.text) >>> textbox1 = result from query >>> textbox1.selstart = lenght >>> texbox1.sellenght = 0 >>> >>> >>>syntax is not correct but I think u see what I menthe >>>Hope it helps >> >> I'm not sure how that would help the OP, but firing off an >> SQL on a textbox change event is certainly a good way to >> slow things down further ;-) >> >> >> >> Jan Hyde (VB MVP) >> >> -- >> I saw this bloke chatting up a cheetah, I thought "he's trying to >> pull a fast one". >> >> [Abolish the TV Licence - http://www.tvlicensing.biz/] >> > -- Who knocks on your door selling Halloween cookies? Ghoul Scouts (Ken Pinkham) [Abolish the TV Licence - http://www.tvlicensing.biz/] Ok, now I understand now. But what do you suggest? I have table with 50.000
records, field for example "ProductCode". If I load this in combo, loading takes long time and make a lot of trafic on network. Instead I make textbox, and do like I write before. Is it better to load all records in recordset after first character is putted in textbox and then I filter this recordest? regards, John Show quoteHide quote "Jan Hyde" <StellaDrin***@REMOVE.ME.uboot.com> wrote in message news:jjopk1pbqjup7ok9bk67d81cuke7kofsep@4ax.com... > "_john_" <j***@microsoft.com>'s wild thoughts were released > on Wed, 12 Oct 2005 10:37:40 +0200 bearing the following > fruit: > >>I use it and works just fine. Can you explain, please. > > Sure, your psudo code doesn't seem to have anything to do > with populating a combo box, the OP's question was how to > speed up populationg the combo box. > > As far as firing SQLs off on a change event goes, it may > well work fine for you for now, but the slowest part of any > database application is the database access. You might not > notice it now, but throw multiple users, remote databases, > busy networks, large tables etc into the mix and suddenly > you can find an app becomes unusable. > > Generally firing off several sqls that returns only 1 record > will take more time overall than firing 1 SQL to return many > records and it would also increase database activity which > could impact other users. > > J > > > > > >>regards, >>John >> >>"Jan Hyde" <StellaDrin***@REMOVE.ME.uboot.com> wrote in message >>news:i4gnk1110hgu014b7m4hekbe2mal9pg7dm@4ax.com... >>> "_john_" <j***@microsoft.com>'s wild thoughts were released >>> on Tue, 11 Oct 2005 15:14:34 +0200 bearing the following >>> fruit: >>> >>>>Try to create custom function like this >>>> >>>>on textbox1_change >>>> query = "select top 1 from table1 where field1 like ' " & >>>>me.textbox1.text & " %' " >>>> execute query >>>> lenght = len(textbox1.text) >>>> textbox1 = result from query >>>> textbox1.selstart = lenght >>>> texbox1.sellenght = 0 >>>> >>>> >>>>syntax is not correct but I think u see what I menthe >>>>Hope it helps >>> >>> I'm not sure how that would help the OP, but firing off an >>> SQL on a textbox change event is certainly a good way to >>> slow things down further ;-) >>> >>> >>> >>> Jan Hyde (VB MVP) >>> >>> -- >>> I saw this bloke chatting up a cheetah, I thought "he's trying to >>> pull a fast one". >>> >>> [Abolish the TV Licence - http://www.tvlicensing.biz/] >>> >> > > > Jan Hyde (VB MVP) > > -- > Who knocks on your door selling Halloween cookies? > Ghoul Scouts > > (Ken Pinkham) > > [Abolish the TV Licence - http://www.tvlicensing.biz/] > "_john_" <j***@microsoft.com>'s wild thoughts were released on Wed, 12 Oct 2005 12:59:19 +0200 bearing the followingfruit: >Ok, now I understand now. But what do you suggest? I'd first query why they would need to be loaded into acombo at all. If someone were to enter a ProductCode which, once entered does something then I would trigger that 'something' in a different way, either by the user doing something, like clicking on a submit button or via some other event. Without knowing the specifics of what your doing it's difficult to make a suggestion. >I have table with 50.000 No, I'd say it's better to load in the specific record once>records, field for example "ProductCode". If I load this in combo, loading >takes long time and make a lot of trafic on network. Instead I make textbox, >and do like I write before. Is it better to load all records in recordset >after first character is putted in textbox and then I filter this recordest? the entire product code has been entered. Show quoteHide quote >regards, Jan Hyde (VB MVP)>John > >"Jan Hyde" <StellaDrin***@REMOVE.ME.uboot.com> wrote in message >news:jjopk1pbqjup7ok9bk67d81cuke7kofsep@4ax.com... >> "_john_" <j***@microsoft.com>'s wild thoughts were released >> on Wed, 12 Oct 2005 10:37:40 +0200 bearing the following >> fruit: >> >>>I use it and works just fine. Can you explain, please. >> >> Sure, your psudo code doesn't seem to have anything to do >> with populating a combo box, the OP's question was how to >> speed up populationg the combo box. >> >> As far as firing SQLs off on a change event goes, it may >> well work fine for you for now, but the slowest part of any >> database application is the database access. You might not >> notice it now, but throw multiple users, remote databases, >> busy networks, large tables etc into the mix and suddenly >> you can find an app becomes unusable. >> >> Generally firing off several sqls that returns only 1 record >> will take more time overall than firing 1 SQL to return many >> records and it would also increase database activity which >> could impact other users. >> >> J >> >> >> >> >> >>>regards, >>>John >>> >>>"Jan Hyde" <StellaDrin***@REMOVE.ME.uboot.com> wrote in message >>>news:i4gnk1110hgu014b7m4hekbe2mal9pg7dm@4ax.com... >>>> "_john_" <j***@microsoft.com>'s wild thoughts were released >>>> on Tue, 11 Oct 2005 15:14:34 +0200 bearing the following >>>> fruit: >>>> >>>>>Try to create custom function like this >>>>> >>>>>on textbox1_change >>>>> query = "select top 1 from table1 where field1 like ' " & >>>>>me.textbox1.text & " %' " >>>>> execute query >>>>> lenght = len(textbox1.text) >>>>> textbox1 = result from query >>>>> textbox1.selstart = lenght >>>>> texbox1.sellenght = 0 >>>>> >>>>> >>>>>syntax is not correct but I think u see what I menthe >>>>>Hope it helps >>>> >>>> I'm not sure how that would help the OP, but firing off an >>>> SQL on a textbox change event is certainly a good way to >>>> slow things down further ;-) >>>> >>>> >>>> >>>> Jan Hyde (VB MVP) >>>> >>>> -- >>>> I saw this bloke chatting up a cheetah, I thought "he's trying to >>>> pull a fast one". >>>> >>>> [Abolish the TV Licence - http://www.tvlicensing.biz/] >>>> >>> >> >> >> Jan Hyde (VB MVP) >> >> -- >> Who knocks on your door selling Halloween cookies? >> Ghoul Scouts >> >> (Ken Pinkham) >> >> [Abolish the TV Licence - http://www.tvlicensing.biz/] >> > -- Insinuate: Adam and Eve’s least favorite word (Johnny Hart) [Abolish the TV Licence - http://www.tvlicensing.biz/] On Wed, 12 Oct 2005 12:59:19 +0200, "_john_" <j***@microsoft.com> Much against the advice of most people here, I have often presentedwrote: >Ok, now I understand now. But what do you suggest? I have table with 50.000 >records, field for example "ProductCode". If I load this in combo, loading >takes long time and make a lot of trafic on network. Instead I make textbox, >and do like I write before. Is it better to load all records in recordset >after first character is putted in textbox and then I filter this recordest? users with what looks like a list of 20,000 ... 100,000 records It comforts them Of course the trick is to just grab a few more records than you need to display on screen Basically, present them with a 'virtual listbox' or if you want to get flashy /fake/ a combo "jack" <gautams.m***@gmail.com> wrote in message Make the combobox sorted property false?news:1129020031.297067.33760@z14g2000cwz.googlegroups.com... > Hi to populate a combobox with one of the field from the database we > have to run a while loop which will add items in combobox like this > > While Not rst.EOF = True > combo1.AddItem rst.Fields("name") > rst.MoveNext > Wend > > > can this be done in other way to increase the speed. Chris Show quoteHide quote > 
Other interesting topics
|
|||||||||||||||||||||||
