Someone please help!
I'm writing a program that enable a user to search for a patient Info when patient number is given.
here is the search code snippet

public ArrayList searchPatient(String patientNum)

	{
		try	{
String sql = "SELECT fname, sname, location, dob, dor, race, gender, status, initials, idnum FROM PatientTable WHERE patientNo = " + patientNum;

			// Create a prepared statement
 			Statement s = con.createStatement();
			
            String pno = "";
	    String firstname="";
	    String lastname="";
	    String locate = "";
	    String dOB="";
	    String dOR="";
	    String Race="";
	    String Gen="";
	    String Stat="";
	    String Initial="";
            double id;

                        ResultSet rs = s.executeQuery(sql);

		while(rs.next())
	{
                    pno = rs.getString(1);
		    firstname = rs.getString(2);
		    lastname = rs.getString(3);
		    locate=rs.getString(4);
		    dOB = rs.getString(5);
		    dOR = rs.getString(6);
		    Race=rs.getString(7);
		    Gen=rs.getString(8);
		    Stat=rs.getString(9);
		    Initial=rs.getString(10);
		    id = rs.getDouble(11);
				
				//Create a PatientInfo object
PatientInfo patient = new PatientInfo(id, pno, firstname, lastname, locate,dOB, dOR, Race, Gen, Stat, Initial);

				//Add the patient object to array list
				patientList.add(patient);
			}
		}

don't get it the Sql statement looks fine but when I run it gives me this exception:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

Thamks for your support!

Seems to be a problem with the way your query is created; what's the JDBC type of `patientNo'? Is it a VARCHAR? If yes, then you need to wrap the passed in patient number in single quotes when constructing the query. If you don't, your database engine considers the passed in patient number as some kind of identifier or parameter and hence the given error.

BTW, your code is vulnerable to SQL Injection. Try passing in "xxx' or 1=1--" as patient number and watch all the rows being fetched instead of the one you requested. Use PreparedStatement instead of normal statements to save yourself from the trouble of escaping and quoting your input as well as SQL Injection.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.