[priyarose]

my table name is "temp", and one of the field is item_id..how can i count how much item_id was added in this table...??

is the code

"SELECT item_id, COUNT(*) FROM temp GROUP BY item_id"

it doesn't work.. i'm using OLEDB connection and can't use sql property.
how can I solve this? after that i want to show the number of records in a label...
can anyone help me?


thanks in advance

[raziel_]

why it dose not work it should work. Are you sure your reading the right field?
Do you want to get the ID too or just to sort them by ID?

This post has been edited by NoBrain: 18 June 2011 - 03:42 AM

[priyarose]

i just want to count the total record number....
i declared a variable named "b" as integer..and wrote the code

b="SELECT item_id, COUNT(*) FROM temp GROUP BY item_id"
Label1.Text=b

the field item_id is a text type field...
in this code error occurs....
the error is:- Conversion from string "SELECT item_id, COUNT(*) FROM te" to type 'Integer' is not valid.

[raziel_]

Uhm yea you dont execute the querry. the query should looks like this:

        Dim strCnn As String = "YourConnectionStringHere" 'connection string to your DB'
        Dim strSQL As String = "SELECT COUNT(*) FROM temp GROUP BY item_id" 'Query to the DB'
        Dim cnn As New OleDbConnection(strCnn) 'Create new connection'
        Dim CMD As New OleDbCommand(strSQL, cnn) 'Create new command'
        Dim DR As OleDbDataReader
        DR = CMD.ExecuteReader() 'Populate the reader'
        While (DR.Read())
            'Loop all the records in your case it will be only one'
            MessageBox.Show(DR(0).ToString())'Show the result of the query'
        End While

you can find the connection string here

EDIT: here is a tutorial about OLEDB:
oledb basics in vbnet

This post has been edited by NoBrain: 18 June 2011 - 04:27 AM

[priyarose]

thanks...it works......
but it filters by the item id .....and counts the number of following item id.....
but..I'm trying to know the whole record number....no need to be filtered....

[raziel_]

well then remove the group clause in your query. here is example of the Count() function:
http://www.w3schools..._func_count.asp

[Vishu Sukhdev]

Hi,
Try This Query

"SELECT item_id,(SELECT COUNT(*) FROM temp) FROM temp"

[priyarose]

Thanks a lot....it works completely what i need....thank you very much....

[priyarose]

NoBrain, on 18 June 2011 - 04:49 AM, said:

well then remove the group clause in your query. here is example of the Count() function:
http://www.w3schools..._func_count.asp

thanks a lot....it helped me..