How to query an ACCESS database to a variable

Question

I am trying to query an access database to find in a row exists in the database and if the value of IsManager = True or not. I'm new to using databases so any help is good thanks yall :) <3

    Try
        Dim SQL As String
        Dim CMD As New OleDb.OleDbCommand
        Dim DT As New DataTable
        Dim D_A As New OleDb.OleDbDataAdapter
        MainMenu.Con.Open()

        SQL = ("SELECT * FROM Staff WHERE Login = " & ID)
        CMD.Connection = MainMenu.Con
        CMD.CommandText = SQL
        D_A.SelectCommand = CMD

        D_A.Fill(DT)
    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        MainMenu.Con.Close()
    End Try

@Nathan_Sav i haven't yet because i have no idea how. I thought of trying to do a 'For ID in X' but no clue how if that makes sense? — user17626353
– user17626353, Commented Jan 13, 2022 at 14:51
Does this answer your question? How to retrieve specific data from Access database VB.net? — June7
– June7, Commented Jan 13, 2022 at 20:10

Marco · Accepted Answer · 2022-01-17 10:43:31Z

1

Don't fill a DataTable and then retrieve a field inside it. Insert in a variable the value you want to get.

Dim IsManager as Boolean
Try
    Dim SQL As String
    Dim CMD As New OleDb.OleDbCommand
    Dim D_A As New OleDb.OleDbDataAdapter    

    SQL = ("SELECT IsManager FROM Staff WHERE Login = " & ID)
    CMD.Connection = MainMenu.Con
    CMD.CommandText = SQL
    D_A.SelectCommand = CMD

    MainMenu.Con.Open()

    Dim Dr as OleDbDataReader = CMD.ExecuteReader

    Dr.Read()
    IsManager = Dr.Item("IsManager")
    Dr.Close()

Catch ex As Exception
    MsgBox(ex.Message)
Finally
    MainMenu.Con.Close()
End Try

edited Jan 17, 2022 at 10:43

answered Jan 14, 2022 at 7:41

Marco

1261 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user17626353 Over a year ago

Thank you very much. I get a red line under 'SQLdatareader' however?

Mary Over a year ago

Connections need to be disposed as well as closed. Try Using blocks for connections, commands and data readers.

Marco Over a year ago

@Ben711Gaming Use OleDbDataReader instead of SqlDataReader (my bad).

Nick.Mc Over a year ago

Further improvement might be made by actually including the IsManager filter in the query and just checking for existence. It doesn't matter much on MS Access but it's a good habit to get into for larger databases.

Collectives™ on Stack Overflow

How to query an ACCESS database to a variable

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related