1,105,288 Community Members

Select from multiple tables for VB.net program

Member Avatar
404notfound
Newbie Poster
22 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Using Access, in one table, I have Social Security numbers and earnings/tax records, and in the other table I have Social's and emp id's.
I'm trying to get an output to a tablegridview, and using the following query line.

SELECT AcctNum,SUI,SDI,FICA,Medicare,State,Fed,TotWages FROM tblEarnings INNER JOIN tblPersonel On tblEarnings.SS=tblPersonel.SS

I've been informed (by VB.net) that I'm doing something wrong. Any help would be awesome.

Member Avatar
JorgeM
IT Addict
6,378 posts since Dec 2011
Reputation Points: 567 [?]
Q&As Helped to Solve: 952 [?]
Skill Endorsements: 172 [?]
Moderator
Featured
Sponsor
 
0
 

I've been informed (by VB.net) that I'm doing something wrong.

What's the error message you are getting? You've only provided the SQL query, but that's not enough to know what the problem is.

Member Avatar
404notfound
Newbie Poster
22 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

There is no error. When I load the form, my datagridview is empty. If I change the query to something simple like

SELECT * FROM tblEarnings

then I have a nicely populated datagridview. So, I assume something in my first query is wrong.
Unfortunately I can't just do a SELECT * FROM tblEarnings INNER JOIN tblPersonel On tblEarnings.SS=tblPersonel.SS because each table has a lot of garabage I can't have.

Here is my code.

Public Class Form1


    Dim dbProvider As String = "PROVIDER=microsoft.ace.oledb.12.0;"
    Dim dbSource As String = "Data Source = C:\D&R\Data\DR.accdb"
    Dim conn As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    Dim sql As String
    Dim query As String = "SELECT AcctNum,SUI,SDI,FICA,Medicare,State,Fed,TotWages FROM tblEarnings INNER JOIN tblPersonel On tblEarnings.SS=tblPersonel.SS"
    Dim command As New OleDb.OleDbCommand(query, conn)


    Private Sub connect()
        conn.ConnectionString = dbProvider & dbSource
        conn.Open()
        MsgBox(conn.State.ToString)
    End Sub



    Private Sub dgPopulate()
        connect()
        da.SelectCommand = command
        da.Fill(ds)
        Dim dt As New DataTable
        da.Fill(dt)

        Me.dgEmployees.DataSource = dt
        conn.Close()
    End Sub    

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        dgPopulate()
    End Sub
    End Class
Member Avatar
ricky.subiantoputra
Newbie Poster
12 posts since Jan 2013
Reputation Points: 11 [?]
Q&As Helped to Solve: 3 [?]
Skill Endorsements: 0 [?]
 
0
 

try adding error handling in your code

Private Sub dgPopulate()
    connect()
    try
        da.SelectCommand = command
        da.Fill(ds)
        Dim dt As New DataTable
        da.Fill(dt)
        Me.dgEmployees.DataSource = dt
    catch ex as exception
        msgbox ex.message
    end try
    conn.Close()
End Sub 
Member Avatar
JorgeM
IT Addict
6,378 posts since Dec 2011
Reputation Points: 567 [?]
Q&As Helped to Solve: 952 [?]
Skill Endorsements: 172 [?]
Moderator
Featured
Sponsor
 
0
 

Ok, i have a better understanding of your problem now. The good thing is that if when you tried a general SQL statement, your grid populates, that means that your VB code is good.

When you are in Access, have you tried to run this SQL query to see if it results in any records?

SELECT AcctNum,SUI,SDI,FICA,Medicare,State,Fed,TotWages FROM tblEarnings INNER JOIN tblPersonel On tblEarnings.SS=tblPersonel.SS

Maybe you have a typo on one of the table columns or with the table called 'tblPeronel'?

Member Avatar
404notfound
Newbie Poster
22 posts since Nov 2012
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

you Jorge sir, are a genius. I will now go beat my head with a frying pan.
My program now works great.

Member Avatar
JorgeM
IT Addict
6,378 posts since Dec 2011
Reputation Points: 567 [?]
Q&As Helped to Solve: 952 [?]
Skill Endorsements: 172 [?]
Moderator
Featured
Sponsor
 
0
 

you Jorge sir, are a genius

I appreciate that, but its far from the truth...I'm glad I was able to help you find your issue.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article