Help with JDBC Inserting Row&column

[danpotter]

well im making GUI application which when i enter the data in textfield it will automatically gets entered in MS ACCESS database..fine...

i created rows and columns in MS ACCESS like

SID   SNAME   BID   BNAME   ISSUE   RETURN

i took class with students and enter data sid and sname and it apears like this

SID   SNAME   BID   BNAME   ISSUE   RETURN
1       a
2       d 
3       f
4       d
5       s
4       d

FIne..but when i took class books and enter bid &bname it enters like

SID   SNAME   BID   BNAME   ISSUE   RETURN
1       a
2       d 
3       f
4       d
5       s
4       d        
              32     C book
              433    JAVA

but i want like

SID   SNAME   BID   BNAME   ISSUE   RETURN
1       a    32     C Book
2       d    433     JAVA
3       f
4       d
5       s
4       d

what exactly i need to to do in Result set..please help mee....
PLEASE DO PROVIDE WHAT CODE I NEED TO INSERT AND EXPLAIN IT....

Here my Book class code..

import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.io.*;
import java.awt.event.*;
import java.sql.*;
class book extends Frame implements ActionListener,ItemListener{
	
	Button ad,bck;
	Label bkn,bkid,hd,conf,bklst;
	TextField bin,bii;
	Choice up;
      ResultSet rs;
	Connection con;
	PreparedStatement ps;
	Statement stmt;
public book(){

		
		Font f=new Font("Arial",Font.BOLD,15);
		Font n=new Font("Arial",Font.PLAIN,15);
		Font g=new Font("Arial",Font.BOLD+Font.ITALIC,30);
		
		hd=new Label("BOOKS LOG",Label.CENTER);
		bkid=new Label("Book ID:",Label.LEFT);
		bkn=new Label("Book Name:",Label.LEFT);
		bklst=new Label("Books List:",Label.LEFT);
		conf=new Label();

		bii=new TextField(20);
		bin=new TextField(20);
		up=new Choice();

		ad=new Button("Add");
		bck=new Button("Back");

		

		hd.setFont(g);
		bck.setFont(f);
		ad.setFont(n);
		bkn.setFont(f);
		bklst.setFont(f);
		conf.setFont(f);
		bkid.setFont(f);
		bii.setFont(n);
           bin.setFont(n);

		hd.setBounds(80,50,300,100);
          	bkid.setBounds(80,200,70,40);
		bkn.setBounds(80,245,90,40);
		bklst.setBounds(80,290,90,40);
		bii.setBounds(180,205,150,26);
		bin.setBounds(180,250,150,26);
		up.setBounds(180,300,150,26);
		conf.setBounds(180,350,200,26);		
		bck.setBounds(100,410,60,40);
		ad.setBounds(330,410,60,40);

		setLayout(null);
	

		add(up);
		add(bklst);
		add(ad);
		add(bck);
		add(bkn);
		add(bkid);
		add(hd);
		add(bin);
		add(bii);
		add(conf);
		
		ad.addActionListener(this);
		up.addItemListener(this);
		bck.addActionListener(this);

try
	 				{

	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	con=DriverManager.getConnection("jdbc:odbc:mydb");
	 		     	stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	 		        rs=stmt.executeQuery("select * from mydb");
	 		        rs.next();
				
		        }
catch(Exception e){}


}

           
public void itemStateChanged(ItemEvent e){
		repaint();
	}

		public void actionPerformed(ActionEvent ae){
		String record;
		
		
if(ae.getSource()==ad){

			

try
{

	

     int bkidd=Integer.parseInt(bii.getText());
     String bknm=(bin.getText());

         stmt.executeUpdate("insert into mydb(BookID,BookName) values("+bkidd+",'"+bknm+"')");
         conf.setText("BOOK ADDED");
dbClose();
 dbOpen();
         
}
catch (java.sql.SQLException se) {
se.printStackTrace();

}

}
if(ae.getSource()==bck){
	GetFrame2 f2=new GetFrame2();
		f2.setVisible(true);
		f2.setSize(500,500);
		this.dispose();


}

}

public void dbOpen()
	{
		try
				{


		     	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");


		     	con=DriverManager.getConnection("jdbc:odbc:mydb");
		     	stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		        rs=stmt.executeQuery("select * from mydb");
		        rs.next();

		        }catch(Exception e){}
	}




