Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Hello ,

I am New to Vb.Net 2008 with Access 03

Sample Code : This Fill All Agents in Desc Order by Selected Country in DropDown
Dim Da As New SDS.OleDbDataAdapter("select AgentId,count(TripId) AS Trips from Trip where country='" & CmbCountry.Text & "'  group by AgentId order by COUNT(TripID) desc", con)
        Dim Ds As New DataSet
        Ds.Clear()
        Try
            Da.Fill(Ds, "Trip")
            DataGridView1.DataSource = Ds
            DataGridView1.DataMember = "Trip"
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

Example Output for Required Code :
Australia  India  Iran  Iraq
abc xyz abc xyz
xyc abc xyc abc
wrs wrs wrs wrs

I Have Fild Named Agent, TripID, Country Date etc.

I Fill Datagrid with top Three Agent by only one country. Its fine till here.

Now I want to Fill Datagrid with Top Three Agent for All Country like Above Showed in Example,

Like Australia has top three agent named abc, xyz & wrs

Output should be like this :
First Row = Country Names (India, Iran, Australia etc)
Second Row = Agent Names who Done maximum Trips in Related Country(First Row)
Third Row = Agent Names who Done Second maximum Trips in Related Country(First Row)
Fourth Row = Agent Names who Done Third maximum Trips in Related Country(First Row)

How Can I Do It? What's changes Required in above code?
Posted 7-Jun-11 4:33am
s1a2b3643
Edited 8-Jun-11 2:56am
v8
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I also use Datagrid but when I'm putting records to it I use this.
 
Public Sub OpenRs(ByVal str As String)
    Set RS = New ADODB.Recordset
    RS.Open str, DB, adOpenStatic, adLockOptimistic
End Sub
 
Private Sub ViewDataGrid()
    OpenRs "SELECT phone as PHONE, fname as FIRSTNAME, " & _
        "lname as LASTNAME, age as AGE, address as ADDRESS " & _
        "FROM phone WHERE user_id = " & userID & ";"
    Set dtgPhone.DataSource = RS
End Sub
 
  Permalink  
v2
Comments
s1a2b3 at 7-Jun-11 13:07pm
   
Thanks for Help but Can you Elaborate Code like Which Namespace & other Declareble Items. I request to guide me with my fields so I can understand it better. Sorry!
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

' Hi... i am trieng to solve this
' pls try my code, It is not tested properly but i hope it is OK
' trap it if little error
 
Private Sub btnImprtToGrd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
 
