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 "ResultSetMetaD ata" 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.sho wInputDialog("P lease enter student's name");
String input2 = JOptionPane.sho wInputDialog("P lease enter student's class");
String input3 = JOptionPane.sho wInputDialog("P lease enter student's house");
conn = DriverManager.g etConnection ("jdbc:odbc:Sch ool", "", "");
set = conn.createStat ement();
set.executeUpda te("INSERT INTO tblStudent " + "VALUES ('" + input1 + " ', '" + input2 + "', '" + input3 + "')");
sql2 = "SELECT * FROM tblStudent";
rs = set.executeQuer y(sql2);

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

while (rs.next())
{
name = rs.getString("S tudent Name");
studentClass = rs.getString("C lass");
house = rs.getString("H ouse");
System.out.prin tln(name + "\t\t" + studentClass + "\t\t" + house);
}
conn.close();
}

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

case 2: //Search by name
try
{
conn = DriverManager.g etConnection ("jdbc:odbc:Sch ool", "", "");
set = conn.createStat ement();
sql = "SELECT * FROM tblStudent";
rs = set.executeQuer y(sql);
String[] names = new String[8];

while (rs.next())
{
names[0] = "SELECT * FROM tblStudent" + "WHERE ID = 1";
System.out.prin tln(names[0]);
name = rs.getString("S tudent Name");
String input = JOptionPane.sho wInputDialog("W hich name are you searching for?");
SearchNames obj4 = new SearchNames(nam e, input);
int place = obj4.getPlace() ;

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

case 3: //Delete a name

try
{
conn = DriverManager.g etConnection ("jdbc:odbc:Sch ool", "", "");
set = conn.createStat ement();
sql = "DELETE FROM tblStudent WHERE Student Name = Thabo";
set.executeUpda te(sql);
}
catch (Exception e)
{
System.out.prin tln("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 ResultSetMetaDa ta?

[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 ResultSetMetaDa ta?

Sorry about that, this is my first post - I will remember that next time thanks for pointing it out. About the ResultSetMetaDa ta, 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 ResultsSetMetaD ata interface to obtain the column names using the column numbers. You need to instantiate a new ResultSetMetaDa ta 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 ResultSetMetaDa ta, 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 ResultsSetMetaD ata interface to obtain the column names using the column numbers. You need to instantiate a new ResultSetMetaDa ta 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 [CODE=sql]select * from tableName where 1 = 2[/CODE] because you are not really worried about the data)
Execute that sql and store the results in ResultSet. Then call the ResultSet.getRe sultSetMetaData to get a ResultSetMetaDa ta 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 [CODE=sql]select * from tableName where 1 = 2[/CODE] because you are not really worried about the data)
Execute that sql and store the results in ResultSet. Then call the ResultSet.getRe sultSetMetaData to get a ResultSetMetaDa ta object and play around with that as you like.

Ok thanks a lot! That takes care of the ResultsSetMetaD ata, 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 ResultsSetMetaD ata, 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
[CODE=java] catch (Exception e) {
e.printStackTra ce();
}[/CODE]
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
[CODE=java] catch (Exception e) {
e.printStackTra ce();
}[/CODE]
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 PreparedStateme nts and read that.