This is a migrated thread and some comments may be shown as answers.

NumberOfItems shows wrong value

3 Answers 79 Views
ComboBox
This is a migrated thread and some comments may be shown as answers.
abhay
Top achievements
Rank 1
abhay asked on 07 May 2009, 10:39 AM
Hi,


 

protected void RadComboBoxDiagnosis_ItemsRequested(object sender, RadComboBoxItemsRequestedEventArgs e)

 

{

 

string app = string.Empty;

 

 

 

if (e.Text.Length > 0)

 

{

app =

"DESCRIPTION LIKE '" + e.Text.Replace("'", "''") + '%' + "' AND";

 

}

 

string diagnosisData = "select DESCRIPTION,FK_CODE_VALUE,PK_ALL_CODE_DESC_ID from M_ALL_CODES_DESC WHERE " + app + " STATUS=1 order by DESCRIPTION ";

 

 

DataTable rowsCount = SqlHelper.ExecuteDataset(new Connection().GetCon, CommandType.Text, diagnosisData).Tables[0];

 

 

int itemsPerRequest = 10;

 

 

int itemOffset = e.NumberOfItems;

 

 

int endOffset = itemOffset + itemsPerRequest;

 

 

string text = e.Text;

 

 

DataRow[] rows = rowsCount.Select();

 

 

if (endOffset > rows.Length)

 

{

endOffset = rows.Length;

}

 

RadComboBoxDiagnosis.ClearSelection();

RadComboBoxDiagnosis.Items.Clear();

 

 

 

 

string diagnosisData1 = "select top " + endOffset + " DESCRIPTION,FK_CODE_VALUE,PK_ALL_CODE_DESC_ID from M_ALL_CODES_DESC WHERE " + app + " STATUS=1 order by DESCRIPTION ";

 

 

DataTable dtData = SqlHelper.ExecuteDataset(new Connection().GetCon, CommandType.Text, diagnosisData1).Tables[0];

 

RadComboBoxDiagnosis.DataTextField =

"DESCRIPTION";

 

RadComboBoxDiagnosis.DataValueField =

"FK_CODE_VALUE";

 

RadComboBoxDiagnosis.DataSource = dtData;

RadComboBoxDiagnosis.DataBind();

e.Message = GetStatusMessage(endOffset, rowsCount.Rows.Count);

}



first 10 records shows in combo but when i click for next 10 then e.NumberOfItems changes 20 ,previous items will be there ,it shows duplicates values and NumberOfItems  will be 40 due to that duplicate value.

 

 

Plz help me.

3 Answers, 1 is accepted

Sort by
0
ManniAT
Top achievements
Rank 2
answered on 07 May 2009, 11:15 AM
Hi,

of course you get a larger number of records.
This has nothing to do with the combobox - it's a thing about your query.

