8 Replies - 16684 Views - Last Post: 18 June 2011 - 05:45 AM Rate Topic: -----

#1 priyarose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 17-June 11

how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 03:24 AM

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 :helpsmilie: :sweatdrop:

Is This A Good Question/Topic? 0
  • +

Replies To: how can i count record number of an access table using vb.net oledb?

#2 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 03:42 AM

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

Was This Post Helpful? 0
  • +
  • -

#3 priyarose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 17-June 11

Re: how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 04:04 AM

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.
Was This Post Helpful? 0
  • +
  • -

#4 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 04:22 AM

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

Was This Post Helpful? 1
  • +
  • -

#5 priyarose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 17-June 11

Re: how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 04:46 AM

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....
Was This Post Helpful? 0
  • +
  • -

#6 raziel_  Icon User is offline

  • Like a lollipop
  • member icon

Reputation: 465
  • View blog
  • Posts: 4,255
  • Joined: 25-March 09

Re: how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 04:49 AM

well then remove the group clause in your query. here is example of the Count() function:
http://www.w3schools..._func_count.asp
Was This Post Helpful? 1
  • +
  • -

#7 Vishu Sukhdev  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 150
  • Joined: 19-February 10

Re: how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 05:05 AM

Hi,
Try This Query
"SELECT item_id,(SELECT COUNT(*) FROM temp) FROM temp"


Was This Post Helpful? 0
  • +
  • -

#8 priyarose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 17-June 11

Re: how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 05:07 AM

Thanks a lot....it works completely what i need....thank you very much....
:yes: :rockon:
Was This Post Helpful? 0
  • +
  • -

#9 priyarose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 13
  • Joined: 17-June 11

Re: how can i count record number of an access table using vb.net oledb?

Posted 18 June 2011 - 05:45 AM

View PostNoBrain, 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..
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1