Table to Array and Action

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 536 Views - Last Post: 08 May 2013 - 07:33 AM Rate Topic: -----

#1 TonyReilly  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 13

Table to Array and Action

Posted 08 May 2013 - 05:17 AM

Afternoon Guys and Girls, wonder if you can help!

Background:
I have a database with 2 tables: Users and Entries
The users table contains the names only.
When a user makes an entry with the form they select a user from a combox box (Mapped to the users table) and it is saved as an entry in the Entries Table.

Problem:
There can be any number of names in the users table as it is populated by the user when they use the program.
What I need to be able to do is work out how many entries there are for each person in the users table.
E.g. Bob is in the users table and I want to know how many time bob appears in the Entries table.

I can do this hard coding the names: Count * Where Name = "Bob" but it wont always be bob.

What I would really like to do is be able to perform the count command on all items in the table users. Would it be best to create an array from the users table and then perform an action on each item in the array? If so can someone point me in the right direction.

Hope this makes sense, appreciate your help too!!!

Cheers
Tony

Is This A Good Question/Topic? 0
  • +

Replies To: Table to Array and Action

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3223
  • View blog
  • Posts: 10,815
  • Joined: 12-December 12

Re: Table to Array and Action

Posted 08 May 2013 - 05:29 AM

You need to learn some SQL (and database design :)). Specifically, JOINS and the GROUP BY clause. It will be something like:

SELECT Users.User, COUNT(*) FROM Users INNER JOIN Entries 
ON Users.User = Entries.User GROUP BY Users.User

Was This Post Helpful? 0
  • +
  • -

#3 TonyReilly  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 13

Re: Table to Array and Action

Posted 08 May 2013 - 05:35 AM

Sorry I'm not sure exactly what you mean?

I want to take the contents of a table, then for each item in the table perform a specific action.
On this occasion I want to do an SQL Count command (No problem with that).

How is it that JOINS and GROUPS will help?
My problem is getting the contents of the table into a form where the program can work its way though each item and perform the action.

The way I see it is:
For each item in array > Perform SQL Count?

Is this incorrect?
Was This Post Helpful? 0
  • +
  • -

#4 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Table to Array and Action

Posted 08 May 2013 - 05:48 AM

View PostTonyReilly, on 08 May 2013 - 05:35 AM, said:

Sorry I'm not sure exactly what you mean?


he is counting the user entries from the table with the function count in sql

This post has been edited by vks.gautam1: 08 May 2013 - 05:49 AM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3223
  • View blog
  • Posts: 10,815
  • Joined: 12-December 12

Re: Table to Array and Action

Posted 08 May 2013 - 05:58 AM

View PostTonyReilly, on 08 May 2013 - 12:35 PM, said:

Sorry I'm not sure exactly what you mean?

This is why I am suggesting that you need to study SQL and database design. We use JOINs to get information from more than one table. A join states what information in one table is related to information in another table. GROUP BY will collect information for each User, and COUNT(*) will count the corresponding number of rows in the Entries table (for each user).

I don't usually recommend W3Schools but the SQL Tutorials are pretty straight-forward.
Was This Post Helpful? 0
  • +
  • -

#6 TonyReilly  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 13

Re: Table to Array and Action

Posted 08 May 2013 - 06:00 AM

I think there is a understanding issue here, probably on my part.

The end result of this is I get a figure for each item in the users table as to how many times it appears in the entries table so this information can then be plotted on a graph?
Does this make sense?

If tblUsers contains

Bob
Steve
Luke

I need to be able to count how many times these names appear in the Entries table and display the results.
I can do this if I hard coded the names but the user will define the names as the use the program, Bob might be removed and Mike added.

Is there a way to get vb.net to see the table contents as a list and then perform the action(Count in this case) for each item in the list.

Sorry if this is me getting this wrong, it seems complicated when on paper but feels like it should be a simple process in my head.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3223
  • View blog
  • Posts: 10,815
  • Joined: 12-December 12

Re: Table to Array and Action

Posted 08 May 2013 - 06:01 AM

View PostTonyReilly, on 08 May 2013 - 12:35 PM, said:

The way I see it is:
For each item in array > Perform SQL Count?

Is this incorrect?

It is possible to do it this way, but if you use a SQL statement with a JOIN you can get all the information you need in one go, and loop through the data that is returned.
Was This Post Helpful? 0
  • +
  • -

#8 TonyReilly  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 13

Re: Table to Array and Action

Posted 08 May 2013 - 06:06 AM

View Postandrewsw, on 08 May 2013 - 05:58 AM, said:

