CODEWITHSUNDEEP
sqlvb.netms-access-2007
user1352057
user1352057

Reputation: 3182

VB Count query result in a textbox

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?

Upvotes: 0

Views: 4204

Answers (2)

codingbiz
codingbiz

Reputation: 26396

Try this

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

Upvotes: 0

Steve
Steve

Reputation: 216363

  • 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

Upvotes: 1

Related Questions