filtering access tables (2010) in visual studio(2010 ultimate)

Question

1.00/5 (1 vote)

See more:

VS2010

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: http://www.youtube.com/watch?v=4H2g8H0bqEg

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

VB

Imports System.Data.OleDb
Public Class Form1

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

    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)
        dbDataAdapter.Fill(dtFlats)
        grdFlats.DataSource = dtFlats
    End Sub
End Class

Posted 10-Apr-13 1:42am

suhail19

Updated 10-Apr-13 2:27am

NotPolitcallyCorrect

v2

Add a Solution

Comments

[no name] 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

Do not repost: Filter in vs210 using access 2010 table[^]!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2013-04-10T03:05:00

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

Maciej Los wrote:
Suhail19,
(...)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(...)

Solution:

VB

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
Try
    sConn = "specify connection"
    oConn = New OleDbConnection(sConn)
    oConn.Open()
    sComm = "SELECT * FROM Flats WHERE landlord_ID = " & lstLID.SelectedValue.ToString()
    oComm = New OleDbCommand(sComm, oConn)
    oRdr = oComm.ExecuteReader()
    oDt = New Data.DataTable("TableName")
    oDt.Load(ordr)
    grdFlats.DataSource=oDt
    oConn.Close()

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

Straight from a head... not tested!