Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

inserting record into MSAccess Table

 
sonali mundke
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
I have problem in JDBC.
I am using MSAccess as backend.
The problematic area is the code in red color.
I am reading two string values and one integer value from GUI to insert into Employee table having fields empCode, empName, empAge.
I am not able to concatenate single quote with the varible.
If the empCode is A001 instead of value of a variable it is inserting '+strCode+' in the table.
can anybody plz help me

import java.sql.*;
import java.awt.*;
import java.awt.event.*;
public class InsertRecord extends Frame implements ActionListener {
Connection conn;
Statement state;
Panel p1,p2,p3,p4;
TextField txtCode,txtName,txtAge;
Button btnInsert;
String strCode, strName;
int intAge;

InsertRecord() {
setSize(300,300);
setLayout(new GridLayout(4,1));

p1=new Panel();
p2=new Panel();
p3=new Panel();
p4=new Panel();
add(p1);
add(p2);
add(p3);
add(p4);
txtCode=new TextField(4);
txtName=new TextField(15);
txtAge=new TextField(2);
btnInsert=new Button("Insert");
p1.add(txtCode);
p2.add(txtName);
p3.add(txtAge);
p4.add(btnInsert);
}

public void connection() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn=DriverManager.getConnection("jdbc dbc:java");
System.out.println("Successful connection!!!");
state=conn.createStatement();
System.out.println("Statement created");
} catch (ClassNotFoundException e) {
System.out.println("Error1 : "+ e.getMessage());
}
catch (SQLException se) {
System.out.println("Error2 :"+se.getMessage() );
}
btnInsert.addActionListener(this);
}

public void actionPerformed(ActionEvent ae) {
strCode=txtCode.getText();
strName=txtName.getText();
intAge=Integer.parseInt(txtAge.getText());
System.out.println(strCode +"\t"+strName+"\t"+intAge);
try {
state.executeUpdate("insert into Employee (empCode,empName,empAge) values ('''+strCode+''','''+strName+''',intAge)");
System.out.println("Records inserted");
state.close();
conn.close();

} catch (SQLException sqle) {
System.out.println("Error3 :"+sqle.getMessage() );
}
}
public static void main(String args[]) {
InsertRecord that=new InsertRecord();
that.setVisible(true);
that.connection();
}
}
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Personally I never create a sql query like that, I always use PreparedStatements cos they remove a lot of the complexity (and are more efficient?)
Like this:

So what you are doing is replacing the '?' with each desired value. The fact that it needs single quotes or not is handled invisibly.
The danger to be aware of is the index field in the setXXX methods, they start at 1 NOT 0 and there are exactly the same number as you have question marks...
I've found it saves a lot of trouble in the long run.
Dave.
 
sonali mundke
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot!
I tried it and got the correct answer.
Please explain me how does it save a lot of trouble in the long run?
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually I told a slight fib, the format I usually use is:

since when you use a select statement you want to call the table attributes specifically by name and this allows you to insert and delete returned attributes without fixing all of the values.
Another difference is that we use internal tools for handling DB operations since the following steps are the same every time:
- set prepared statement
- insert values to prepared statement
- inflate BusinessObject (in the case of a select, map the ResultSet to an object)
so the rest of the code can be abstracted.
I'd be interested if someone could actually give some specifics on whether a PreparedStatement and setting values on it is more efficient than hard coding the values into a String.
Dave.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic