ListView and SqlCommand

I create a procedure for a listview to populate all personnel form an office usinng vb.net and sql server 2005
here is my code:


Public Sub FillList()
       
        With lvList
            .Clear()
 
            .View = View.Details
            .FullRowSelect = True
            .GridLines = True
 
            .Columns.Add("Key", 50)
            .Columns.Add("Rank", 90)
            .Columns.Add("Profession", 120)
            .Columns.Add("Lastname", 140)
            .Columns.Add("Firstname", 90)
            .Columns.Add("ASMA", 90)
            .Columns.Add("Squadron", 60)
 
            FillListView(lvList, cmd.ExecuteReader(CommandBehavior.CloseConnection))
        End With
    End Sub
 
'where lvList my list view
so
what is next....
 
  Dim conn As New SqlConnection(My.Settings.MyConnectionString)
    Dim query As String = "SELECT Key_Personnel,Rank, Profession,Lastname,Firstname,ASMA,Moira FROM   Q_Personnel_1 WHERE id_Moira = '" & TextBox1.Text & "'",conn)
 
'Q_Personnel_1 is a view form sql server  
  
Dim cmd As New SqlCommand(query, conn)
    
    Private Sub PersonnelListView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        If conn.State = ConnectionState.Open Then conn.Close()
        conn.Open()
 
        Call FillList()
    End Sub 
 
  The value from textbox1.text is a public variable that gives a value for id_Moira..(id_Moira is a particular office)
the problem is in WHERE id_Moira = '" & TextBox1.Text & "'" statement...
lets say the value of id_Moira in my textbox1.text is 2.....
when i put WHERE id_Moira = '" & 2 & "'" i have results in my list view 
when i put WHERE id_Moira = '" & TextBox1.Text & "'" i have an unhandled null reference exception 
"Object reference not set to an instance of an object."
could someone plz tell me why this happened?

Open in new window

LVL 18
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
your sql looks fine
are you sure you have a textbox called TextBox1 in your form?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>when i put WHERE id_Moira = '" & TextBox1.Text & "'" i have an unhandled null reference exception
means that TextBox1 is not set at that time.

please double check your code/settings.
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
yes of course there is a textbox1 in my form .
and the public variable passed the value in the textbox1.text..
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
and also the i give in my textbox1.text the value of the public variable before i call the fillList procedure
Guy Hengel [angelIII / a3]Billing EngineerCommented:
textbox1.text or TextBox1.Text
remember, it might be a case sensitivity issue...
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
it is Textbox1.Text
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Textbox1.Text or TextBox1.Text ?
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
So the problem is not the public variable or the sql command ... something happens with the TextBox1
I am trying to find another way.
Before i open the form with the list view i use this code:
Private Sub ListViewToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ListViewToolStripMenuItem.Click
       
            Dim PersonnelWindow As New PersonnelListView
            PersonnelWindow.MdiParent = Me
            PersonnelWindow.StartPosition = FormStartPosition.CenterScreen
            PersonnelWindow.TextBox1.Text = passedIndex
            PersonnelWindow.TextBox2.Text = PassedText
            PersonnelWindow.Show()
            If PersonnelWindow.WindowState <> FormWindowState.Maximized Then PersonnelWindow.WindowState = FormWindowState.Maximized
    End Sub
 
at the top of PersonnelListView Form :
 
Imports System.Data.SqlClient
 
Public Class PersonnelListView
    Private _passedText As String
 
    Public Property [PassedText]() As String
        Get
            Return _passedText
        End Get
        Set(ByVal Value As String)
            _passedText = Value
        End Set
    End Property
 
    Private _passedIndex As Integer
    Public Property [passedIndex]() As Integer
        Get
            Return _passedIndex
        End Get
        Set(ByVal value As Integer)
            _passedIndex = value
        End Set
    End Property
this is how i pass the public variable to the next form ....
 
'if i set directly the passedindex in the sql command the listview is empty

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes. but I don't see what is wrong in that part of the code...
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
i used the autocomplete suggestion when i am writing the so that is not the problem ..it is TextBox1.text
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
TextBox1.Text
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
did you run it also?

John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
it seems to be a trick or something obvious that i can not find ...i need this value ...cause is the filter parameter....what else can i do ?
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
angelIII look what i've done...
i change a little bit the FillList procedure


  Dim conn As New SqlConnection(My.Settings.MyconnectionString)
  Dim query As String = "SELECT Key_Personnel,Rank, Profession,Lastname,Firstname,ASMA,Moira FROM Q_Personnel_1 WHERE id_Moira = '"
 
Public Sub FillList()
       
        With lvList
            .Clear()
 
            .View = View.Details
            .FullRowSelect = True
            .GridLines = True
 
            .Columns.Add("Key", 50)
            .Columns.Add("Rank", 90)
            .Columns.Add("Profession", 120)
            .Columns.Add("Lastname", 140)
            .Columns.Add("Firstname", 90)
            .Columns.Add("ASMA", 90)
            .Columns.Add("Squadron", 60)
 
            Dim conn As New SqlConnection(My.Settings.MyConnectionString)
 
            If conn.State = ConnectionState.Open Then conn.Close()
            conn.Open()
            Dim Ssql = query & TextBox1.Text & "'"
 
            Dim cmd As New SqlCommand(Ssql, conn)
            FillListView(lvList, cmd.ExecuteReader(CommandBehavior.CloseConnection))
        End With
    End Sub
 
Private Sub PersonnelListView_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        If conn.State = ConnectionState.Open Then conn.Close()
        conn.Open()
 
        Call FillList()
    End Sub
it is working fine
..what cause the problem i think is this:
despite of calling the fillList after the form_load event the problem was the declaration of cmd....
do you agree?

Open in new window

Guy Hengel [angelIII / a3]Billing EngineerCommented:
why not use _passedText instead of & TextBox1.Text?

apart from any other problems, this code:
            Dim Ssql = query & TextBox1.Text & "'"

is subject for sql injection. read up about that, otherwise you live at high risks...
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
so can i need to add a parameter first to id_Moira to avoid sql injection?
John (Yiannis) ToutountzoglouInstructor Multiengine PilotAuthor Commented:
what about this lines?:
.
.
.
.
 
Dim conn As New SqlConnection(My.Settings.MyConnectionString)

            If conn.State = ConnectionState.Open Then conn.Close()
            conn.Open()
            Dim Ssql = query & TextBox1.Text & "'"
            Dim cmd As New SqlCommand(Ssql, conn)
            Dim PassNameParam As New SqlParameter("@id_Moira", Me.TextBox1.Text)

            cmd.Parameters.Add(PassNameParam)
            FillListView(lvList, cmd.ExecuteReader(CommandBehavior.CloseConnection))
Guy Hengel [angelIII / a3]Billing EngineerCommented:
almost there.

see here for the fixed version:

  Dim query As String = "SELECT Key_Personnel,Rank, Profession,Lastname,Firstname,ASMA,Moira FROM Q_Personnel_1 WHERE id_Moira = @id_Moira "
 
 
 
 
If conn.State = ConnectionState.Open Then conn.Close()
            conn.Open()
            Dim cmd As New SqlCommand(query , conn)
            Dim PassNameParam As New SqlParameter("@id_Moira", Me.TextBox1.Text)
    

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.