Click here to Skip to main content
14,240,831 members
Rate this:
Please Sign up or sign in to vote.
Hi, I have created the following code and want to filter my database table by landlord_ID. No errors have been shown and when I debug it all seems to go well (all the landlord_IDs come up with all the information e.g how many properties. However, when I double-click to select a certain ID nothing happens. If I add to part of the code like this : SQLString = "SELECT = FROM Flats WHERE landlord_ID = 1" '& landlord_ID & ""
Then number 1 comes up but so do all the others (the rest should be filtered out).
Also, I have used this youtube link to help me:

Finally, This is my first time using visual studio (2010 Ultimate and the access databse was made on access2010) so I do not know much so would appreciate answers that are a little more specific.

Thank you for reading all of this and I hope you can help me

Imports System.Data.OleDb
Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    End Sub

   Private Sub lstLID_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lstLID.SelectedIndexChanged
        Dim landlord_ID, SQLString As String
        Dim dtFlats As New DataTable()
        Dim dbDataAdapter As OleDbDataAdapter
        Dim ConnectString As String = "Provider= Microsoft.ACE.OLEDB.12.0;" & "Data Source = Database.accdb"
        landlord_ID = lstLID.Text
        SQLString = "SELECT = FROM Flats WHERE landlord_ID = " '& landlord_ID & ""
        dbDataAdapter = New OleDbDataAdapter(SQLString, ConnectString)
        grdFlats.DataSource = dtFlats
    End Sub
End Class
Updated 10-Apr-13 2:27am
NotPolitcallyCorrect 10-Apr-13 8:29am
Your SQL is invalid. You have a single quote where it does not belong.
Maciej Los 10-Apr-13 8:48am

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

You did nothing to help yourself! You haven't even read my past answer (see my comment).

Maciej Los wrote:
(...)what you need to do is:
1) Create OleDbConnection[^],
2) Open it using method Open()[^] method,
3) Create OleDbCommand[^]
4) Set CommandType[^], CommandText[^] properties,
5) Create OleDbDataReader[^] and call ExecuteReader()[^] method.
6) Create DataTable[^] and Load()[^] data from OleDbDataReader.
7) Bind data with your component to display data(...)

Dim oConn As OleDbConnection = Nothing
Dim oComm As OleDbCommand = Nothing
Dim sConn As String = String.Empty
Dim sComm AS String = String.Empty
Dim oRdr As OleDbDataReader = Nothing
Dim oDt As Data.DataTable = Nothing
    sConn = "specify connection"
    oConn = New OleDbConnection(sConn)
    sComm = "SELECT * FROM Flats WHERE landlord_ID = " & lstLID.SelectedValue.ToString()
    oComm = New OleDbCommand(sComm, oConn)
    oRdr = oComm.ExecuteReader()
    oDt = New Data.DataTable("TableName")

Catch ex As Exception
   'show error 
   'clean up
End Try

Straight from a head... not tested!
suhail19 10-Apr-13 14:41pm
thanks a lot! ok I know I am really pushing it now but could you possibly tell me why there are lots of "nothings"? Thanks a lot and in future I'll try my best to do it myself!
Also, where do I put in my datasource?(It is called Database.accdb on my code)
Maciej Los 10-Apr-13 14:50pm
could you possibly tell me why there are lots of "nothings"?
It's a force of habit (joking). These are initial values for those objects.

If solution was helpful, it would be great to get stars from you ;)
suhail19 10-Apr-13 14:52pm
The solution opens up the table but when I double click, it still won't filter. it just does nothing. I will give you stars if you can make this work, I am so stressed!
Maciej Los 10-Apr-13 15:02pm
Suhail19, i post my answer to your question. If you don't want to vote-up, don't do that. But remeber, rewarding cost nothing ;)
Try to solve your NEXT problem without my help! Try, try, try!!!
Tip: You need to do exactly the same, but your SELECT statement should not contain WHERE clause.
suhail19 10-Apr-13 15:08pm
I have given you stars. Please you don't understand. I really have no experience. I am averaging 75% in my university modules and I have no prior computing experience. I really don't understand it. Most of the code I used from that video link.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100