Resolved Something caused not to get result in DataGridView?

[Socarsky]

The query string is right and tested on Access successfully but there is something wrong that caused not to get result in DataGridView? Can You figure out what's the matter? The program is simple one, combobox control gets all employees name when it runs and when you pick up a name then check in and out must be show off in DataGridView.

Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        num = CShort(num + 1)
        Dim cmbStr As String
        cmbStr = ComboBox1.GetItemText(ComboBox1.SelectedItem)
        MessageBox.Show(cmbStr.ToString)
        If num <> 0 Then
            Try
                If (CONN.State = ConnectionState.Closed) Then
                    CONN.Open()
                End If
                Dim da As New OleDb.OleDbDataAdapter("SELECT  USERINFO.Name, CHECKINOUT.CHECKTIME FROM USERINFO LEFT JOIN CHECKINOUT  ON USERINFO.USERID = CHECKINOUT.USERID WHERE USERINFO.Name Like '" &  cmbStr & "*';", CONN)
                Dim ds As New DataSet
                Dim cmd As New OleDb.OleDbCommandBuilder(da)
                ds.Clear()
                DataGridView1.DataSource = ds.Tables.Add("att2000")
                da.Fill(ds, "att2000")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
End Sub

 

[jmcilhinney]

What value does Fill return? What happens if you call Fill first and then bind the existing DataTable? By setting the DataSource before calling Fill you are not going to add any columns to the grid because the =y don't exist in the DataTable at that time. Do you even need a DataSet at all? are you using multiple DataTables? If not then just use a DataTable and forget about the DataSet.

 

[Socarsky]

I am getting below secreen after choosing a name. Btw, you can see the other captured image under below that show the query result in Access and datetype. Check is a datetime field. I dont know answers of the other questions that you asked me.

 

[jmcilhinney]

I asked several questions in my previous post and you haven't answered any of them. I asked them for a reason but you're wasting my time. I won't be posting further to this thread.

 

[Socarsky]

What value does Fill return?

I put a screen capture and it shows what values must return, fullname and datetime if you emphasis your question like what I typed.

What happens if you call Fill first and then bind the existing DataTable?

I face with a error message that says "A DataTable names 'att2000' already belongs to this DataSet.

Do you even need a DataSet at all?

I don't know really! I'm serious

are you using multiple DataTables?

Actually there are two table that I used in the query to retrieve data as result as a name. Is that enough answer of your question?

 

[Socarsky]

Below is last version of my project and I noticed something that caused fail that If I change the query string with this:

("Select Name from USERINFO Order by Name", CONN)

Then I can get the result well. Btw, I checked my first longer query syntax its works well in Access Sql query.

Dim num As Short = -1
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        num = CShort(num + 1)
        Dim cmbStr As String
        cmbStr = ComboBox1.GetItemText(ComboBox1.SelectedItem)
        If num <> 0 Then
            Try
                Dim ds As New DataSet
                Dim da As New OleDb.OleDbDataAdapter("SELECT  USERINFO.Name, CHECKINOUT.CHECKTIME FROM USERINFO LEFT JOIN CHECKINOUT  ON USERINFO.USERID = CHECKINOUT.USERID WHERE USERINFO.Name Like '" & cmbStr & "*'", CONN)
                da.Fill(ds, "att2000")
                DataGridView1.DataSource = ds.Tables("att2000")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

 

[IanRyder]

Hi,

From what I can see you are doing this all wrong. If you want to re-query the contents of a DataGridView based on the value of some information in a ComboBox then you should be using Parent / Child (also known as Master / Detail) Data Binding using a Relationship in a DataSet.

To demonstrate this, add a DataGridView and a ComboBox to a new form and then have a play with this code below. Just change the Connection and DataAdapter details to your own.

Imports System.Data.SqlClient
 
