Member Avatar for Mackeny

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 by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
  • 3 Contributors
  • 5 Replies
  • 2K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Mackeny

Dani AI

Generated

Good news: the empty grid is almost always a filter issue with Access Yes/No fields plus a tiny SQL gotcha. In Access/ACE, True is stored as -1 and False as 0, so comparing a Yes/No column to 1 will return no rows. Use True/False (or Yes/No, or -1/0) and fully qualify the columns, e.g., tblRequests.Accepted and tblMainTags.Completed. Also double-check the exact column name in tblMainTags: Completed vs Complete. A mismatch there will quietly zero out results. was on the right track with qualifying the columns; disposing the DataSet is not the root cause here, as suspected.

That OleDbException you saw (Characters found after end of SQL statement) happened because you concatenated two SELECTs into one command. OleDbDataAdapter.Fill expects a single SELECT. Keep only one statement per Fill.

Quick way to verify your filter before binding the grid: run a COUNT with parameters (OleDb uses positional ? parameters; order matters). This avoids literal confusion and proves whether your WHERE is correct.

Using conn As New OleDb.OleDbConnection(connString), _
      cmd As New OleDb.OleDbCommand( _
        "SELECT COUNT(*) " &
        "FROM tblRequests AS r INNER JOIN tblMainTags AS m " &
        "ON r.Tag_Request_No = m.Tag_Request_No " &
        "WHERE r.Accepted=? AND m.Completed=?", conn)

    cmd.Parameters.Add("@p1", OleDb.OleDbType.Boolean).Value = True
    cmd.Parameters.Add("@p2", OleDb.OleDbType.Boolean).Value = False

    conn.Open()
    Dim matches As Integer = CInt(cmd.ExecuteScalar())
    Debug.WriteLine("Matches: " & matches)
End Using

A few extra tips:

  • Select only the columns you need to avoid duplicate names from both tables.
  • Use brackets for any reserved words (e.g., [Accepted_Date], [Accepted_Time]).
  • For a reliable record count in the UI, prefer DataTable.Rows.Count over DataGridView.RowCount.

Recommended Answers

All 5 Replies

Member Avatar for kvprajapati 
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;
Member Avatar for Mackeny 
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;"
Member Avatar for mrwass

do not dispose the dataset and the adapter.

Member Avatar for Mackeny

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

Member Avatar for Mackeny

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));"
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.