data from multiple tables

[Kartikey]

hello sir ,

Q:-How to display data in datagrid from multiple tables in SQL server 2000 and vb.net 2003
its very imp for my project

i have tried with following code
Pl tell me the correct way to do it

tables i am using are

TABLES COLUMN CONSTRAINTS COLUMN NAMES

t_candidates reg_no primary key , f_name ,email_id
t_additioinalinfo reg_no foreign key , cat_id foreign key
t_category cat_id primary key category (this column ontains actual category values)

t_result reg_no foreign key, rc_name ,salary

Dim ds As DataSet
ds = New DataSet
Dim objshortlistedresume As New frmshortlisted
Dim str As String
Try
str = "select reg_no,category,rc_name from t_candidates" & _
"Join t_result on t_candidates.reg_no=t_result.reg_no" & _
"join t_additionalinfo on t_category.cat_id =t_additionalinfo.cat_id" & _
"join t_additionalinfo on t_result.reg_no=t_additionalinfo.reg_no"

Catch exp As SqlException
MsgBox(exp.Message, MsgBoxStyle.Critical, "SQL error")
Catch exp As Exception
MsgBox(exp.Message, MsgBoxStyle.Critical, "General error")
End Try

Dim mydataadapter As SqlDataAdapter = New SqlDataAdapter(strshortlisted, myconn)

mydataadapter.SelectCommand = New SqlCommand
mydataadapter.SelectCommand.Connection = myconn
mydataadapter.SelectCommand.CommandText = str
mydataadapter.SelectCommand.CommandType = CommandType.Text

Try
myconn.Open()

mydataadapter.SelectCommand.ExecuteNonQuery()
mydataadapter.Fill(ds, "t_candidates")
myconn.Close()
' dv = New DataView(ds.Tables("t_candidates"))

objshortlistedresume.dg_shortlisted.DataSource = ds
objshortlistedresume.dg_shortlisted.DataMember = "t_candidates"

Catch exp As SqlException
MsgBox(exp.Message, MsgBoxStyle.Critical, "SQL error")
Catch exp As Exception
MsgBox(exp.Message, MsgBoxStyle.Critical, "General error")
End Try

objshortlistedresume.Show()
Me.Hide()

[Jx_Man]

maybe on select statment for join tables

[Jx_Man]

this is my code when read data from multiple tables, in my code just for two tables, you can change to three or more tables.

Private Sub Show_Datagrid()
        Dim cmdUser As New SqlCommand
        Dim daUser As New SqlDataAdapter
        Dim dsUser As New DataSet
        Dim dtUser As New DataTable

        conn = GetConnect()
        Try
            cmdUser = conn.CreateCommand
            cmdUser.CommandText = "SELECT a.Id_User as 'User Id', a.Password , b.Id_Role as 'Role Id', b.Status FROM USERS a join ROLE b on (a.Id_Role=b.Id_Role)"
            daUser.SelectCommand = cmdUser
            daUser.Fill(dsUser, "User")
            dgUser.DataSource = dsUser
            dgUser.DataMember = "User"
            dgUser.ReadOnly = True
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Error Connection!!")
        End Try
    End Sub

[chan_lemo]

data from multiple table using dataset

http://vb.net-informations.com/dataset/dataset-multiple-tables-sqlserver.htm

lemo