0

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)
d99
  • 37
  • 2
  • 9

4 Answers4

1

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

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • Thanks, is there a way to assign the results of the reader to a string that can be used in this query? It previously used the "coursenumbers" variable which was the result of "courseselectcom" command turned into a string. – d99 Apr 17 '13 at 12:32
  • Using totalStudentCom As New SqlCommand("SELECT COUNT(student_ID) FROM student where course_name like '%" & courseNumbers & "' ", _ connection) – d99 Apr 17 '13 at 12:32
  • Yes, added a StringBuilder that collects the individual records in its internal buffer separating each record with a newline. You could use it to set the Text property of a Multiline TextBox and show the result in your program. Or you can use a collection List(Of String) and add each record to the collection – Steve Apr 17 '13 at 12:35
  • Thanks but how or what do I put in the second sql query in order to use the result of the previous query? – d99 Apr 17 '13 at 13:00
  • To use that results as input for the where clause in the second query I think that you need to use a very different approach. Probably you need a StoredProcedure that executes [dynamic SQL](http://msdn.microsoft.com/en-us/library/ms188001.aspx) or that receives a [Table Valued Parameter](http://www.sommarskog.se/arrays-in-sql-2008.html). I think that this could be handled better if you post a new question – Steve Apr 17 '13 at 13:10
  • Sorry I'm just really lost in this part of code and am finding it hard to explain what it is I need. I need to get all the courses from a school(selected via a dropdown list) and then use this data in other where clauses to find out the total number of students in a school(gained from adding up the total number on each course within a school) – d99 Apr 17 '13 at 13:17
  • I have undestood your problem. But to answer this requires a very broad analisys. You could try to concatenate the results in a string separating them with a comma, then use this string as a parameter for a IN statement, or execute dynamic sql or call a stored procedure passing table parameter value. I suggest to try something of these methods and then post a new question. – Steve Apr 17 '13 at 13:23
  • Thanks, I have no idea about any of those but i'll have a look. – d99 Apr 17 '13 at 13:26
0

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.

hemant gautam
  • 791
  • 8
  • 9
  • Dim courseNumbers As String courseNumbers = CType(courseSelectCom.ExecuteScalar(), String) – d99 Apr 17 '13 at 11:58
  • This will give you only first value. For all values you need to use either ExecuteReader or use DataAdapter to populate a DataSet – hemant gautam Apr 17 '13 at 12:02
  • Could you give me an example of how I would use the reader in my case? – d99 Apr 17 '13 at 12:05
  • Look at this url http://msdn.microsoft.com/en-us/library/9kcbe65k.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1 – hemant gautam Apr 17 '13 at 12:06
0
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

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133
0

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")
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • It gives the error 'group_concat' is not a recognized built-in function name. – d99 Apr 17 '13 at 13:34
  • @d99 . . . Do you know what your underlying database is? Oh, I see, it is probably SQL Server or Access. Harder to do in those databases. – Gordon Linoff Apr 17 '13 at 13:39
  • Yea sql server, sorry I didn't notice the mysql part. Is there anything equivalent in sql server? – d99 Apr 17 '13 at 13:41
  • How is this code written into this sql query? I keep getting errors when i try to implement it. Dim courseSelectCom As New SqlCommand("SELECT course_name FROM course where school= '%"schoolSelect & "' ", _ connection) – d99 Apr 17 '13 at 14:01
  • Thanks, how do I get the result into the second query, do I have to try and use a reader? – d99 Apr 17 '13 at 14:15
  • @d99 . . . Yes, it would be the same as getting the result from the first query. The column name is `coursenames`. – Gordon Linoff Apr 17 '13 at 14:20
  • Still can't get the data reader to work, i'm not exactly sure what code needs to be used. – d99 Apr 17 '13 at 14:33
  • The error "expression is not a method" appears on the reader("course_names"). Also does anything else need to be added in order to use the readers results in the second sql query as it is currently looking for "courseNumbers" which is no longer referenced. – d99 Apr 17 '13 at 17:44