Solved

ListView  and SqlCommand

Posted on 2009-05-17
18
403 Views
Last Modified: 2012-05-07
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

0
Comment
  • 11
  • 6
18 Comments
 
LVL 37

Expert Comment

by:momi_sabag
Comment Utility
your sql looks fine
are you sure you have a textbox called TextBox1 in your form?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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.
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
yes of course there is a textbox1 in my form .
and the public variable passed the value in the textbox1.text..
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
and also the i give in my textbox1.text the value of the public variable before i call the fillList procedure
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
textbox1.text or TextBox1.Text
remember, it might be a case sensitivity issue...
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
it is Textbox1.Text
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
Textbox1.Text or TextBox1.Text ?
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
yes. but I don't see what is wrong in that part of the code...
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
i used the autocomplete suggestion when i am writing the so that is not the problem ..it is TextBox1.text
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
TextBox1.Text
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
did you run it also?

0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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 ?
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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...
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
so can i need to add a parameter first to id_Moira to avoid sql injection?
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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))
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now