Public Class Form1
  'This example application will show how to create Related Data Tables in a Dataset and then add those Tables to Controls using Binding Sources
  Dim myDataSet As New DataSet
 
  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    'Declare a Database Connection and two DataAdapters to retrieve the data from the DataSource
    Using sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True"),
      daUsers As New SqlDataAdapter("Select * from tblUserNames", sqlConn),
      daUserInfo As New SqlDataAdapter("Select * From tblUserInfo", sqlConn)
 
      'Declare two Binding Sources to be bound to th controls
      Dim bsParent As New BindingSource
      Dim bsChild As New BindingSource
 
      'Fill the Dataset with the two Data Tables from the DataSource
      daUsers.Fill(myDataSet, "tblUsers")
      daUserInfo.Fill(myDataSet, "tblUserInfo")
 
      'Set the tblUserInfo Table to Seed Temporary ID values when entering New Records in the DataGridView 
      With myDataSet.Tables("tblUserInfo").Columns(0)
        .AutoIncrement = True
        .AutoIncrementSeed = -1
        .AutoIncrementStep = -1
      End With
 
      'Now Create a Relationship between the two Tables in the Dataset by using a DataRelation object
      'Firstly create two Datacolumn ojects to hold the columns of the Relationship and then add the 
      'Relationship to the Dataset
      Dim userIDCol As DataColumn = myDataSet.Tables("tblUsers").Columns("IDNo")
      Dim userInfoUserIDCol As DataColumn = myDataSet.Tables("tblUserInfo").Columns("UserID")
      Dim DR As New DataRelation("UserNamesToUserInfo", userIDCol, userInfoUserIDCol)
      myDataSet.Relations.Add(DR)
 
      'Bind the Dataset's Parent Table to the Parent Binding Source
      bsParent.DataSource = myDataSet
      bsParent.DataMember = "tblUsers"
 
      'Bind the Child Binding Source to the Defined Relationship in the DataSet
      bsChild.DataSource = bsParent
      bsChild.DataMember = "UserNamesToUserInfo"
 
      'Now that we have the Binding Sources set correctly we can add the Binding Sources
      'to the each of the Contriols that we want to use
 
      'So, Add the UserNames to a ComboBox
      With cbUserNames
        .DisplayMember = "UserName"
        .ValueMember = "IDNo"
        .DataSource = bsParent
      End With
 
      'And Add the UserInfo to a DataGridView
      With dgvUserInfo
        .DataSource = bsChild
      End With
    End Using
  End Sub
End Class

As you will see, once you now change the value of the ComboBox the contents of the DataGridView are automatically changed to represent those records that are related to the User Name due to the binding of the bsChild Binding Source being added to the DataGridView's DataSource.

Hope that helps.

Cheers,

Ian

 

[Socarsky]

Hi,
IanRyder Sorry about that but please think twice before saying "From what I can see you are doing this all wrong."
I knew that my code almost fine but something lack that caused the issue but now I figured out why.
Thanks all for suggestions even someone wasted his time who was quite impatient that couldn't wait a little longer to type his questions' answers, anyway.
Asteriks causes that issue not to get good result in my project and I only changed asteriks with '%' it just like sql syntax but I am so sure that asteriks successfully works in Access queries.

Here is entire code that who wants to see:

Public Class Form1
    Dim CONN As New OleDb.OleDbConnection
    Dim DA As OleDb.OleDbDataAdapter

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim dbprovider As String = "Provider=Microsoft.Jet.OleDb.4.0;"
        Dim dbsource As String = "DATA SOURCE=C:\Users\dynamo\Desktop\att2000.mdb"
        CONN.ConnectionString = dbprovider & dbsource
        If (CONN.State = ConnectionState.Closed) Then
            CONN.Open()
        End If
        tbUnit()
    End Sub

    Private Sub Form1_Leave(sender As Object, e As EventArgs) Handles MyBase.Leave
        CONN.Close()
    End Sub

    Public Sub tbUnit()
        Try
            Dim ds As New DataSet
            Dim myTable As DataTable = New DataTable("MyTable")
            myTable.Columns.Add(New DataColumn("Name"))
            Dim cmd As New OleDb.OleDbCommand("Select Name from USERINFO Order by Name", CONN)
            Dim dr As OleDb.OleDbDataReader = cmd.ExecuteReader(CommandBehavior.Default)
            Dim myRow As DataRow
            While dr.Read()
                myRow = myTable.NewRow
                myRow.Item(0) = dr(0)
                myTable.Rows.Add(myRow)
            End While
            dr.Close()
            ds = New DataSet()
            ds.Tables.Add(myTable)
            ComboBox1.DisplayMember = "Name"
            ComboBox1.DataSource = myTable
            ComboBox1.Text = String.Empty
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Dim num As Short = -1
    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
        num = CShort(num + 1)
        Dim cmbStr As String
        cmbStr = ComboBox1.GetItemText(ComboBox1.SelectedItem)
        If num <> 0 Then
            Try
                Dim ds As New DataSet
                Dim da As New OleDb.OleDbDataAdapter("SELECT  USERINFO.Name, CHECKINOUT.CHECKTIME FROM USERINFO LEFT JOIN CHECKINOUT  ON USERINFO.USERID = CHECKINOUT.USERID WHERE USERINFO.Name Like '" & cmbStr & "%'", CONN)
                da.Fill(ds, "att2000")
                DataGridView1.DataSource = ds.Tables("att2000")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class