Need help with basic ready to program issue

graviti · Jul 28, 2013

Shanse said:
The program seems to work 99% now, the only issue is when the username or password is wrong, It does not throw the error box

try
{
String user = myuser.getText ();
String pass = mypass.getText ();

String sql = "SELECT COUNT(*) FROM LOGIN WHERE Username = '" + user + "' AND Password = '" + pass + "';";

rs = stmt.executeQuery (sql);
while (rs.next ())
{
int count = rs.getInt (1);
if (count == 1)
{
myframe.setVisible (false);
myprofile.setVisible (true);
break;
}
else
{
JOptionPane.showMessageDialog (null, "Incorrect. Please retry or contact a game manager.");
}
}
}
/QUOTE]
I forgot the 1 in the rs.getInt(1); Sorry. That's just the column number which should be 1, as there will only be one column.

There shouldn't be a while block for the rs.next()

I'm not quite sure as to why it would not be throwing the error box. Do you have a corresponding catch block for the try block. Are you printing out the Exceptions that it catches, so you can see them?

Try printing out count before the if statement and see what the database is returning. It should be working fine

Shanse · Jul 28, 2013

I adjusted the code to:

try
{
String user = myuser.getText ();
String pass = mypass.getText ();

String sql = "SELECT COUNT(*) FROM LOGIN WHERE Username = '" + user + "' AND Password = '" + pass + "';";
rs = stmt.executeQuery (sql);

rs.next ();

int count = rs.getInt (1);
System.out.println (count);
if (count == 1)
{
JOptionPane.showMessageDialog (null, "Redirecting you to profile management.. please wait a while.");
myframe.setVisible (false);
JOptionPane.showMessageDialog (null, "You are now entering your profile.");
JOptionPane.showMessageDialog (null, "Please be patient while stats are loading. . . . .");
myprofile.setVisible (true);

}
else
{

JOptionPane.showMessageDialog (null, "Incorrect. Please retry or contact a game manager.");
}

}

catch (Exception e)
{
System.out.println ("Mybtn1 Coding onsuksesvol");
e.printStackTrace ();
}

Seem to still be getting a:

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state

when:

I fill in incorrect details (not in the database)

Sorry if I'm being utterly clueless, lol

graviti · Jul 28, 2013

OK

Between these two lines

rs = stmt.executeQuery (sql);

rs.next ();

add rs.beforeFirst();

If this is still throwing an exception, your connection string may need to change.

Code:

		try 
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			String filename = "myDatabaselocation\myDatabase.mdb";
			String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
			database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end 
			// now we can get the connection from the DriverManager
			con = DriverManager.getConnection( database ,"",""); 
			s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
                       // JOptionPane.showMessageDialog(null, "Successfully connected to the database.");
		}

This is the code I use to connect to a Access DB. The last bit,
s = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

is important, for cursor states. If your's doesn't look like this, try it.

Shanse · Jul 28, 2013

Code:

void DatabaseConnection ()
    {
        try
        {
            Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
            String filename = "E:/Java/2013/PatDB1.mdb";
            String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
            database += filename.trim () + ";DriverID=22;READONLY=true}"; // add on to the end
            // now we can get the connection from the DriverManager
            con = DriverManager.getConnection (database, "", "");
            stmt = con.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            System.out.println ("Successfully connected to the database.");
        }
        catch (Exception ex)
        {
            System.out.println ("Connection with database NOT successful");
            ex.printStackTrace ();

        }
    }

I added this for connection to DB, is this correct?

Code:

            try
            {

                String user = myuser.getText ();
                String pass = mypass.getText ();

                String sql = "SELECT COUNT(*) FROM LOGIN WHERE Username = '" + user + "' AND Password = '" + pass + "';";

                rs = stmt.executeQuery (sql);
                rs.beforeFirst ();
                rs.next ();

                int count = rs.getInt (1);

                System.out.println (count);

                if (count == 1)

                    {
                        JOptionPane.showMessageDialog (null, "Redirecting you to profile management.. please wait a while.");
                        myframe.setVisible (false);
                        JOptionPane.showMessageDialog (null, "You are now entering your profile.");
                        JOptionPane.showMessageDialog (null, "Please be patient while stats are loading. . . . .");
                        myprofile.setVisible (true);

                    }

                else

                    {
                        JOptionPane.showMessageDialog (null, "Incorrect. Please retry or contact a game manager.");
                    }


            }

            catch (Exception e)
            {
                System.out.println ("Mybtn1 Coding onsuksesvol");
                e.printStackTrace ();
            }


        }

I did that aswell but the problem persists

graviti · Jul 28, 2013

A simple debugging mechanism is to add System.out.println after each code step, to figure out at which line you getting the error. Theoretically your stack trace should tell you as well, but lets try and figure this out with the printing. We need to know if the problem happens when we execute the statement, or before. It's possible that the text from your textfields are breaking the SQL. Theoretically, before you check against the database, you should check the values in your textfields to make sure they have values, and don't contains illegal characters. Once you're happy with that, then you query against the database.

To tell us that it is breaking is to vague for us to help you properly. We need to know what data was input, and the full output of the stacktrace

