none
Getting column data from different tables in ms access using oledb RRS feed

  • Question

  • Hi!

    I am trying to get data for different columns in different tables but this code give me an error

    I tried using union select too but I don't know how to get the result devided with executereader 

    SELECT Pas_ID FROM ABR UNION SELECT Nr_dok FROM C ORDER BY Pas_ID;
    But I get all results mixed together

    What I want is get on listbox1 column1,column2 of table1 and on listbox2 column1,column2 of table2


    Dim cmdString As String = "SELECT Pas_ID FROM ABR;SELECT Nr_dok FROM C;"
            Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test.mdb;Jet OLEDB:Database Password=;Mode=Share Deny Read|Share Deny Write;"
            Dim myConnection As OleDbConnection = New OleDbConnection(connString)
            myConnection.Open()
            Dim TheCommand As OleDbCommand = New OleDbCommand(cmdString, myConnection) With {
                .CommandType = CommandType.Text
            }
            Dim TheDataReader As OleDbDataReader = TheCommand.ExecuteReader()
            While TheDataReader.Read()
                ListBox1.Items.Add(TheDataReader.GetString(0))
            End While
            If TheDataReader.NextResult() Then
                While TheDataReader.Read()
                    ListBox2.Items.Add(TheDataReader.GetString(0))
                End While
            End If

    Wednesday, July 3, 2019 3:27 PM

Answers

  • I found another way to get what I want

    With DataGridView1
                .RowsDefaultCellStyle.BackColor = Color.Bisque
                .AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
            End With
            rreshtiaktual2 = 0
            lidhje2.Open()
            adaptori2 = New OleDbDataAdapter("SELECT ABR.Pas_ID ,C.Nr_dok  " &
                                   "FROM ABR INNER JOIN C ON ABR.Pas_ID=C.Pas_ID " &
                                   "ORDER BY ABR.Pas_ID ASC,C.Pas_ID ASC", lidhje2)
            adaptori2.Fill(setitedhenave2, "test")
            DataGridView1.DataSource = setitedhenave2.Tables(0)
            'Merrtedhenat(rreshtiaktual2)
            lidhje2.Close()

    • Marked as answer by ai1231 Thursday, July 4, 2019 1:14 PM
    Wednesday, July 3, 2019 8:54 PM

All replies

  • Hello,

    Two questions

    • What is the exact error message
    • Have you tried creating this query inside of MS-Access

    https://access-excel.tips/union-and-union-all-access-query/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 3, 2019 3:44 PM
    Moderator
  • With this I don't get error but on datareader.read() I get all columns together

    SELECT Pas_ID FROM ABR UNION SELECT Nr_dok FROM C ORDER BY Pas_ID;

    "What I want is get on listbox1 column1,column2 of table1 and on listbox2 column1,column2 of table2"

    Wednesday, July 3, 2019 4:42 PM
  • With this I don't get error but on datareader.read() I get all columns together

    SELECT Pas_ID FROM ABR UNION SELECT Nr_dok FROM C ORDER BY Pas_ID;

    "What I want is get on listbox1 column1,column2 of table1 and on listbox2 column1,column2 of table2"

    So after taking another look a couple of things. First off you have two columns in your SELECT, not enough for what you are after as you need four columns.

    Next up, I have never tried using NextResult with MS-Access as NextResult is for batch operations. For instance in SQL-Server these queries work.

    SELECT CategoryID, CategoryName FROM dbo.Categories;SELECT EmployeeID, FirstName + ' ' + LastName As FullName FROM dbo.Employees

    Where the separator is a semi-colon. To see the following working example search for ReferenceItem in the page below.

    https://social.technet.microsoft.com/wiki/contents/articles/53065.vb-net-upgrading-from-ms-access-to-sql-server-part-23.aspx

    I think you need to do a JOIN rather than a UNION unless i'm missing something data wise.

     


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 3, 2019 5:10 PM
    Moderator
  • The question in theory is how to get from OleDbDataReader columns in different tables because what I get is mixed data and I can't devide

    Using join would be the same?

    I have two tables

    Table1 

    Columns on table 1->ID,Name,Surname

    Table2

    Columns on table 2->ID,Email,Age

    I want to get

    On listbox1-> Name,Surname,Email,Age where ID=ID sorted by ID

    Is OleDbDataReader able to get this?

    Wednesday, July 3, 2019 7:47 PM
  • Let's forgo union and join and keep it simple since this is ms-access and not sql-server. Why not simply load tables into class instances like this

    https://github.com/karenpayneoregon/WorkingWithAccessDatabases/blob/master/DatabaseTableDesignConsiderations/Classes/DatabaseOperations.vb#L81


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 3, 2019 8:14 PM
    Moderator
  • Hmm thank you but is to complicated for me

    I wanted a more simple solution based on my code

    Wednesday, July 3, 2019 8:40 PM
  • I found another way to get what I want

    With DataGridView1
                .RowsDefaultCellStyle.BackColor = Color.Bisque
                .AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
            End With
            rreshtiaktual2 = 0
            lidhje2.Open()
            adaptori2 = New OleDbDataAdapter("SELECT ABR.Pas_ID ,C.Nr_dok  " &
                                   "FROM ABR INNER JOIN C ON ABR.Pas_ID=C.Pas_ID " &
                                   "ORDER BY ABR.Pas_ID ASC,C.Pas_ID ASC", lidhje2)
            adaptori2.Fill(setitedhenave2, "test")
            DataGridView1.DataSource = setitedhenave2.Tables(0)
            'Merrtedhenat(rreshtiaktual2)
            lidhje2.Close()

    • Marked as answer by ai1231 Thursday, July 4, 2019 1:14 PM
    Wednesday, July 3, 2019 8:54 PM
  • Sounds good and it was a JOIN after all.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, July 3, 2019 10:37 PM
    Moderator
  • How I can join now 3 or more tables and get columns

    How to edit the query to make that happen

    Thursday, July 4, 2019 7:57 AM