Solved

Dump simple sql statement into a listview control in VB

Posted on 2014-07-29
7
284 Views
Last Modified: 2014-08-05
I have a table in MS SQL 2005 and I want  pull a few fields from a table called MLBPlayers and I'm not sure how to write the syntax to dump these values from MS SQL Database to Listview control on a windows form. I got this far and then got stuck.
Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Shared WithEvents con As SqlConnection

    Shared Sub Main()
        con = New SqlConnection("Persist Security Info=False;User ID=sa;Password=50Cent;Initial Catalog=TSN2;Server=tsnappdev01")

        Dim cmd As New SqlCommand()

        cmd.CommandText = "SELECT FirstName, LastName, Position, Height , Weight FROM MLBPlayers"
        cmd.Connection = con

        Try
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            Do
                While reader.Read()
                    Console.WriteLine(reader(0))
                End While
            Loop While reader.NextResult()

            reader.Close()
        Finally
            con.Close()
        End Try
    End Sub


End Class

Open in new window


Instead of console.write that's where the add Listview will go with Option Explict and Option Strict on. Thanks EE
0
Comment
Question by:powerztom
  • 5
7 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 40227157
try:

ListView1.Items.Add(reader(0).ToString)

Where ListView1 is the name of your ListView control
0
 

Author Comment

by:powerztom
ID: 40227288
I tried but the form was never seen don't I have to define columns and then

do something like

ListView1.Items.Add(reader(Firstname).ToString)
ListView1.Items.Add(reader(Lastname).ToString)
ListView1.Items.Add(reader(Position).ToString) ect.

It may be there are too many records in DATABASE TO DISPLAY. I appreciate your help.
0
 

Author Comment

by:powerztom
ID: 40227302
Also
The program '[4376] ListviewBaseball.vshost.exe' has exited with code -1073741819 (0xc0000005) 'Access violation'.

Just added some formatting to Listview1

code is

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Dim con As SqlConnection

    Public Sub Main()
        con = New SqlConnection("Persist Security Info=False;User ID=sa;Password=DrDre;Initial Catalog=TSN2;Server=tsnappdev01")

        Dim cmd As New SqlCommand()

        cmd.CommandText = "SELECT ID,FirstName, LastName, Position, Height, Weight FROM MLBPlayers"
        cmd.Connection = con

        Try
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            Do
                While reader.Read()
                    ListView1.Items.Add(reader(0).ToString)
                End While
            Loop While reader.NextResult()

            reader.Close()
        Finally
            con.Close()
        End Try
    End Sub
    Public Sub FormatLVStatus()
        'lvHotfixes.Location = New System.Drawing.Point(13, 54)
        ListView1.Margin = New System.Windows.Forms.Padding(4)
        ListView1.MultiSelect = False
        ListView1.Name = "LVCONNECT1"

        'LVCONNECT.TabIndex = 7
        ListView1.UseCompatibleStateImageBehavior = True
        ListView1.View = System.Windows.Forms.View.Details
        ListView1.Columns.Add("", 125, HorizontalAlignment.Left)
        ListView1.Columns.Add("ID", 100, HorizontalAlignment.Left)
        ListView1.Columns.Add("FirstName", 150, HorizontalAlignment.Left)
        ListView1.Columns.Add("LastName", 150, HorizontalAlignment.Left)
        ListView1.Columns.Add("Position", 150, HorizontalAlignment.Left)
        ListView1.Columns.Add("Height", 350, HorizontalAlignment.Left)
        ListView1.Columns.Add("Weight", 150, HorizontalAlignment.Left)

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        FormatLVStatus()
        Main()
    End Sub
End Class

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:powerztom
ID: 40227347
I think I have a Database problem access violation. Credentials are correct but not sure what the deal is I even tried a datagridview same access violation.
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 40228615
Can you debug and see where you are stuck?
0
 

Accepted Solution

by:
powerztom earned 0 total points
ID: 40232134
Got it syntax however I have a new problem. I'll close this and reopen new

Imports listviewone


Public Class Form1



    Public Sub FormatLVStatus()
        'lvHotfixes.Location = New System.Drawing.Point(13, 54)
        lvPlayers.Margin = New System.Windows.Forms.Padding(4)
        lvPlayers.MultiSelect = False
        lvPlayers.Name = "LVCONNECT1"

        'LVCONNECT.TabIndex = 7
        lvPlayers.UseCompatibleStateImageBehavior = True
        lvPlayers.View = System.Windows.Forms.View.Details
        lvPlayers.Columns.Add("", 25, HorizontalAlignment.Left)
        lvPlayers.Columns.Add("PlayerID", 100, HorizontalAlignment.Left)
        lvPlayers.Columns.Add("Firstname", 150, HorizontalAlignment.Left)
        lvPlayers.Columns.Add("Lastname", 150, HorizontalAlignment.Left)
        lvPlayers.Columns.Add("Position", 150, HorizontalAlignment.Left)
        lvPlayers.Columns.Add("Height", 150, HorizontalAlignment.Left)
        lvPlayers.Columns.Add("Weight", 150, HorizontalAlignment.Left)

    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles Me.Load
        FormatLVStatus()
    End Sub

    Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
        Dim playersList As List(Of Players)
        Try
            playersList = Players.GetPlayers
            If playersList.Count > 0 Then
                Dim players As Players
                For i As Integer = 0 To playersList.Count - 1
                    players = playersList(i)
                    lvPlayers.Items.Add(players.playersNumber)
                    lvPlayers.Items(i).SubItems.Add(
                        CInt(players.PlayerID).ToString)
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Firstname))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Lastname))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Position))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Height))
                    lvPlayers.Items(i).SubItems.Add(
                        CStr(players.Weight))
                Next
            Else
                MessageBox.Show("All playerss are paid in full.",
                    "No Balance Due")
                Me.Close()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, ex.GetType.ToString)
            Me.Close()
        End Try
    End Sub
End Class

Open in new window

0
 

Author Closing Comment

by:powerztom
ID: 40240784
I found some code that appears to work.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with Syntax 9 37
Need a starter for ETL protocol? 4 57
Update a summary table with values from detail records 6 26
Sql server, import complete table, using vb.net 9 34
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question