CODEWITHSUNDEEP
javamysqldatabasevariablesscope
sadeee nadeee
sadeee nadeee

Reputation: 144

Java ,Mysql- Column name identified as a variable

 Connection connection;
   Statement statement=null;

            try{

             connection = DBConnection.getDBConnection().getConnection();
             statement=connection.createStatement();

                 for (int j = 0; j < students.length; j++) {
                    if(students[j][0]!=null){
                        String sql="SELECT * from students WHERE id="+students[j][0]+" LIMIT 1"; 
                        ResultSet rs=statement.executeQuery(sql);
                        if(rs.next()){
                             String reg=rs.getString("regNo");
                          //  if(students[j][1]==toDayDate){
                                 float num1=Math.round(Float.valueOf(students[j][2]));
                                 String rrr=String.valueOf(num1)+"0";
                                 String sql1="SELECT * from reg_courses WHERE regNo="+reg+" && day="+students[j][4]+" && start="+rrr+" LIMIT 1"; 
                                ResultSet rs1=statement.executeQuery(sql1);
                                if(rs1.next()){
                                  if(students[j][3]=="I"){
                                        String sql2="UPDATE attendances SET status = '1' WHERE regNo ="+reg+"&& code="+rs1.getString("code")+"&& time="+rrr+"&& date="+students[j][1];
                                          statement.executeUpdate(sql2);

                                  }
                                  else if(students[j][3]=="O"){
                                       String sql2="UPDATE attendances SET statusA = '1',present = '1', WHERE regNo ="+reg+"&& code="+rs1.getString("code")+"&& time="+rrr+"&& date="+students[j][1]+"&& status='1'";
                                      statement.executeUpdate(sql2);
                                  }
                                }

                           // }
                        }

                    }

            } 


            }
            catch(Exception e){
                e.printStackTrace();
            }

There are 3 tables in my database as students,reg_courses and attendances.Every one of them have a column to store index number.regNo is the column name of all of them.When I run it it says that

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'SC9208' in 'where clause'.... SC9208 is index number in a table.Here in the sql1 ,

String sql1="SELECT * from reg_courses WHERE regNo="+reg+" && day="+students[j][4]+" && start="+rrr+" LIMIT 1";

the regNo is identified as a variable.But not as column name.??Any idea .Why? There are no errors in the other part of the code.I have checked many times.Can someone indicate the place with the error??

Upvotes: 0

Views: 324

Answers (2)

Keyur Panchal
Keyur Panchal

Reputation: 1402

Update your query variables:

String sql1="SELECT * from reg_courses WHERE regNo='"+reg+"' && day='"+students[j][4]+"' && start='"+rrr+"' LIMIT 1";

// In if block
String sql2="UPDATE attendances SET status='1' WHERE regNo='"+reg+"' && code='"+rs1.getString("code")+"' && time='"+rrr+"' && date='"+students[j][1]+"'";

// In else if block
String sql2="UPDATE attendances SET statusA='1', present='1' WHERE regNo='"+reg+"' && code='"+rs1.getString("code")+"' && time='"+rrr+"' && date='"+students[j][1]+"' && status='1'";

As per your query I am assuming that status and statusA are VARCHAR or CHAR.
If they are INT, then you need remove single quotes, i.e., status=1.

Upvotes: 1

Scary Wombat
Scary Wombat

Reputation: 44844

the value needs to be single-quoted

e.g.

SELECT * from reg_courses WHERE regNo='SC9208' and day=1 and start=14.00 LIMIT 1

Although it would be better to use a PreparedStatement and setString

Upvotes: 2

Related Questions