really strange problem i have no explanation

[peroja]

hello dear comunity, my first posting
im bored at home, suffering a pneumonia after a biketour

so i'm here, doin some small stuff for my company. and im stuck with a real strange problem. allow me first to post my code and then explain the problem. involved is an access db and datagridview. the db is built like this: (37MB size table)

Imports System.Data.OleDb
Public Class frmSM
    'Global Declaration for Class frmSM
    Dim AddMode As Boolean
    Dim ID As Integer
    Dim myConn As New OleDbConnection
    Dim myCmd As New OleDbCommand
    Dim myDA As New OleDbDataAdapter
    Dim myDR As OleDbDataReader
    Dim strSQL As String

    Function IsConnected() As Boolean
        Try
            'Checks first if already connected to database,if connected, it will be disconnected.
            If myConn.State = ConnectionState.Open Then myConn.Close()
            myConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Randy\Desktop\programmieren\vb.net\rwydatas\icao.mdb;"
            myConn.Open()
            IsConnected = True
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
    Function IsExists() As Boolean
        SearchSM(ID)
        If myDR.HasRows = True Then
            Return True
        Else
            Return False
        End If
    End Function
    Sub SearchSM(ByVal SearchWord As String)
        Try
            If IsConnected() = True Then
                strSQL = "SELECT top 1000 * FROM G5 WHERE ICAO LIKE '" & SearchWord & "%'"
                'strSQL = "SELECT top 10 * FROM G5 WHERE ICAO LIKE 'LSZH'"
            End If
            myCmd.CommandText = strSQL
            myCmd.Connection = myConn
            myDA.SelectCommand = myCmd
            myDR = myCmd.ExecuteReader()

            'Display results to table
            lstICAO.Items.Clear()
            While (myDR.Read())
                With lstICAO.Items.Add(myDR("ICAO"))
                    .SubItems.Add(myDR("POSITION"))
                    .SubItems.Add(myDR("POS_NR"))
                    .SubItems.Add(myDR("LATITUDE"))
                    .SubItems.Add(myDR("LONGITUDE"))
                    .SubItems.Add(myDR("RADIUS"))
                    .SubItems.Add(myDR("TRUEHEADING"))
                    .SubItems.Add(myDR("GATETYPE"))
                End With
            End While
        Catch ex As Exception
        End Try
    End Sub
    Private Sub TextBox1_TextChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBox1.KeyDown
        If e.KeyCode = Keys.Return Then
            'Checks first if system is connected then if true, continues to search
            If IsConnected() = True Then Call SearchSM(TextBox1.Text)
            RadioButton1.Visible = True
        End If
    End Sub
    Private Sub frmSM_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SearchSM("")
    End Sub
    Private Sub txtSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        TextBox1.SelectAll()
    End Sub

   
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs)

    End Sub

    Private Sub RadioButton1_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RadioButton1.CheckedChanged
        'Dim SearchWord As String

        Try
            If IsConnected() = True Then
                strSQL = "SELECT * FROM G5 WHERE ICAO LIKE 'LSZH' AND POSITION LIKE 'Park'"
            End If
            myCmd.CommandText = strSQL
            myCmd.Connection = myConn
            myDA.SelectCommand = myCmd
            myDR = myCmd.ExecuteReader()

            'Display results to table
            lstICAO.Items.Clear()
            While (myDR.Read())
                With lstICAO.Items.Add(myDR("ICAO"))
                    .SubItems.Add(myDR("POSITION"))
                    .SubItems.Add(myDR("POS_NR"))
                    .SubItems.Add(myDR("LATITUDE"))
                    .SubItems.Add(myDR("LONGITUDE"))
                    .SubItems.Add(myDR("RADIUS"))
                    .SubItems.Add(myDR("TRUEHEADING"))
                    .SubItems.Add(myDR("GATETYPE"))
                End With
            End While
        Catch ex As Exception
        End Try
    End Sub
End Class

The Problem here:

It does load the db perfectly and shows me the first 1000 entries.
When i enter "LSZH" in the textfield (ICAO) it shows me all LSZH entries in the table. what i want to do now is to further limmit the search, limmited to the row "POSITION".
i have a radiobutton which when i click, then limmit all entries that have the word "Park" in the column "POSITION" that does not work. Strangewise ONLY in the column POSITION??!!

not working: strSQL = "SELECT * FROM G5 WHERE ICAO LIKE 'LSZH' AND POSITION LIKE 'Park'"

works perfect: strSQL = "SELECT * FROM G5 WHERE ICAO LIKE 'LSZH' AND RADIUS LIKE '10'"

i can use every single column to further limmit the search and all work but POSITION.

seriously i have no clue.

another thing, i know "SELECT * FROM G5 WHERE ICAO LIKE 'LSZH'" actually searches only LSZH and not "SearchWord". This is wrong. The second searchlimmitation of course must search 'Park' withing the previously search result (ICAO) as far as i understand the first search pulls everything out of the table begining with {ICAO} and prints it into the datagridview when i hit enter. exactly what i want. but this data is a "array"? how can i use this whole bunch again to do a further limmitated search? (like show me everything in the column POSITION with the code "LSZH" and then when i hit the radiobutton (park) Show me all these LSZH entries which have the word "Park" in the column POSITION.

That is the first problem i need to solve (relatively urgent)

Later i need to be able to click on a row in the datagridview and have these 3 things inside 3 separate textfields : LATITUDE, LONGITUDE, HEADINGTRUE
(no multiple selection like shown in the picture. i will change that)

Thanks a lot for helping me.

Randy

[GaryMazzone]

try like this:

strSQL = "SELECT * FROM G5 WHERE ICAO LIKE 'LSZH' AND POSITION LIKE '%Park%'"

When you use like you should include the wild cards (%). Your query would only return values where Park was the only thing in the column.

[si_the_geek]

Welcome to VBForums

There is a good chance that Position is a reserved word, and if so should not be used as a field/table name (it confuses the query parser within the database system). If you change the name (in the database and your code), it is likely to work correctly.

For more information (including lists of Reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of this forum)


In addition to that, is there a particular reason you are using Like without wildcards (usually * or % to match multiple characters)? There generally isn't a reason to do that, usually you should be using = instead (or Like with wildcards).

[peroja]

Welcome to VBForums

There is a good chance that Position is a reserved word, and if so should not be used as a field/table name (it confuses the query parser within the database system). If you change the name (in the database and your code), it is likely to work correctly.

that was it! caused the trouble. thank you. problem solved