none
how to show specific information from another table? RRS feed

  • Question

  • hello guys. i have a table in my sql cities and students. how do I show all students living from a selected city? i have a gridview of the cities and if I click a city, all students from that city will show, but I don't know what to do. I need your help guys. thanks:)
    Tuesday, November 13, 2018 2:36 PM

Answers

  • Hello,

    Hope below code helps you

    Create Two Table

    CREATE TABLE [dbo].[cities](
    	[CityName] [varchar](max) NULL
    ) 
    CREATE TABLE [dbo].[students](
    	[Name] [varchar](max) NULL,
    	[city] [varchar](max) NULL
    ) 
    


    Imports System.Data.SqlClient
    
    Public Class Form16
        'Load Cities
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Try
                Dim myConn As SqlConnection = New SqlConnection("Initial Catalog=Test;" & _
                        "Data Source=PGK_IDEAPAD\SQLSERVER2012;Integrated Security=True;")
                myConn.Open()
                Dim cmd As SqlCommand = New SqlCommand("Select * from cities", myConn)
                Dim rd As SqlDataReader = cmd.ExecuteReader()
                Dim dt As New DataTable
                dt.Load(rd)
                DataGridView1.DataSource = dt
                myConn.Close()
            Catch ex As Exception
                Dim errormsg As String = String.Empty
                errormsg = ex.ToString()
            End Try
        End Sub
        Private Sub Form16_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Try
                Dim myConn As SqlConnection = New SqlConnection("Initial Catalog=Test;" & _
                        "Data Source=PGK_IDEAPAD\SQLSERVER2012;Integrated Security=True;")
                myConn.Open()
                Dim cmd As SqlCommand = New SqlCommand("Select * from students", myConn)
                Dim rd As SqlDataReader = cmd.ExecuteReader()
                Dim dt As New DataTable
                dt.Load(rd)
                DataGridView3.DataSource = dt
                myConn.Close()
            Catch ex As Exception
                Dim errormsg As String = String.Empty
                errormsg = ex.ToString()
            End Try
        End Sub
    
        Private Sub DataGridView1_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
            Try
                If e.RowIndex > -1 Then
                    Dim selected_city As String = String.Empty
                    selected_city = DataGridView1(e.ColumnIndex, e.RowIndex).Value.ToString()
                    Dim myConn As SqlConnection = New SqlConnection("Initial Catalog=Test;" & _
                        "Data Source=PGK_IDEAPAD\SQLSERVER2012;Integrated Security=True;")
                    myConn.Open()
                    Dim cmd As SqlCommand = New SqlCommand("Select Name from students where city = '" + selected_city + "'", myConn)
                    Dim rd As SqlDataReader = cmd.ExecuteReader()
                    Dim dt As New DataTable
                    dt.Load(rd)
                    DataGridView2.DataSource = dt
                    myConn.Close()
                End If
            Catch ex As Exception
                Dim errormsg As String = String.Empty
                errormsg = ex.ToString()
            End Try
        End Sub
    End Class

    • Marked as answer by Judzzz Friday, November 16, 2018 1:20 AM
    Tuesday, November 13, 2018 4:59 PM

All replies

  • https://docs.oracle.com/cd/E17952_01/connector-net-en/connector-net-tutorials-entity-framework-winform-data-source.html

    The Entity Framework uses Linq. You see Linq being used in the above tutorial.

    http://www.tutorialsteacher.com/linq/why-linq

    However, EF doesn't work wit Access if that's what you are using. You will need to use MS SQL Server Express. 

    You could use VS's Service Based Database to get started, which is a MS SQL Server Express MDF file using Localdb.

    https://docs.microsoft.com/en-us/visualstudio/data-tools/create-a-sql-database-by-using-a-designer?view=vs-2017

    https://blogs.msdn.microsoft.com/sqlexpress/2011/07/12/introducing-localdb-an-improved-sql-express/

    Tuesday, November 13, 2018 4:46 PM
  • Here EF Tutorial is in VB.NET that Karren has provided, which you can apply to links in my first post.

    https://code.msdn.microsoft.com/windowsapps/Entity-Framework-6-for-06edd4a8

    Tuesday, November 13, 2018 4:52 PM
  • Hello,

    Hope below code helps you

    Create Two Table

    CREATE TABLE [dbo].[cities](
    	[CityName] [varchar](max) NULL
    ) 
    CREATE TABLE [dbo].[students](
    	[Name] [varchar](max) NULL,
    	[city] [varchar](max) NULL
    ) 
    


    Imports System.Data.SqlClient
    
    Public Class Form16
        'Load Cities
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Try
                Dim myConn As SqlConnection = New SqlConnection("Initial Catalog=Test;" & _
                        "Data Source=PGK_IDEAPAD\SQLSERVER2012;Integrated Security=True;")
                myConn.Open()
                Dim cmd As SqlCommand = New SqlCommand("Select * from cities", myConn)
                Dim rd As SqlDataReader = cmd.ExecuteReader()
                Dim dt As New DataTable
                dt.Load(rd)
                DataGridView1.DataSource = dt
                myConn.Close()
            Catch ex As Exception
                Dim errormsg As String = String.Empty
                errormsg = ex.ToString()
            End Try
        End Sub
        Private Sub Form16_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            Try
                Dim myConn As SqlConnection = New SqlConnection("Initial Catalog=Test;" & _
                        "Data Source=PGK_IDEAPAD\SQLSERVER2012;Integrated Security=True;")
                myConn.Open()
                Dim cmd As SqlCommand = New SqlCommand("Select * from students", myConn)
                Dim rd As SqlDataReader = cmd.ExecuteReader()
                Dim dt As New DataTable
                dt.Load(rd)
                DataGridView3.DataSource = dt
                myConn.Close()
            Catch ex As Exception
                Dim errormsg As String = String.Empty
                errormsg = ex.ToString()
            End Try
        End Sub
    
        Private Sub DataGridView1_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
            Try
                If e.RowIndex > -1 Then
                    Dim selected_city As String = String.Empty
                    selected_city = DataGridView1(e.ColumnIndex, e.RowIndex).Value.ToString()
                    Dim myConn As SqlConnection = New SqlConnection("Initial Catalog=Test;" & _
                        "Data Source=PGK_IDEAPAD\SQLSERVER2012;Integrated Security=True;")
                    myConn.Open()
                    Dim cmd As SqlCommand = New SqlCommand("Select Name from students where city = '" + selected_city + "'", myConn)
                    Dim rd As SqlDataReader = cmd.ExecuteReader()
                    Dim dt As New DataTable
                    dt.Load(rd)
                    DataGridView2.DataSource = dt
                    myConn.Close()
                End If
            Catch ex As Exception
                Dim errormsg As String = String.Empty
                errormsg = ex.ToString()
            End Try
        End Sub
    End Class

    • Marked as answer by Judzzz Friday, November 16, 2018 1:20 AM
    Tuesday, November 13, 2018 4:59 PM
  • it works! thanks!:)
    • Edited by Judzzz Thursday, November 15, 2018 5:49 PM
    Thursday, November 15, 2018 2:54 PM
  • thanks:)
    Thursday, November 15, 2018 2:59 PM
  • Hi,

    I am glad you have got your solution. It would be appreciated if you could share us your solution and then mark it as an answer.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 16, 2018 1:03 AM