-
Jun 17th, 2021, 08:44 AM
#1
Thread Starter
New Member
Left join access Table and Sql Table
Hello
I try to create a left join between an access table and sql table and display the result in dataview
Final result:
This is my code:
Code:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim connectionString As String = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=U:\JV23\JV236\JV236I.mdb"
Dim sql As String = "SELECT Nopiece,Qte_Bom FROM Pere_Fils"
Dim connection As New OleDbConnection(connectionString)
Dim dataadapter As New OleDbDataAdapter(sql, connection)
Dim ds As New DataSet()
dataadapter.Fill(ds, "Pere_Fils")
Dim Sql2 As String
Dim da2 As SqlDataAdapter
Connection_SQL("sa", "SAD", "MAN-APP1.nesting.ca\TDL", "tdl2012sql")
Sql2 = "SELECT Nopiece,Supplier FROM [SAD].[dbo].[Prog_Sigmanest] where [Job]='JV236I'"
da2 = New SqlDataAdapter(Sql2, cn)
da2.Fill(ds, "Prog_Sigmanest")
Deconection_SQL()
DataGridView1.DataSource = ??????
Thanks
Last edited by dday9; Jun 17th, 2021 at 08:53 AM.
-
Jun 17th, 2021, 08:57 AM
#2
Re: Left join access Table and Sql Table
Hi Jackb223, welcome to VBForums! Keep in mind that your posts will need to go through the moderation queue for the first couple of posts, so please don't double post.
I've also added code tags to your post. If you want more information on those, please visit the link in my signature.
Finally, could you please elaborate on your problem? You did good in provided the expected results along with the code you have, but it is still difficult to understand what it is that you're trying to do. I highly recommend visiting this stickied thread: https://www.vbforums.com/showthread....our-hovercraft
-
Jun 17th, 2021, 10:34 AM
#3
Re: Left join access Table and Sql Table
The datasource will be the datatable that you filled with the dataadapter. You don't really need a dataset, in this case, because it looks like you only have the one table. A dataset is just a collection of datatables, so what you have is essentially a collection with one item in it. There's nothing particularly wrong with that, it's just a bit more involved than you need.
So, your datasource could be just this:
DataGridView1.DataSource = ds.Tables("Prog_Sigmanest")
Or just:
DataGridView1.DataSource = ds(0)
My usual boring signature: Nothing
-
Jun 17th, 2021, 10:47 AM
#4
Thread Starter
New Member
Re: Left join access Table and Sql Table
Sorry I was not enough clear
So I have 1 access database "JV236I.mdb with a table Pere_Fils"
and I have another SQL database "tdl2012sql with a table Prog_Sigmanest"
and I want select all item from Pere_Fils left join with Prog_Sigmanest
If all the table are in the same database , access or sql , I could do select with left join but
with 2 different database , I don't know
I hope is more clear
-
Jun 17th, 2021, 11:06 AM
#5
Re: Left join access Table and Sql Table
In this case, a Dataset is ideal... fill one DataTable with the data from the one Access table, fill another Datatable from the SQL Server data... put both in the DataSet... then create a DataRelation (I think that's the right name) between the two that describes the relationship - this creates a left join via code between the two DataTables... with all that you should be able then to create a Dataview that pulls the data from the two DataTables based on the DataRelation.
DataRelations: https://duckduckgo.com/?q=vb.net+datarelation
DataViews: https://duckduckgo.com/?q=vb.net+dataview
-tg
-
Jun 21st, 2021, 06:35 AM
#6
Thread Starter
New Member
Re: Left join access Table and Sql Table
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|