Display a row in jtextarea each time a new name is entered

Question

0.00/5 (No votes)

See more:

I want to display a row in jtextarea each time a new name is entered. Suppose a user writes "Paul" in a jtextfield, based on SQLite, it should show it's corresponding response, which will be "Smith". I have a database with several names and no matter what I write, say "Peter", it shows "Smith" where it should have shown "Parker". What I want is each time a user enters a name, it's corresponding surname should appear.

What I have tried:

I have tried this code but only the first row from the db appears:
EDIT: This is the method I used

public void fetch() throws Exception{	
	Connection con = null;
	con = sqliteConnection.sqliteConnector();
	PreparedStatement pst = null;
	ResultSet rs = null;
	try
	    {
		String quest=userInputField.getText().toLowerCase().trim().toString();
			String sql = "select * from user where name LIKE '% %'";
			pst = con.prepareStatement(sql);
			//pst.setString(1, quest);//getting array index out of bounds 0
			rs = pst.executeQuery();
			if(rs.next()){ 
				String ans = rs.getString("answer");
				textarea.append(ans +"\n");   
				
			}else {
            	  textarea.append("Nothing was found." +"\n");  
            }
			
			System.out.println("Succesfully retrieved data from bcsdb");
			if (pst != null) pst.close();
            System.out.println("PST was successfully closed!");
            if (rs != null) rs.close();
            System.out.println("PS was successfully closed!");
            if (con != null) con.close();
            System.out.println("Con was successfully closed!");
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}		
	}

Thanks in advance!

Posted 15-Jul-18 20:02pm

Member 13780097

Updated 19-Jul-18 19:09pm

v5

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2018-07-15T20:37:00

Solution 1

Your SQL statement will match all names. You need to modify it with a parameter that specifies the name entered by the user.

Posted 15-Jul-18 20:37pm

Richard MacCutchan

Comments

Member 13780097 17-Jul-18 0:27am

Can you show me how please? I tried with pst.setString(1, variable_name) but I'm having his error: java.lang.ArrayIndexOutOfBoundsException: 0

Richard MacCutchan 17-Jul-18 2:51am

You need to show the code and explain where the error occurs. See Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)[^] for more details.

Member 13780097 17-Jul-18 23:47pm

I'm having an error at this line : pst.setString(1, userInputField.getText().toLowerCase().trim());

Richard MacCutchan 18-Jul-18 1:13am

I cannot see anything obvious in that statement. Please edit your question and show more of the code.

Member 13780097 20-Jul-18 1:09am

I posted the full method I used. Hope it can help you to find a solution for me

Richard MacCutchan 20-Jul-18 3:15am

Your SQL statement is still incorrect, and you should not be using LIKE since you are looking for a record with a specific name. It should be something like:
"select * from user where name = ?". You may need to check the documentation for the exact syntax.

Member 13780097 21-Jul-18 3:36am

Thanks for your reply. I tried your solution but it doesn't seem to work. Everything works with SQlite, but it just can't fetch data based on user input. I tried this:
String sql = "SELECT name, surname FROM user WHERE UPPER(name) LIKE UPPER('"+quest+"') || '%'"; - it fetches from the first row only. I don't know if it's the version of SQLite that's causing the issue.

Richard MacCutchan 21-Jul-18 3:51am

Why are you still using the LIKE phrase, when you do not need it? Just use the sample I suggested with the correct name value in the parameter (correctly cased), or:
"select * from user where name = \"" + quest + "\";"

Member 13780097 21-Jul-18 10:16am

When I use your sample, nothing is returned:
String sql = "select * from user where name = ' " +quest+ " '; ";//nothing is found Only the like seems to work. I'm about to give up! Do you think it's because I'm using SQLite?

Richard MacCutchan 21-Jul-18 10:21am

No it's most likely because you are not using the correct values for the name parameter. If the name in the database is Norman, then the value passed in must be exactly the same, including upper case for the initial letter and lowercase for the rest.

You can check the exact content just by using "SELECT * FROM user;".

Member 13780097 21-Jul-18 10:49am

I agree with you. Say I use "SELECT * FROM user;" and then I type "Paul", "Smith" is returned, which is correct. But when I type "Peter", "Smith" is returned again and SQLite doesn't seem to know that it should return "Parker". I think I've used all the option to help SQLite know what I want:


//String sql = "select * from user where name match ' ' ";//prints first row only
//String sql = "SELECT * FROM user WHERE name like ? ";//shows first row only
//String sql = "SELECT * FROM user WHERE LOWER(name) LIKE ?";
//String sql = "select * from user where name LIKE :name"; //show answer from first row only
String sql = "select * from user";//first row is returned
//String sql = "select * from user where name LIKE '"+quest+"' ";//showing first row only
//String sql = "select * from user where name = '%" + quest + "%' ";//nothing is retrieved from the db
//String sql = "select * from user where name=?";//not fetching any data
//String sql = "select * from user where name like 'a%'";
//String sql = "SELECT name, surname FROM user WHERE UPPER(name) LIKE UPPER('"+quest+"') || '%'";
//String sql = "select * from user where name like '%" + quest + "%'";//show from first row only

<code>

Richard MacCutchan 21-Jul-18 13:02pm

It seems that whatever I suggest (having tested it on my system) you continue to do things differently. And you don't seem to understand how SQL actually works. When you enter "select * from user"; it will return every row, but you need to process the results to actually display them. I suggest you go back to the Java documentation and study how to process SQL result sets.

Member 13780097 22-Jul-18 10:33am

Thank you for your patience and not giving up. Believe me I have tried everything that you said and even went through Java documentations. Did you use SQLite when you tested on your system? No matter what I try, this part doesn't seem to work. Can you eventually show me everything that you did? Maybe it's the if(rs.next()) that's causing an issue or I should be using createStatement instead of PreparedStatement. Thanks a lot!

Richard MacCutchan 22-Jul-18 11:26am

Work through the tutorial that I gave you the link for and see how to do it. That is what I did, and it works correctly.

Richard MacCutchan 22-Jul-18 4:18am

Take a look at SQlite Java - How To Use JDBC To Interact with SQLite[^].