I have two tables 1) tblRequests and 2.) tblMainTags. The Fields in tblRequests are Tag_Request_No, Employee_ID , Accepted, Accepted_Date, and Accepted_Time.
The Fields in tblMaintags are Tag_Request_No, Tag_ID, Completed, ... The fields 'Accepted' and 'Completed' are set to YES/NO.

I want to display in a datagridview the two tables joined together and filtered to show only the tblRequests 'Accepted' = YES and tblMainTags 'Completed' =NO. However when I run the Sub below, I don't get an error, but the datagridview only shows headings but no content. The database has plenty of rows in both table that meet the above criteria .

Let me know what I'm doing wrong. It's driving me NUTS!! - Thanks

Sub ReadPendCmplt() 'This sub joins two tables a
        Dim dsPendCmplt As New DataSet
        Try
            dsPendCmplt.Tables().Clear()
            connstr.Open()  'Open connection 
            sql = "SELECT * FROM tblRequests INNER JOIN tblMainTags ON " & _
            "tblRequests.Tag_Request_No = tblMainTags.Tag_Request_No " & _
            "WHERE Accepted = 1 AND Complete =0"
            da = New OleDb.OleDbDataAdapter(sql, connstr)
            da.Fill(dsPendCmplt, "PendCmplt")
            'Populate DatagridView called dtGdPendCmplt
            dtGdPendCmplt.DataSource = dsPendCmplt.Tables("PendCmplt")
            'Count and display number of records in datagridview
            txtPendCmplt.Text = dtGdPendCmplt.RowCount.ToString
            dsPendCmplt.Dispose()
            da.Dispose()
            connstr.Close()
        Catch ex As Exception
            msg = MsgBox("The program has encountered an error. " & _
                         "Do you want to see the full details?", MsgBoxStyle.YesNo, "Error")
            If msg = MsgBoxResult.Yes Then
                ErrorMsg = ex.ToString
                NoticeForm.ShowDialog()
            End If
            connstr.Close()
        End Try
    End Sub

Edited 6 Years Ago by __avd: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

SELECT tblRequests.*, tblMaintags.*
FROM tblMaintags INNER JOIN tblRequests ON tblMaintags.Tag_Request_No=tblRequests.Tag_request_no
WHERE tblRequests.Accepted=true and tblMaintags.Completed=false;
SELECT tblRequests.*, tblMaintags.*
FROM tblMaintags INNER JOIN tblRequests ON tblMaintags.Tag_Request_No=tblRequests.Tag_request_no
WHERE tblRequests.Accepted=true and tblMaintags.Completed=false;

Still didn't work. When i type the statement like you suggested, I'm getting this exception, "System.Data.OleDb.OleDbException: Characters found after end of SQL statement." at this line

da.Fill(dsPendCmplt, "PendCmplt")

I typed the sql statement as.

sql = "SELECT tblRequests.*, tblMaintags.* FROM tblMaintags INNER JOIN tblRequests ON tblMaintags.Tag_Request_No=tblRequests.Tag_request_no " & _
            "WHERE tblRequests.Accepted=true and tblMaintags.Completed=false; SELECT tblRequests.*, tblMaintags.* FROM tblMaintags INNER JOIN " & _
            "tblRequests ON tblMaintags.Tag_Request_No=tblRequests.Tag_request_no " & _
            "WHERE tblRequests.Accepted=true and tblMaintags.Completed=false;"

do not dispose the dataset and the adapter.

Thanks, but that does not make any difference.

My Original Sql command that I posted works but witout filtering the rows if I comment out the following line;

"WHERE Accepted = 1 AND Complete =0"

Is it because 'Accepted' is in tblrequests and 'Completed' is in tblMaintags? if so how do you do that? Thanks

SOLVED!!
I solved my own problem by buiding the query in Access Design view and then switching to SQL view, copied and pasted the generated sql statement (shown below) in vb.net and works like a charm. Thanks all for your assistance.

sql = "SELECT tblRequests.Tag_Request_No, tblMainTags.Tag_ID, tblRequests.Tag_Type, tblRequests.Equipment_Name, tblRequests.Work_Description " & _
            "FROM tblRequests INNER JOIN tblMainTags ON tblRequests.[Tag_Request_No] = tblMainTags.[Tag_Request_No] " & _
            "WHERE (((tblRequests.Accepted)=Yes) AND ((tblMainTags.Complete)=No));"
This question has already been answered. Start a new discussion instead.