Access DB Query in Visual Basic Form

Question

I'm seeking help on how to return more than one row in a query. For example if I want the entire contents of a column in access, I want to return all rows to a ListBox.

Imports System.Data.OleDb

Public Class Form1

    Dim theConnectionString As New OleDbConnection

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        txtSQL.Clear()
        theResults.Items.Clear()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        theConnectionString.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Marc Wilson\Documents\FiddleFuckDB.accdb"
        theConnectionString.Open()
        Dim theDataSet As New DataSet
        Dim theDataTable As New DataTable
        theDataSet.Tables.Add(theDataTable)
        Dim theDataAdapter As New OleDbDataAdapter

        Dim theSQLStatement As String
        Dim theDBCommand As New OleDbCommand
        theSQLStatement = txtSQL.Text


        Try
            theDataAdapter = New OleDbDataAdapter("SELECT [City] FROM PI", theConnectionString)
            theDataAdapter.Fill(theDataTable)
            Dim theRowCount As Integer = theDataTable.Rows.Count
            Dim theItemCount As Integer = theDataTable.Columns.Count
            MessageBox.Show("Row Count: " & theRowCount & vbNewLine & "Item Count: " & theItemCount, "Your Results...")
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        Try
            theDBCommand = New OleDbCommand(theSQLStatement, theConnectionString)
            theResults.Items.Add(theDataTable.Rows(0).Item(0))
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
        theConnectionString.Close()
    End Sub
End Class

The SQL Statement I'm using is SELECT [City] FROM PI (I have 3 rows of data and 4 different columns) I believe my mistake resides in this portion theResults.Items.Add(theDataTable.Rows(0).Item(0))

mwilson · Accepted Answer · 2013-06-05 02:03:51Z

1

You haven't shown the sql statement from txtSQL.Text that you are attempting to use.

A simple SELECT statement will retrieve all rows from a table, unless a WHERE clause is used to restrict the number of rows returned.

edited Jun 5, 2013 at 2:03

mwilson

13.1k10 gold badges65 silver badges103 bronze badges

answered Jun 5, 2013 at 1:31

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

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

1 Comment

mwilson Over a year ago

I'm sorry. The SQL Statement I'm using is "SELECT [City] FROM PI" (I have 3 rows of data and 4 different columns)

matzone · Accepted Answer · 2013-06-05 02:23:03Z

0

Try like this ........

Dim x as Integer

Try

   theDataAdapter = New OleDbDataAdapter("SELECT [City] FROM PI", theConnectionString)
   theDataAdapter.Fill(theDataTable)

   For x = 0 to theDataTable.Rows.Count -1
      theResults.Items.Add(theDataTable.Rows(x).Item(0))
   Next

Catch ex As Exception

   MessageBox.Show(ex.ToString)

End Try

edited Jun 5, 2013 at 2:23

answered Jun 5, 2013 at 2:01

matzone

5,7293 gold badges19 silver badges20 bronze badges

2 Comments

mwilson Over a year ago

Tried it. It just returns Row 1 three times.

matzone Over a year ago

@mwilson : have you try this ? .. any comment ?

Collectives™ on Stack Overflow

Access DB Query in Visual Basic Form

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related