CODEWITHSUNDEEP
sqlvb.net
d99
d99

Reputation: 37

Select all data from 1 column

I have code that is supposed to take all "course names" from a module based on what school is chosen.

For example the school of biology has the modules "biochemistry" and "marine biology". I need to select both of these in order to use in a calculation.

Currently when the code is run it will only take the first module ie "biochemistry" but I need it to select all of the modules.

Can anyone help?

  Dim courseSelectCom As New SqlCommand("SELECT course_name FROM course where school= '%"schoolSelect & "' ", _
                                connection)

Upvotes: 0

Views: 262

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If you want all the courses in a comma delimited list, then use group_concat (assuming you are using MySQL):

SELECT group_concat(course_name)
FROM course
where school= '%"schoolSelect & "'

This returns one row, with all the courses in a single column, like 'biochemistry,marine biology'.

To do this in SQL Server, you can do:

select stuff((select ','+course_name from course where school= '%"schoolSelect & "' for xml path ('')),
             1, 1, '') as coursenames

SQL Server does not have a native aggregate string concatenation operator, so this uses XML features of the database.

I think it would be written as:

Dim courseSelectCom = New SqlCommand("select stuff((select ','+course_name from course where school= '%"schoolSelect & "' for xml path ('')),  1, 1, '') as coursenames", connection)

You need something like this:

Dim reader = courseSelectCom.ExecuteReader()
reader.Read()
reader("course_names")

Upvotes: 0

Steve
Steve

Reputation: 216263

The first thing to do on your query is to use a parametrized query.
This will avoid Sql Injection and parsing problems.

Then you shoud explain why you use the wildcard % in front of SchoolSelect.
Do you have many kind of schools that ends with the same suffix and do you want to retrieve all of them?

Last, you should use a DataReader to loop over the returned data or a DataAdapter to fill a DataTable.

So summarizing 

Dim sb = new StringBuilder()
Using connection = New SqlConnection("your_con_string_here")
    connection.Open()
    Dim courseSelectCom = New SqlCommand("SELECT course_name FROM course where school=@schoolName", connection)
    courseSelectCom.Parameters.AddWithValue("@schoolName", SchoolSelect.Text)
    Dim reader = courseSelectCom.ExecuteReader()
    while reader.Read()
         sb.AppendLine(reader("course_name")) ' or add to some kind of list to reuse'
    End While
End Using

Console.WriteLine(sb.ToString())

if you want to store the data in a DataTable then replace the DataReader loop with

    Dim dt = New DataTable()
    Dim da = New SqlDataAdapter(courseSelectCom)
    da.Fill(dt)

And you have a DataTable object to use for binding to a DataGridView, Listbox or whatever....

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Dim courseSelectCom As New SqlCommand("SELECT course_name FROM course where school=@School", _connection)

courseSelectCom.Parameter.Add("@School", SqlDbType.VarChar).Value = SchoolSelect.Text

SchoolSelect is the textbox from which you select school

Upvotes: 0

hemant gautam
hemant gautam

Reputation: 811

What are you using for Executing the command? If you use ExecuteScalar you will get only first result. Use ExecuteReader and read through or Use DataAdapter and fill a DataSet with it.

Upvotes: 0

Related Questions