Question Filtering Data source code problem

Joined
Apr 15, 2013
Messages
9
Programming Experience
Beginner
Hi , i am currently designing a Inventory Management System for my school Project , the add data edit data and delete data part of the software works for me but i am kinda stuck at the filtering data part and it does not seem to work at all . This is my source Code

Sub combobox_itemID()


        Dim rs = New ADODB.Recordset


        With rs
            .Open("Select ItemID from Inventory", cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)
            ComboBox1.Items.Clear()
            .MoveFirst()
            While Not .EOF
                ComboBox1.Items.Add(.Fields(0).Value)
                .MoveNext()
            End While
            ComboBox1.Text = ComboBox1.Items(0)




        End With
    End Sub
  
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
        MessageBox.Show("Select Item ID - " & ComboBox1.Text)
        Dim lst
        Dim rs = New ADODB.Recordset
        ListView1.Items.Clear()
        With rs
            .Open("select t.ItemID,t.ItemName,t.ItemDescription,t.Quantity,t.Unit,t.Location,t.Cost,t.Status,t.DateReceived,t.Packed* from Inventory t,Warehouse s where t.ItemID=s.ItemID and s.ItemName='" & ComboBox1.Text & "'", cn, 2, 3)
            Do While Not .EOF
                lst = New ListViewItem
                lst = ListView1.Items.Add(.Fields(0).Value)
                lst.SubItems.Add(.Fields(1).Value)
                lst.SubItems.Add(.Fields(2).Value)
                lst.SubItems.Add(.Fields(3).Value)
                lst.SubItems.Add(.Fields(4).Value)
                lst.SubItems.Add(.Fields(5).Value)
                lst.SubItems.Add(.Fields(6).Value)
                lst.SubItems.Add(.Fields(7).Value)
                lst.SubItems.Add(.Fields(8).Value)
                lst.SubItems.Add(.Fields(9).Value)




                .MoveNext()
            Loop




        End With
    End Sub 


P.s I am really new to programming , i just started learning the language 4 weeks ago , sometimes i might not know what i am coding
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
It saddens me to see new programmers doing things completely the wrong way, indicating that there are some very poor teaching resources out there. Firstly, you are using ADO for data access, which is an old VB6 technology. You should be using ADO.NET. Also, you should not be using a ListView, but rather a DataGridView. What you want to do is extremely simple then. Create a data adapter and Fill a DataTable, bind the DataTable to a BindingSource and bind that to a DataGridView. When you want to filter, set the Filter property of the BindingSource.
 
Joined
Apr 15, 2013
Messages
9
Programming Experience
Beginner
It saddens me to see new programmers doing things completely the wrong way, indicating that there are some very poor teaching resources out there. Firstly, you are using ADO for data access, which is an old VB6 technology. You should be using ADO.NET. Also, you should not be using a ListView, but rather a DataGridView. What you want to do is extremely simple then. Create a data adapter and Fill a DataTable, bind the DataTable to a BindingSource and bind that to a DataGridView. When you want to filter, set the Filter property of the BindingSource.
i know it saddens me as well , in actual fact its sadden me alot . anyway i am thinking to use a bindingsource but it will affect the other parts of my programming of my ListView , is there any other way i can do it without changing my ListView or using the same connection? ? ( p.s my Supervisor of this project doesn recommend using binding source as he said it is unprofessional ( which is ironic i am just a noob at coding ) ) .
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
It is totally incredible that someone would call using a BindingSource unprofessional while advocating putting data access code in a form and using ADO in that data access code. I'm sorry to say that, while your supervisor may be able to knock out a program that gets the job done, they simply don't know what they're talking about when it comes to VB.NET development. A sobering thought for you, I'm sure, and I'm afraid that you're going to have to unlearn a reasonable amount of what you've been taught.

I'm afraid that I'm not going to try to fix any ADO code because I've never used ADO and I never want to use ADO and noone writing code in VB.NET should be using ADO either.
 
Joined
Apr 15, 2013
Messages
9
Programming Experience
Beginner
It is totally incredible that someone would call using a BindingSource unprofessional while advocating putting data access code in a form and using ADO in that data access code. I'm sorry to say that, while your supervisor may be able to knock out a program that gets the job done, they simply don't know what they're talking about when it comes to VB.NET development. A sobering thought for you, I'm sure, and I'm afraid that you're going to have to unlearn a reasonable amount of what you've been taught.

I'm afraid that I'm not going to try to fix any ADO code because I've never used ADO and I never want to use ADO and noone writing code in VB.NET should be using ADO either.
erm my supervisor does not teach me coding just access my work . sadly to say i learn everything through youtube ... when i started this work i have totally no coding background at all , anyway thanks , i will try to solve it on my own
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
OK then, I apologise to your supervisor. I'm afraid that, where data access is concerned, you have either been given bad information or you have looked in the wrong place because, while ADO works in VB.NET, that is primarily for compatibility with upgraded VB6 code. Noone should be writing new code using ADO unless it's for consistency with code that was upgraded from VB6 that already uses ADO. You should change all your data access code immediately. You should also replace your ListView with a DataGridView but that is of secondary importance. Here are some code examples of common ADO.NET scenarios:

Retrieving and Saving Data in Databases
 
