Solved

ListView  and SqlCommand

Posted on 2009-05-17
18
404 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
ID: 24405627
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]
ID: 24405631
>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
ID: 24405641
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
ID: 24405645
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]
ID: 24405668
textbox1.text or TextBox1.Text
remember, it might be a case sensitivity issue...
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24405674
it is Textbox1.Text
0
 
LVL 142

Expert Comment

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

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24405696
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]
ID: 24405711
yes. but I don't see what is wrong in that part of the code...
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24405714
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
ID: 24405716
TextBox1.Text
0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24405729
did you run it also?

0
 
LVL 18

Author Comment

by:John (Yiannis) Toutountzoglou
ID: 24405789
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
ID: 24406014
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]
ID: 24407304
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
ID: 24413617
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
ID: 24413884
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
ID: 24414618
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 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

17 Experts available now in Live!

Get 1:1 Help Now