Help with SQL string using a union join into a dataset

[Mike Fellows]

Help,

im trying to use a union join from an SQL database and a Access database -
the program works fine from my own machine as i have the odbc DSN
connections setup
from access to link to the SQL tables.

i need to set this up in code to connect to both databases and do my union
join but i have no idea how to

below is my current code (using the DSN connection):

Dim strConnect As String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=\\Gringo tts\Common Documents\Maste r Database\OwnAnd Save.mdb;" &
_
"Persist Security Info=False"
Dim RowCount As Integer = 0

Dim ocon1 As OleDbConnection = New OleDbConnection (strConnect)
ocon1.Open()
Dim ds1 As DataSet = New DataSet()
Dim da1 As OleDbDataAdapte r = New OleDbDataAdapte r()
da1.SelectComma nd = New OleDbCommand("S ELECT Personal.ID, Personal.Surnam e1,
Personal.Postco de, Personal.Town, LiveTracker.Sal esAppointment,
LiveTracker.Sal esManager FROM (Personal LEFT JOIN LiveTracker ON Personal.ID
= LiveTracker.ID) LEFT JOIN Closed ON Personal.ID = Closed.ID WHERE
(((LiveTracker. SalesAppointmen t) Between Date() And Date()+28) AND
((LiveTracker.P ostalPackReturn ed)=#1/1/1900#) AND ((Closed.ID) Is Null))
UNION SELECT dbo_Personal.ID , dbo_Personal.su rname1, dbo_Personal.po stcode1,
dbo_Personal.to wn1, dbo_LiveTracker .SalesAppointme nt,
dbo_LiveTracker .SalesManager FROM (dbo_Personal INNER JOIN dbo_LiveTracker
ON dbo_Personal.ID = dbo_LiveTracker .ID) LEFT JOIN dbo_Closed ON
dbo_Personal.ID = dbo_Closed.ID WHERE
(((dbo_LiveTrac ker.SalesAppoin tment)>Date()) AND
((dbo_LiveTrack er.PostalPackRe turned)=#1/1/1900#) AND ((dbo_Closed.ID ) Is
Null));", ocon1)
da1.Fill(ds1)

any help would be greatly appreciated

Thanks

Mike Fellows

[Cor Ligthert]

Re: Help with SQL string using a union join into a dataset

Hi Mike,

I think a Join question is typical a question for Miha, the two Bills, Kevin
or others who are often active in the newsgroup

Adonet
<news://msnews.microsof t.com/microsoft.publi c.dotnet.framew ork.adonet>

Web interface:

<http://communities2.microsoft.com/co...s/?dg=microsof
t.public.dotnet .framework.adon et>

I thought that there is a bug with the Join in OleDb with an access
database.

However try it in the adonet group, although a lot of us know the answer I
think, I think there you have a better change.

Cor

[Ken Tucker [MVP]]

Re: Help with SQL string using a union join into a dataset

Hi,

Here is a similar stored procedure I used. Maybe this will help

ALTER Procedure ByCategory
@Start DateTime,
@Finish DateTime
AS Select PriceCategory.C ategoryName,
SUM(InvoiceData .Each) as TotalPrice
From ((InvoiceData Inner Join Invoices ON Invoices.Invoic eID =
InvoiceData.Inv oiceID)
Inner Join PriceList ON PriceList.Descr iption LIKE InvoiceData.Des cription)
Inner Join PriceCategory ON PriceList.Categ ory = PriceCategory.C ategoryID
Where Invoices.BillDa te > @Start AND Invoices.BillDa te < @Finish
Group by PriceCategory.C ategoryName

Ken
----------------------------
"Mike Fellows" <mike.fellows.S PAM@equityhouse .co.uk> wrote in message
news:EKjrc.35$X r.8@newsfe2-gui.server.ntli .net...[color=blue]
> Help,
>
> im trying to use a union join from an SQL database and a Access database -
> the program works fine from my own machine as i have the odbc DSN
> connections setup
> from access to link to the SQL tables.
>
> i need to set this up in code to connect to both databases and do my union
> join but i have no idea how to
>
> below is my current code (using the DSN connection):
>
> Dim strConnect As String = _
> "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> "Data Source=\\Gringo tts\Common Documents\Maste r Database\OwnAnd Save.mdb;"
> &
> _
> "Persist Security Info=False"
> Dim RowCount As Integer = 0
>
> Dim ocon1 As OleDbConnection = New OleDbConnection (strConnect)
> ocon1.Open()
> Dim ds1 As DataSet = New DataSet()
> Dim da1 As OleDbDataAdapte r = New OleDbDataAdapte r()
> da1.SelectComma nd = New OleDbCommand("S ELECT Personal.ID,
> Personal.Surnam e1,
> Personal.Postco de, Personal.Town, LiveTracker.Sal esAppointment,
> LiveTracker.Sal esManager FROM (Personal LEFT JOIN LiveTracker ON
> Personal.ID
> = LiveTracker.ID) LEFT JOIN Closed ON Personal.ID = Closed.ID WHERE
> (((LiveTracker. SalesAppointmen t) Between Date() And Date()+28) AND
> ((LiveTracker.P ostalPackReturn ed)=#1/1/1900#) AND ((Closed.ID) Is Null))
> UNION SELECT dbo_Personal.ID , dbo_Personal.su rname1,
> dbo_Personal.po stcode1,
> dbo_Personal.to wn1, dbo_LiveTracker .SalesAppointme nt,
> dbo_LiveTracker .SalesManager FROM (dbo_Personal INNER JOIN dbo_LiveTracker
> ON dbo_Personal.ID = dbo_LiveTracker .ID) LEFT JOIN dbo_Closed ON
> dbo_Personal.ID = dbo_Closed.ID WHERE
> (((dbo_LiveTrac ker.SalesAppoin tment)>Date()) AND
> ((dbo_LiveTrack er.PostalPackRe turned)=#1/1/1900#) AND ((dbo_Closed.ID ) Is
> Null));", ocon1)
> da1.Fill(ds1)
>
> any help would be greatly appreciated
>
> Thanks
>
> Mike Fellows
>
>[/color]

[Mike Fellows]

Re: Help with SQL string using a union join into a dataset

the SQL Side is not a problem i can easily create the stored procedure

i can also get the data from access easily

its gettign it from both at the same time which i am having problems with
( i need them in a single sorted dataset)

Mike


"Ken Tucker [MVP]" <vb2ae@bellsout h.net> wrote in message
news:OeCeeJyPEH A.3012@tk2msftn gp13.phx.gbl...[color=blue]
> Hi,
>
> Here is a similar stored procedure I used. Maybe this will help
>
> ALTER Procedure ByCategory
> @Start DateTime,
> @Finish DateTime
> AS Select PriceCategory.C ategoryName,
> SUM(InvoiceData .Each) as TotalPrice
> From ((InvoiceData Inner Join Invoices ON Invoices.Invoic eID =
> InvoiceData.Inv oiceID)
> Inner Join PriceList ON PriceList.Descr iption LIKE[/color]
InvoiceData.Des cription)[color=blue]
> Inner Join PriceCategory ON PriceList.Categ ory = PriceCategory.C ategoryID
> Where Invoices.BillDa te > @Start AND Invoices.BillDa te < @Finish
> Group by PriceCategory.C ategoryName
>
> Ken
> ----------------------------
> "Mike Fellows" <mike.fellows.S PAM@equityhouse .co.uk> wrote in message
> news:EKjrc.35$X r.8@newsfe2-gui.server.ntli .net...[color=green]
> > Help,
> >
> > im trying to use a union join from an SQL database and a Access[/color][/color]
database -[color=blue][color=green]
> > the program works fine from my own machine as i have the odbc DSN
> > connections setup
> > from access to link to the SQL tables.
> >
> > i need to set this up in code to connect to both databases and do my[/color][/color]
union[color=blue][color=green]
> > join but i have no idea how to
> >
> > below is my current code (using the DSN connection):
> >
> > Dim strConnect As String = _
> > "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
> > "Data Source=\\Gringo tts\Common Documents\Maste r[/color][/color]
Database\OwnAnd Save.mdb;"[color=blue][color=green]
> > &
> > _
> > "Persist Security Info=False"
> > Dim RowCount As Integer = 0
> >
> > Dim ocon1 As OleDbConnection = New OleDbConnection (strConnect)
> > ocon1.Open()
> > Dim ds1 As DataSet = New DataSet()
> > Dim da1 As OleDbDataAdapte r = New OleDbDataAdapte r()
> > da1.SelectComma nd = New OleDbCommand("S ELECT Personal.ID,
> > Personal.Surnam e1,
> > Personal.Postco de, Personal.Town, LiveTracker.Sal esAppointment,
> > LiveTracker.Sal esManager FROM (Personal LEFT JOIN LiveTracker ON
> > Personal.ID
> > = LiveTracker.ID) LEFT JOIN Closed ON Personal.ID = Closed.ID WHERE
> > (((LiveTracker. SalesAppointmen t) Between Date() And Date()+28) AND
> > ((LiveTracker.P ostalPackReturn ed)=#1/1/1900#) AND ((Closed.ID) Is Null))
> > UNION SELECT dbo_Personal.ID , dbo_Personal.su rname1,
> > dbo_Personal.po stcode1,
> > dbo_Personal.to wn1, dbo_LiveTracker .SalesAppointme nt,
> > dbo_LiveTracker .SalesManager FROM (dbo_Personal INNER JOIN[/color][/color]
dbo_LiveTracker[color=blue][color=green]
> > ON dbo_Personal.ID = dbo_LiveTracker .ID) LEFT JOIN dbo_Closed ON
> > dbo_Personal.ID = dbo_Closed.ID WHERE
> > (((dbo_LiveTrac ker.SalesAppoin tment)>Date()) AND
> > ((dbo_LiveTrack er.PostalPackRe turned)=#1/1/1900#) AND ((dbo_Closed.ID )[/color][/color]
Is[color=blue][color=green]
> > Null));", ocon1)
> > da1.Fill(ds1)
> >
> > any help would be greatly appreciated
> >
> > Thanks
> >
> > Mike Fellows
> >
> >[/color]
>
>[/color]