Avatar of skdan
skdan
Flag for Slovakia asked on

VB 2010 LINQ to Untyped Dataset (3)

Hi experts,

My situation:
- Visual Studio Professional 2010
- .NET Framework 4

I try to use code from Microsoft site:
http://msdn.microsoft.com/en-us/library/bb386969(v=vs.100).aspx

My working code:

Option Explicit On

Imports System.Data.OleDb

Imports System
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization


Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim conn1 As OleDbConnection

        Dim ds As DataSet = New DataSet
        Dim sqlcmd As OleDbCommand
        Dim da As OleDbDataAdapter = New OleDbDataAdapter

        Try
            ds.Clear()

            conn1 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=f:\Demo\Access\myDB1.accdb")
            conn1.Open()


            sqlcmd = New OleDbCommand("SELECT * FROM SalesOrderHeader ", conn1)

            da.SelectCommand = sqlcmd
            da.Fill(ds, "SalesOrderHeader")
            conn1.Close()


            sqlcmd = New OleDbCommand("SELECT * FROM SalesOrderDetail", conn1)

            da.SelectCommand = sqlcmd
            da.Fill(ds, "SalesOrderDetail")
            conn1.Close()


            'Start
            Dim orders As DataTable = ds.Tables("SalesOrderHeader")
            Dim details As DataTable = ds.Tables("SalesOrderDetail")

            Dim query = _
                From order In orders.AsEnumerable() _
                Join detail In details.AsEnumerable() _
                On order.Field(Of Object)("SalesOrderID") Equals _
                        detail.Field(Of Object)("SalesOrderID") _
                Where order.Field(Of Boolean)("OnlineOrderFlag") = True And _
                        order.Field(Of DateTime)("OrderDate").Month = 8 _
                Select order

            Dim ord

            For Each ord In query
                Console.WriteLine(ord("SalesOrderID") & vbTab & _
                    ord("OrderDate") & vbTab & _
                    ord("OnlineOrderFlag"))
            Next
            'End

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub
End Class

Open in new window


I would like to have in "Select" command also all columns from table "details", some thing like:

Select order, detail

Open in new window


but I get error.

Thanks
Visual Basic.NET

Avatar of undefined
Last Comment
Ioannis Paraskevopoulos

8/22/2022 - Mon
Ioannis Paraskevopoulos

Do you need a left outer join?

Dim query = From order In orders.AsEnumerable() _
            Group Join detail In details.AsEnumerable() _
            On order.Field(Of Object)("SalesOrderID") Equals _
            detail.Field(Of Object)("SalesOrderID") _
			Into temp = Group _
			Where order.Field(Of Boolean)("OnlineOrderFlag") = True And _
					order.Field(Of DateTime)("OrderDate").Month = 8 _
            From b In temp.DefaultIfEmpty(DefaultTableB) _
            Select temp

Open in new window


Giannis
skdan

ASKER
What is DefaultTableB in your query? Compiler underlined it. Thanks
Ioannis Paraskevopoulos

Remove that line please, I have some templates for linq joins that i use and left this :)

Giannis
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
skdan

ASKER
When I use this code

            Dim query = From order In orders.AsEnumerable() _
                        Group Join detail In details.AsEnumerable() _
                        On order.Field(Of Object)("SalesOrderID") Equals _
                        detail.Field(Of Object)("SalesOrderID") _
                        Into temp = Group _
                        Where order.Field(Of Boolean)("OnlineOrderFlag") = True And _
                        order.Field(Of DateTime)("OrderDate").Month = 8 _
                        Select temp

Open in new window


I get this error:

LINQ_error
Ioannis Paraskevopoulos

When exactly is that error happening? I have tested the code and it works.
Please run the code step by step and see exactly what line raises it.

Thanks,
Giannis
skdan

ASKER
Please try to create new Windows Form application (.NET 4), one Form and one Button. Please do not add file myDB1.accdb to your solution. Use only connection string in my code. You can change path to file, of course. May be this different between my application and yours.

My code with your query:

Option Explicit On

Imports System.Data.OleDb

Imports System
Imports System.Linq
Imports System.Linq.Expressions
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Globalization


Public Class Form1

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim conn1 As OleDbConnection

        Dim ds As DataSet = New DataSet
        Dim sqlcmd As OleDbCommand
        Dim da As OleDbDataAdapter = New OleDbDataAdapter

        Try
            ds.Clear()

            conn1 = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=f:\Demo\Access\myDB1.accdb")
            conn1.Open()


            sqlcmd = New OleDbCommand("SELECT * FROM SalesOrderHeader ", conn1)

            da.SelectCommand = sqlcmd
            da.Fill(ds, "SalesOrderHeader")
            conn1.Close()


            sqlcmd = New OleDbCommand("SELECT * FROM SalesOrderDetail", conn1)

            da.SelectCommand = sqlcmd
            da.Fill(ds, "SalesOrderDetail")
            conn1.Close()


            'Start
            Dim orders As DataTable = ds.Tables("SalesOrderHeader")
            Dim details As DataTable = ds.Tables("SalesOrderDetail")

            Dim query = From order In orders.AsEnumerable() _
                        Group Join detail In details.AsEnumerable() _
                        On order.Field(Of Object)("SalesOrderID") Equals _
                        detail.Field(Of Object)("SalesOrderID") _
                        Into temp = Group _
                        Where order.Field(Of Boolean)("OnlineOrderFlag") = True And _
                        order.Field(Of DateTime)("OrderDate").Month = 8 _
                        Select temp

            Dim ord

            For Each ord In query
                Console.WriteLine(ord("SalesOrderID") & vbTab & _
                    ord("OrderDate") & vbTab & _
                    ord("OnlineOrderFlag"))
            Next
            'End

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try
    End Sub
End Class

Open in new window


Let me know, please, if it works for you. I have error on lines:

               Console.WriteLine(ord("SalesOrderID") & vbTab & _
                    ord("OrderDate") & vbTab & _
                    ord("OnlineOrderFlag"))

Open in new window

myDB1.accdb
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Ioannis Paraskevopoulos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
skdan

ASKER
Excellent. This code really works :-)
Thank you very much for help :-)
Ioannis Paraskevopoulos

Glad to help!