listview is empty. why?

[hlsc1983]

list view is empty. i have used the following code. why? please help

Public Sub populate_SubjectsDepartmentsLvw()
        SubjectsDepartmentsLvw.Items.Clear()
        ds.Clear()
        Dim cmds As New SqlCommand(" select subject_name, department_name" _
                              & " from Departments_Subjects_junction" _
                               & "join Subjects on Subjects.subject_id = Departments_Subjects_junction.subject_id" _
                              & "join Departments on Departments.department_id= Departments_Subjects_junction.department_id", con)
        con.Open()
        Using dr As SqlDataReader = cmds.ExecuteReader
            While dr.Read
                Dim li As New ListViewItem
                li.Text = Convert.ToString(dr.Item("subject_name"))
                li.SubItems.Add(Convert.ToString(dr.Item("department_name")))
                SubjectsDepartmentsLvw.Items.Add(li)
            End While
            End Using
        con.Close()


    End Sub

 

[jmcilhinney]

I suggest that you actually look at the CommandText of your SqlCommand rather than just assuming that you have written your SQL code properly.

 

[JuggaloBrotha]

list view is empty. i have used the following code. why? please help

Public Sub populate_SubjectsDepartmentsLvw()
        SubjectsDepartmentsLvw.Items.Clear()
        ds.Clear()
        Dim cmds As New SqlCommand(" select subject_name, department_name" _
                              & " from Departments_Subjects_junction" _
                               & "join Subjects on Subjects.subject_id = Departments_Subjects_junction.subject_id" _
                              & "join Departments on Departments.department_id= Departments_Subjects_junction.department_id", con)
        con.Open()
        Using dr As SqlDataReader = cmds.ExecuteReader
            While dr.Read
                Dim li As New ListViewItem
                li.Text = Convert.ToString(dr.Item("subject_name"))
                li.SubItems.Add(Convert.ToString(dr.Item("department_name")))
                SubjectsDepartmentsLvw.Items.Add(li)
            End While
            End Using
        con.Close()


    End Sub

If you run your query directly on the database, what results do you get?

select subject_name, department_name
from Departments_Subjects_junction
join Subjects on Subjects.subject_id = Departments_Subjects_junction.subject_id
join Departments on Departments.department_id = Departments_Subjects_junction.department_id

 

[jmcilhinney]

If you run your query directly on the database, what results do you get?

select subject_name, department_name
from Departments_Subjects_junction
join Subjects on Subjects.subject_id = Departments_Subjects_junction.subject_id
join Departments on Departments.department_id = Departments_Subjects_junction.department_id

That's not the SQL that's being executed there though. Take a look at the string concatenation and you'll see that it's not valid SQL. I'm guessing that that method is called from the Load event handler and the exception that gets thrown for the invalid syntax is simply swallowed. It worries me that someone would not see the results they expect and yet not bother to check that the SQL they think is being executed is what is actually executed.

 

[JuggaloBrotha]

That's not the SQL that's being executed there though. Take a look at the string concatenation and you'll see that it's not valid SQL. I'm guessing that that method is called from the Load event handler and the exception that gets thrown for the invalid syntax is simply swallowed. It worries me that someone would not see the results they expect and yet not bother to check that the SQL they think is being executed is what is actually executed.

It's the only sql he's given and it's what's in the sql command object he builds and executes, all I did was take out the string concatenation so it can be copied/pasted in SSMS and ran as a query.

 

[jmcilhinney]

It's the only sql he's given and it's what's in the sql command object he builds and executes, all I did was take out the string concatenation so it can be copied/pasted in SSMS and ran as a query.

Yeah but what you showed is what the OP presumably thinks they're executing but it's actually not what will be executed. There are missing spaces so that SQL will end up being this:

 select subject_name, department_name from Departments_Subjects_junctionjoin Subjects on Subjects.subject_id = Departments_Subjects_junction.subject_idjoin Departments on Departments.department_id= Departments_Subjects_junction.department_id

This is a perfect example of why using XML literals for SQL code is a good idea. I wouldn't necessarily expect a beginner to know that but I would expect them to actually check that their String contains what they think it does when using it doesn't work as they intend.