[RESOLVED] I don't know whats wrong.. sorting by dcombo.. datagrid.

[rheanunabia]

i'm trying to sort datagrid by branch name.

Private Sub dcmbSortBranch_Click(Area As Integer)
Dim BranchSearh As String

BranchSearch = UCase(dcmbSortBranch.Text)

If BranchSearch = "" Then
Adodc1.RecordSource = "SELECT e.end_of_day_report_id, e.report_date, b.branch_name, e.total_sales, e.total_expenses, e.details_expenses, e.total_unsold, e.remarks FROM end_of_day_reports e, branch_tbl b WHERE e.branch_id=b.branch_id"
Else
    Adodc1.Recordset.MoveFirst
    Do While Adodc1.Recordset.EOF = False
        If BranchSearch = Adodc1.Recordset.Fields("branch_name") Then
            Adodc1.RecordSource = "SELECT e.end_of_day_report_id, e.report_date, b.branch_name, e.total_sales, e.total_expenses, e.details_expenses, e.total_unsold, e.remarks FROM end_of_day_reports e, branch_tbl b WHERE e.branch_id=b.branch_id AND b.branch_name =' & BranchSearch & '"
        End If
        Adodc1.Recordset.MoveNext
    Loop
    
  
End If
End Sub

[rheanunabia]

sorry.. it's not sorting.. i want to retrieve record with the branch name when i select in the dcombo...

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

I don't understand why you put in the loop etc... the only line you should have between the Else and the matching End If is the Adodc1.RecordSource one.


By the way, if you haven't seen it already I recommend reading the article Why is using bound controls a bad thing? from our Database Development FAQs/Tutorials (at the top of this forum)

That article contains a link to the recommended alternative (ADO code), which has an example that replicates the main features of the data control. There are many other articles in the "Classic VB - ADO" section of the FAQs thread (particularly "the further steps" article), which help you to extend it in many ways - like adding search functionality, working with other controls, etc.

[rheanunabia]

i changed the code:

Dim BranchSearh As String

BranchSearch = UCase(dcmbSortBranch.Text)

If BranchSearch = "" Then
Adodc1.RecordSource = "SELECT e.end_of_day_report_id, e.report_date, b.branch_name, e.total_sales, e.total_expenses, e.details_expenses, e.total_unsold, e.remarks FROM end_of_day_reports e, branch_tbl b WHERE e.branch_id=b.branch_id"
ElseIf BranchSearch = Adodc1.Recordset.Fields("branch_name") Then
Adodc1.RecordSource = "SELECT e.end_of_day_report_id, e.report_date, b.branch_name, e.total_sales, e.total_expenses, e.details_expenses, e.total_unsold, e.remarks FROM end_of_day_reports e, branch_tbl b WHERE e.branch_id=b.branch_id AND b.branch_name =' & BranchSearch & '"
End If

nothing happens

[si_the_geek]

Well this part is a problem:

AND b.branch_name =' & BranchSearch & '"

...because it will search the field b.branch_name for the text " & BranchSearch & ", rather than doing anything with your BranchSearch variable.

Take a look at the FAQ article How can I put the value of a variable/control into my SQL statement?

[rheanunabia]

i change it to this..

b.branch_name ='" & BranchSearch & "'"

still nothing is happening.. i'm just confused now..

[si_the_geek]

Well that's as far as I can help - because like most people I don't use bound-controls.

All I can suggest is that you read the other article in the "Classic VB - Data-bound controls" section of the DB FAQs, which is likely to help.

[brucevde]

What is the purpose of the ElseIf statement? If that statement returns False, nothing will happen. You just need an Else statement.

Call ADODC1.Refresh after the If statement.

[rheanunabia]

i have fixed it.. tnx

If dcmbSort.Text = "" Then
Adodc1.Refresh
Else
Adodc1.RecordSource = "SELECT e.end_of_day_report_id, e.report_date, b.branch_name, e.total_sales, e.total_expenses, e.details_expenses, e.total_unsold, e.remarks FROM end_of_day_reports e, branch_tbl b WHERE e.branch_id=b.branch_id AND b.branch_name='" & dcmbSort.Text & " '"
Adodc1.Refresh
End If
End Sub