Color Change of data

[Java.child]

Hello Experts,

My requirement is if priority is highest priority then data appears in red color otherwise in black color.I wrote folowing query for this.


SELECT * FROM CRVMReq where [Priority] like 'Highest Priority'
AND ReqStatus like 'OPEN'
and ReqStatusPool like 'FOCUS'



if([CRVMReq].[Priority]='Highest Priority')

{

ListCRVM.setForeground( Color.red );
}
else
{
ListCRVM.setForeground( Color.black );

}

}


I don't know how to write it in a programming language.
How i will put query in if loop.
Also suggest me if there is any other way to reflect this.

Please help me.

Thanks

[mtyoung]

SELECT * FROM CRVMReq where ReqStatus like 'OPEN'
and ReqStatusPool like 'FOCUS'

you get and check the value of Priority from result

[Java.child]

Here I am posting the code which i am executing.


private Recordset rst = null;
private Connection dbs = null;
int NewSqlCount=0;


{

rst = dbs.openRecordset( "SELECT * FROM CRVMReq " +
"where [Priority] like '" +Highest Priority + "%' " +
"AND ReqStatus like 'OPEN' " +
"and ReqStatusPool NOT like 'FOCUS' " +
“and ReadyTD like'YES'”+
"", AdoConst.adOpenDynamic, 0 );

if( rst.getRecordCount()>0 )
{
NewSqlCount=rst.getRecordCount();

For(int i=0;i<NewSqlCount ; i++)
{

ListCRVM.setForeground( Color.red ); //The problem is at this point.I don't know at this point how i will force to change the color of that rowset only.I hope there must be some propertry of recordset
}
}
Else
{
NewSqlCount=0;
ListCRVM.setForeground( Color.black );
}

}

{

rst = dbs.openRecordset(select * from CRVMReq where priority!='Highest Priority' and ReqStatus like 'OPEN' and ReqStatusPool not like 'FOCUS'and ReadyTD='YES'+"", AdoConst.adOpenDynamic, 0 );


if( rst.getRecordCount()>0 )
{

NewSqlCount=rst.getRecordCount();

For(int i=0;i<NewSqlCount ; i++)
{

ListCRVM.setForeground( Color.black );
}

}
Else
{
NewSqlCount=0;
ListCRVM.setForeground( Color.black );
}
}

[Java.child]

Please reply.........if anybody has any idea?
i tried the following code.Even that is not running.
String T ="Highest Priority";

String T2="A-Today";



{







rst = dbs.openRecordset( "SELECT priority FROM CRVMReq " +

"where [Priority] like 'Highest Priority'" +

"AND ReqStatus like 'OPEN' " +

"and ReqStatusPool NOT like 'FOCUS' " +

"and ReadyTD like'YES'"+

"", AdoConst.adOpenDynamic, 0 );





if(rst.equals(T))

{

ListCRVM.setForeground( Color.black );

}





rst.close();



}

{



rst = dbs.openRecordset( "SELECT * FROM CRVMReq " +

"where [Priority] NOT like 'Highest Priority'" +

"AND ReqStatus like 'OPEN' " +

"and ReqStatusPool NOT like 'FOCUS' " +

"and ReadyTD like'YES'"+

"", AdoConst.adOpenDynamic, 0 );



if(rst.equals(T2))





{

ListCRVM.setForeground( Color.black );



}





rst.close();

}

[Jasonre]

well personally, what i would do is a few things...
Don't use Select * but instead Select [columnName,columnName,...] that you are using. it's less intensive on the database.
Next, in your form that you are passing back ListCRVM, instead that i would suggest having a boolean called highestPriority and then you can set it equal to true, and have it default as false.
and lastly, change colors at the display level because it's a presentation layer issue that you are trying to work with so if you are using a jsp
<% if (form.highestPriority) { %> style="background-color: red" <% } %>
on the page where you want it.

if you have any questions just ask.

[Java.child]

Thanks for response !!
I m using swing.
Did you want me to do this?


Boolean HighestPriority = false;

