VB.Net SQL Count Statement into a label

Question

I'm trying to count the students whose teacher where teacher = '" & lblTeacher.Text & "'"

EXAMPLE :

Public Class Form1
Dim conn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Richard\Desktop\Dbase.mdb"
Dim con As New OleDbConnection
Dim da, da1 As New OleDbDataAdapter
Dim dt, dt1 As New DataTable
Dim sql As String
Dim ds As New DataSet

Public Sub display()
    sql = "select * from Info"
    dt.Clear()
    con.Open()
    da = New OleDbDataAdapter(sql, con)
    da.Fill(dt)
    con.Close()
    DataGridView1.DataSource = dt.DefaultView
End Sub
Public Sub count()
    sql = "select COUNT(name) from Info where teacher = '" & lblTeacher.Text & "'"
    da1 = New OleDbDataAdapter(sql, con)
    ds.Clear()
    con.Open()
    da.Fill(ds)
    lblCount.Text = ds.Tables(0).Rows.Count.ToString
    con.Close()
End Sub
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    con.ConnectionString = conn
    display()
End Sub

Private Sub DataGridView1_Click(sender As System.Object, e As System.EventArgs) Handles DataGridView1.Click
    lblTeacher.Text = DataGridView1.CurrentRow.Cells("teacher").Value.ToString
    count()
End Sub
End Class

1:

and the problem i'm facing is ... ? the error i'm getting is ... ? — Stavm
– Stavm, Commented Mar 16, 2017 at 12:13
Also: seems strange to use an old JET mdb Access file, rather than a newer ACE accdb file. — Joel Coehoorn
– Joel Coehoorn, Commented Mar 16, 2017 at 16:06

Joel Coehoorn · Accepted Answer · 2017-03-17 17:05:33Z

Try this instead of your current count() method. Pay special attention to my comments; they address some poor practices from the original code:

' Better functional style: accept a value, return the result
Public Function GetStudentCount(teacher As String) As Integer
    '**NEVER** use string concatenation to put data into an SQL command!!!
    Const sql As String = "select COUNT(name) from Info where teacher =  ?"

    'Don't try to re-use the same connection in your app.
    '  It creates a bottleneck, and breaks ADO.Net's built-in connection pooling,
    '  meaning it's more likely to make object use *worse*, rather than better.
    'Additionally, connection objects should be created in a Using block,
    '  so they will still be closed if an exception is thrown.
    '  The original code would have left the connection hanging open.
    Using con As New OleDbConnection(conn), _
          cmd As New OleDbCommand(sql, con)

        'This, rather than string concatenation, is how you should put a value into your sql command
        'Note that this NEVER directly replaces the "?" character with the parameter value,
        '   even in the database itself. The command and the data are always kept separated.
        cmd.Parameters.Add("teacher", OleDbType.VarChar).Value = teacher

        con.Open()
        '    No need to fill a whole dataset, just to get one integer back
        Return DirectCast(cmd.ExecuteScalar(), Integer)

       'No need to call con.Close() manually. The Using block takes care of it for you.
    End Using
End Function

Here it is again, without all the extra comments:

Public Function GetStudentCount(teacher As String) As Integer
    Const sql As String = "select COUNT(name) from Info where teacher =  ?"

    Using con As New OleDbConnection(conn), _
          cmd As New OleDbCommand(sql, con)
        cmd.Parameters.Add("teacher", OleDbType.VarChar).Value = teacher           
        con.Open()
        Return DirectCast(cmd.ExecuteScalar(), Integer)
    End Using
End Function

Call it like this:

Private Sub DataGridView1_Click(sender As System.Object, e As System.EventArgs) Handles DataGridView1.Click
    lblTeacher.Text = DataGridView1.CurrentRow.Cells("teacher").Value.ToString()
    lblCount.Text = GetStudentCount(lblTeacher.Text).ToString()
End Sub

Hey, I had a bug in the code for a while (ExecuteNonQuery() vs ExecuteScalar()). It's fixed now, so you may want to check it again.

Collectives™ on Stack Overflow

VB.Net SQL Count Statement into a label

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related