Results 1 to 20 of 20

Thread: Load data in DGV based on the values using NOT IN Clause

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Load data in DGV based on the values using NOT IN Clause

    Hi all,

    I am trying a simple employee management system in vb.net 2010 and MS Access 2003
    I have a task, In form1 their is a button and a DGV if the user click the button, list of employee names will shown in another form (say) form2 DGV with a check box column if user checks multiple rows and click ok button in form2 those rows will get in the form1 DGV and i want the form2 DGV to show only the employee list NOT IN form1 DGV
    I have tried the below code but i am not able to filter the form2 DGV if form1 DGV has multiple rows.

    Please help me to get out of this.

    Code:
    Private Sub frmEmpPickList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    With frmEmpEntry.DataGridView2
    Dim codes As String
                Dim i As Int16
                For i = 0 To .RowCount - 1
                    codes = frmEmpEntry.DataGridView2.Rows(i).Cells(1).Value 
                Next
    
                Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\EmployeeDB.mdb;" & _
                                                "Jet OLEDB:Database Password=12345;"
                Dim selectCommand As String
                Dim connection As New OleDbConnection(connectionString)
                selectCommand = "SELECT EmpCode, EmpName, EmpDept FROM EmployeeMaster WHERE EmpCode NOT IN ('" & codes & "')"
                     
                Me.dataAdapter = New OleDbDataAdapter(selectCommand, connection)
                Dim commandBuilder As New OleDbCommandBuilder(Me.dataAdapter)
                Dim table As New DataTable()
                Me.dataAdapter.Fill(table)
                Me.bindingSource1.DataSource = table
                Dim data As New DataSet()
                Me.DataGridView1.DataSource = Me.bindingSource1

  2. #2
    Frenzied Member MattP's Avatar
    Join Date
    Dec 2008
    Location
    WY
    Posts
    1,227

    Re: Load data in DGV based on the values using NOT IN Clause

    Put a break point after your for loop.

    What does your codes variable contain? What would you expect it to contain if you were to write the select query?
    This pattern in common to all great programmers I know: they're not experts in something as much as experts in becoming experts in something.

    The best programming advice I ever got was to spend my entire career becoming educable. And I suggest you do the same.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi MattP,

    Thanks for your response...
    I tried with break point as you said, the variable codes contains the last selected EmpCode but I need the previous EmpCode's also which is already in form1 DGV, so that my form2 DGV will not have the same empcodes, empname and empdept which is already selected by user.
    This is to avoid the user's to select same employee twice.

    Thanks...
    Last edited by tnncprojects; Jan 13th, 2013 at 05:07 AM.

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Any advice please.....

  5. #5
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    For your query to work the structure of the Where statement should be:-

    Code:
    Not In ('Value1','Value2','Value_etc')
    As you currently have it, your For loop is going to return:-

    Code:
    Not In ('LastValueInForLoop')
    This is due to the fact that you keep overwriting the value of your codes variable with each execution of the For loop.

    To overcome this you need to change your For loop to:-

    Code:
    For i = 0 to .RowCount -1
      codes +="'" & frmEmpEntry.DataGridView2.Rows(i).Cells(1).Value & "',"
    Next
    Then at the end of the For loop you need to get rid of that last comma in the string. i.e:-

    Code:
    codes = codes.Substring(0, codes.Length - 1)
    You then need to make a small modification to your SQL Where clause so it looks like:-

    Code:
    WHERE EmpCode NOT IN (" & codes & ")"
    Notice the removal of the single quotes here. This is because you have now build them into your For loop.

    Hope that helps.

    Cheers,

    Ian

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi Ian,

    Thanks for your valuable suggestion and support. I tried as per your advice every thing fine and your logic is excellent.
    And I also changed my code as per the actual requirement, but I am getting an error "Syntax error (missing operator) in query expression 'EmpCode NOT IN ()'."

    Thanks in advance.....

    Below is my final code.

    Code:
    Public Class frmEmpPickList
        Dim dataAdapter As OleDbDataAdapter
        Dim codes As String
        Private Sub frmEmpPickList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
                With frmEmpEntry.DataGridView2
                Dim i As Int16
                For i = 0 To .RowCount - 1
                    codes += "'" & frmEmpEntry.DataGridView2.Rows(i).Cells(1).Value & "',"
                    codes = codes.Substring(0, codes.Length - 1)
                Next
            End With
            Dim Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\EmployeeMaster.mdb;" & _
                                               "Jet OLEDB:Database Password=12345;")
            Con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT EmpCode, EmpName," & _
                                                       "EmpDept FROM EmployeeMaster WHERE EmpCode NOT IN (" & codes & ")", Con)
            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim myDataSet As DataSet = New DataSet()
            myDA.Fill(myDataSet, "EmployeeMaster")  '\\Syntax error (missing operator) in query expression 'EmpCode NOT IN ()'. \\ Here i am getting this error.
            DataGridView1.DataSource = myDataSet.Tables("EmployeeMaster").DefaultView
            Con.Close()
        End Sub
    Note: At the first time when i click the form1 button to view the list of employee form1 DGV will be empty.

  7. #7
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    You got one of my points wrong. The stripping of the LAST comma in your codes variable has to come at the END of your For loop. i.e:-

    Code:
    For i etc...
      'do work
    Next
    codes = codes.Substring(0, codes.Length - 1)
    Hope that helps.

    Cheers,

    Ian

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi Ian,
    I replaced the code as you mentioned but its showing "Object reference not set to an instance of an object." Please refer the below code

    I think at the first time when i am clicking the form1 button the query is going blank. instead it should contain two single quote ' ' so that it will fetch all the rows from access db, and i am not sure about this.

    Code:
    With frmEmpEntry.DataGridView2
    Dim i As Int16
    For i = 0 To .RowCount - 1
    codes += "'" & frmEmpEntry.DataGridView2.Rows(i).Cells(1).Value & "',"
    Next
    codes = codes.Substring(0, codes.Length - 1)  '\\ Object reference not set to an instance of an object.
    End With
    Thanks....

  9. #9
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    You need a slight modification to your for loop to ensure you are not hitting the IsNewRow row in the DataGridView. See here:-

    Code:
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
      Dim codes As String = Nothing
    
      With DataGridView1
        Dim i As Integer
        For i = 0 To .RowCount - 1
          If Not .Rows(i).IsNewRow Then
            codes += "'" & .Rows(i).Cells(1).Value.ToString & "',"
          End If
        Next
        codes = codes.Substring(0, codes.Length - 1)
        'MsgBox(codes)
      End With
    End Sub
    Hope that helps.

    Cheers,

    Ian

  10. #10

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi Ian,

    I like to thank you for taking effort for my issue.

    Ian, I have changed the code as per your advice but no fruitful and I am getting the same error "Object reference not set to an instance of an object."
    Please refer the code.

    Code:
     
    Private Sub frmEmpPickList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
           
            Dim codes As String = Nothing
            With frmEmpEntry.DataGridView2
                Dim i As Integer
                For i = 0 To .RowCount - 1
                    If Not .Rows(i).IsNewRow Then
                        codes += "'" & .Rows(i).Cells(1).Value.ToString & "',"
                        'codes += "'" & frmEmpEntry.DataGridView2.Rows(i).Cells(1).Value.ToString & "',"
                    End If
                Next
                codes = codes.Substring(0, codes.Length - 1) '\\ Object reference not set to an instance of an object.
                'MsgBox(codes)
            End With
            Dim Con = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\EmployeeMaster.mdb;" & _
                                               "Jet OLEDB:Database Password=12345;")
            Con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT EmpCode, EmpName," & _
                                                       "EmpDept FROM EmployeeMaster WHERE EmpCode NOT IN (" & codes & ")", Con)
            Dim myDA As OleDbDataAdapter = New OleDbDataAdapter(cmd)
            Dim myDataSet As DataSet = New DataSet()
            myDA.Fill(myDataSet, "EmployeeMaster")
            DataGridView1.DataSource = myDataSet.Tables("EmployeeMaster").DefaultView
            Con.Close()
     End Sub

  11. #11
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    I think you are going to have to be a bit more specific about when and where you are getting this error since I am not able to replicate it and it certainly should not be giving you this error on the line of code you are indicating?

    Let us know please.

    Cheers,

    Ian

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    I am getting this error on form load itself which contains list of employees in DGV

    A dry run of my idea...

    I have two forms say form1 and form2 and each form has one DGV and a button. In form1 DGV user will enter the emp details by clicking the form1 btn (Here i am getting the error form2 should open ), form2 will open with the list of emp (DGV) so now the user will able to select the employees from form2 DGV (each form2 DGV rows as Checkbox) and user clicks the form2 Ok_btn to get the selected emp's in form1 DGV.
    And my idea is user should not see already picked emp's list in form2 DGV.

    Thanks...

  13. #13
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    We already understand the concept of what you are trying to do so unfortunately that does not help. You say, and I quote:-

    I have changed the code as per your advice but no fruitful and I am getting the same error "Object reference not set to an instance of an object."
    So the question is, WHERE are you getting this error in the Form.Load event? Please do be specific, i.e which line of code produces this error?

    It may help you to encase your code in the Form.Load event in a Try / Catch block to display the error message that you are getting. If that does not help then add a Breakpoint to the Form.Load event and step through your code to find out what is going wrong.

    Hope that helps.

    Cheers,

    Ian

  14. #14

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    Thanks all for your patience...

    I set the break point and attached the screen shot please refer.

    Thanks.....

    Name:  Screen Shot.jpg
