How to Insert Data Row wise in Datagrid Using Vb.Net & Access Database?

Question

5.00/5 (1 vote)

See more:

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

VB

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 :

VB

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

s1a2b3

Updated 8-Dec-20 19:55pm

v8

Add a Solution

5 solutions

Solution 1

I also use Datagrid but when I'm putting records to it I use this.

VB

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

Posted 7-Jun-11 3:48am

hansoctantan

Updated 7-Jun-11 3:49am

v2

Comments

s1a2b3 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!

Solution 3

VB

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

Posted 8-Jun-11 22:32pm

Sahu.Ashok

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.

Posted 9-Jun-11 0:38am

s1a2b3

Comments

Sahu.Ashok 9-Jun-11 8:20am

Hi dear, Chek some edits in my previus solution2. Hope it will solve this, reply me

s1a2b3 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

Solution 6

Just some idea:

dim i as integer
rs.movefirst
do until rs.eof
    '<<< write datagrid 'add row' code here >>>
    for i = 0 to rs.fields.count - 1
        datagrid1.columns.add(rs.fields(i).value.toString, i)
    next
rs.movenext
loop

Other solution that might be better:
Create a table containning both Country and AgentID
Call by SQL: "Select distinct Country,AgentId from [tablename] order by country"

Posted 13-Jun-16 22:33pm

Rock4J

Updated 13-Jun-16 22:48pm

v4

Comments

CHill60 14-Jun-16 4:55am

If you are going to answer 5 year old questions that already have an accepted answer (don't) then make sure you are bringing something new to the conversation (you haven't) and that you are being accurate.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sahu.Ashok · Accepted Answer · 2011-06-08T02:36:00

' 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

VB

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

How to Insert Data Row wise in Datagrid Using Vb.Net & Access Database?

5 solutions

Solution 1

Solution 2

Solution 3

Solution 5

Solution 6

Add your solution here

Preview 0

Existing Members

...or Join us