getting distinct req_no is a list box

Question

firoz.raj -3 Posting Pro in Training

15 Years Ago

sir i want to show only distinct row should come in list box.
any help would be greately appreciated.

Dim con As adodb.Connection
Dim rs As adodb.Recordset
Dim i As Integer
Dim strReq As String * 10
Dim strName As String * 20
Dim strJob As String * 10
Dim strDate As String * 10
Dim x As Integer
Private Sub Form_Load()
Set con = New Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\asfserver\itp$\Product_tabletest.mdb")
Set rs = New adodb.Recordset
rs.Open "SELECT * from mr ", con, adOpenDynamic, adLockOptimistic
' List1.AddItem (rs!req_no & vbNullString & " " & rs!emp_name & " " & rs!job_no & " " & rs!mr_date)
While (Not (rs.EOF))
strReq = rs!req_no & ""
strName = rs!emp_name & ""
strJob = rs!job_no & ""
strDate = rs!mr_date & ""
List1.Font = "Courier New"
List1.AddItem strReq & " " & strName & " " & strJob & " " & strDate
rs.MoveNext
Wend
End Sub

list.zip

visual-basic

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

list.zip (16.12 KB)

3 Contributors
7 Replies
101 Views
3 Days Discussion Span
Latest Post 15 Years Ago Latest Post by firoz.raj

debasisdas 580 Posting Genius

15 Years Ago

what do you mean by only distinct row .

i believe that means all the fields in a row are same.

distinct will definately filter that out.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ryan311 -7 Posting Whiz in Training · Answer 1 · 2009-01-22T18:37:40+00:00

rs.Open "SELECT Distinct * from mr ", con, adOpenDynamic, adLockOptimistic

firoz.raj -3 Posting Pro in Training · Answer 2 · 2009-01-22T18:42:03+00:00

i have tried but all the records is comming in a
list box1

rs.Open "SELECT distinct * from mr ", con, adOpenDynamic, adLockOptimistic

ryan311 -7 Posting Whiz in Training · Answer 3 · 2009-01-22T18:49:03+00:00

rs.Open "SELECT distinct(*) from mr ", con, adOpenDynamic, adLockOptimistic

firoz.raj -3 Posting Pro in Training · Answer 4 · 2009-01-24T13:10:59+00:00

i got syntax error.

Private Sub Form_Load()
  Set con = New Connection
 con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
 Set rs = New adodb.Recordset
'rs.Open "SELECT distinct req_no,emp_name,job_no,mr_date from mr  ", con, adOpenDynamic, adLockOptimistic
 [B]rs.Open "SELECT distinct(*) from mr ", con, adOpenDynamic, adLockOptimistic[/B]
' List1.AddItem (rs!req_no & vbNullString & "         " & rs!emp_name & "    " & rs!job_no & "  " & rs!mr_date)
 While (Not (rs.EOF))
 strReq = rs!req_no & ""
 strName = rs!emp_name & ""
 strJob = rs!job_no & ""
 strDate = rs!mr_date & ""
 List1.Font = "Courier New"
 List1.AddItem strReq & " " & strName & " " & strJob & " " & strDate
 rs.MoveNext
 Wend
 End Sub

ryan311 -7 Posting Whiz in Training · Answer 5 · 2009-01-25T18:31:58+00:00

try this

"Select DISTINCT req_no, emp_name, job_no, mr_date from NAME OF THE TABLE"

firoz.raj -3 Posting Pro in Training · Answer 6 · 2009-01-25T18:49:59+00:00

MRPOST.zip

i have already written distinct row not working my friend.

Private Sub Form_Load()
Set con = New Connection
con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\asfserver\itp$\Product_tabletest.mdb")
Set rs = New adodb.Recordset
rs.Open "SELECT distinct req_no,emp_name,job_no,mr_date from mr ", con, adOpenDynamic, adLockOptimistic
' List1.AddItem (rs!req_no & vbNullString & " " & rs!emp_name & " " & rs!job_no & " " & rs!mr_date)
While (Not (rs.EOF))
strReq = rs!req_no & ""
strName = rs!emp_name & ""
strJob = rs!job_no & ""
strDate = rs!mr_date & ""
List1.Font = "Courier New"
List1.AddItem strReq & " " & strName & " " & strJob & " " & strDate
rs.MoveNext
Wend
End Sub
Private Sub List1_Click()
rs.MoveFirst
MsgBox (List1.ListIndex)
'rs.Find "req_no= '" & STRREC & "' "
If List1.ListIndex >= 0 Then
If (rs.BOF = True) Or (rs.EOF = True) Then
MsgBox "Record not found"
Else
List2.AddItem rs.Fields("productname") & vbNullString
List2.AddItem rs.Fields("qty") & vbNullString
List2.AddItem rs.Fields("unit") & vbNullString
End If
End If
End Sub