Link to home
Start Free TrialLog in
Avatar of skdan
skdanFlag 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
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

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
Avatar of skdan

ASKER

What is DefaultTableB in your query? Compiler underlined it. Thanks
Remove that line please, I have some templates for linq joins that i use and left this :)

Giannis
Avatar of 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:

User generated image
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
Avatar of 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
ASKER CERTIFIED SOLUTION
Avatar of Ioannis Paraskevopoulos
Ioannis Paraskevopoulos
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of skdan

ASKER

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