3 августа 2012 г. 11:57
Ok, so in my vb project I have a datagridview being populated by some data from an Access db. the code for which is as follows which includes the SQL query to generate the info:
Dim sql_3 As String = "SELECT tbl_cx_" & yr & ".Loc," & _ " tbl_cx" & yr & ".Product," & _ " tbl_cx_" & yr & ".price," & _ " tbl_cx_" & yr & ".comment," & _ " tbl_cx_" & yr & ".other" & _ " FROM tbl_cx_" & yr & _ " WHERE tbl_cx_" & yr & ".mth like '%" & mth & "%' AND" & src_sql Dim con As New OleDb.OleDbConnection("PROVIDER=Microsoft.Ace.OLEDB.12.0;Data Source = F:\me\Databases\dbLPM.accdb") Dim oleCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(sql_3, con) con.Open() Dim da_3 As New OleDb.OleDbDataAdapter(oleCmd) Dim ds_3 As New DataSet da_3.Fill(ds_3) Me.DataGridView1.DataSource = ds_3.Tables(0) con.Close()
So this is working fine.
I am interested in updating a label with the count of the distinct entries in a column from the database called "products", based on the query that is run above. i.e. one query may generate 10 results, of which 5 products may be distinct. another may generate 100 results, of which 80 are distinct.
I have the following SQL which I think does the trick, but I dont know how to integrate it to the above SQL query in order for it to run at the same time:
(select Count(*) AS Numberofproducts from (SELECT DISTINCT product FROM tbl_cx_2012) as T)
Can anyone help?
3 августа 2012 г. 14:04It looks like two separate queries to me - just using the same criteria in the WHERE statement. One returns zero or more rows, while the other a count of unique items for a column in the query. Since they are logically different queries can't you just run one after the other?
Paul ~~~~ Microsoft MVP (Visual Basic)
- Помечено в качестве ответа bigcdh 3 августа 2012 г. 14:45
3 августа 2012 г. 14:45cool, thanks Paul. Sometimes its the obvious that I just dont see. :-)