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()

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
This article has been dead for over six months. Start a new discussion instead.