Solved

ListView  and SqlCommand

Posted on 2009-05-17
18
408 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 143

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 143

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 143

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
 
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 143

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 143

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

734 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