LiquidCocaine · Jul 28, 2013

What IDE are you using?

Shanse · Jul 28, 2013

It would appear that right after:

Code:

rs = stmt.executeQuery (sql);
 rs.beforeFirst ();
 rs.next ();
System.out.println("Hey"); <-- works.
 int count = rs.getInt (1); 
System.out.println("Test"); <-- does not work.

 //The system.out.println's stop working.

You mentioned in this case the value inserted in the rs.getInt(x) is the amount of columns, which you said is 1 in this case, however, I have a Username + Password tab, does this count as 2? or is this still 1? It's 1 table (Login) --> 2 columns (Username/Password)

Shanse,
Confused.

Shanse · Jul 28, 2013

LiquidCocaine said:
What IDE are you using?

I'm using Ready to Program V1.71

LiquidCocaine · Jul 28, 2013

Shanse said:
I'm using Ready to Program V1.71

Seems like you're doing your PAT, why not use a better IDE such as Netbeans?

Shanse · Jul 28, 2013

I'm not experienced in Netbeans, as I mentioned at the begin, as a rookie programmer, I'm best equipped with RtP I guess.

LiquidCocaine · Jul 28, 2013

On phone so excuse the typing.

rs.beforeFirst ();
rs.next ();

Instead of that use
Rs.first();

Take a look at the java API

Shanse · Jul 28, 2013

LiquidCocaine,

A matter of pure curiousity, when's the deadline on your PAT?

It would appear the change of // rs.beforeFirst();
//rs.next();

to:

rs.first();

Didn't change anything, I still get: (When typing incorrect details into Database)

Code:

java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state

LiquidCocaine · Jul 28, 2013

A deadline has not been issued as yet as the moderation date for the PAT is not confirmed yet.

Shanse · Jul 28, 2013

Here's the complete list of code if anyone wanted to review it in order to solve the problem:

Code:

if (eve.getSource () == mybtn1)
        {

            String GH = myuser.getText ();
            lblnaam.setText ("Welcome to your profile, " + GH + "                                          ");
            
            try
            {

                String user = myuser.getText ();
                String pass = mypass.getText ();

                String sql = "SELECT COUNT(*) FROM LOGIN WHERE Username = '" + user + "' AND Password = '" + pass + "';";

                rs = stmt.executeQuery (sql);

                rs.beforeFirst ();
                rs.next ();
                //          rs.first ();

                int count = rs.getInt (1);



                if (count == 1)

                    {
                        JOptionPane.showMessageDialog (null, "Redirecting you to profile management.. please wait a while.");
                        myframe.setVisible (false);
                        JOptionPane.showMessageDialog (null, "You are now entering your profile.");
                        JOptionPane.showMessageDialog (null, "Please be patient while stats are loading. . . . .");
                        myprofile.setVisible (true);

                    }

                else

                    {
                        JOptionPane.showMessageDialog (null, "Incorrect. Please retry or contact a game manager.");
                    }


            }

            catch (Exception e)
            {
                System.out.println ("Mybtn1 has encountered a error:");
                e.printStackTrace ();
            }


        }

graviti · Jul 28, 2013

Try change the int count = rs.getInt(1) to

int count = Integer.parseInt(rs.getString(1));

Alternatively change the SQL to reflect SELECT COUNT(*) AS Cnt FROM...
And then

int count = Integer.parseInt(rs.getString("Cnt"));

graviti · Jul 28, 2013

BTW, are you doing a school PAT, or are you just learning on your own?

Shanse · Jul 28, 2013

I'm doing a School PAT @ Grade 12.

& I didn't really make heads or tails of your previous post

What should the SQL-Statement be?

Code:

int count = Integer.parseInt(rs.getString(1));
String sql = "SELECT Cnt(*) FROM LOGIN WHERE Username = '" + user + "' AND Password = '" + pass + "';";
int count = Integer.parseInt(rs.getString("Cnt"));

Is this correct? because if so, double declaration of count.

wolv · Jul 28, 2013

You should put all your SQL statements into a stored procedure and then call the stored procedure from your code

Shanse · Jul 28, 2013

wolv said:
You should put all your SQL statements into a stored procedure and then call the stored procedure from your code

Are you suggesting I create a:

Code:

void test()
{

                String user = myuser.getText ();
                String pass = mypass.getText ();

                String sql = "SELECT COUNT(*) FROM LOGIN WHERE Username = '" + user + "' AND Password = '" + pass + "';";

                rs = stmt.executeQuery (sql);         
                int count = rs.getInt (1);

}

and call it via test(); where the original query is currently?

graviti · Jul 28, 2013

Code:

String sql = "SELECT COUNT(*) AS Cnt FROM LOGIN WHERE Username = '" + user + "' AND Password = '" + pass + "';";
rs = stmt.executeQuery(sql);
int count = Integer.parseInt(rs.getString("Cnt")); 
System.out.println("The result of the sql count is " +count);

Join the MyBroadband community

Get started

Need help with basic ready to program issue

Senior Member

Active Member

Senior Member

Active Member

Senior Member

Well-Known Member

Active Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Senior Member

Senior Member

Active Member

Active Member

Active Member

Senior Member