如何仅从Access数据库返回true列 [英] How do I return only true columns from access database

问题描述

大家好



我使用vb.net连接到访问DB



我有表格WhatToSelect



它有1行字段：LearnerName，LearnerSurname，LearnerDOB等

所有字段均为是/否



它只有1行



如何只返回设置为YES的字段



想法是客户确定要查看哪些字段，打印。



然后我想写只使用Yes字段的声明



即从WhatToSelect中选择LearnerName，LearnerDOB



这可能吗？



我们将非常感谢您的帮助



问候

Johan



我的尝试：



遗憾的是，我对此知之甚少。



如果单个字段为true，我知道如何添加WHERE子句返回在这种情况下，我需要所有字段都是真的，我不知道如何存档这个



谢谢

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

推荐答案

这是一个糟糕的设计。你能改变它吗？

如果是这样的话，那么考虑让'WhatToSelect'表有2列和多行。

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.

如果您无法更改它，那么您将必须加载整行并逐一检查VB代码中的列





为了证明我的意思，如果你离开你的桌子设计，这就是你需要经历的事情

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


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

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

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

您可以使用

You can use

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"

If我理解正确，你想在哟中动态构建一个SELECT查询你的VB.NET代码从表 XY 中选择那些列，具体取决于表格单行中的哪些列 WhatToSelect 设置为 true 。



你使它变得比它需要的更复杂：只是做a

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

SELECT * FROM WhatToSelect

然后循环遍历结果行的列，如果值为 true ，则将该列包含在表 XY 。



除此之外：CHill60写的是什么，它的方式更好。这样，当您更改 XY 表的架构时，您不必更改 WhatToSelect 表的架构。另一种更好的方法是以XML序列化配置类对象的形式存储该信息（例如，具有 List（Of String）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.

这篇关于如何仅从Access数据库返回true列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！