asked on

Dump simple sql statement into a listview control in VB

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

Paul Jackson

try:

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

Where ListView1 is the name of your ListView control

Tom Powers

ASKER

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.

Tom Powers

ASKER

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

Tom Powers

ASKER

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.

Harish Varghese

Can you debug and see where you are stuck?

ASKER CERTIFIED SOLUTION

Tom Powers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tom Powers

ASKER

I found some code that appears to work.