Select Query of Access database

Posted on 2011-03-22
Last Modified: 2012-05-11
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
Question by:Aurii
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +5
LVL 86

Expert Comment

ID: 35188821
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

LVL 40

Expert Comment

ID: 35188829
LVL 61

Expert Comment

ID: 35188877
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");
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


Author Comment

ID: 35189894
None of the solutions are working
LVL 40

Expert Comment

ID: 35189943
have you tried prepared statement?
LVL 61

Expert Comment

ID: 35190166
What are the exact issues you are having with each of the suggestions?
LVL 10

Expert Comment

by:Luke Chung
ID: 35191185
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.
LVL 47

Expert Comment

ID: 35194338

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

LVL 92

Expert Comment

ID: 35194950
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?
LVL 21

Accepted Solution

Amitkumar Panchal earned 250 total points
ID: 35196214
Use "select * from Subtest" in a statement and Use ResultSetMetaData to get the value for a specific column.
LVL 86

Expert Comment

ID: 35196353
That's unnecessary and will simply result in your db doing more work than necessary

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question