VB Count query result in a textbox

Question

I want to populate the result of an SQL count query on a Access database in a textbox showing how many results there are. For example I have a code number inputted into the database 200 time, i want the textbox to show 200.

Heres my code so far:

    ID = DataGridView1.CurrentRow.Cells(0).Value
    fn = DataGridView1.CurrentRow.Cells(1).Value
    ln = DataGridView1.CurrentRow.Cells(2).Value
    SKU = DataGridView1.CurrentRow.Cells(4).Value
    FC = DataGridView1.CurrentRow.Cells(5).Value


    Dim countNoTwo As String = "SELECT COUNT skuNo FROM table WHERE skuNo = " & SKU & ""
    Dim connection As New OleDbConnection(duraGadgetDB)
    Dim dataadapter As New OleDbDataAdapter(countNoTwo, connection)
    Dim ds As New DataSet()
    connection.Open()
    dataadapter.Fill(ds, "dura")
    connection.Close()

   txtbox1.Text

How do i bind the result of the dataset to the txtbox1.Text?

score 1 · Answer 1 · edited May 23 '17 at 10:31

First, do not use string concatenation to build sql commands (reason=Sql Injection + Parsing problems)
Second, if your command returns only one single result you could use ExecuteScalar

Third, use the Using statement to be sure that your connection and commands are correctly disposed after use

Dim countNoTwo As String = "SELECT COUNT skuNo FROM table WHERE skuNo = ?"
Using connection As New OleDbConnection(duraGadgetDB)
    Using command As New OleDbCommand(countNoTwo, connection)
       command.Parameters.AddWithValue("@sku", SKU)
       connection.Open()
       Dim result = Convert.ToInt32(command.ExecuteScalar())
       txtbox1.Text = result.ToString
    End Using
End Using

codingbiz · Accepted Answer · 2013-01-28T00:00:59.750

0

Try this

Dim dt As DataTable = ds.Tables(0)
txtbox1.Text = dt.Rows(0).Item(0).ToString()

edited Jan 28 '13 at 00:00

answered Jan 27 '13 at 23:31

codingbiz

26,179
8
59
96

VB Count query result in a textbox

2 Answers2