Views: 87
Size:  139.3 KB

  15. #15
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    My apologies, this is actually a small flaw in my own logic for not anticipating the fact that NO rows would be picked up in your frmIdenEntry.DataGridView2 control. To fix this use an If statement to test the contents of the codes variable. i.e:-

    Code:
    If Not IsNothing(codes) Then
      codes = codes.Substring(0, codes.Length - 1)
      MsgBox(codes)
    End If
    That said, this implies that there may be a flaw in your own logic since I am guessing that their should be rows in your frmIdenEntry.DataGridView2 control which need to be picked up in this code block?

    Hope that helps.

    Cheers,

    Ian

  16. #16

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    hi Ian,

    If Not IsNothing(codes) Then
    codes = codes.Substring(0, codes.Length - 1)
    MsgBox(codes)
    End If
    I have applied the above logic in my code and I am not sure that i have applied correctly and i am getting index out of range error.
    Please refer the screen shot below

    Name:  Screen Shot2.jpg
Views: 99
Size:  141.1 KB

  17. #17
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    You have mixed it up. This is what you should be using:-

    Code:
    With DataGridView1
      Dim i As Integer
      For i = 0 To .RowCount - 1
        If Not .Rows(i).IsNewRow Then
          codes += "'" & .Rows(i).Cells(1).Value.ToString & "',"
        End If
      Next
      If Not IsNothing(codes) Then
        codes = codes.Substring(0, codes.Length - 1)
        'MsgBox(codes)
      End If
    End With
    Hope that helps.

    Cheers,

    Ian

  18. #18

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    I have made all changes whatever you advice and its seems to be a good approach to get rid of this thread.

    Now i am getting new error and i tried with possibilities by googling but i cant find the right solution.
    Please refer the below screen shot.

    Thanks for all your efforts...

    Name:  Screen Shot3.png
Views: 92
Size:  197.4 KB

  19. #19
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,232

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi,

    The thing to do now is to interrogate the SQL string that you are trying to use with your OleDbCommand object. After you have built your Command string I suggest that you display the contents of the string in a message box i.e. MsgBox(cmd.CommandText). This will show you the string that is being passed to your database as your SQL code.

    As it is, you will see that your Where Clause is incorrect due to the fact that your code block iterating the frmIdentEntry.DataGridView2 control is not actually picking up any rows hence my point in Post No.15 suggesting that you have a logic error of your own to deal with BEFORE you call this Form Load event.

    Hope that helps.

    Cheers,

    Ian

  20. #20

    Thread Starter
    Lively Member
    Join Date
    Oct 2011
    Posts
    76

    Re: Load data in DGV based on the values using NOT IN Clause

    Hi Ian,

    Thanks for guidance...

    I will check and let you know...

    Thanks.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width