Database help
-
I experimenting with Writing database applications and trying to do different things with different controls. My current problem is I've been working on a feature to search a database containg my DVD collection. It is an Access 2003 Database with only one table. Here is my code thus far with the area where the program bombs in bold, any help would be greatly appreciated.Also the code that is supposed to check for a database is'nt working either, but I commented it out for now:
'Search 'If they have no active dataset, refuse: 'If ds.Tables.Count = 0 Then ' MsgBox("Please use the file menu to open a dataset, or create 'a new one first.") ' Exit Sub 'End If 'search for the target in both columns, then display it Dim searchfor As String = InputBox("Enter you Search Term", "Search") searchfor = searchfor.ToLower lstResults.Items.Clear() Dim i, x, y, count As Integer **dt = ds.Tables!DVDLIST ' set dt to point to this table** TotalRows = dt.Rows.Count For i = 0 To TotalRows - 1 x = ds.Tables(Name).Rows(i).Item(0).ToString.ToLower.IndexOf(searchfor) 'See if Title column matches y = ds.Tables(0).Rows(i).Item(1).ToString.ToLower.IndexOf(searchfor) 'see about desc column too If x <> -1 Or y <> -1 Then 'Match lstResults.Items.Add(ds.Tables(0).Rows(i).Item(0)) 'Add title field to a listbox count += 1 End If Next i If count = 0 Then 'no matches found MsgBox("No match for " & searchfor & " was found...") Else lstResults.Visible = True End IfBINARY -
I experimenting with Writing database applications and trying to do different things with different controls. My current problem is I've been working on a feature to search a database containg my DVD collection. It is an Access 2003 Database with only one table. Here is my code thus far with the area where the program bombs in bold, any help would be greatly appreciated.Also the code that is supposed to check for a database is'nt working either, but I commented it out for now:
'Search 'If they have no active dataset, refuse: 'If ds.Tables.Count = 0 Then ' MsgBox("Please use the file menu to open a dataset, or create 'a new one first.") ' Exit Sub 'End If 'search for the target in both columns, then display it Dim searchfor As String = InputBox("Enter you Search Term", "Search") searchfor = searchfor.ToLower lstResults.Items.Clear() Dim i, x, y, count As Integer **dt = ds.Tables!DVDLIST ' set dt to point to this table** TotalRows = dt.Rows.Count For i = 0 To TotalRows - 1 x = ds.Tables(Name).Rows(i).Item(0).ToString.ToLower.IndexOf(searchfor) 'See if Title column matches y = ds.Tables(0).Rows(i).Item(1).ToString.ToLower.IndexOf(searchfor) 'see about desc column too If x <> -1 Or y <> -1 Then 'Match lstResults.Items.Add(ds.Tables(0).Rows(i).Item(0)) 'Add title field to a listbox count += 1 End If Next i If count = 0 Then 'no matches found MsgBox("No match for " & searchfor & " was found...") Else lstResults.Visible = True End IfBINARYI don't see a bold line.
Binary0110 wrote:
searchfor = searchfor.ToLower
Is Access case sensitive ? SQL Server isn't.
Binary0110 wrote:
ds.Tables!DVDLIST
What does this do ?
Binary0110 wrote:
For i = 0 To TotalRows - 1 x = ds.Tables(Name).Rows(i).Item(0).ToString.ToLower.IndexOf(searchfor) 'See if Title column matches y = ds.Tables(0).Rows(i).Item(1).ToString.ToLower.IndexOf(searchfor) 'see about desc column too If x <> -1 Or y <> -1 Then 'Match lstResults.Items.Add(ds.Tables(0).Rows(i).Item(0)) 'Add title field to a listbox count += 1 End If Next i
This is definately not how to write a database. You may as well store your data in text files if you're not going to use SQL to search through your data. "SELECT * from DVDList where title LIKE '%" + searchfor +"' OR desc LIKE '%" + searchfor + "'" would generate you something that would have a good shot of giving you what you need. I suggest you google for "Access VB.NET Connection" and see what you come up with. Christian Graus - Microsoft MVP - C++
-
I don't see a bold line.
Binary0110 wrote:
searchfor = searchfor.ToLower
Is Access case sensitive ? SQL Server isn't.
Binary0110 wrote:
ds.Tables!DVDLIST
What does this do ?
Binary0110 wrote:
For i = 0 To TotalRows - 1 x = ds.Tables(Name).Rows(i).Item(0).ToString.ToLower.IndexOf(searchfor) 'See if Title column matches y = ds.Tables(0).Rows(i).Item(1).ToString.ToLower.IndexOf(searchfor) 'see about desc column too If x <> -1 Or y <> -1 Then 'Match lstResults.Items.Add(ds.Tables(0).Rows(i).Item(0)) 'Add title field to a listbox count += 1 End If Next i
This is definately not how to write a database. You may as well store your data in text files if you're not going to use SQL to search through your data. "SELECT * from DVDList where title LIKE '%" + searchfor +"' OR desc LIKE '%" + searchfor + "'" would generate you something that would have a good shot of giving you what you need. I suggest you google for "Access VB.NET Connection" and see what you come up with. Christian Graus - Microsoft MVP - C++