View PostTonyReilly, on 08 May 2013 - 12:35 PM, said:

Sorry I'm not sure exactly what you mean?

This is why I am suggesting that you need to study SQL and database design. We use JOINs to get information from more than one table.


I don't information from more than one table, I gave that information as an example.
I want to separate the 2 processes.
First i want to make an array out of the contents of tblUsers.
Then for each item in the array perform a search in a different table.

For example the array would be create from a table containing the name: Bob
Now using the array item Bob Search the second database for the string Bob
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3223
  • View blog
  • Posts: 10,815
  • Joined: 12-December 12

Re: Table to Array and Action

Posted 08 May 2013 - 06:11 AM

I'll try again :). If you execute a query like this:

SELECT Users.User AS user, COUNT(*) AS no_entries FROM Users INNER JOIN Entries 
ON Users.User = Entries.User GROUP BY Users.User

this will yield a resultset like this:

user | no_entries
Bob 8
Steve 10
Luke 14

If you only want Bob's number of entries you could use:

SELECT Users.User AS user, COUNT(*) AS no_entries FROM Users INNER JOIN Entries 
ON Users.User = Entries.User GROUP BY Users.User WHERE Users.User = 'Bob'

Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3223
  • View blog
  • Posts: 10,815
  • Joined: 12-December 12

Re: Table to Array and Action

Posted 08 May 2013 - 06:16 AM

View PostTonyReilly, on 08 May 2013 - 01:06 PM, said:

I don't information from more than one table, I gave that information as an example.
I want to separate the 2 processes.
First i want to make an array out of the contents of tblUsers.
Then for each item in the array perform a search in a different table.

For example the array would be create from a table containing the name: Bob
Now using the array item Bob Search the second database for the string Bob

Ah well, ignore me. I was basing my response on this statement in your first post:

Quote

What I would really like to do is be able to perform the count command on all items in the table users.

Good luck!
Was This Post Helpful? 0
  • +
  • -

#11 TonyReilly  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 13

Re: Table to Array and Action

Posted 08 May 2013 - 06:36 AM

Sorry your last post helped a lot, I didnt realise it was doing the whole of what I wanted in one go, I though it would be a lot more complicated. I have got the code into the program.
However, it doesn't appear as simple to display as a simple count.
While to command appears to work correctly (Doesn't throw and exception.)
How would you then get it to display the results.
On a basic SQL count I could just create a basic myDataSet.Tables(0).Rows.Count

Appreciate your help, and apologise it took me so long to catch on!

Cheers
Was This Post Helpful? 0
  • +
  • -

#12 vks.gautam1  Icon User is offline

  • D.I.C Regular

Reputation: 17
  • View blog
  • Posts: 317
  • Joined: 21-March 08

Re: Table to Array and Action

Posted 08 May 2013 - 06:46 AM

View PostTonyReilly, on 08 May 2013 - 06:36 AM, said:

Sorry your last post helped a lot, I didnt realise it was doing the whole of what I wanted in one go, I though it would be a lot more complicated. I have got the code into the program.
However, it doesn't appear as simple to display as a simple count.
While to command appears to work correctly (Doesn't throw and exception.)
How would you then get it to display the results.
On a basic SQL count I could just create a basic myDataSet.Tables(0).Rows.Count

Appreciate your help, and apologise it took me so long to catch on!

Cheers

sql query is already counting user for you..on what type of control you wanna display your result
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3223
  • View blog
  • Posts: 10,815
  • Joined: 12-December 12

Re: Table to Array and Action

Posted 08 May 2013 - 07:02 AM

View PostTonyReilly, on 08 May 2013 - 01:36 PM, said:

Appreciate your help, and apologise it took me so long to catch on!

Cheers

No worries. Andy.
Was This Post Helpful? 0
  • +
  • -

#14 TonyReilly  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 08-May 13

Re: Table to Array and Action

Posted 08 May 2013 - 07:12 AM

Id be looking at getting the results into a text box or some readable control e.g. Label

Thanks
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3223
  • View blog
  • Posts: 10,815
  • Joined: 12-December 12

Re: Table to Array and Action

Posted 08 May 2013 - 07:20 AM

View PostTonyReilly, on 08 May 2013 - 02:12 PM, said:

Id be looking at getting the results into a text box or some readable control e.g. Label

Thanks

You could use ExecuteReader (depending on your database) to loop through the results constructing a string, then set the .Text of your textbox or label to this string. There is a simple example half-way down that page.

You might consider a DataGridView or ListBox in preference to a textbox or label, they are suited to tabulated data.

This post has been edited by andrewsw: 08 May 2013 - 07:23 AM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2