2 chengzui chengzui 于 2017.01.02 01:02 提问

delete出现的两个错误

create table teacher(tno char(6) primary key,
tname varchar(20) not null,
authority varchar(10) not null,
password varchar(20) not null
);

insert into teacher values('xg2221','钟飞','teacher','123456');
insert into teacher values('111111','张欢欢','teacher','123456');
有两个错误:
1、、、、、、、、、、、、、、、、、、、、、、
public class TeacherDaoImpl{
public static void main(String[] args) {
TeacherDaoImpl tdi=new TeacherDaoImpl();
String[] str={"111111"};
tdi.deleteTeacherByNo(str);

public int deleteTeacherByNo(String[] tno) {
    con=DBUtils_mssql.getCon();
    String tnos="";
    for(int i=0;i<tno.length;i++){
        if(i==tno.length-1){
            tnos=tnos+tno[i]+")";//最后一个学号
        }else{
            tnos=tnos+tno[i]+",";//中间的学号
        }
    }
    String sql="delete from Teacher where tno in ("+tnos;
    int i=0;
    try {
        /*
         * delete from teacher where tno in (tno1,tno2,...)
         * */
        ps=con.prepareStatement(sql);
        i=ps.executeUpdate();
        return i;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return 0;
}
}
}

1、运行代码会出现错误:
com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the varchar value 'xg2221' to data type int.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)
at com.student.daoImpl.TeacherDaoImpl.deleteTeacherByNo(TeacherDaoImpl.java:89)
at com.student.daoImpl.TeacherDaoImpl.main(TeacherDaoImpl.java:28)

2、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、

public static void main(String[] args) {
TeacherDaoImpl tdi=new TeacherDaoImpl();
String[] str={"111111"};
tdi.deleteTeacherByNo(str);
}
要是将main函数改成下面的代码
public class TeacherDaoImpl{
public static void main(String[] args) {
TeacherDaoImpl tdi=new TeacherDaoImpl();
String[] str={"xg2221"};
tdi.deleteTeacherByNo(str);
}

public int deleteTeacherByNo(String[] tno) {
    con=DBUtils_mssql.getCon();
    String tnos="";
    for(int i=0;i<tno.length;i++){
        if(i==tno.length-1){
            tnos=tnos+tno[i]+")";//最后一个学号
        }else{
            tnos=tnos+tno[i]+",";//中间的学号
        }
    }
    String sql="delete from Teacher where tno in ("+tnos;
    int i=0;
    try {
        /*
         * delete from teacher where tno in (tno1,tno2,...)
         * */
        ps=con.prepareStatement(sql);
        i=ps.executeUpdate();
        return i;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return 0;
}
}
2、就会出现如下的提示:
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'xg2221'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1635)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:426)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:372)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:6276)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1793)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:184)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:159)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:315)
at com.student.daoImpl.TeacherDaoImpl.deleteTeacherByNo(TeacherDaoImpl.java:89)
at com.student.daoImpl.TeacherDaoImpl.main(TeacherDaoImpl.java:28)

4个回答

danielinbiti
danielinbiti   Ds   Rxr 2017.01.02 09:21
已采纳
 if(i==tno.length-1){
            tnos=tnos+tno[i]+")";//最后一个学号
        }else{
            tnos=tnos+tno[i]+",";//中间的学号
        }
改成
if(i==tno.length-1){
            tnos=tnos+"'"+tno[i]+"')";//最后一个学号
        }else{
            tnos=tnos+"'" + tno[i]+"',";//中间的学号
        }
caozhy
caozhy   Ds   Rxr 2017.01.02 01:57

sql 用 insert into 表(tno, tname, authority, [password]) values (值1, 值2, ...) 这样,明确你插入的字段,同时password是关键字,需要转义
qingtongwangzhe
qingtongwangzhe   2017.01.02 11:04

我怎么感觉你sql语句后面少了个括号

guwei4037
guwei4037   Ds   Rxr 2017.01.02 11:22

String sql="delete from Teacher where tno in ("+tnos; //后面括号不匹配,少个)

教你个技巧,打印出最终的sql,带到sqlserver中执行一下就知道哪里有问题了,sql语句的错误。

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!