vb.net - not sorting thru datareader correctly...

this isnt working for me...

-=-=-=-

Private Sub VERIFY_CLIENT_RESPONSE_client_stored_EMAIL()
        ' SO IF THE CLIENT IS STORING THE MASTER FILES TOO BE COPIED
        ' LETS

        Dim client_response As New List(Of String)
        Dim client_stores_files As New List(Of String)

        Dim dataReader As SqlDataReader

        Try

            ' ---=-=-=-=-=
            '
            '
            ' "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"


            Dim x As Integer = 0

            Dim sqlConnection As String

            Using sqlConn As New SqlConnection

                sqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=xx;User Id=;Password=;"
                sqlConn.ConnectionString = sqlConnection

                Dim SQLString As String = "SELECT cr.EMAIL_RESPONSE, cr.color_number, cr.paper_line, pt.product_weight, pt.paper_legal FROM CLIENT_RESPONSE cr JOIN paper_table pt ON pt.client_id = cr.client_id WHERE server_name=" & server_name & _
                    "and P_NAME = " & P_NAME

                ' Dim dataReader As SqlDataReader
                sqlConn.Open()
                Using command As New SqlCommand(SQLString, sqlConn)
                    dataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                    Do While dataReader.Read()
                        client_response.Add(dataReader.GetValue(0).ToString())
                    Loop
                    dataReader.Close()
                    sqlConn.Close()
                End Using
            End Using

            'Retrieve the number of record.
            x = client_response.Count

            MsgBox("CLIENT FILE PATHS ARE NOT FOUND...")
            'UsernameTextBox.Focus()

           If client_response.Exists(dataReader(x)) = "legal" Then
                switch_paper_to8_x _11()
            End If

            If client_response.Exists(dataReader(x)) = "red" Then
                switch_color_to_red()
            End If

            If client_response.Exists(dataReader(x)) = "blue" Then
                switch_color_to_blue()
            End If


            If client_response.Exists(dataReader(x)) = "green" Then
                switch_color_to_green()
            End If




        Catch ex As FileNotFoundException
        Catch ex As Exception
            MessageBox.Show("Please reJECT: DL ISSUE_sql connection....BODY: ERROR: " + ex.Message)
        End Try

    End Sub
LVL 10
GlobaLevelProgrammerAsked:
Who is Participating?
 
puru1981Connect With a Mentor Commented:
have you used case construct in SQL?

http://msdn.microsoft.com/en-us/library/ms181765.aspx

secondly,

you can use datatables and dataset and datarelation to minimize the looping.

third
you can use select and filter in datatable.

you have a lot of choices.
0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
Hi ...if the sorting is your problem ....where is your sort method....?You may set an ORDER BY Clause in your SQL Statement....
0
 
GlobaLevelProgrammerAuthor Commented:
Not sure in .net what the sort method would look like
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
puru1981Commented:
what is not working here

after closing the datareader you can't use datareader.

Secondly, datareader is not a connection less object and uses forward only cursor so when it reaches to the last record it can't go back and check for any index you are trying to read.
0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
as i said just set an ORDER BY clause in your sql statement
0
 
GlobaLevelProgrammerAuthor Commented:
How can I sort my query based on if statements with datareader then
0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
Dim SQLString As String = "SELECT cr.EMAIL_RESPONSE, cr.color_number, cr.paper_line, pt.product_weight, pt.paper_legal FROM CLIENT_RESPONSE cr JOIN paper_table pt ON pt.client_id = cr.client_id WHERE server_name=" & server_name & _
                    "and P_NAME =  & P_NAME  & "ORDER BY ........."
0
 
käµfm³d 👽Commented:
You can't sort within the datareader. You can either sort in SQL (as jtoutou demonstrates), or load the reader into a DataTable and sort the DataTable:
Dim dt As DataTable
Dim myDataView As DataView

dt.Load(dataReader)
myDataView = dt.DefaultView
myDataView.Sort = "color_number DESC"

Open in new window

0
 
käµfm³d 👽Commented:
I guess technically I should say you would be sorting the DataView, not the DataTable.
0
 
GlobaLevelProgrammerAuthor Commented:
I changed it per your recommendations...narrowed down the issue..

Part of the problem is trying to find fields that have the same values across various columns..then once located feed into a variable...

Please have a look, if I have issues with the syntax...


