java.lang.RuntimeException: java.sql.SQLException: Field 'UserName' doesn't have a default value

Question

I am developing a web app.... in which there will be a list of users requesting registration ...Wen ever we click on accept all the details must be moved from register table to login table...

This is my servlet:

ClientApproveService approve=new ClientApproveService();
approve.clientApprove();

this is my service:

public class ClientApproveService {

    public void clientApprove() {
        ClientApproveDAO cad=new ClientApproveDAO();
        DataSource dataSource=new DataSource();
        cad.setDataSource(dataSource);
        cad.insertClient();

    }

}

And this is my DAO:

public class ClientApproveDAO {

        private DataSource dataSource;
        public void setDataSource(DataSource dataSource) {
       this.dataSource = dataSource;
    }
    Connection conn=null;
    PreparedStatement statement=null;
    ResultSet rs=null;


    public void insertClient() {    
        try{
            conn=dataSource.createConnection();
            PreparedStatement ps=conn.prepareStatement("insert into login(id,FirstName,LastName,Gender,Category,Dateofbirth,Age,Address,Country,State,city,PinCode,EmailId,ContactNo,MobileNo)select * from register ");
            ps.executeUpdate();
        }
        catch (SQLException e) {
            throw new RuntimeException(e);

    } finally {
            if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
        if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
        if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
    }


    }

}

This is my register table :

create table register(
id int(100) not null AUTO_INCREMENT primary key,
FirstName varchar(300) default null,
LastName varchar(300) default null,
Gender varchar(200) default null,
Category varchar(200) default null,
DateOfBirth varchar(200) default null,
Age int(3)default null,
Address varchar(1000) default null,
Country varchar(500) default null,
State varchar (500) default null,
city varchar(500)default null,
PinCode int(10)default null,
EmailId varchar(500)default null,
ContactNo varchar(20) default null,
MobileNo varchar(20) default null
);

And this is my login table:

create table login(
UserName varchar(100)not null,
PassWord varchar(100)not null,
id int(100) not null AUTO_INCREMENT primary key,
FirstName varchar(300) default null,
LastName varchar(300) default null,
Gender varchar(200) default null,
Category varchar(200) default null,
DateOfBirth varchar(200) default null,
Age int(3)default null,
Address varchar(1000) default null,
Country varchar(500) default null,
State varchar (500) default null,
city varchar(500)default null,
PinCode int(10)default null,
EmailId varchar(500)default null,
ContactNo varchar(20) default null,
MobileNo varchar(20) default null
)

i have to insert all data from register table to login table but i ve nt still auto generated username and password fields in login table... But i should be able to generate the rest ryt??? But when i run this it throws an exception

java.lang.RuntimeException: java.sql.SQLException: Field 'UserName' doesn't have a default value

Please someone help me fix this... Thanks in advance....

Alexander · Accepted Answer · 2014-02-18 07:27:54Z

3

You are not giving UserName filed a vaule, and it does not have default. Try this:

insert into login(id,FirstName,LastName,Gender,Category,Dateofbirth,Age,Address,Country,State,city,PinCode,EmailId,ContactNo,MobileNo,UserName) 
select *, 'undefined' from register

Or, alternatively, you can ALTER the table, so userName field would have the default value:

ALTER TABLE login MODIFY userName varchar(100) NULL DEFAULT NULL;

edited Feb 18, 2014 at 7:27

answered Feb 18, 2014 at 7:22

Alexander

3,1992 gold badges21 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3222718 Over a year ago

i tried this and i got an exception like :java.lang.RuntimeException: java.sql.SQLException: Column count doesn't match value count at row 1

user3222718 Over a year ago

hey its working but i ve two ppl registered for the post and am clicking accept for one user but both the users are getting added to the login table i want only that person to be added... i think i ve to fetch the id when i click accept can u guide me on that??

user3222718 Over a year ago

'insert into login(id,FirstName,LastName,Gender,Category,Dateofbirth,Age,Address,Country,State,city,PinCode,EmailId,ContactNo,MobileNo,UserName) select *, 'undefined' from register' I tried this

Alexander Over a year ago

great. Now, when this problem is solved, you probably need to post another question, and fully desribe it, because right now, I'm not following you.

user3222718 Over a year ago

hey and one more thing even yo alter worked... I think for my project alter is more suitable at this stage so thanks a ton..... and ya surely ll post a new question..... thank u

emre · Accepted Answer · 2014-02-18 07:23:20Z

3

You need to provide a username value since the column definition is "NOT NULL". You may first query the register table, generate a username, and then insert the new record to the login table.

answered Feb 18, 2014 at 7:23

emre

6628 silver badges18 bronze badges

Comments

anish · Accepted Answer · 2014-02-18 07:21:22Z

-1

Run the following sql query, in sql command prompt and check whether it's working or not

  insert into login(id,FirstName,LastName,Gender,Category,Dateofbirth,Age,Address,Country,State,city,PinCode,EmailId,ContactNo,MobileNo)select * from register

answered Feb 18, 2014 at 7:21

anish

7,50014 gold badges90 silver badges155 bronze badges

Collectives™ on Stack Overflow

java.lang.RuntimeException: java.sql.SQLException: Field 'UserName' doesn't have a default value

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related