CODEWITHSUNDEEP
vb.netdatagridview
Pradip Dhakal
Pradip Dhakal

Reputation: 1962

DatagridView get data from ms-access 2 different table

I have a 2 ms-access table like that:

student                     library
----------------------      ----------------------
|ID  | Name | Address |     |s_ID   | book | date |
|10  | John | add1    |     | 20    | book1| 03   |
|20  | Joe  | add2    |     | 30    | book2| 05   |
|30  | User3| add3    |     ----------------------

SO I want all that data in one single datagridview like that:

datagridview1
----------------------------------
|S.N | ID  | Name |  book  | date |
| 1  | 10  | John |   -    |  -   |
| 2  | 20  | Joe  | book1  | 03   |
| 3  | 30  | User3| book2  |  05  |

I tried this:

dim i as Integer = 1
sql = "SELECT ID, Name FROM student"
cmd = New OleDbCommand(sql, connection)
reader = cmd.ExecuteReader()
If Not reader.HasRows Then
   DataGridView1.Rows().Clear()
ElseIf reader.HasRows Then
   Do While reader.Read
      Dim row() As String = {CStr(i), reader.Item(0), reader.Item(1),"",""}
      DataGridView1.Rows.Add(row)
      i += 1
   Loop
End If

I don't know how to do further.

How can I get that? Or any other methods?

Any help is appreciated. Thank You

Upvotes: 0

Views: 43

Answers (1)

Visakh R krishnan
Visakh R krishnan

Reputation: 99

Try this out 

  Dim sql As String = "select  row_number() over (order by ID) as S.N, ID, Name, book, date from student left outer join library on id=s_id"
    Dim dataadapter As New SqlDataAdapter(sql, connection)
    Dim ds As New DataSet()
    connection.Open()
    dataadapter.Fill(ds, "ID")
    connection.Close()
    DataGridView1.DataSource = ds

Upvotes: 1

Related Questions