	    	public void dbClose()
				{
					try{
						stmt.close();
					rs.close();
					con.close();
					}catch(Exception e){}
	}

public static void main(String args[]){
book bks=new book();


	}
	}

NOTE:-

Above code is Book.Java filewhich has bid and bname. I have another file that is Students.java which have sid sname information and Database coding is same as Book.java file

[sehudson]

To achieve what you are trying to do, you need to do an UPDATE after you insert the initial columns with the SID and SNAME, maybe something like(assuming the BID column holds integers):

UPDATE MYTABLE SET BID = 32, BNAME = 'C Book' WHERE SID= 1 AND SNAME='a'
UPDATE MYTABLE SET BID = 433, BNAME = 'JAVA' WHERE SID= 1 AND SNAME='a'

If you post some of the code that you have, I may be able to offer more direct help, but the general idea is that if you already have the rows with SID and SNAME data, you need to do an UPDATE for each record that you want to add BID and BNAME data for, not an INSERT, as this creates new records(rows), which isn't what you want.

[DarrylBurke]

Moved from New to Java.

db

[danpotter]

well i need to enter values after running the program,in GUI application..so is it should be like this??

String ups;
ups = "UPDATE MYTABLE SET BID = ' ', BNAME = ' ' WHERE SID= 1 AND SNAME='a'
stmt.executeUpdate(ups);

if required..i will surely post the code...

[sehudson]

Few things:
-You can combine lines 1 and 2 into a single line, by declaring and initializing your string with 1 line:

String ups = "UPDATE MYTABLE SET BID = ' ', BNAME = ' ' WHERE SID= 1 AND SNAME='a'";

-Class names should be capitalized, so "Book" instead of "book".

-Just an idea. Instead of creating records for a student, and then updating them later, what if you created a method in your Student class called checkoutBook, and you pass that method a Book object as an argument, and inside the method, a record is created for that student in the database with his sid, sname(from the Student), and bname, bid(from the Book).

So it would look something like this:

public void checkoutBook(Book bookToBeCheckedOut){
//Call to database to update
}

[danpotter]

Few things:
-You can combine lines 1 and 2 into a single line, by declaring and initializing your string with 1 line:

String ups = "UPDATE MYTABLE SET BID = ' ', BNAME = ' ' WHERE SID= 1 AND SNAME='a'";

-Class names should be capitalized, so "Book" instead of "book".

yah no problem with above two things..i know.....

-Just an idea. Instead of creating records for a student, and then updating them later, what if you created a method in your Student class called checkoutBook, and you pass that method a Book object as an argument, and inside the method, a record is created for that student in the database with his sid, sname(from the Student), and bname, bid(from the Book).

So it would look something like this:

public void checkoutBook(Book bookToBeCheckedOut){
//Call to database to update
}

well your idea is really good,but as im working in a collage proj..,i have to work my sir says and he is saying to do only in this way[what im doing now...]so pls guide me in that way only.....

[Tolls]

Not too sure about the database model, but I'm presuming that was handed to you to work with.
How are you supposed to identify which line the book is to be added to?
In the example you give in your first post you have to rows with an sid of '4' and an sname of 'd', so there is no possible update that can be done which will single out just one of these lines for update.

[danpotter]

Not too sure about the database model, but I'm presuming that was handed to you to work with.
How are you supposed to identify which line the book is to be added to?
In the example you give in your first post you have to rows with an sid of '4' and an sname of 'd', so there is no possible update that can be done which will single out just one of these lines for update.

im sorry..i really didnt get what u mean to say.....

[sehudson]

im sorry..i really didnt get what u mean to say.....

Tolls is saying that based on what you provided, you initially have multiple records containing the same SID and SNAME (look at rows 5 and 7 of your 2nd code snippet in your 1st post).

If you want to add the book data to a record, how would you know whether row 5 is to be updated or if row 7 is to be updated? When you do an SQL UPDATE, you would distinguish between records by using a WHERE clause and specify some particular row/rows you want to update, but since you have 2 rows with identical SNAME and SID values, BOTH of those rows will be updated.

So basically, it seems like there is something flawed with the way the database table is setup, or, your explanation isn't correct.

[Tolls]

Which I suppose goes back to my first point about the database model.
Is that the model you have to work with, or is it one you've come up with?