none
Table Creation using SQL client and a custom class RRS feed

  • Question

  • Hello,

    I have this code snippet below:

    Namespace Emails
        Public Class EmailTables
            Public Property EmailID() As Integer
            Public Property Email() As String
    
            Public ReadOnly Property EmailArray() As String()
                Get
                    Return {EmailID, Email}
                End Get
            End Property
    
        End Class
    
    End Namespace
    
    
    Imports SQL_App_One.Emails
    
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'DBZDataSet.EmployeeSales' table. You can move, or remove it, as needed.
            Me.EmployeeSalesTableAdapter.Fill(Me.DBZDataSet.EmployeeSales)
    
        End Sub
    
        Private Sub CreateTableFromSQLButton_Click(sender As Object, e As EventArgs) Handles CreateTableFromSQLButton.Click
            Dim SQLConnX As New SqlClient.SqlConnection
            Dim SQLDBX As New SqlClient.SqlDataAdapter
            Dim SQLZZZ As New SqlClient.SqlParameter
            Dim SQLDgv As New DataGridView
            Dim SQLTblAdpt As New DataTable
            Dim SQLDSet As New DataSet
            Dim SQLBindSrc As New BindingSource
            Dim SQLCmd As New SqlClient.SqlCommand
            Dim clsEm As New EmailTables
    
    
            SQLConnX.ConnectionString = "Data Source=DESKTOP-ALKNN6N;Initial Catalog=DBZ;Integrated Security=True"
    
            SQLCmd = New SqlClient.SqlCommand("CREATE TABLE tblEmails(" & clsEm.EmailID & " INT NULL, " & clsEm.Email & ", VARCHAR(50) NULL)", SQLConnX)
    
            SQLConnX.Open()
    
            SQLCmd.ExecuteNonQuery()
    
            SQLConnX.Close()
    
    
            SQLDSet.DataSetName = "EmailListings"
            SQLBindSrc.DataSource = SQLDSet
            SQLBindSrc.DataMember = "EmailListings"
    
    
    
    
    
    
            With SQLDgv
                .Height = 500
                .Width = 500
                .Top = 100
                .Left = 100
                .DataSource = SQLBindSrc
            End With
    
    
    
    
    
            SQLDgv.Show()
    
            SQLConnX.Close()
    
            Me.Controls.Add(SQLDgv)
    
    
        End Sub
    
    
    
    
    
    End Class
    

    I don't know how to explain this but what I want to perform is how to assign parameters of the custom public class to the text box cells of its data grid view control.

    But first I need to declare some text box cells for its data grid view control. That needs to be initialized first prior to assigning its custom class parameters to them.

    Regards,

    JohnDBCTX


    jp

    Friday, January 24, 2020 10:02 AM

