query string not working right

[Andy]

I've got this string:

SQLCMD.CommandText = "select usoc_code, usoc_description " & _
     "from features " & _
     "WHERE telephone_number = '" & RemoveHyphen(telephone) & "' " & _
      "and (usoc_code = 'eamntc' or " & _
      "usoc_code = 'ramntc' or " & _
      "usoc_code = 'ranmntc' or " & _
      "usoc_code = 'bamntc' or " & _
      "usoc_code = 'baiw' or " & _
      "usoc_code = 'banmntc') "

I can run that in Query Analyzer and it works BUT when used in my app, the dataReader doesn't get populated meaning (to me) the query doesn't pass any of the 'or's' . One thing to note is that when I take out the or's, I get back data.

is there something wrong with the syntax or is there a limitation to a datareader I don't know about?

also, the data in the reader is going to the statement here:

reader = SQLCMD.ExecuteReader
        reader.Read()
 
        While reader.Read
 
            MainForm.Ticketing.tab_0_lstMaintOptions.Items.Add(reader.Item("usoc_code") & " - " & reader.Item("usoc_description"))
 
        End While
 
        MainForm.Ticketing.tab_0_lstMaintOptions.Update()

[si_the_geek]

I dont use .Net so there might be an issue with it

You could always use the alternative SQL:

..
"WHERE telephone_number = '" & RemoveHyphen(telephone) & "' " & _
      "and usoc_code IN ('eamntc', 'ramntc', 'ranmntc', 'bamntc', 'baiw', 'banmntc') "

[salvelinus]

Probably nothing, but try taking out the extra space before the last quote.

[Andy]

I'd forgotten about the IN keyword. Thanks, that saves some typing! unfortunately, the same behavior is working.

I just don't under stand why a simple SELECT statement works but by adding some filtering, it doesn't.

[Andy]

Originally posted by salvelinus
Probably nothing, but try taking out the extra space before the last quote.

nope that doesn't work either.

I should note that the 'while' block gets skipped. it hits the "while' and then goes straight to 'end while' and never gets to the code inside of it.

[salvelinus]

I use oledb, so maybe it's different, but I don't loop through the reader that way. Here's an example of how I do it (a generic function to fill a listbox)

Public Shared Sub FillListBox(ByVal strSQL As String, ByVal lb As ListBox, ByVal strItem As String, _
                            ByRef cn As OleDbConnection, ByRef cmd As OleDbCommand, ByRef dr As OleDbDataReader)
        Try
            cn.Open()
            cmd.CommandText = strSQL
            dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            lb.Items.Clear()
 
            Do While dr.Read
                lb.Items.Add(dr(strItem))
            Loop
            dr.Close()
 
        Catch ex As Exception
            MessageBox.Show(ex.Message & Environment.NewLine & "Closing program.", "Error Finding Modules", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Application.Exit()
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
    End Sub

[Andy]

Here's something interesting:
The below code works. But, if I uncomment the commented sections and comment out the other parts (code is below), it doesn't. There must be something about the while statment. BUT, i have another function that has a 'while' statement...setup EXACTLY like these and it works fine!

'Dim item As String
 
            SQLCMD.CommandText = "Select usoc_code, usoc_description " & _
                                                            "From Features " & _
                                                            "Where (member_number = '" & RepairTicket.MemberNumber & "') and " & _
                                                            "USOC_CODE in ('dslntv' ,'dsl1' ,'dsl3' ,'dsl5', 'dsl3em')"
 
            reader = SQLCMD.ExecuteReader
            reader.Read()
 
            'While reader.Read
            '    item = reader.Item("usoc_code") & " - " & reader.Item("usoc_description")
            '    MainForm.Ticketing.tab_11_lstDSL.Items.Add(item)
            'End While
 
            'DSL Plan
            Try
                RepairTicket.DSLplan = reader.Item("usoc_code") & " - " & reader.Item("usoc_description")
            Catch ex As Exception
 
            End Try
            reader.Close()

now, the one that DOESNT work:

Dim item As String
 
            SQLCMD.CommandText = "Select usoc_code, usoc_description " & _
                                                            "From Features " & _
                                                            "Where (member_number = '" & RepairTicket.MemberNumber & "') and " & _
                                                            "USOC_CODE in ('dslntv' ,'dsl1' ,'dsl3' ,'dsl5', 'dsl3em')"
 
            reader = SQLCMD.ExecuteReader
            reader.Read()
 
            While reader.Read
                item = reader.Item("usoc_code") & " - " & reader.Item("usoc_description")
                MainForm.Ticketing.tab_11_lstDSL.Items.Add(item)
            End While
 
            ''DSL Plan
            'Try
            '    RepairTicket.DSLplan = reader.Item("usoc_code") & " - " & reader.Item("usoc_description")
            'Catch ex As Exception
 
            End Try
            reader.Close()

[Andy]

ok, I figured out what is the problem:

right above my While statement, I had reader.read. this was causing a conflict, I suppose. I took out the reader.read and it works fine now

whew!,, 5 hours working on that small problem!!!!