Inner join SQL syntax in vb.net

Question

This is my select SQL syntax to display the category choices, but what I need to do is display the corresponding category chosen based on equipment_id

I got 2 tables, tblOfficeEquipmentCategory and tblOfficeEquipmentProfile

I need to inner join tblOfficeEquipmentProfile so I can add the WHERE equipment_id = '"txtid.text"'

What would be the corresponding SQL syntax

Public Sub DisplayCategory()
    'based on oe_id
    Dim sqlconn As New SqlClient.SqlConnection
    sqlconn.ConnectionString = "server = SKPI-APPS1;" & _
    "Database = EOEMS;integrated security=true"

    Dim sqlcommand As SqlCommand

    sqlconn.Open()
    Dim da As New SqlDataAdapter("select * from tblOfficeEquipmentCategory", sqlconn)
    Dim dt As New DataTable
    da.Fill(dt)
    cmbCategory.DataSource = dt
    cmbCategory.ValueMember = "CAT_Name"
    cmbCategory.DisplayMember = "CAT_ID"
    sqlconn.Close()
End Sub

John Woo · Accepted Answer · 2013-04-16 01:40:23Z

1

Assuming equipment_id is located on table tblOfficeEquipmentProfile and a column CAT_ID which links it to table tblOfficeEquipmentCategory.

SELECT  a.CAT_Name
FROM    tblOfficeEquipmentCategory a
        INNER JOIN tblOfficeEquipmentProfile b
            ON a.CAT_ID = b.CAT_ID
WHERE   b.equipment_id = @ID

To further gain more knowledge about joins, kindly visit the link below:

Visual Representation of SQL Joins

answered Apr 16, 2013 at 1:40

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ivandinglasan Over a year ago

i will try sir, im really not used in to using parameters, but thanks for the info

John Woo Over a year ago

i have seen in your previous question that you ask about parameters :D please do use it.

ivandinglasan Over a year ago

still configuring out on how to make it work sir im still debugging the code and parameters

Joel Coehoorn · Accepted Answer · 2013-04-16 03:42:20Z

0

Best to start thinking about how to separate your data access from your presentation code. You need something like this:

Public Function DisplayCategory() 
    cmbCategory.ValueMember = "CAT_Name"
    cmbCategory.DisplayMember = "CAT_ID"
    cmbCategory.DataSource = GetEquipmentCategories(Convert.ToInt32(txtid.text))
End Sub

Public Function GetEquipmentCategories(ByVal EquipmentID As Integer) As DataTable
    'based on oe_id

    Dim sql As String = "SELECT a.CAT_ID, a.CAT_Name" & _ 
                       " FROM tblOfficeEquipmentCategory a" & _
                       " INNER JOIN tblOfficeEquipmentProfile b ON a.CAT_ID = b.CAT_ID" & _
                       " WHERE b.equipment_id = @ID"

    Dim result As New DataTable
    Using cn New SqlConnection("server=SKPI-APPS1;Database=EOEMS;integrated security=true"), _
          cmd As new SqlCommand(sql, cn)

        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = EquipmentID

        cn.Open()
        Using rdr As SqlDataReader = cmd.ExecuteReader
            result.Load(rdr)
        End Using
    End Using
    Return result
End Function

answered Apr 16, 2013 at 3:42

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

2 Comments

ivandinglasan Over a year ago

sir ID contains char and integer ex. oeq-su-001 what would be the corresponding sqldbtype

Joel Coehoorn Over a year ago

Match the sqldbtype to the column definition in sql server. Look here for the list of possibilities: msdn.microsoft.com/en-us/library/system.data.sqldbtype.aspx

مهندس إستشارى مصطفى الوكيل · Accepted Answer · 2020-06-24 07:48:36Z

0

        Dim sql = "Select  Authors.Au_ID, Authors.Author, [Title Author].ISBN FROM(Authors INNER JOIN [Title Author] ON Authors.Au_ID = [Title Author].Au_ID)"

answered Jun 24, 2020 at 7:48

مهندس إستشارى مصطفى الوكيل

111 bronze badge

Comments

مهندس إستشارى مصطفى الوكيل · Accepted Answer · 2020-06-24 08:08:32Z

0

   Try
        con.Open()
        Dim cmd As New OleDb.OleDbCommand
        Dim dt As New DataTable
        Dim da As New OleDb.OleDbDataAdapter
        Dim sql = "Select  Authors.Au_ID, Authors.Author, [Title Author].ISBN FROM(Authors INNER JOIN [Title Author] ON Authors.Au_ID = [Title Author].Au_ID)"
        cmd.Connection = con
        cmd.CommandText = sql
        da.SelectCommand = cmd
        da.Fill(dt)
        DGV2.DataSource = dt
        con.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

answered Jun 24, 2020 at 8:08

مهندس إستشارى مصطفى الوكيل

111 bronze badge

Collectives™ on Stack Overflow

Inner join SQL syntax in vb.net

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related