All replies

  • Here is a simple example where the DataGridView will create columns itself for the two non readonly properties, the read only property is ignored.

    I'm using mocked data, same will work when the class items are populated from another data source too.

    Public Class Form1
        Private BindingSource As New BindingSource
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            BindingSource.DataSource = New List(Of EmailTables) From {
                New EmailTables() With {.EmailID = 1, .Email = "Jim@gmail.com"},
                New EmailTables() With {.EmailID = 2, .Email = "mary@gmail.com"}}
    
            DataGridView1.DataSource = BindingSource
    
            DataGridView1.Columns("EmailID").DefaultCellStyle.BackColor = Color.Red
            DataGridView1.Columns("EmailID").DefaultCellStyle.ForeColor = Color.White
    
        End Sub
    End Class
    Public Class EmailTables
        Public Property EmailID() As Integer
        Public Property Email() As String
    
        Public ReadOnly Property EmailArray() As String()
            Get
                Return New String() {EmailID.ToString(), Email}
            End Get
        End Property
    
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 24, 2020 10:59 AM
    Moderator
  • Imports SQL_App_One.Emails
    Imports System.Data.SqlClient
    
    Public Class Form1
        Private BindingSource As New BindingSource
        Private DataGridView1 As New DataGridView
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'DBZDataSet.EmployeeSales' table. You can move, or remove it, as needed.
            Me.EmployeeSalesTableAdapter.Fill(Me.DBZDataSet.EmployeeSales)
    
        End Sub
    
        Private Sub CreateTableFromSQLButton_Click(sender As Object, e As EventArgs) Handles CreateTableFromSQLButton.Click
    
    
            BindingSource.DataSource = New List(Of EmailTables) From {
                New EmailTables() With {.EmailID = 1, .Email = "Jim@gmail.com"},
                New EmailTables() With {.EmailID = 2, .Email = "mary@gmail.com"}}
    
            DataGridView1.DataSource = BindingSource
    
            With DataGridView1
                .Height = 500
                .Width = 300
                .Top = 100
                .Left = 100
            End With
    
            Me.Controls.Add(DataGridView1)
    
        End Sub
    
    
    
    
    
    End Class

    Improved your code snippet.  Run it along with the public class of EmailTables at your convenience.

    Regards,

    JohnDBCTX


    jp

    Friday, January 24, 2020 11:53 AM
  • Imports SQL_App_One.Emails
    Imports System.Data.SqlClient
    
    Public Class Form1
        Private BindingSource As New BindingSource
        Private DataGridView1 As New DataGridView
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'DBZDataSet.EmployeeSales' table. You can move, or remove it, as needed.
            Me.EmployeeSalesTableAdapter.Fill(Me.DBZDataSet.EmployeeSales)
    
        End Sub
    
        Private Sub CreateTableFromSQLButton_Click(sender As Object, e As EventArgs) Handles CreateTableFromSQLButton.Click
    
    
            BindingSource.DataSource = New List(Of EmailTables) From {
                New EmailTables() With {.EmailID = 1, .Email = "Jim@gmail.com"},
                New EmailTables() With {.EmailID = 2, .Email = "mary@gmail.com"}}
    
            DataGridView1.DataSource = BindingSource
    
            With DataGridView1
                .Height = 500
                .Width = 300
                .Top = 100
                .Left = 100
            End With
    
            Me.Controls.Add(DataGridView1)
    
        End Sub
    
    
    
    
    
    End Class

    Improved your code snippet.  Run it along with the public class of EmailTables at your convenience.

    Regards,

    JohnDBCTX


    jp

    You might not have thought about this but I can't run the code without your database, of course I can imagine it and see no difference. If I had your database I would had (if appropriate) loaded the EmailTables from your database into a list using the following.

    ''' <summary>
    ''' Pass in a DataTable to create a list of EmailTables
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    Public Function DataTableToList(dt As DataTable) As List(Of EmailTables)
        Dim emailData As IEnumerable(Of EmailTables) =
                dt.AsEnumerable().Select(Function(dataRow) New EmailTables() With {
                                            .EmailID = dataRow.Field(Of Integer)("EmailId"),
                                            .Email = dataRow.Field(Of String)("Email")}).ToList()
    
        Return emailData.ToList()
    
    End Function

    So it would look like this (and remember I'm doing this without your database so it may be a little off)

    Private Sub CreateTableFromSQLButton_Click(sender As Object, e As EventArgs) Handles CreateTableFromSQLButton.Click
    
    
    	BindingSource.DataSource = DataTableToList(Me.DBZDataSet.EmployeeSales)
    
    	DataGridView1.DataSource = BindingSource
    
    	With DataGridView1
    		.Height = 500
    		.Width = 300
    		.Top = 100
    		.Left = 100
    	End With
    
    	Me.Controls.Add(DataGridView1)
    
    End Sub
    Public Function DataTableToList(dt As DataTable) As List(Of EmailTables)
    	Dim emailData As IEnumerable(Of EmailTables) =
    			dt.AsEnumerable().Select(Function(dataRow) New EmailTables() With {
    										.EmailID = dataRow.Field(Of Integer)("EmailId"),
    										.Email = dataRow.Field(Of String)("Email")}).ToList()
    
    	Return emailData.ToList()
    
    End Function

    Hopefully what I provided solved your question. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, January 24, 2020 12:23 PM
    Moderator