Please help - Loading a datagrid from relational database

[ProgMan]

Hi everyone,

I know this is a very easy question for many of you people but I've been trying to solve it since yesterday and can't figure it out.

First, I have two tables 'Student_Module' (fields are StudID and ModID) and 'Module' (fields are ModuleName and ModuleID).

Say, I have a combo box in my form filled with student Id (I'm using VS2005).

What I'm trying to do is get the id from the combobox and use it to find the module names associated with that id from the module table. So, first I'm making query to the Student_Module table to find all ModuleIDs associated with the StudentID and then with that resultset looking into the Module table to find the module names.

The problem is I can't join these two statements into one sql command that will load the desired data into a datagrid.

Please help me if you can. I'm really helpless !

Thanks.

Edit : I tried the following code which I got from the MSAccess query wizard. It works but what I want to do is use studentid from the combobox and make the query.

SELECT Modul.ModuleName
FROM (Modul INNER JOIN Student_Modul ON Modul.ModuleId = Student_Modul.ModID) INNER JOIN Student ON Student_Modul.StudID = Student.Id

 

[hadinatayp]

try this,

Select m.ModuleName from Module m inner join Student_Module s on m.ModuleID=s.ModID
where s.StudID='Id from combo box'

 

[ProgMan]

Thanks hadinatayp...

I'm not sure I understand this but I'm gonna try it.

Edit : How can I execute a sql command in runtime ? I'm using the following...

Private myADOConnect As New ADODB.Connection()
Private myRS As New ADODB.Recordset()
 
myADOConnect.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\school.mdb")

But how can I use the sql statement ?

 

[ProgMan]

Ok...

The code seems to be working (provided by hadinatayp). So, far I've tested it only within the query builder.

Lemme check if I can use it with my form.

Thanks again for the guide.

I'll come back later.

 

[hadinatayp]

        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\school.mdb")
        Dim cmd As New OleDbCommand("select * from Student", conn)
        Dim reader As OleDbDataReader
        conn.open()

        'if the sql returns rows
        reader = cmd.ExecuteReader()

        'if the sql isn't returing any row
        cmd = New OleDbCommand("delete from student", conn)
        cmd.ExecuteNonQuery()

        'if the sql return exactly 1 row & col
        Dim totalStudent As Integer
        cmd = New OleDbCommand("select count(*) from student", conn)
        totalStudent = CInt(cmd.ExecuteScalar())

 

[ProgMan]

Hi hadinatayp,

I'm trying to fill the datgrid using a button and by the following codes

[SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] [B]Button1_Click[/B]([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].ModulTableAdapter.FillBy([/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].TestDataSet.Modul)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub [/COLOR][/SIZE]

where 'FillBy' contains the query

SELECT m.ModuleName FROM (Modul m INNER JOIN Student_Modul s ON m.ModuleId = s.ModID) WHERE  (s.StudID = textbox1.TEXT)

But its returning nothing... (It works if i use a predefined specific id like WHERE (s.StudID = '12345')

 

[hadinatayp]

button1 :: OnClick

Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\school.mdb")
        Dim sql As String = "SELECT m.ModuleName FROM Modul m INNER JOIN Student_Modul s ON m.ModuleId = s.ModID WHERE s.StudID='" + textbox1.TEXT + "'"
        Dim dss As New DataSet()
        Dim daa As New OleDbDataAdapter(sql, conn)
        daa.Fill(dss, "Module")

        DataGridView1.DataSource = dss
        DataGridView1.DataMember = "Module"

 

[ProgMan]

I simply can't thank enough hadinatayp for your kind support throughout the day.

I was able to solve the problem by modifying the fillby method of the tableadapter and using a filter although it has cost me a whole day

However, I have copied down the code you have provided with 'oledb' which i'll need later.

Thanks again my friend for your kind cooperation. You are the man