Select Query of Access database

I have developed an application in java with Access database.
In that I am using select query as

ResultSet rs = stmt.executeQuery("select stestid,mtestid,stestname,"+docid+" from Subtest");

Here I have table 'subtest' which contains columns stestid,mtestid,stestname,1,2,3,4,5,6,7,8,9,10,11

I want to select value from one of the column 1,2,3,4,5,6,7,8,9,10,11 dynamically. Thats why I took variable 'docid' for same.
But if I take docid=1 or anything between 1,2,3,4,5,6,7,8,9,10,11  I get wrong result.
e.g. If I take docid=5 then it results '5' insteade of value from table '5'. Please help

Added MS Access and SQL Syntax zones
Access Zone Advisor
Who is Participating?
Amitkumar PConnect With a Mentor Sr. ConsultantCommented:
Use "select * from Subtest" in a statement and Use ResultSetMetaData to get the value for a specific column.
Very strange column names - asking for trouble. You might try

ResultSet rs = stmt.executeQuery("select stestid,mtestid,stestname,"\"" + docid + "\"" from Subtest");

Open in new window

Gurvinder Pal SinghCommented:
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

With those numeric column "names", you need to enclose the field name (a number) in brackets, otherwise wou will simply return the number from the query.

Anytime you have unusual column names (numbers, special characters, spaces, etc), you need to use brackets to define it as a column name:

Try this:

ResultSet rs = stmt.executeQuery("select stestid,mtestid,stestname,["+docid+"] from Subtest");
AuriiAuthor Commented:
None of the solutions are working
Gurvinder Pal SinghCommented:
have you tried prepared statement?
What are the exact issues you are having with each of the suggestions?
Luke ChungPresidentCommented:
Sounds like a data normalization problem for data stored in a spreadsheet format.

Can the data be transformed so they reside in one column rather than all those columns?  A select query on one field would be very fast and scalable.

perhaps you can try to select it this way:

select Subtest.stestid, Subtest.mtestid,Subtest.stestname, Subtest.1, Substest.2,... from Subtest

and also you can try with aliases for the fields

select Subtest.stestid, Subtest.mtestid,Subtest.stestname, Subtest.1 as column_one, Substest.2 as column_two,... from Subtest

Agree with the suggestion to use a PreparedStatement, handles escaping and protects you from sql injection. But in your case you unfortunately can't.

what was the result of mbizup suggestion?
That's unnecessary and will simply result in your db doing more work than necessary
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.