Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ListView  and SqlCommand

Posted on 2009-05-17
18
Medium Priority
?
410 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
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…

721 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