How do I return only true columns from access database

Question

0.00/5 (No votes)

See more:

Hi All

I am using vb.net connecting to an access DB

I have a table "WhatToSelect"

It has 1 row with fields : LearnerName,LearnerSurname,LearnerDOB etc
All fields are Yes/No

It only has 1 row

How do I return only the fields where it is set to YES

The idea is the client determines which fields to be viewed, printed.

I then would like to write a statement using the Yes fields only

ie Select LearnerName, LearnerDOB from WhatToSelect

Is this possible?

Your help would be highly appreciated

Regards
Johan

What I have tried:

I have unfortunately limited knowledge of this.

I do know how to add a WHERE clause to return if a single field is true but in this case i need all the fields where true and i am not sure how to archive this

Thanks

Posted 16-Mar-16 4:25am

Member 11259478

Updated 16-Mar-16 6:37am

Add a Solution

2 solutions

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)

CHill60 · Answer 1 · 2016-03-16T04:39:00

This is an awful design. Are you able to change it?
If so then consider having the table 'WhatToSelect' having 2 columns and many rows.

FieldName      Select
LearnerName    Yes
LearnerDOB     No ... etc.

If you are not able to change it then you will have to load the entire row and examine the columns in your VB code one by one

[EDIT]
To demonstrate what I mean, this is what you have to go through if you leave your table design as it is

VB

Dim results As StringBuilder = New StringBuilder("SELECT ")
Dim fieldFound As Boolean = False
Using myConn = New SqlConnection(ConnectionString)
    Using myCmd = myConn.CreateCommand()
        myCmd.CommandText = "SELECT * FROM WhatToSelect"
        myConn.Open()
        Using myReader = myCmd.ExecuteReader()
            If myReader.Read() Then
                For i As Integer = 0 To myReader.FieldCount - 1
                    If myReader.GetBoolean(i) Then
                        results.Append(myReader.GetName(i))
                        results.Append(",")
                        fieldFound = True
                    End If
                Next
            End If
        End Using

        If fieldFound Then
            results.Remove(results.Length - 1, 1) 'remove final comma
            results.Append(" FROM myTable")     'and any WHERE clause you want
        End If
    End Using
    myConn.Close()
End Using
'do whatever you want with the results
MessageBox.Show(results.ToString())

But if you change the table design to something like this

SQL

create table Better
(
	FieldName varchar(125),
	[Select] bit
)
insert into Better values
('LearnerName', 1),
('LearnerSurname',0),
('LearnerDOB',1)

You can use

VB

Dim results1 As StringBuilder = New StringBuilder("SELECT ")
Using myConn = New SqlConnection(ConnectionString)
    Using myCmd = myConn.CreateCommand()
        myCmd.CommandText = "SELECT * FROM Better WHERE [Select] = 1"
        myConn.Open()
        Using myReader = myCmd.ExecuteReader()
            Do While myReader.Read()
                results1.Append(myReader.GetString(0))
                results1.Append(",")
            Loop
        End Using
    End Using
End Using
If results1.Length <> "SELECT ".Length Then
    results1.Remove(results1.Length - 1, 1) 'remove final comma
    results1.Append(" FROM myTable")     'and any WHERE clause you want
End If
'do whatever you want with the results
MessageBox.Show(results1.ToString())

Both versions produce the same results

"SELECT LearnerName,LearnerDOB FROM myTable"

Sascha Lefèvre · Answer 2 · 2016-03-16T04:40:00

If I understand correctly, you want to dynamically build a SELECT-query in your VB.NET code to select those columns from a table XY depending on which columns in the single row of the table WhatToSelect are set to true.

You're making it more complicated than it needs to be: Just do a

SQL

SELECT * FROM WhatToSelect

and then loop over the columns of the result row and if the value is true then include that column into your SELECT-query for table XY.

Other than that: What CHill60 wrote, it's way better. That way you don't have to change the schema of your WhatToSelect table when you change the schema of your XY table. Another better way would be to store that information in form of an XML-serialized configuration class object (e.g. a class with a List(Of String) WhatToSelect) in a single cell of a configuration table.

How do I return only true columns from access database

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0