Access Database SELECTION with VB.NET code

Question

I have a table with 2 field 'Medico' and 'Listino' so I want to select only the fields Listino where there's 'Medico' = Medhi

MEDICO        LISTINO
---------------------
Medhi         Gheller
Ashi          Cadon
Pamdo         Gheller


result:
Gheller

Because Medhi has the 'Listino' Gheller

Here's the code

    Public DBPath As String = "Listini.accdb"
    Public StringaConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & DBPath & ""
    Public DBConn As New OleDbConnection(StringaConn)

    Dim Medico As String = "Medhi"
    DBConn.Open()
    Dim DBCommAssociazione As OleDbCommand = DBConn.CreateCommand
    DBCommAssociazione.CommandType = CommandType.Text
    DBCommAssociazione.CommandText = "SELECT Listino FROM Associazioni WHERE Medico=Medhi"

    Dim reader As OleDbDataReader
    reader = DBCommAssociazione.ExecuteReader

    While reader.Read()
        Me.ListBox1.Items.Add(reader(0))
        Me.ListBox1.Items.Add(reader(1))

    End While

    DBConn.Close()

Thank you.

Medico is a text field. To search on this column for a constant value you need to put single quotes around the constant value 'Mehdi' — Steve
– Steve, Commented Jun 13, 2016 at 12:39

Abdellah OUMGHAR · Accepted Answer · 2016-06-13 12:48:39Z

1

You are missing cotes '' in query WHERE Medico='Medhi', but i advice you to use the Parameter to avoid SQL injections , like this :

Dim Medico As String = "Medhi"
DBConn.Open()
Dim DBCommAssociazione As OleDbCommand = DBConn.CreateCommand
DBCommAssociazione.CommandType = CommandType.Text
DBCommAssociazione.CommandText = "SELECT Listino FROM Associazioni WHERE Medico=@Medico"
DBCommAssociazione.Parameters.AddWithValue("@Medico", Medico)

Dim reader As OleDbDataReader
reader = DBCommAssociazione.ExecuteReader

While reader.Read()
    Me.ListBox1.Items.Add(reader(0))
End While

edited Jun 13, 2016 at 12:48

answered Jun 13, 2016 at 12:43

Abdellah OUMGHAR

3,7751 gold badge13 silver badges18 bronze badges

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

Comments

Steve · Accepted Answer · 2016-06-13 12:43:50Z

0

Medico is a text field. To search values on this columns you should put constant values between single quotes. But in a more realistic scenario, you search for a particular Medico following the input of your user.

So you should use a code like the following

Public DBPath As String = "Listini.accdb"
Public StringaConn As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source= " & DBPath & ""

Using DBConn = New OleDbConnection(StringaConn)
Using DBCommAssociazione = DBConn.CreateCommand
    DBConn.Open()
    Dim Medico = WhateverMethodYouHaveToReturnAMedicoFromUserInput()
    DBCommAssociazione.CommandText = "SELECT Listino FROM Associazioni WHERE Medico=@medico"

    DBCommAssociazione.Parameters.Add("@medico", OleDbType.VarWChar).Value = Medico
    Using reader = DBCommAssociazione.ExecuteReader
       While reader.Read()
          Me.ListBox1.Items.Add(reader(0))
        End While
    End Using
End Using

Notice also that the query returns just one field. In your While loop you try to read two fields (reader(0) and reader(1)) and this will give you an ArgumentOutOfRangeException. A DataReader process one record at time and the new record is loaded only when you call the Read method.

answered Jun 13, 2016 at 12:43

Steve

217k22 gold badges242 silver badges296 bronze badges

1 Comment

Nicolò Favaretto Over a year ago

Thank you much!!! Yes I forget the reader(1) because I take the cycle from another part of the code.

Collectives™ on Stack Overflow

Access Database SELECTION with VB.NET code

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related