Need help with derby database connection/insert

[javaguy345]

Hi guys I'm currently using netbeans and need to make a program for school where the user is able to store questions he's written in a database. I have connected to the database well and have no errors except when I try to save the information to the database here is my code:

String myInsertsql = " VALUES(\'" + newquestion1.getQuestion() + "\', "
+ "\'" + newquestion1.getQuestionNo() + "\', \'" + newquestion1.getAnswerA() + "\', " +
"\'" + newquestion1.getAnswerB() + "\', " + "\'" + newquestion1.getAnswerC() + "\', " + "\'" + newquestion1.getAnswerD() + "\')";


try
{
Connection con = DriverManager.getConnection(databaseURL, "user", "password");
Statement stmt = con.createStatement();
stmt.executeUpdate("INSERT INTO QUESTIONS (QUESTION, QUESTIONNUMBER, ANSWER, ANSWERA, ANSWERB, ANSWERC, ANSWERD)" + myInsertsql);

con.close();
} catch (SQLException errorObject)
{
JOptionPane.showMessageDialog(null, "ERROR: "+ errorObject);
}





String databaseURL = "jdbc:derby://localhost:1527/questions";

public main() {
initComponents();

try
{
Class.forName("org.apache.derby.jdbc.ClientDriver" );

}catch (ClassNotFoundException e)
{
JOptionPane.showMessageDialog(null, e);
}


I tried to align it the best I could I get an error saying: Scheme 'USER' does not exist would be awesome if anyone can fix it.

[Dark]

Unless you put it in [code][/code] tags it will not save the formatting.

What error do you get when you try to save?

[doWhile]

Post an SSCCE that demonstrates the problem, along with the full error message as Dark mentioned. Secondly, you might consider using a PreparedStatement
Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)

[JosAH]

I tried to align it the best I could I get an error saying: Scheme 'USER' does not exist would be awesome if anyone can fix it.

I wonder how you've created your table; tables belong in a schema; have you created a schema first and created a table in that schema?

kind regards,

Jos

[javaguy345]

I'm kind of lost can you link me to some info about schemas? The error code says something about schema and user iI got it on my OP.
EDIT: In properties it said my schema is in APP, which my table is in.

[JosAH]

I'm kind of lost can you link me to some info about schemas? The error code says something about schema and user iI got it on my OP.
EDIT: In properties it said my schema is in APP, which my table is in.

Ok, then the full name of your table is APP.QUESTIONS. Change the text of your SQL query.

kind regards,

Jos

[javaguy345]

Okay now I'm receiving a different error message it says Columns type of Numeric cannot hold type CHAR. But I'm sure I'm passing an int into the numeric column

[JosAH]

Okay now I'm receiving a different error message it says Columns type of Numeric cannot hold type CHAR. But I'm sure I'm passing an int into the numeric column

You think you're passing a numeric value but obviously you're not; print the complete SQL query before you execute it and see what you're feeding to the database; there's an error there (pay very close attention to the quotes).

kind regards,

Jos

[Tolls]

You are surrounding every one of your values in quotes, so essentially saying they're CHARs.

This should be a PreparedStatement, so you wouldn't have to worry about quotes at all. Your SQL would become:

INSERT INTO QUESTIONS (QUESTION, QUESTIONNUMBER, ANSWER, ANSWERA, ANSWERB, ANSWERC, ANSWERD)VALUES(?,?,?,?,?,?,?)
(assuming I've counted my ? correctly)

You would then use the relevant setXXX() method for each value ('?').