Need help with Database and SQL Please

[08butoryr]

Hey guys I could really use your help with some very basic java programming. I know you programming fundis out there will find this child's play but I'm struggling with it a bit because I'm realtively new at programming.The topic is "Using a database and SQL". The exercise supplies a table called tblStudent in a database called School and requires you to perform certain actions with the table. Here are the requirements that I am having problems with:

NB: I am using JOptionPane as a GUI

Create a menu with the following functions (I've created the menu already using "switch"):
1. Insert additional records into tblStudent repeatedly until the user wishes to exit (there are 3 fields: Name, Class (A, B or C), House (Red or Blue) ). Display the table each time a student is added.
2. Search for a student by name, based on the user's input.
3. Delete a student based on their name. Before a student is to be deleted, display the student's entire details and ask the user if the displayed record is the one they wish to delete. Only delete the student if the user confirms the delete.
4. Edit a student's details: The user must be prompted to input a student's name and then the student's details must be displayed ont the screen. The user is then asked to enter new details for the student, field by field. The new details must be used to update the student's record. If the user doesn't want to change the value of a certain field, allow them to press <ENTER> - therefore only change the field's value if a new value has been entered.
5. Quit

Can you please also explain to me how to use the "ResultSetMetaData" interface to obtain the column names using the column numbers.

Here is my source code so far if needed:

case 1: //Add a student

try
{
String input1 = JOptionPane.showInputDialog("Please enter student's name");
String input2 = JOptionPane.showInputDialog("Please enter student's class");
String input3 = JOptionPane.showInputDialog("Please enter student's house");
conn = DriverManager.getConnection ("jdbc:odbc:School", "", "");
set = conn.createStatement();
set.executeUpdate("INSERT INTO tblStudent " + "VALUES ('" + input1 + " ', '" + input2 + "', '" + input3 + "')");
sql2 = "SELECT * FROM tblStudent";
rs = set.executeQuery(sql2);

System.out.println("This is the table containing the students' data:");
System.out.println("------------------------------------------------");
System.out.println("Student's name:" + "\t" + "Class:" + "\t\t" + "House:");

while (rs.next())
{
name = rs.getString("Student Name");
studentClass = rs.getString("Class");
house = rs.getString("House");
System.out.println(name + "\t\t" + studentClass + "\t\t" + house);
}
conn.close();
}

catch (Exception e)
{
}
;break;
//--------------------------------------------------------------------------------

case 2: //Search by name
try
{
conn = DriverManager.getConnection ("jdbc:odbc:School", "", "");
set = conn.createStatement();
sql = "SELECT * FROM tblStudent";
rs = set.executeQuery(sql);
String[] names = new String[8];

while (rs.next())
{
names[0] = "SELECT * FROM tblStudent" + "WHERE ID = 1";
System.out.println(names[0]);
name = rs.getString("Student Name");
String input = JOptionPane.showInputDialog("Which name are you searching for?");
SearchNames obj4 = new SearchNames(name, input);
int place = obj4.getPlace();

if (place == -1)
{
System.out.println("\nThe name: " + input + " has not been found");
}
else
{
System.out.println("\nSorry, the name: " + input + " is present");
}
}
}
catch (Exception e)
{
}
;break;
//--------------------------------------------------------------------------------------------

case 3: //Delete a name

try
{
conn = DriverManager.getConnection ("jdbc:odbc:School", "", "");
set = conn.createStatement();
sql = "DELETE FROM tblStudent WHERE Student Name = Thabo";
set.executeUpdate(sql);
}
catch (Exception e)
{
System.out.println("Sorry, there is an error");
}
; break;
//---------------------------------------------------------------------------------------------

All help will be greatly appreciated as knowing how to do this is crucial for my upcoming final exams (I'm in high school). I can provide my email address at request if needed. Thank you very much in advance.

[r035198x]

1.) Use code tags if you have to post code.
2.) Only post the code that is relevant to a specific problem not to dump the whole lot.
3.) Did you open the API documentation page for ResultSetMetaData?

