1.11M Members

Select from multiple tables for VB.net program

 
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.

 
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.

 
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
 
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 
 
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'?

 
0
 

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

 
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 six months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article