Solved

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

Posted on 2010-08-28
14
551 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:GlobaLevel
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33549576
Hi ...if the sorting is your problem ....where is your sort method....?You may set an ORDER BY Clause in your SQL Statement....
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33549596
Not sure in .net what the sort method would look like
0
 
LVL 9

Expert Comment

by:puru1981
ID: 33549597
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33549634
as i said just set an ORDER BY clause in your sql statement
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33549671
How can I sort my query based on if statements with datareader then
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33549676
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
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 33556414
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 33556418
I guess technically I should say you would be sorting the DataView, not the DataTable.
0
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33559049
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33559054
otherwise I have to open up a ton of connections to the db...
0
 
LVL 9

Expert Comment

by:puru1981
ID: 33559193
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
 
LVL 10

Author Comment

by:GlobaLevel
ID: 33559620
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
 
LVL 9

Accepted Solution

by:
puru1981 earned 500 total points
ID: 33560610
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
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33561457
The question was about sorting ....and the accepted solution is an answer to another question despite that was correct...:)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now