<pre>
   ' add refference to MS activex data object
   Dim conObj As New ADODB.Connection
   Dim RS As New ADODB.Recordset
   Dim str As String
   Dim RS1 As New ADODB.Recordset
   Dim str1 As String
   Dim colNum As Integer
   Dim i As Integer
 
   conObj = New ADODB.Connection
 
   conObj.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\fllename.mdb;Jet OLEDB:Database Password=;&quot;
   conObj.Open()
 
   str = &quot;select distinct country from trip &quot;
   RS = New ADODB.Recordset
   RS.Open(str, conObj, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
 
   RS.MoveFirst()
   colNum = 0
 
       DataGridView1.ColumnCount = rs.recordcount ' edit 4 (newly added)
       DataGridView1.ColumnHeadersVisible = True ' edit 5 (newly added)

       DataGridView1.RowCount = 3 ' edit 6 (newly added)

   Do While Not RS.EOF
 
   DataGridView1.Columns(colNum).Name = rs(&quot;country&quot;).value.tostring ' edit 1

       str1 = &quot;select AgentId,count(TripId) AS Trips from Trip where country='&quot; &amp; RS(&quot;country&quot;).Value &amp; &quot;'  group by AgentId order by COUNT(TripID) desc&quot;

       RS1 = New ADODB.Recordset
       RS1.Open(str1, conObj, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
 
       'DataGridView1.Columns(colNum).HeaderText = RS(&quot;country&quot;).Value.ToString
       'DataGridView1.Columns.Add(&quot;aaa&quot;, RS("country").Value.ToString) edit 2 (commented)
       'DataGridView1.ColumnHeadersVisible = True 'edit 3 (commented)

       RS1.MoveFirst()
       For i = 0 To 2
           DataGridView1.Item(colNum, i).Value = RS1(&quot;AgentId&quot;).Value.ToString
           RS1.MoveNext()
       Next
       colNum = colNum + 1
 
       RS.MoveNext()
   Loop

End Sub
  Permalink  
v4
Comments
s1a2b3 at 8-Jun-11 10:05am
   
Thanks for Code. I got Error in "DataGridView1.Columns(colNum).HeaderText = RS("country").Value.ToString" says : "Index out of range. Must be non-negative and less than the size of the collection." If I Manual Add 1 Column Its Fill One Country Header Name in it but then throw error. I requset you to Guide me ASAP.
Sahu.Ashok at 9-Jun-11 1:02am
   
Hello, sorry for above....... Check the changes in the code given in solution2. Remove the error line and add the two lines given below the commented line. Hope it will trap this error, thanks...
s1a2b3 at 9-Jun-11 2:46am
   
Sir, Thanks but Same error Comes in
For i = 0 To 2
DataGridView1.Item(colNum, i).Value = RS1("AgentId").Value.ToString
RS1.MoveNext()
Next
If I put
'DataGridView1.Item(colNum, i).Value = RS1("AgentId").Value.ToString
'RS1.MoveNext()
Above lines in Comment Codee fills Country Names, I also try to put boolean in loop of "i" its throw same error. Guide me for Same ASAP. Thanks for Quick Reply. . . . Waiting for Solution :)
s1a2b3 at 9-Jun-11 3:29am
   
can we try it with Reader?
Sahu.Ashok at 9-Jun-11 3:37am
   
I have not much idea about reader, I have no any access file like this, so ican't understand the problem. The error u r telling is not shown when i mannually add 3 rows in grid. Mostly it might need little change.
s1a2b3 at 9-Jun-11 4:15am
   
This code work fine for One Country
For i = 0 To 2
DataGridView1.Rows.Add(RS1("AgentId").Value.ToString)
RS1.MoveNext()
Next
s1a2b3 at 9-Jun-11 4:16am
   
How You add rows in Datagrid1 Manual ? Tell Me I also Try Same , See If I Trap the error.
Sahu.Ashok at 9-Jun-11 4:34am
   
Check in solution3 for my mannual code. Thanks...
s1a2b3 at 9-Jun-11 5:41am
   
Thanks , I Post my code in Deleted Solution 4, Check It. I also face error in Manual Code of Solution 3. Why I dont catch the error . . . :(
Sahu.Ashok at 9-Jun-11 6:31am
   
sorry, where is your code solution4, what error in sol3 ? I have not any error in that code....
s1a2b3 at 9-Jun-11 6:40am
   
Ok, I have Same "Index out of range. Must be non-negative and less than the size of the collection." in Sol.3 Code "DataGridView1.Item(colNum, i).Value = "test(" & i + 1 & "," & J + 1 & ")"
"
s1a2b3 at 9-Jun-11 7:06am
   
Traped ERROR . . . . .Yupiiiiiiiii . . . .
Thanks a lot Sir, :D , Works Fine Now . . . .! Same Code !
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Dim J As Integer
      Dim colNum As Integer
      For J = 0 To 2
          DataGridView1.Columns.Add("aaa", "COLUMN" & J)
          DataGridView1.ColumnHeadersVisible = True
          'DataGridView1.Rows.Add(RS1("AgentId").Value.ToString
          Dim i As Integer
          For i = 0 To 2
              DataGridView1.Item(colNum, i).Value = "test(" & i + 1 & "," & J + 1 & ")"
          Next i
          colNum = colNum + 1
      Next J
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Try
            ' add refference to MS activex data object
            Dim conObj As New ADODB.Connection
            Dim RS As New ADODB.Recordset
            Dim str As String
            Dim RS1 As New ADODB.Recordset
            Dim str1 As String
            Dim colNum As Integer
            Dim i As Integer
            Dim f As Boolean = False
 
<pre>
        conObj = New ADODB.Connection
        conObj.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Db.mdb;Jet OLEDB:Database Password=;"
        conObj.Open()
        str = "Select distinct (Country) from Trip "
 
        'str = "select distinct country from trip "
        RS = New ADODB.Recordset
        RS.Open(str, conObj, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
 
        RS.MoveFirst()
        colNum = 0
        Do While Not RS.EOF
 
            str1 = "Select AgentId from Trip where Country = '" &amp; RS("Country").Value &amp; "' group by AgentId order by COUNT(TripID) desc"
 
            RS1 = New ADODB.Recordset
            RS1.Open(str1, conObj, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
 
            'DataGridView1.Columns(colNum).HeaderText = RS("country").Value.ToString
            DataGridView1.Columns.Add("aaa", RS("country").Value.ToString)
            DataGridView1.ColumnHeadersVisible = True
 
            RS1.MoveFirst()
            For i = 0 To 2
 
                'DataGridView1.Rows.Add()
                'DataGridView1.Item(colNum, i).Value = RS1("AgentId").Value.ToString

'THIS CODE GIVES ERROR . . . . . . .
'Its Not working by Adding Row by Manual > Frown | :( 'RS1.MoveNext()
'f = True
DataGridView1.Rows.Add(RS1("AgentId").Value.ToString)
RS1.MoveNext()
Next
colNum = colNum + 1
 
            RS.MoveNext()
        Loop
        'If f = True Then
        '    DataGridView1.Rows.Add(RS1("AgentId").Value.ToString)
        '    'DataGridView1.RowHeadersVisible = True
        '    RS1.MoveNext()
        '    'f = False
        'End If
 
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
 
End Sub

In This Code Its Fills All Country and One Countries Agents.
What Should I Change for Fill All Contries agent in Datagrid.
  Permalink  
Comments
Sahu.Ashok at 9-Jun-11 8:20am
   
Hi dear, Chek some edits in my previus solution2. Hope it will solve this, reply me
s1a2b3 at 9-Jun-11 8:55am
   
I Traped Error. . . . . Can you suggest How to Dynamic Path in Application. for Current Path is like C:\Db.mdb; but I want like something |DataDirectory|Db.mdb

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 490
1 Sergey Alexandrovich Kryukov 405
2 Maciej Los 285
3 ProgramFOX 265
4 Peter Leow 210
0 OriginalGriff 490
1 Sergey Alexandrovich Kryukov 405
2 Maciej Los 285
3 ProgramFOX 265
4 Peter Leow 210


Advertise | Privacy | Mobile
Web04 | 2.8.150331.1 | Last Updated 9 Jun 2011
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100