very basic question about getting data from database

[UndeaDS]

Hi everyone,
I have a very basic question about how to get data from a database to my code.

The idea is this:
There is a listbox on my form where a user picks something.
I have an acces mdb where the items from the listbox are listed alphabetically, and in another column there is a formula.
A user can select one item from the listbox, and I want vbcode to look for the corresponding formula, and load it into a variable without showing it in the form.
So basicly I am looking for a vb version of the excel code lookup. (because it needs to look for a unique value, and than retun a value next to it)

I use visual studio, and I have already managed to load the mdb file into my project, and I even managed to load the right column into the listbox.
All that is missing is the code to look for the formula and load it into a variable.
I really hope someone can help me with this, I have looked around, but most information is about non local databases or OLE is used (which I don't need because my database is already in my project, atleast thats what I think).

Thanks for your help in advance.

Greetings,

 

[Hack]

Here is a basic example of how to connect to an Access database, and how to retrieve some information from it. You will need to tweak it to fit your needs - especially the SQL as you will want to add a WHERE clause to your query.

Imports System.Data.OleDb

Public Class Form1

    Private connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\fullpath\yourmdb.mdb;"
    Private conn As OleDb.OleDbConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDbConnection(connstring)
        conn.Open()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sSQL As String = String.Empty
        sSQL = "SELECT field1 FROM atable "
        Dim command As New OleDbCommand(sSQL, conn)
        Dim reader As OleDbDataReader = command.ExecuteReader()
        ListBox1.Items.Clear()
       If reader.HasRows = True Then 'make sure there is data returned
             While reader.Read()
                      ListBox1.Items.Add(reader.GetString(0))
              End While
       Else
              MessageBox.Show("No data matched your search criteria.")
       End If
        reader.Close()
    End Sub
End Class

 

[UndeaDS]

Thanks for your help!!
But I am not there yet, I am testing my code with another aplication first and I get an error. I believe my WHERE is incorrect, because when I exclude the WHERE statement I get the entire column. So almost everything is working, exept for the WHERE.

This is my code:

Imports System.Data.OleDb

Public Class Form1
    Private connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VB6SBS\Own Project\vlinderdatabase.mdb;"
    Private conn As OleDb.OleDbConnection


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDbConnection(connstring)
        conn.Open()

    End Sub

    Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        'Dim lookfor As String
        'lookfor = ListBox1.Text

        Dim sSQL As String = String.Empty
[B]sSQL = "SELECT [Latijnse naam] FROM Vlindertabel02 WHERE [Nederlandse naam] = listbox1.text"[/B]

        Dim command As New OleDbCommand(sSQL, conn)
        Dim reader As OleDbDataReader

        reader = command.ExecuteReader()

        ListBox2.Items.Clear()
        If reader.HasRows = True Then 'make sure there is data returned
            While reader.Read()
                ListBox2.Items.Add(reader.GetString(0))
            End While
        Else
            MessageBox.Show("No data matched your search criteria.")
        End If
        reader.Close()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        End
    End Sub
End Class

 

[UndeaDS]

Alright, I got one step further, I changed that specific line to:

        Dim sSQL As String = String.Empty
        sSQL = "SELECT [Latijnse naam] FROM Vlindertabel02 WHERE '[Nederlandse naam]' = 'listbox1.text'"

Now I don't get an error, but I keep getting the error "No data matched your search criteria." But the data is enter properly and the data is available in the database. How do I solve this?

 

[UndeaDS]

I think I solved the problem, I changes that specific line to:

 sSQL = "SELECT [Latijnse naam] FROM Vlindertabel02 WHERE [Nederlandse naam] = '" & ListBox1.Text & "'"

 

[Hack]

So, is everything working now?