hi,
im creating a Java program that connects through a microsoft access database.. my plan is that I could manipulate the data through java.. I would like to view, add, edit and delete data.. so far im finished with view and delete but.. I seem to have a problem with my delete.. It doesn't seem to delete alphanumeric values (such as A1 or, A)
the following is my code.. anyone could you please help me..
I would appreciate suggestions from anyone of you..
Scenario:
if I input numbers for the emp_no to be deleted it works
but if I input alphanumeric for emp_no it doesn't work
emp_no is a field in my database it is the primary key and is of text data type..
Thanks in advance..
import java.sql.*;
import javax.sql.*;
import java.io.*;
import java.lang.*;
import java.util.*;
public class JDBCtest2
{
public static void Selection()
{
System.out.println("***********Employee Records Management************" + '\n');
System.out.println("What do you wish to do?");
System.out.println("Legend:");
System.out.println("To View Records: Enter v or V");
System.out.println("To Add a New Record: Enter a or A");
System.out.println("To Delete Records: Enter d or D");
System.out.println("To Quit Program: Enter q or Q");
System.out.print("Enter Choice:");
}
public static void main(String[] args) throws Exception
{
BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
char input, rec_num;
String INPUT, record_num;
int x = 0, numinput;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dataSourceName = "emp2ODBC";
String dbURL = "jdbc
dbc:" + dataSourceName;
Connection con = DriverManager.getConnection(dbURL, "","");
Statement s = con.createStatement();
Selection();
//choice = stdin.readLine();
input = (char)System.in.read();
INPUT = in.readLine();
while(input!='Q' || input != 'q')
{
//if(choice.equalsIgnoreCase("v"))
if(input == 'v' || input == 'V')
{
System.out.println("");
s.execute("select * from employee");
System.out.println("Employee Records" + '\n');
//s.execute("select emp_title from Employee");
ResultSet rs = s.getResultSet();
// if rs == null, then there is no ResultSet to view
if (rs != null)
// this will step through our data row-by-row
while ( rs.next() )
{
//OUTPUT TO SCREEN
//System.out.println("Record Number: " + rs.getString(1) );
System.out.println("Employee Number: " + rs.getString("emp_no") );
System.out.println("Employee Name: " + rs.getString("emp_name") );
System.out.println("Employee Title: " + rs.getString("emp_title") );
System.out.println("Employee Department: " + rs.getString("emp_dept") );
System.out.println("Employee Contact Number: " + rs.getString("emp_contactno") + '\n');
}
System.out.println("End of Record");
System.out.println("-----------------------------------");
x=0;
Selection();
input = (char)System.in.read();
INPUT = in.readLine();
//break;
}
else if(input == 'd' || input == 'D')
{
System.out.print("Enter the Employee Number you wish to delete: ");
INPUT = in.readLine();
//input = (char)System.in.read();
//INPUT = in.readLine();
//rec_num = (char)System.in.read();
//record_num = in.readLine();
System.out.println("Input = " + INPUT);
//numinput = Integer.parseInt(record_num);
s.execute("delete * from employee where emp_no LIKE "+ INPUT +"");
//WHERE `id` IN (142,156,168)
s.execute("select * from employee");
System.out.println('\n' + "Remaining Employee Records" + '\n');
ResultSet rs = s.getResultSet();
if (rs != null)
while ( rs.next() )
{
//OUTPUT TO SCREEN
//x=x+1;
System.out.println("Employee Number: " + rs.getString("emp_no") );
System.out.println("Employee Name: " + rs.getString("emp_name") );
System.out.println("Employee Title: " + rs.getString("emp_title") );
System.out.println("Employee Department: " + rs.getString("emp_dept") );
System.out.println("Employee Contact Number: " + rs.getString("emp_contactno") + '\n');
}
System.out.println("End of Record");
System.out.println("-----------------------------------");
x=0;
Selection();
input = (char)System.in.read();
INPUT = in.readLine();
}
else if(input == 'q' || input == 'Q')
{
System.out.println('\n' + "EXITING PROGRAM..." + '\n');
System.exit(0);
}
else if(input == 'a' || input == 'A')
{
System.out.println('\n' + "asasasEXITING PROGRAM..." + '\n');
Selection();
input = (char)System.in.read();
INPUT = in.readLine();
}
else
{
System.out.println('\n' + "WARNING!!!" + '\n' + "You have entered incorrect command!" + '\n');
Selection();
input = (char)System.in.read();
INPUT = in.readLine();
}
}
// close the Statement to let the database know we're done with it
s.close();
// close the Connection to let the database know we're done with it
con.close();
}
catch (Exception err)
{
System.out.println("WARNING!!!: " + err);
//Selection();
//System.exit(0);
}
}
}