{

rst = dbs.openRecordset( "SELECT priority FROM CRVMReq " +
"where [Priority] like 'Highest Priority'" +
"AND ReqStatus like 'OPEN' " +
"and ReqStatusPool NOT like 'FOCUS' " +
"and ReadyTD like'YES'"+
"", AdoConst.adOpenDynamic, 0 );
HighestPriority = true;

if(HighestPriority)
{
ListCRVM.setForeground( Color.red);
}

rst.close();

}
{

rst = dbs.openRecordset( "SELECT priority FROM CRVMReq " +
"where [Priority] NOT like 'Highest Priority'" +
"AND ReqStatus like 'OPEN' " +
"and ReqStatusPool NOT like 'FOCUS' " +
"and ReadyTD like'YES'"+
"", AdoConst.adOpenDynamic, 0 );

if(HighestPriority)

{
ListCRVM.setForeground( Color.black );

}

rst.close();
}


I tried this but even this is not working.
As per my understanding rst is not able to link up with ListCRVM.
Please suggest me what to do?

Thanks

[Jasonre]

close but there are some syntax errors..
you will have to add the field highestPriority to the ListCRVM class, and then generate the getter/setter for the new field in the class. then

{

rst = dbs.openRecordset( "SELECT Priority FROM CRVMReq " +
"where Priority = 'Highest Priority'" +
"AND ReqStatus = 'OPEN' " +
"and ReqStatusPool NOT = 'FOCUS' " +
"and ReadyTD = 'YES'"+
"", AdoConst.adOpenDynamic, 0 );

if(HighestPriority)
{
//this is for the red...
ListCRVM.setHighestPriority(true);
} else {
//this is for the black... so you don't have to read the database again.
ListCRVM.setHighestPriority(false);
}

rst.close();

}


and then at the swing level, do a check on each of the ListCRVM.getHighestPriority() == true

[Java.child]

ListCRVM is not a class.
See the declaration.


public MultiColumnListBox ListCRVM = new MultiColumnListBox();


And I am using swing not JSP.
Please advise what to do?

[mtyoung]

sql should not set to where Priority = 'Highest Priority', because it will fetch highest priority records only...

what is MultiColumnListBox Class?

[Jasonre]

ah this is correct, that wouldn't make sense to set that = to 'highest priority' i was just doing a quick replace the word "like" with = and didn't bother to check his sql. try not to use the word like when you have full quotes like that. like is a search command in sql and if you are searching, the string should also include the search chars. if you have like "text" then you mean =. it will greatly speed up sql calls.

also the
public MultiColumnListBox ListCRVM = new MultiColumnListBox();
was not in any of the previous post, so i didn't recognize that it was something you declared or created. however you are still instaniating a class when you do this, my thought is it's probably not a class you can add to it, however it sounds like you should make a new class, and extend MulticolumnListBox and then add the boolean variable. but i don't really know what's in, or how MultiColumnListBox works. Anyhow, only partial code will get you partial answers. if you post your code again, please format it so it's easy to read on the page, use the preview button please.

[Java.child]

Hope this will help you to help me.


public MultiColumnListBox ListCRVM = new MultiColumnListBox();
public Data dataListCRVM = new Data();

ListCRVM.setName( "ListCRVM" );
ListCRVM.clear();
add( ListCRVM );
ListCRVM.setVisible( true );
ListCRVM.setEnabled( true );
ListCRVM.setRequestFocusEnabled( true );
ListCRVM.setTabOrder( 0 );
ListCRVM.setRowSource( dataListCRVM );
ListCRVM.setColumnCount( 10 );
ListCRVM.setColumnHeaderVisible( true );
ListCRVM.setColumnWidths( "1550;1000;440;400;2592;432;5098;1728;0;5040;1 728" );
ListCRVM.setBoundColumn( 0 );
ListCRVM.setShowGrid(true);
ListCRVM.setForeground( Color.black );
ListCRVM.setLocation( 40, 168 );
ListCRVM.setSize( 1108, 228 );
ListCRVM.setFont( new Font("SansSerif", Font.PLAIN, 11) );