What ever you do before (I didn't look close) it all ends up in those lines of code:
string diagnosisData1 = "select top " + endOffset + " DESCRIPTION,FK_CODE_VALUE,PK_ALL_CODE_DESC_ID from M_ALL_CODES_DESC WHERE " + app + " STATUS=1 order by DESCRIPTION ";   
 
DataTable dtData = SqlHelper.ExecuteDataset(new Connection().GetCon, CommandType.Text, diagnosisData1).Tables[0];   
 
RadComboBoxDiagnosis.DataTextField = "DESCRIPTION";   
 
RadComboBoxDiagnosis.DataValueField = "FK_CODE_VALUE";   
 
   
RadComboBoxDiagnosis.DataSource = dtData;  
 
So I assume you calculate the endOffset which is the number of the last record you want to show.
Initially loading this is 10 - later it is 20, 30 or what ever you calculate in the code before.
Ignoring the "like clause" you ask SQL Server the following:
Please take out the fields Description, FK_Code_Value... from the table (view) M_ALL_CODES_DESC and only retrive those records which have STATUS=1.
Then please sort the result by DESCRIPTION and from that result give me the first "endOffset" records.

Next you take this table and bind it to the combobox.
If endOffset is 10 - you'll get 10 records - if it is 40 you'll get 40 records.

In other words - you tell SQL "hey, please give me endOffset number of records" - and that is what you get.

So I guess your "ShowStatus" method will always give the same numbers for endOffset and rowsCount....Count.

To achive what you want you need to create a query which does not only set the "maxNumber" or "endNumber" it also must use some kind of "startingNumber".

One approach (if you don't have such a criteria in your data) is the ROW_NUMBER() function.
At MSDN you can find a sample for this approach: http://msdn.microsoft.com/en-us/library/ms186734.aspx
Take a look at sample B - the last but one sample on the page.

Regards

Manfred
PS: notice that you must have SQL 2005 or newer for this function!!
0
abhay
Top achievements
Rank 1
answered on 07 May 2009, 11:29 AM
Hi Minniat,


Everything will fine in this code if item get cleared.


protected

 

void RadComboBoxDiagnosis_ItemsRequested(object sender, RadComboBoxItemsRequestedEventArgs e)

 

{

 

string app = string.Empty;

 

 

 

if (e.Text.Length > 0)

 

{

app =

"DESCRIPTION LIKE '" + e.Text.Replace("'", "''") + '%' + "' AND";

 

}

 

string diagnosisData = "select DESCRIPTION,FK_CODE_VALUE,PK_ALL_CODE_DESC_ID from M_ALL_CODES_DESC WHERE " + app + " STATUS=1 order by DESCRIPTION ";

 

 

DataTable dtAllData = SqlHelper.ExecuteDataset(new Connection().GetCon, CommandType.Text, diagnosisData).Tables[0];

 

 

int itemsPerRequest = 10;

 

 

int itemOffset = e.NumberOfItems;

 

 

int endOffset = itemOffset + itemsPerRequest;

 

 

string text = e.Text;

 

 

DataRow[] rows = dtAllData.Select();

 

 

if (endOffset > rows.Length)

 

{

endOffset = rows.Length;

}

RadComboBoxDiagnosis.Items.Clear();  // it clears the item but items will there in combobox so when i retrieve next 10 results it append s to previous result.

RadComboBoxDiagnosis.ClearSelection();

 

DataTable dtData = dtAllData.Clone();

 

 

for (int i = 0; i < endOffset; i++)

 

dtData.ImportRow(dtAllData.Rows[i]);

RadComboBoxDiagnosis.DataTextField =

"DESCRIPTION";

 

RadComboBoxDiagnosis.DataValueField =

"FK_CODE_VALUE";

 

RadComboBoxDiagnosis.DataSource = dtData;

 

RadComboBoxDiagnosis.DataBind();

e.Message = GetStatusMessage(endOffset, dtAllData.Rows.Count);

}


first time it shows 10 records fine but when i request for next items then query will return 20 records but append to the previous 10 records so total no of records will 30 instead of 20 Means RadComboBoxDiagnosis.Items.Clear();   this code doesn't work.

Plz give me any idea to solve this issue.

0
ManniAT
Top achievements
Rank 2
answered on 07 May 2009, 11:50 AM
Hi,

items get cleared - event without your call to combo.Items.Clear it would be clear since you totally rebind the items.

Maybe you don't believe me when I say "you retrieve to much records from the database".

I would ask you to debug your solution and add a breakpoint at this line:
RadComboBoxDiagnosis.DataTextField = "DESCRIPTION";   
 
Now check two things - first check the value of your variable endOffset and next (that's the point) check the value of
dtData.Rows.Count

This count is the number of records you bind to the combobox - and (maybe I'm wrong) I see nothing which changes the number to anything else than the number of records you retrieve from SQL.
DataTable dtData = dtAllData.Clone();   
 
for (int i = 0; i < endOffset; i++)   
dtData.ImportRow(dtAllData.Rows[i]);  
 
I don't know why you clone - but I know one thing about this loop:
You get (lets say) the "top 20" rows from your query.
This means row[0] to row[19]. And this loop goes from row[0] to row[19] importing those 20 rows to the table you later bind to the combobox.

Again - please check (in debug) the number of rows dtData has (add dtData.Rows.Count to the watch window).
Or simply add the line
int nTest=dtData.Rows.Count behind your loop. In debug you'll see that this number is the same as endOffset (and in subsequent calls larger than 10).

BUT even if you still don't believe me - try to change your loop to this:
int nStart=endOffset-10;
if(nStart<0) nStart=0;
for(int i=nStart;i<endOffset;i++)

I guess this could help

Manfred
Tags
ComboBox
Asked by
abhay
Top achievements
Rank 1
Answers by
ManniAT
Top achievements
Rank 2
abhay
Top achievements
Rank 1
Share this question
or