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 3:33am
s1a2b3643
Edited 8-Jun-11 1: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
 
       ' 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 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\fllename.mdb;Jet OLEDB:Database Password=;"
       conObj.Open()
 
       str = "select distinct country from trip "
       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("country").value.tostring ' edit 1

 
           str1 = "select AgentId,count(TripId) AS Trips from Trip where country='" & RS("country").Value & "'  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) edit 2 (commented)
           'DataGridView1.ColumnHeadersVisible = True 'edit 3 (commented)

 
           RS1.MoveFirst()
           For i = 0 To 2
               DataGridView1.Item(colNum, i).Value = RS1("AgentId").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
 
            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 = '" & RS("Country").Value & "' 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 > :(
                    '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)



Advertise | Privacy | Mobile
Web03 | 2.8.141022.2 | Last Updated 9 Jun 2011
Copyright © CodeProject, 1999-2014
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