Fill ComboBox quickly

Sebastian Schweer

Hallo Everybody,



i programmed a ComboBox on Marketingdocuments (like Sales Order Form) for link this ComboBox with and UDF with the Country of selling this item.

This ComboBox i wish to fill with all Countries from B1 (from OCRY Table). Therefore i build a SQL Data Reader with a while loop and read every

single entry from the OCRY table and fill it to the ComboBox with ComboBox.ValidValues.Add();



But the OCRY Table has round about 250 entries and the adding takes a while(5 - 7 Seconds).



Is there a chance to fill the ComboBox more quickly?



Something like a AddRange() functionallity?

And what is the ComboBox.ValidValues.LoadSeries() method for? Can someone explain this method to me?



Thanks and Best Regards,



Sebastian

Anders Olsson

Hi Sebastian,



Add() is the only means of adding valid values to the ValidValues collection.

LoadSeries() is not what you are looking for in the case. From SAP API documentation:



LoadSeries

Loads into the valid values a list of the IDs of the series attached to a specific UDO.

The UDO must have been created with the Manage Series service, and one or more series must be connected to the UDO. To check the series connected to a UDO, go to Administration --> System Initialization --> Document Numbering. An entry exists for each UDO with Manage Series.



Regards,

Anders

Sebastian Schweer

Hallo Anders,



thank you for your fast reply



Unfortunatly it dosen't help me



I solve the Porblem by loading and filling the ComboBox only by a GetFocus Event for that Field,

so the user don't have to wait by loading the Form for 10 Seconds.



Best Regards,



Sebastian

SeKo

I'm having the same issue. How do they do it on the contact person field? There you also have the countries in a combobox, and it loads much faster..

SeKo

I had a problem somewhere else.. this is working for me:

string query = "SELECT Code,Name FROM OCRY order by name";

using (System.Data.SqlClient.SqlDataReader rdr = SwissAddonFramework.B1Connector.GetB1Connector().ExecuteQuery(query))

{

while(rdr.Read()){

combo.ValidValues.Add(rdr.GetString(0), rdr.GetString(1));



}

}

Sebastian Schweer

Hallo Seko,



thank you for your reply. I have made it the same way. Just a little bit more complicated

System.Data.SqlClient.SqlCommand cmdOrderReasonReader = new System.Data.SqlClient.SqlCommand();



// ComboBox will only be filled if ComboBox was empty

// So it will prevent that an error occur due to fill in double values to the ComboBox

if(ComboBox.GetFromUID(pVal.Form, "cb_ORDERRE").ValidValues.Values.Count == 0)

{

ComboBox.GetFromUID(pVal.Form, "cb_ORDERRE").ValidValues.Add("", "");



// Create SQL Command Object and fill it with and SQL Query for reading the table

cmdOrderReasonReader.CommandText = "select Code, Name from dbo.[@BEU_ORDERREASON] order by code";



// execute SQL Command

using (System.Data.SqlClient.SqlDataReader sdr_OrderReason = SwissAddonFramework.B1Connector.GetB1Connector().ExecuteQuery(cmdOrderReasonReader))

{

// Read the Data the SQL Command gives back

while(sdr_OrderReason.Read()) 

{

string text0 = sdr_OrderReason.GetString(0).ToString();

string text1 = sdr_OrderReason.GetString(1).ToString();



// Add the readed Data to the Combobox

ComboBox.GetFromUID(pVal.Form, "cb_ORDERRE").ValidValues.Add(text0, text1);

}

sdr_OrderReason.Close();

}

}

Best Regards,



Sebastian

SeKo

are you sure that the sql code is the part that makes your code slow?

Sebastian Schweer

Hi,



no, not the SQL Code is the Bottleneck in this case.

I have try and check this.



The Most time it needs to fill the ComboBox.

I fill the ComboBox with round about 250 Values from the Country Table.

And that needs up to 4 Seconds. Thats a lot for the user.



Best regards,



Sebastian

SeKo

Perhaps you could try to do it like this, so he doesn't need to go find the item on the form.

 System.Data.SqlClient.SqlCommand cmdOrderReasonReader = new System.Data.SqlClient.SqlCommand();

ComboBox myAwesomeBox = ComboBox.GetFromUID(pVal.Form, "cb_ORDERRE");

        // ComboBox will only be filled if ComboBox was empty

        // So it will prevent that an error occur due to fill in double values to the ComboBox

        if(myAwesomeBox .ValidValues.Values.Count == 0)

        {

                myAwesomeBox .ValidValues.Add("", "");



                // Create SQL Command Object and fill it with and SQL Query for reading the table

                cmdOrderReasonReader.CommandText = "select Code, Name from dbo.[@BEU_ORDERREASON] order by code";



                // execute SQL Command

                using (System.Data.SqlClient.SqlDataReader sdr_OrderReason = SwissAddonFramework.B1Connector.GetB1Connector().ExecuteQuery(cmdOrderReasonReader))

                {

                        // Read the Data the SQL Command gives back

                        while(sdr_OrderReason.Read()) 

                        {

                                string text0 = sdr_OrderReason.GetString(0).ToString();

                                string text1 = sdr_OrderReason.GetString(1).ToString();

                                

                                // Add the readed Data to the Combobox

                                myAwesomeBox .ValidValues.Add(text0, text1);

                        }

                        sdr_OrderReason.Close();

                }

        }

Sebastian Schweer

Hey Seko,



thank you. That really works



I was not aware, that the Search for the Item takes so much times.

Now i have rebuild my Code and now it only takes 1 or 2 Seconds.

Thats a huge Improvement =)



But i have one more question. maybe you can answer it to me.



I have create a rule which will be executed at a FormLoad of a Sales Order.

In that Rule i create the ComboBox and fill it later with the Data from the Business One Country Table OCRY.

The ComboBox should show the Description value, so i say "DisplayDescripton = true".

So in the ComboBox stands the Value "Brazil" and not "BR".

So far so good and so far everything is working.



But if i open the ComboBox again, only the Code Value "BR" are shown in the ComboBox.

Do you know why?



Best Regards,



Sebastian

Sebastian Schweer

Ah, i know why.



For the Reason that the Loading of the ComboBox takes so much time, I only fill the ComboBox at clicking on it.

If i fill in the ComboBox at the FormLoad than this value is shown.



Thank you for your suggegtions. It give me some new Ideas



Best Regards,



Sebastian