Private Sub VERIFY_CLIENT_RESPONSE_client_stored_EMAIL()
        ' SO IF THE CLIENT IS STORING THE MASTER FILES TOO BE COPIED
        ' LETS

        Dim client_response As New List(Of String)
        Dim client_stores_files As New List(Of String)

        Dim dataReader As SqlDataReader

        Try

            ' ---=-=-=-=-=
            '
            '
            ' "Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"


            Dim x As Integer = 0

            Dim sqlConnection As String

            Using sqlConn As New SqlConnection

                sqlConnection = "Data Source=xx.xx.xx.xx;Initial Catalog=xx;User Id=;Password=;"
                sqlConn.ConnectionString = sqlConnection

                Dim SQLString As String = "SELECT cr.EMAIL_RESPONSE, cr.color_number, cr.paper_line, pt.product_weight, pt.paper_legal FROM CLIENT_RESPONSE cr JOIN paper_table pt ON pt.client_id = cr.client_id WHERE server_name=" & server_name & _
                    "and P_NAME = " & P_NAME & "ORDER BY cr.email_response asc, cr.color_number asc,cr.paper_line asc"

                ' Dim dataReader As SqlDataReader
                sqlConn.Open()
                Using command As New SqlCommand(SQLString, sqlConn)
                    dataReader = command.ExecuteReader(CommandBehavior.CloseConnection)
                    Do While dataReader.Read()
                        client_response.Add(dataReader.GetValue(0).ToString())
                    Loop


                  '-------------------------------------------------------------------------------

                  ' SORT TO DATAVIEW...THEN FEED INTO VARIABLES TO BE USED LATER....
                  '       NOT GOOD TO KEEP CONNECTION OPEN FOR TOO LONG
                  ' IF COLUMN = 'THIS' THEN FEED INTO VARAIBLE
                  ' BECAUSE SIMILIAR VALUES EXIST IN DIFFERENT COLUMNS

                  Dim dt As DataTable
                  Dim myDataView As DataView

                  dt.Load(dataReader)
                  myDataView = dt.DefaultView
                  myDataView.Sort = "color_number DESC"

                  if dt.cr.paper_line = "y" and dt.cr.EMAIL_RESPONSE = "y" then
                            dim client_response_wants_legal_paper as string
                        dt = client_response_wants_legal_paper
                  end if

      
                  if dt.cr.color_number = "y" and dt.cr.vm_RESPONSE = "y" then
                            dim client_vm_wants_red_paper as string
                        dt = client_vm_wants_red_paper
                  end if

                  if dt.pt.product_weight = "y" and dt.cr.vm_RESPONSE = "y" then
                            dim client_vm_wants_weighted_paper as string
                        dt = client_vm_wants_weighted_paper
                  end if

      
                  '-------------------------------------------------------------------------------


                    dataReader.Close()
                    sqlConn.Close()
                End Using
            End Using

            'Retrieve the number of record.
            x = client_response.Count

            MsgBox("CLIENT FILE PATHS ARE NOT FOUND...")
            'UsernameTextBox.Focus()

      '-------------------------------------------------------------------------------
      '
      ' NOW THAT THE VARAIBLES HAVE BEEN SET...
      ' IF IT EXISTS...REDIRECT TO ANOTHER FUNCTION IN THE PROGRAM..

           If client_response_wants_legal_paper <> "" Then
                switch_paper_to8_x _14()
            End If

            If client_vm_wants_red_paper <> "" Then
                switch_color_to_red()
            End If

            If client_vm_wants_weighted_paper <> "" Then
                switch_paper_to_weighted()
            End If



        Catch ex As FileNotFoundException
        Catch ex As Exception
            MessageBox.Show("Please reJECT: DL ISSUE_sql connection....BODY: ERROR: " + ex.Message)
        End Try

    End Sub
0
 
GlobaLevelProgrammerAuthor Commented:
otherwise I have to open up a ton of connections to the db...
0
 
puru1981Commented:
can you clarify the question.

it would be better if you can write the requirement clearly as i am not able to understand the requirement.
0
 
GlobaLevelProgrammerAuthor Commented:
hopefully this simplifies...

1) I would to query a ms sql 2005 db on several tables
2) if columnA = 'x' and columnC = 'y' where id = 1 then this var1 = 'true'
3) if columnc = 'x' and columnd = 'y' where id = 2 then this var2 = 'true'
4) if var1='true' then
      run_this_func()
   end if
5) if var2='true'then
      run_that_func()
   end if
0
 
John (Yiannis) ToutountzoglouInstructor Multiengine PilotCommented:
The question was about sorting ....and the accepted solution is an answer to another question despite that was correct...:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.