[08butoryr]

1.) Use code tags if you have to post code.
2.) Only post the code that is relevant to a specific problem not to dump the whole lot.
3.) Did you open the API documentation page for ResultSetMetaData?

Sorry about that, this is my first post - I will remember that next time thanks for pointing it out. About the ResultSetMetaData, the problem is I'm not sure how to apply it in the context of the program. This is what I am supposed to do:

"Change the displayCD() method so that it uses the ResultsSetMetaData interface to obtain the column names using the column numbers. You need to instantiate a new ResultSetMetaData object before you can use any of the methods."

Thanks for replying to my first thread, can you please help me further?

[r035198x]

Sorry about that, this is my first post - I will remember that next time thanks for pointing it out. About the ResultSetMetaData, the problem is I'm not sure how to apply it in the context of the program. This is what I am supposed to do:

"Change the displayCD() method so that it uses the ResultsSetMetaData interface to obtain the column names using the column numbers. You need to instantiate a new ResultSetMetaData object before you can use any of the methods."

Thanks for replying to my first thread, can you please help me further?

Do a select * from the table that you want to get column names from. (You can do a

select * from tableName where 1 = 2

because you are not really worried about the data)
Execute that sql and store the results in ResultSet. Then call the ResultSet.getResultSetMetaData to get a ResultSetMetaData object and play around with that as you like.

[08butoryr]

Do a select * from the table that you want to get column names from. (You can do a

select * from tableName where 1 = 2

because you are not really worried about the data)
Execute that sql and store the results in ResultSet. Then call the ResultSet.getResultSetMetaData to get a ResultSetMetaData object and play around with that as you like.

Ok thanks a lot! That takes care of the ResultsSetMetaData, but what about the other things like inserting, searching, deleting and editing? I tried, as you can see from my source code, to use the sql commands the way I learned, but when I run each of the portions it doesn't work (e.g. I select "Insert a student" from the menu and it prompts the user to input the name, class and house, but then nothing happens. What's wrong with my code?

Thanks again in advance.

[r035198x]

Ok thanks a lot! That takes care of the ResultsSetMetaData, but what about the other things like inserting, searching, deleting and editing? I tried, as you can see from my source code, to use the sql commands the way I learned, but when I run each of the portions it doesn't work (e.g. I select "Insert a student" from the menu and it prompts the user to input the name, class and house, but then nothing happens. What's wrong with my code?

Thanks again in advance.

Perhaps an Exception is being thrown but you would never know that because you are gobbling up all the exceptions with your catch blocks which look like this

catch (Exception e)
{
}

You should use

 catch (Exception e) {
     e.printStackTrace();
}

instead which catches the exception and sprays it to the console.

[08butoryr]

Perhaps an Exception is being thrown but you would never know that because you are gobbling up all the exceptions with your catch blocks which look like this


You should use

 catch (Exception e) {
     e.printStackTrace();
}

instead which catches the exception and sprays it to the console.

Ok I'm going to try that right now and get back to you.

[08butoryr]

I tried what you suggested and it helped me to solve the problems with the Insert method and showed what's wrong with the Delete method - I copied the following from the console so that you can take a look and identify what I need to change, because I'm not sure:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Student Name = Thabo'. 
  at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
  at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
  at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
  at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
  at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate  (JdbcOdbcStatement.java:288)
  at UseSchool.main(UseSchool.java:260)

As for the Insert method, the console still doesn't show me anything. At least I'm getting progress thanks to you.

[r035198x]

I tried what you suggested and it helped me to solve the problems with the Insert method and showed what's wrong with the Delete method - I copied the following from the console so that you can take a look and identify what I need to change, because I'm not sure:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Student Name = Thabo'. 
  at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
  at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
  at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:3111)
  at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:338)
  at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate  (JdbcOdbcStatement.java:288)
  at UseSchool.main(UseSchool.java:260)

As for the Insert method, the console still doesn't show me anything. At least I'm getting progress thanks to you.

Get a tutorial on how to use PreparedStatements and read that.