-
Jan 11th, 2013, 02:44 PM
#1
Thread Starter
Lively Member
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
-
Jan 11th, 2013, 04:04 PM
#2
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.
-
Jan 12th, 2013, 06:13 AM
#3
Thread Starter
Lively Member
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.
-
Jan 13th, 2013, 05:09 AM
#4
Thread Starter
Lively Member
Re: Load data in DGV based on the values using NOT IN Clause
-
Jan 13th, 2013, 06:19 AM
#5
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
-
Jan 13th, 2013, 10:15 AM
#6
Thread Starter
Lively Member
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.
-
Jan 13th, 2013, 11:05 AM
#7
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
-
Jan 13th, 2013, 12:20 PM
#8
Thread Starter
Lively Member
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....
-
Jan 14th, 2013, 03:59 AM
#9
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
-
Jan 14th, 2013, 07:24 AM
#10
Thread Starter
Lively Member
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
-
Jan 14th, 2013, 11:54 PM
#11
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
-
Jan 15th, 2013, 08:01 AM
#12
Thread Starter
Lively Member
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...
-
Jan 15th, 2013, 09:56 AM
#13
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
-
Jan 15th, 2013, 12:13 PM
#14
Thread Starter
Lively Member
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.....
-
Jan 16th, 2013, 12:09 AM
#15
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
-
Jan 16th, 2013, 04:25 AM
#16
Thread Starter
Lively Member
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
-
Jan 16th, 2013, 05:30 AM
#17
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
-
Jan 16th, 2013, 06:32 AM
#18
Thread Starter
Lively Member
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...
-
Jan 16th, 2013, 09:23 AM
#19
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
-
Jan 17th, 2013, 02:35 PM
#20
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|