Joined
Apr 15, 2013
Messages
9
Programming Experience
Beginner
OK then, I apologise to your supervisor. I'm afraid that, where data access is concerned, you have either been given bad information or you have looked in the wrong place because, while ADO works in VB.NET, that is primarily for compatibility with upgraded VB6 code. Noone should be writing new code using ADO unless it's for consistency with code that was upgraded from VB6 that already uses ADO. You should change all your data access code immediately. You should also replace your ListView with a DataGridView but that is of secondary importance. Here are some code examples of common ADO.NET scenarios:

Retrieving and Saving Data in Databases
Thanks i will look into it
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
Actually, I just took a closer look at your original code and I noticed that you are populating the ComboBox with values from the Inventory.ItemID column but, when the user selects a value, you use it to filter by the Warehouse.ItemName column. That doesn't seem to make sense.
 
Joined
Apr 15, 2013
Messages
9
Programming Experience
Beginner
Actually, I just took a closer look at your original code and I noticed that you are populating the ComboBox with values from the Inventory.ItemID column but, when the user selects a value, you use it to filter by the Warehouse.ItemName column. That doesn't seem to make sense.
do u mean Warehouse ItemID , coz the Warehouse Item ID and Inventory Item ID shares a primary key in the database
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
do u mean Warehouse ItemID , coz the Warehouse Item ID and Inventory Item ID shares a primary key in the database
No, I don;t mean that. Take a look at your code.
Code:
where t.ItemID=s.ItemID and s.[B][U]ItemName[/U][/B]='" & ComboBox1.Text & "'"
 
Joined
Apr 15, 2013
Messages
9
Programming Experience
Beginner
No, I don;t mean that. Take a look at your code.
Code:
where t.ItemID=s.ItemID and s.[B][U]ItemName[/U][/B]='" & ComboBox1.Text & "'"
any suggestion i can change the code to? coz i tried changing but its also doesn seem to work
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
It should be obvious that if you have values from the ItemID column then you can only use them to filter in the ItemID column and if you actually want to filter by the ItemName column then you need to use a value from the ItemName column.
 
Joined
Apr 15, 2013
Messages
9
Programming Experience
Beginner
It should be obvious that if you have values from the ItemID column then you can only use them to filter in the ItemID column and if you actually want to filter by the ItemName column then you need to use a value from the ItemName column.
erm i tried to change the coding to t.itemID but still it doesn work
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
i change it to t.itemID and for that particular part and it still cant work
That doesn't answer either of my questions. Show us your code and tell us what happens when you run it.
 
Joined
Apr 15, 2013
Messages
9
Programming Experience
Beginner
That doesn't answer either of my questions. Show us your code and tell us what happens when you run it.

Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
MessageBox.Show("Select Item ID - " & ComboBox1.Text)
Dim lst
Dim rs = New ADODB.Recordset
ListView1.Items.Clear()
With rs
.Open("select t.ItemID,t.ItemName,t.ItemDescription,t.Quantity,t.Unit,t.Location,t.Cost,t.Status,t.DateReceived,t.Packed* from Inventory t,Warehouse s where t.ItemID=s.ItemID and t.ItemID=" & ComboBox1.Text & "'", cn, 2, 3)
Do While Not .EOF
lst = New ListViewItem
lst = ListView1.Items.Add(.Fields(0).Value)
lst.SubItems.Add(.Fields(1).Value)
lst.SubItems.Add(.Fields(2).Value)
lst.SubItems.Add(.Fields(3).Value)
lst.SubItems.Add(.Fields(4).Value)
lst.SubItems.Add(.Fields(5).Value)
lst.SubItems.Add(.Fields(6).Value)
lst.SubItems.Add(.Fields(7).Value)
lst.SubItems.Add(.Fields(8).Value)
lst.SubItems.Add(.Fields(9).Value)




.MoveNext()
Loop




End With
End Sub

The combo box can't work at all, i want it to filter the itemID in the listview. E.g. when i choose or type "2" only itemID "2" appears in the listview.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
Four things:

1. I have asked you twice now to tell us what happened when you ran that code and still haven't done so. If you want our help then please provide the information we ask for when we ask for it, because we're asking for it for a reason. Don't just keep saying "it doesn't work" over and over. Tell us explicitly what you expect to happen and what actually happens.

2. It took two goes to actually get your current code out of you and this shows exactly why I asked for it in the first place. I can see straight away what the issue is. Don't just tell us what you think you have done because if you had done it right then it would work. If we can't see what you've done then we can't see what's wrong with it. You shouldn't need to be prompted to provide relevant code.

3. This is a perfect example of not looking at your data and just assuming it is what you think it is. You are constructing a SQL string in code there but you have apparently not actually looked at that string to make sure that it is what you think it is. If you had then you would probably have seen the issue. It's as simple as putting this line in there:
Code:
MessageBox.Show("select t.ItemID,t.ItemName,t.ItemDescription,t.Quantity,t .Unit,t.Location,t.Cost,t.Status,t.DateReceived,t. Packed* from Inventory t,Warehouse s where t.ItemID=s.ItemID and t.ItemID=" & ComboBox1.Text & "'")
4. This a perfect example of why you should use parameters to insert values into SQL code rather then string concatenation. It can't save you from all mistakes but it can save you from this one. The Blog link in my signature includes a post about Parameters In ADO.NET but you'd have to be using ADO.NET for that to be completely relevant. You can still use parameters in ADO but the details are different.
 
Top Bottom