jdbc resultset problem
Hi all
i'm gtting a problem with the action listener event of my "Next" and "Previous" Jbuttons. The code that i have written makes the program search only for the next record and not further...and sameproblem for previous button.
That is: If originally i'm on the 3rd record, when i press next it goes to 4th record and when i press next again nothing happens. The same applies to my previous event.
Can anyone give me a hint on how to solve this problem?
i post my code below:
next.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent evt)
{
try{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql:///Super","root","");
Statement s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
s.execute("SELECT * FROM tblcustomer");
ResultSet rs = s.getResultSet();
rs.next();
cus.setText(rs.getString(1));
fname.setText(rs.getString(2));
lname.setText(rs.getString(3));
txtadd.setText(rs.getString(4));
txtcity.setText(rs.getString(5));
txtcountry.setText(rs.getString(6));
}
catch (Exception e)
{
JOptionPane.showMessageDialog(null,"No further record available!");
}
}
});
# 1
I'm not 100% sure but I think if you do everytime create a new connection you cant't get further than second record with next()I think Resultset default starts at first record maybe try it with an counter and for...
# 2
I think nobby is right. You should maybe have the jdbc driver connection outside the actionlistener, as a field in the enclosing class. This way, you'll save some connection time too, since you won't have to reestablish the connection every time the user clicks a button.
rebola at 2007-7-11 23:44:38 >
# 3
yeps i understand what you're trying to say but can one of you put an example on how to create the connecion in the class itself and how to refer to it in the delete actionlistener please?thanx
# 4
Class ClassContainingTheNextButton {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection("jdbc:mysql:///Super","root","");
// That's it, the con variable now represents a connection to the DB.
// look further down to see how you access it.
// you own code here:
next.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent evt)
{
try{
// little change:
Statement s = ClassContainingTheNextButton.this.con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
s.execute("SELECT * FROM tblcustomer");
ResultSet rs = s.getResultSet();
// etc...
If you find ClassContainingTheNextButton.this.con to be long, you can decleare con as a final variable. That way, you'll be able to access it just as con even from inside your inner, anonymous class.
rebola at 2007-7-11 23:44:38 >
# 5
thanx for your help but i still can't access the second record
here is my code below, have a look at it:
Class.forName("com.mysql.jdbc.Driver").newInstance();
final Connection conn=DriverManager.getConnection("jdbc:mysql:///Super", "root","");
previous.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent evt)
{
try
{
final Statement s= conn.createStatement();
s.execute("SELECT * FROM tblcustomer");
ResultSet rs = s.getResultSet();
if (!rs.isFirst())
while ( rs.previous() ){
rs.relative(-1);
cus.setText(rs.getString("Customer_ID"));
fname.setText(rs.getString("First_Name"));
lname.setText(rs.getString("Last_Name"));
txtadd.setText(rs.getString("Address"));
txtcity.setText(rs.getString("City"));
txtcountry.setText(rs.getString("Country"));
}}
catch (Exception e)
{
JOptionPane.showMessageDialog(null,"No further record available!");
} }}
);
# 6
try
int count= [your count defined global]
rs.first();
for (int i=0;i<count; i++)
{
rs.next();
}
cus.setText(rs.getString(1));
fname.setText(rs.getString(2));
lname.setText(rs.getString(3));
txtadd.setText(rs.getString(4));
txtcity.setText(rs.getString(5));
txtcountry.setText(rs.getString(6));
something like this i think
mind in the for statement that count must be smaller than i so you have to set count min on 1 or for(...;i><=coutn;.)
I think that would be a possibility
# 7
what do you mean by:int count= [your count defined global]?can you be more explicit pleasethanx
# 8
just a counter,
each time you press your button the coutner has to be increased
than you can make a simple loop as described
I didn't test it but I think it should work
so start your counter with 0 and your loop with for(int i=0; i<= counter,....){...i++}
or with 1 for(int i=0;i<counter){
i++...}
after the loop you have to increase your counter because it counts how often your button was pressed
all right?>
# 9
yeps i did it with a counter. i've tried for loop, while....still itwont show me more than 1 record:-(
# 10
Where are you closing your Statement and Connection?
# 11
You where given help yesterday.
You where asked to post your SSCCE. You didn't.
You where asked to use the "Code Formatting Tags". You didn't.
It was suggested you create two methods. One for the SQL query, since this should only ever be done once. The second for the reading of the row when you click on the next button. You didn't do this either
Since your are having so much trouble understanding the basics of how an SQL query works it was even suggested you just load the data from the ResultSet on the initial query into some other kind of data structure so you know how many record where read in the query and so you can easily scroll through this structure forwards and backwards. You where ever given code for this!!! Again you ignored this advice.
You are on your own.
Quit wasting peoples time.
