CODEWITHSUNDEEP
vb.netms-accesscount
larca
larca

Reputation: 9

vb.net: how to count row of searched value from one column

lets say there are two columns date received and item

if i searched a date where there are 5 rows of the same date

it will appear in textbox

i nid help plss

*note , i use oledb as database connection

this is my search code

Private Sub TxSearch_TextChanged(sender As System.Object, e As System.EventArgs) Handles TxSearch.TextChanged


    If Con.State = ConnectionState.Closed Then
        Con.Open()
    End If

    Dad = New OleDb.OleDbDataAdapter("select RecordDate, Item from inv where RecordDate LIKE '%" & TxSearch.Text & "%'", Con)
    Dim dt As New DataTable
    Dad.Fill(dt)

    Me.DataGridView1.DataSource = dt
    Con.Close()
    DataGridView1.RowsDefaultCellStyle.BackColor = Color.DeepSkyBlue
    DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White
    Con.Close()


    SortDatagridviewColumn(0)
End Sub

Upvotes: 0

Views: 4771

Answers (2)

Hiroshi Himura
Hiroshi Himura

Reputation: 26

Private Sub TxSearch_TextChanged(sender As System.Object, e As System.EventArgs) Handles TxSearch.TextChanged

If Con.State = ConnectionState.Closed Then
    Con.Open()
End If

Dad = New OleDb.OleDbDataAdapter("select RecordDate, Item from inv where RecordDate LIKE '%" & TxSearch.Text & "%'", Con)
Dim dt As New DataTable
Dad.Fill(dt)

Me.DataGridView1.DataSource = dt

    Dim counter As String
    counter = dt.Rows.Count.ToString
    textBoxRowCount.Text = counter


Con.Close()
DataGridView1.RowsDefaultCellStyle.BackColor = Color.DeepSkyBlue
DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White
Con.Close()


SortDatagridviewColumn(0)


Private Sub textBoxRowCount_TextChanged(sender As System.Object, e As System.EventArgs) Handles textBoxRowCount.TextChanged

End Sub

End Sub

Upvotes: 1

Steve
Steve

Reputation: 216313

Could be just this:

....
Dad.Fill(dt)
textBoxRowCount.Text = "Found " + dt.Rows.Count + " rows"
Me.DataGridView1.DataSource = dt
....

EDIT: Change your query to use parameters, do not ever try to concatenate strings to build sql commands

Dim sqlText = "select RecordDate, Item from inv where RecordDate LIKE @search"
Dad = New OleDb.OleDbDataAdapter(sqlText, Con)
Dad.SelectCommand.Parameters.AddWithValue("@search", "%" + TextSearch.Text + "%")
Dim dt As New DataTable
Dad.Fill(dt)

However in the case above RecordDate is considered as it is a Text field, if, instead, RecordDate is a database field of DateTime type there is no sense to use LIKE. You should use equal (=) or the sql clause BETWEEN

Looking at your comment then you could write a query to count records, (the considerations on RecordDate are still valid)

Dim sqlText = "select COUNT(*) from inv where RecordDate LIKE @search"
Dim cmd  = New OleDb.OleDbCommand(sqlText, Con)
cmd.Parameters.AddWithValue("@search", "%" + TextSearch.Text + "%")
Dim resultCount = cmd.ExecuteScalar()

Upvotes: 0

Related Questions