Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

DB - counting a string occurance in DB how ?

LensmeisterLensmeister Posts: 48Member
Hi,

I need some advice.

One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.

e.g.

the adodc is named adoPerson.

The names will be in a combo box (called cboPersonSearch)

When someone selects the name the text box will be populated with the number of time the name appears.

combobox person name selected = Fred

textbox text = 36

Can anyone help?

Please.

I am away on holiday for the next few days so I will check back later next week.

Thanks in advance guys.





Comments

  • doofusboydoofusboy Posts: 256Member
    [b][red]This message was edited by doofusboy at 2005-7-29 6:0:39[/red][/b][hr]
    : Hi,
    :
    : I need some advice.
    :
    : One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.
    :
    : e.g.
    :
    : the adodc is named adoPerson.
    :
    : The names will be in a combo box (called cboPersonSearch)
    :
    : When someone selects the name the text box will be populated with the number of time the name appears.
    :
    : combobox person name selected = Fred
    :
    : textbox text = 36
    :
    : Can anyone help?
    :
    : Please.
    :
    : I am away on holiday for the next few days so I will check back later next week.
    :
    : Thanks in advance guys.
    :
    :
    :
    :
    :
    :
    I'm going to assume you know how to connect to a database and return a recordset to your project that you can then use to populate your textbox.

    The SQL query you need to return the number you are looking for should be something like this:

    SELECT Count(name_field)
    FROM database_name.table_name
    WHERE name_field = [value from your combobox]

    So you can build your SQL statement and assign it to a string variable that then gets executed by your connection object like this:

    Dim strSQL As String

    strSQL = "SELECT Count(name_field) FROM database_name.table_name WHERE name_field = '" & Combo1.List(Combo1.ListIndex) & "';"

    The recordset returned when your strSQL is executed against the database by your connection object will contain the number you want to put in your textbox.

    **********************************

    Definitions for above:

    database_name = name of the database file you are using
    table_name = name of table in database that has list of names
    name_field = column name in database that contains list of names
    Combo1.List(Combo1.ListIndex) = name user selects in the combobox


  • LensmeisterLensmeister Posts: 48Member
    : [b][red]This message was edited by doofusboy at 2005-7-29 6:0:39[/red][/b][hr]
    : : Hi,
    : :
    : : I need some advice.
    : :
    : : One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.
    : :
    : : e.g.
    : :
    : : the adodc is named adoPerson.
    : :
    : : The names will be in a combo box (called cboPersonSearch)
    : :
    : : When someone selects the name the text box will be populated with the number of time the name appears.
    : :
    : : combobox person name selected = Fred
    : :
    : : textbox text = 36
    : :
    : : Can anyone help?
    : :
    : : Please.
    : :
    : : I am away on holiday for the next few days so I will check back later next week.
    : :
    : : Thanks in advance guys.
    : :
    : :
    : :
    : :
    : :
    : :
    : I'm going to assume you know how to connect to a database and return a recordset to your project that you can then use to populate your textbox.
    :
    : The SQL query you need to return the number you are looking for should be something like this:
    :
    : SELECT Count(name_field)
    : FROM database_name.table_name
    : WHERE name_field = [value from your combobox]
    :
    : So you can build your SQL statement and assign it to a string variable that then gets executed by your connection object like this:
    :
    : Dim strSQL As String
    :
    : strSQL = "SELECT Count(name_field) FROM database_name.table_name WHERE name_field = '" & Combo1.List(Combo1.ListIndex) & "';"
    :
    : The recordset returned when your strSQL is executed against the database by your connection object will contain the number you want to put in your textbox.
    :
    : **********************************
    :
    : Definitions for above:
    :
    : database_name = name of the database file you are using
    : table_name = name of table in database that has list of names
    : name_field = column name in database that contains list of names
    : Combo1.List(Combo1.ListIndex) = name user selects in the combobox
    :
    :
    :
    Thanks doofusboy .... I can see that will work for a single field, but unfortunatly the table I want to search will have the name fred in up to 15 diffrent fields.
    Any idea ?


  • doofusboydoofusboy Posts: 256Member
    : : [b][red]This message was edited by doofusboy at 2005-7-29 6:0:39[/red][/b][hr]
    : : : Hi,
    : : :
    : : : I need some advice.
    : : :
    : : : One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.
    : : :
    : : : e.g.
    : : :
    : : : the adodc is named adoPerson.
    : : :
    : : : The names will be in a combo box (called cboPersonSearch)
    : : :
    : : : When someone selects the name the text box will be populated with the number of time the name appears.
    : : :
    : : : combobox person name selected = Fred
    : : :
    : : : textbox text = 36
    : : :
    : : : Can anyone help?
    : : :
    : : : Please.
    : : :
    : : : I am away on holiday for the next few days so I will check back later next week.
    : : :
    : : : Thanks in advance guys.
    : : :
    : : :
    : : :
    : : :
    : : :
    : : :
    : : I'm going to assume you know how to connect to a database and return a recordset to your project that you can then use to populate your textbox.
    : :
    : : The SQL query you need to return the number you are looking for should be something like this:
    : :
    : : SELECT Count(name_field)
    : : FROM database_name.table_name
    : : WHERE name_field = [value from your combobox]
    : :
    : : So you can build your SQL statement and assign it to a string variable that then gets executed by your connection object like this:
    : :
    : : Dim strSQL As String
    : :
    : : strSQL = "SELECT Count(name_field) FROM database_name.table_name WHERE name_field = '" & Combo1.List(Combo1.ListIndex) & "';"
    : :
    : : The recordset returned when your strSQL is executed against the database by your connection object will contain the number you want to put in your textbox.
    : :
    : : **********************************
    : :
    : : Definitions for above:
    : :
    : : database_name = name of the database file you are using
    : : table_name = name of table in database that has list of names
    : : name_field = column name in database that contains list of names
    : : Combo1.List(Combo1.ListIndex) = name user selects in the combobox
    : :
    : :
    : :
    : Thanks doofusboy .... I can see that will work for a single field, but unfortunatly the table I want to search will have the name fred in up to 15 diffrent fields.
    : Any idea ?
    :
    :
    :

  • doofusboydoofusboy Posts: 256Member
    : : : [b][red]This message was edited by doofusboy at 2005-7-29 6:0:39[/red][/b][hr]
    : : : : Hi,
    : : : :
    : : : : I need some advice.
    : : : :
    : : : : One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.
    : : : :
    : : : : e.g.
    : : : :
    : : : : the adodc is named adoPerson.
    : : : :
    : : : : The names will be in a combo box (called cboPersonSearch)
    : : : :
    : : : : When someone selects the name the text box will be populated with the number of time the name appears.
    : : : :
    : : : : combobox person name selected = Fred
    : : : :
    : : : : textbox text = 36
    : : : :
    : : : : Can anyone help?
    : : : :
    : : : : Please.
    : : : :
    : : : : I am away on holiday for the next few days so I will check back later next week.
    : : : :
    : : : : Thanks in advance guys.
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : :
    : : : I'm going to assume you know how to connect to a database and return a recordset to your project that you can then use to populate your textbox.
    : : :
    : : : The SQL query you need to return the number you are looking for should be something like this:
    : : :
    : : : SELECT Count(name_field)
    : : : FROM database_name.table_name
    : : : WHERE name_field = [value from your combobox]
    : : :
    : : : So you can build your SQL statement and assign it to a string variable that then gets executed by your connection object like this:
    : : :
    : : : Dim strSQL As String
    : : :
    : : : strSQL = "SELECT Count(name_field) FROM database_name.table_name WHERE name_field = '" & Combo1.List(Combo1.ListIndex) & "';"
    : : :
    : : : The recordset returned when your strSQL is executed against the database by your connection object will contain the number you want to put in your textbox.
    : : :
    : : : **********************************
    : : :
    : : : Definitions for above:
    : : :
    : : : database_name = name of the database file you are using
    : : : table_name = name of table in database that has list of names
    : : : name_field = column name in database that contains list of names
    : : : Combo1.List(Combo1.ListIndex) = name user selects in the combobox
    : : :
    : : :
    : : :
    : : Thanks doofusboy .... I can see that will work for a single field, but unfortunatly the table I want to search will have the name fred in up to 15 diffrent fields.
    : : Any idea ?
    : :
    : :
    : :
    :
    :
    In that case, I would probably do something like this:

    SELECT 1st_name_field, 2nd_name_field, 3rd_name_field
    FROM database_name.table_name
    WHERE 1st_name_field = [value from your combobox]
    AND 2nd_name_field = [value from your combobox]
    AND 3rd_name_field = [value from your combobox]

    and then get the total number of records by stepping through the recordset that is returned and increasing a counter variable that you then assign to your textbox.
Sign In or Register to comment.