Convert hard coded values to dynamic values

Question

Hi guys,

Please forgive me if this question is too simple-minded.

I have the following code and as you can see, the values are hardcoded.

    Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not Me.IsPostBack Then
            Dim dt As New DataTable()
            dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), _
                                                   New DataColumn("Name", GetType(String)), _
                                                   New DataColumn("Email", GetType(String))})
            dt.Rows.Add(1, "John Doe", "john.doe@test.com")
            dt.Rows.Add(2, "Jane Doe", "jane.doe@test.com")
            dt.Rows.Add(3, "Jerry McGuire", "jerry.mcguire@test.com")
            dt.Rows.Add(4, "Mary Retton", "mary.retton@test.com")
            GridView1.DataSource = dt
            GridView1.DataBind()
        End If
    End Sub

How do I change to values to dynamic values so they are generated from the database?

For instance, these:

            dt.Rows.Add(Id, Name, "john.doe@test.com") 
            dt.Rows.Add(2, "Jane Doe", "jane.doe@test.com")           
            dt.Rows.Add(3, "Jerry McGuire", "jerry.mcguire@test.com")  
            dt.Rows.Add(4, "Mary Retton", "mary.retton@test.com")

We would prefer to have something like this:

dt.Rows.Add(Id, Name, email)  

Answer 1

Imports System.Data.SqlClient ' Assuming you are using SQL Server, adjust accordingly for your database

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As New DataTable()
        dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), _
                                               New DataColumn("Name", GetType(String)), _
                                               New DataColumn("Email", GetType(String))})

        ' Replace the connection string, query, and field names with your actual database details
        Dim connectionString As String = "YourConnectionString"
        Dim query As String = "SELECT Id, Name, Email FROM YourTable"
        
        Using sqlConnection As New SqlConnection(connectionString)
            Using sqlCommand As New SqlCommand(query, sqlConnection)
                sqlConnection.Open()
                Dim reader As SqlDataReader = sqlCommand.ExecuteReader()

                While reader.Read()
                    ' Assuming your database fields are named "Id", "Name", and "Email"
                    Dim id As Integer = Convert.ToInt32(reader("Id"))
                    Dim name As String = Convert.ToString(reader("Name"))
                    Dim email As String = Convert.ToString(reader("Email"))
                    dt.Rows.Add(id, name, email)
                End While
            End Using
        End Using

        GridView1.DataSource = dt
        GridView1.DataBind()
    End If
End Sub

Answer 2

Make sure to replace "YourConnectionString" with the actual connection string for your database and adjust the query accordingly. Also, ensure that you have the correct field names in the SqlDataReader loop based on your database schema.