• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • Last Modified:

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
mbizup
Access Zone Advisor
0
Aurii
Asked:
Aurii
  • 2
  • 2
  • 2
  • +5
1 Solution
 
CEHJCommented:
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

0
 
Gurvinder Pal SinghCommented:
0
 
mbizupCommented:
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");
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
AuriiAuthor Commented:
None of the solutions are working
0
 
Gurvinder Pal SinghCommented:
have you tried prepared statement?
0
 
mbizupCommented:
What are the exact issues you are having with each of the suggestions?
0
 
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.
0
 
for_yanCommented:

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

0
 
objectsCommented:
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?
0
 
Amitkumar PSr. ConsultantCommented:
Use "select * from Subtest" in a statement and Use ResultSetMetaData to get the value for a specific column.
0
 
CEHJCommented:
That's unnecessary and will simply result in your db doing more work than necessary
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now