really strange problem i have no explanation

[peroja]

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

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

 

[JuggaloBrotha]

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

You're on the right track with those queries:
You're using the 'Like' keyword without using the wildcard character (which is %) so this:

SELECT * FROM G5 WHERE ICAO LIKE 'LSZH' AND POSITION LIKE 'Park'

Is the same thing as this:

SELECT * FROM G5 WHERE ICAO = 'LSZH' AND POSITION = 'Park'

and that's why you're getting only the records where ICAO equals LSZH, if you want to include all records that start with 'LSZH' then you need to change the query to:

SELECT * FROM G5 WHERE ICAO LIKE 'LSZH%'

If you want it pull records that end with 'LSZH':

SELECT * FROM G5 WHERE ICAO LIKE '%LSZH'

If you want it pull records that contain 'LSZH' anywhere in it:

SELECT * FROM G5 WHERE ICAO LIKE '%LSZH%'

Same applies to the Position column:

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

So what you'll need to do is simply change the sql query in code whenever the criteria changes (which would include when the radio button selection is changed) and re-query the database.

 

[Paszt]

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.

You would typically use disconnected datasources (for example a DataTable) and DataBinding.

• Fill a DataTable using the query.
• DataBind a BindingSource to the DataTable.
• DataBind your BindingSource to the DataGridView.

Now you can set properties on the BindingSource (such as the BindingSource.Filter Property) to limit what data is displayed. There is no need for a second trip to the database when you already have the data in memory.

Databinding will also help you with this part:

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

Bind the TextBoxes to the same BindingSource and the BindingSource keeps the controls in sync; if edits are made in the TextBoxes, the DataGridView will reflect those changes, and vice-versa.

---

Now as an aside, I personally use the data wizards supplied by visual studio to create much of this code for me. There are also visual query builders which make debugging SQL queries much quicker. I'd tell you how to get to them, but I don't have Visual Studio in front of me.

 

[Herman]

You're on the right track with those queries:
You're using the 'Like' keyword without using the wildcard character (which is %) so this:

SELECT * FROM G5 WHERE ICAO LIKE 'LSZH' AND POSITION LIKE 'Park'

Is the same thing as this:

SELECT * FROM G5 WHERE ICAO = 'LSZH' AND POSITION = 'Park'

No!

LIKE takes into account collation, ignores trailing spaces in the compared string, and can have case repercussions depending on the DBMS used... For example:

'ABC' LIKE 'abc' might be true or false depending on the database server used

'ABC___' LIKE 'ABC' is true, but 'ABC___' = 'ABC' is false (spaces replaced by _)

'=' is a simple and fast equality operator, no ifs buts or whys. LIKE is a completely different operator, that might or might not give you the same result depending on many variables.