dataListCRVM.setName( "dataListCRVM" );
add( dataListCRVM );
dataListCRVM.setCaption( "dataListCRVM" );
dataListCRVM.setConnectionTimeout( 15 );
dataListCRVM.setCommandTimeout( 30 );
dataListCRVM.setCursorType( AdoConst.adOpenForwardOnly );
dataListCRVM.setLockType( AdoConst.adLockOptimistic );
dataListCRVM.setCommandType( AdoConst.adCmdText );
dataListCRVM.setMaxRecords( 0 );
dataListCRVM.setBOFAction( CtrlConst.adDoMoveFirst );
dataListCRVM.setEOFAction( CtrlConst.adDoMoveLast );
dataListCRVM.setConnectionString("jdbc:odbc:lynx", MISECRETO, MISECRETOTwo, "sun.jdbc.odbc.JdbcOdbcDriver" );
dataListCRVM.setConnectionString( "", "" );
dataListCRVM.setEnabled( true );
dataListCRVM.setUserName( "" );
dataListCRVM.setPassword( "" );
dataListCRVM.setRecordSource( "SELECT [CRVMReq].[ReqId], [CRVMReq].[ClientReq] ,[CRVMReq].[ResumeSample] as RS, [CRVMReq].[Priority], [CRVMReq].[Subject], [CRVMReq].[ConsultantNames], [CRVMReq].[Rec1], [CRVMReq].[Rec2], [CRVMReq].[Note] FROM CRVMReq WHERE [FlashCounter]<3 AND [ReqStatusPool] LIKE '%' and [ReqStatus] like 'OPEN' and [ReadyTD]='YES' ORDER BY [Priority] DESC , [Date] DESC; " );
dataListCRVM.setForeground(Color.black);
dataListCRVM.showAddNewButton( true );
dataListCRVM.setVisible( false );
dataListCRVM.setLocation( 200, 445 );
dataListCRVM.setSize( 180, 22 );
Data.initControls( dataListCRVM );
ListCRVM.setRowSource( dataListCRVM );




int NewSqlCount=0;

{

rst = dbs.openRecordset( "SELECT * FROM CRVMReq " +
"where [Priority] = 'Highest Priority'" +
"AND ReqStatus = 'OPEN' " +
"and ReqStatusPool NOT like 'FOCUS' " +
"and ReadyTD ='YES'"+
"", AdoConst.adOpenDynamic, 0 );

if( rst.getRecordCount()>0 )
{
NewSqlCount=rst.getRecordCount();
for(int i=0;i<NewSqlCount ; i++)
{
ListCRVM.addItem(i);
ListCRVM.setForeground(Color.red) ;
}

}

else
{
NewSqlCount=0;
ListCRVM.addItem(NewSqlCount);
ListCRVM.addItem(Color.black);
}
rst.close();
}



{



rst = dbs.openRecordset( "SELECT * FROM CRVMReq " +
"where [Priority] != 'Highest Priority'" +
"AND ReqStatus = 'OPEN' " +
"and ReqStatusPool NOT like 'FOCUS' " +
"and ReadyTD =YES'"+
"", AdoConst.adOpenDynamic, 0 );


if( rst.getRecordCount()>0 )

{

NewSqlCount=rst.getRecordCount();

for(int i=0;i<NewSqlCount ; i++)
{
ListCRVM.addItem(i);
ListCRVM.addItem(Color.black);
}

}
else
{
NewSqlCount=0;
ListCRVM.addItem(NewSqlCount);
ListCRVM.addItem(Color.black);

}
rst.close();

}

[Jasonre]

I see several things wrong to begin with.

first and foremost, the [] which i had first posted is a pseudo code syntax that means optional so you don't actually want that in your code syntax. Take them out of your sql code.

2nd: you are connected to the database twice to do the exact same read, remove the 2nd read and rewrite the sql code to include all records. To do this, remove the where clause of Priority = 'Highest Priority' as stated by someone just recently. This way when you go through your resultSet, you will obtain all records that have a priority, then you can check for Highest Priority, and mark red or black.

