This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes inserting record into MSAccess Table Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "inserting record into MSAccess Table" Watch "inserting record into MSAccess Table" New topic
Author

inserting record into MSAccess Table

sonali mundke
Greenhorn

Joined: May 28, 2001
Posts: 2
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

Joined: Mar 06, 2001
Posts: 13459

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

Joined: May 28, 2001
Posts: 2
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

Joined: Mar 06, 2001
Posts: 13459

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: inserting record into MSAccess Table
 
Similar Threads
problem in actionlistner
problem in ActionListener
So difficult to use Layout managers!!!
basic things with awt
problem in actionlistner