Also doing a Like command on % means select anything so it's pointless and slows down your sql.

so changes would be

dataListCRVM.setRecordSource( "SELECT ReqId, ClientReq, ResumeSample as RS, Priority, Subject, ConsultantNames, Rec1, Rec2, Note FROM CRVMReq WHERE FlashCounter < 3 AND ReqStatus = 'OPEN' AND ReadyTD = 'YES' ORDER BY Priority DESC, Date DESC; " );

rst = dbs.openRecordset( "SELECT Priority FROM CRVMReq " +
"where ReqStatus = 'OPEN' " +
"and ReqStatusPool NOT like 'FOCUS' " +
"and ReadyTD = 'YES'"+
"", AdoConst.adOpenDynamic, 0 );

and then:
if (rst.getNext()) {
MultiColumnListBox ListCRVM = new MultiColumnListBox();

ListCRVM.setName( "ListCRVM" );
ListCRVM.clear();
add( ListCRVM );
ListCRVM.setVisible( true );
ListCRVM.setEnabled( true );
ListCRVM.setRequestFocusEnabled( true );
ListCRVM.setTabOrder( 0 );
ListCRVM.setRowSource( dataListCRVM );
ListCRVM.setColumnCount( 10 );
ListCRVM.setColumnHeaderVisible( true );
ListCRVM.setColumnWidths( "1550;1000;440;400;2592;432;5098;1728;0;5040;1 728" );
ListCRVM.setBoundColumn( 0 );
ListCRVM.setShowGrid(true);
ListCRVM.setLocation( 40, 168 );
ListCRVM.setSize( 1108, 228 );
ListCRVM.setFont( new Font("SansSerif", Font.PLAIN, 11) );

if (rst.get(1) == "Highest Priority") {
ListCRVM.setForeground( Color.red );
} else {
ListCRVM.setForeground( Color.black );
}
}

rst.close();

then return your list.
don't just copy and paste this code into your editor because i didn't put everything in there. Think for yourself and see what's going wrong. step through your code, find the issues and debug them.

[Java.child]

Thanks for the quick response.

But this is not working.
recordset doesn't have any proprety like rst.getnext() or rst.get().

[Java.child]

Even I tried this code.But still not working.

String HP ="Highest Priority";



rst = dbs.openRecordset( "SELECT Priority FROM CRVMReq " +
"where ReqStatus = 'OPEN' " +
"and ReqStatusPool NOT like 'FOCUS' " +
"and ReadyTD = 'YES'"+
"", AdoConst.adOpenDynamic, 0 );
if( rst.getRecordCount()>0 ) {

if (HP==rst.getField("Priority").getValue().toString( ))
{

ListCRVM.addItem (Color.red);
}
else
{
ListCRVM.addItem (Color.black);
}

rst.close();

}



Please somebody help me.

[Java.child]

I am waiting for the response.
I guess as Jasonre replied me .......that thing will work....but i am not getting any recordset propertry called rst.getNext() or rst.get(1). i seached for the equivalent property but not able to find out.
Please help !!

[Jasonre]

what's your import on the class for rst?

[Java.child]

I don't know if i need to import any particular library for this.
Right now I have following imports in my file.

import diamondedge.util.*;
import diamondedge.ado.*;
import diamondedge.vb.*;
import java.awt.*;
import javax.swing.event.InternalFrameEvent;
import javax.swing.event.InternalFrameListener;
import javax.swing.*;
import javax.swing.table.*;
import java.io.*;
import java.awt.event.*;

[Jasonre]

seriously just private message me that whole class you are working with. those imports didn't help me find which class you are actually using. This is also a reason why you should use the direct class on your imports and not .*, also this would speed up loading only the imports that you need.

[Java.child]

Thxs for response Jasonre.
Here i attached the complete code.

[Jasonre]

Wow, that's a lot of code...

13,477 lines of code is just too much for me to sift through and figure out what you are using and what you aren't using :(

ok, do a loop instead.

for each of the Fields, the best way that i can say is said here at this site.
ADO Queries
and if you need help with the recordSet which is what rst is.
ADO Recordset Object