Results 1 to 21 of 21

Thread: Count rows in tables

  1. Jul 17th, 2007, 05:25 AM #1
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Count rows in tables

    Guys,

    I need to count the number of rows in each table of an oracle database, the database has around 200 tables. I need to do this on a win form in Vis Studio. Usually, I would load into a dataset and just do a count on that, but the size of the database is crashing visual studio.

    Its been a while soince I coded so sorry for the simplicity! I should know this. So far I have this to get a list of all of the table names....

    Code:
    Private Shared mda As OracleDataAdapter
        Public Shared Function GetTableNames() As DataTable
            Dim dt As New DataTable
            Dim strSQL As String = String.Empty
            strSQL = "Select table_name from All_Tables"

            Dim oda As New OracleDataAdapter
            oda.SelectCommand = New OracleCommand(strSQL, cnn)
            With oda.SelectCommand
                If .Connection.State = ConnectionState.Closed Then
                    .Connection.Open()
                End If
            End With
            oda.Fill(dt)
            Return dt
            oda.SelectCommand.Connection.Close()
            cnn.Close()
        End Function

    Now how do I get a row count in there for each table....

    Thanks
    Bob
    Last edited by staticbob; Jul 17th, 2007 at 05:33 AM.
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  2. Jul 17th, 2007, 05:44 AM #2
    penagate
    penagate is offline
    I'm about to be a PowerPoster!
    Join Date
    Jan 2005
    Location
    Everywhere
    Posts
    13,647

    Re: Count rows in tables

    SELECT COUNT(*) FROM table_name
    Reply With Quote Reply With Quote
  3. Jul 17th, 2007, 06:14 AM #3
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    Thanks PG,

    So whats wrong here? I'm getting Invalid table name on the "oda.Fill(dt_temp)".....


    Bob


    Code:
     Public Shared Function GetTableNames() As DataTable
            Dim dt_names As New DataTable
            Dim dt_temp As New DataTable
            Dim dt_data As New DataTable

            Dim strSQL As String = String.Empty
            strSQL = "Select table_name from All_Tables"

            Dim oda As New OracleDataAdapter
            oda.SelectCommand = New OracleCommand(strSQL, cnn)
            With oda.SelectCommand
                If .Connection.State = ConnectionState.Closed Then
                    .Connection.Open()
                End If
            End With
            oda.Fill(dt_names)

            'Return dt_names

            strSQL = String.Empty

            Dim t As String 'tablename
            For Each r As DataRow In dt_names.Rows
                t = r(0).ToString
                strSQL = "Select table_name, Count(*) from '" & t & "'"

                oda.SelectCommand = New OracleCommand(strSQL, cnn)
                With oda.SelectCommand
                    If .Connection.State = ConnectionState.Closed Then
                        .Connection.Open()
                    End If
                End With
                oda.Fill(dt_temp)

                dt_data.Rows.Add(dt_temp.Rows(0))
            Next

            Return dt_data

            oda.SelectCommand.Connection.Close()
            cnn.Close()

        End Function
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  4. Jul 17th, 2007, 06:17 AM #4
    penagate
    penagate is offline
    I'm about to be a PowerPoster!
    Join Date
    Jan 2005
    Location
    Everywhere
    Posts
    13,647

    Re: Count rows in tables

    don't enclose the table name in quotes.
    Reply With Quote Reply With Quote
  6. Jul 17th, 2007, 06:23 AM #5
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    Oh yeah, what a HEEEEEEEEEEEEEEE JET.

    Now I'm getting....
    "ORA-00904: "TABLE_NAME": invalid identifier"

    CommandText = "Select table_name, Count(*) from XDOERGOPLANTYPEC132V0"
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  7. Jul 17th, 2007, 06:27 AM #6
    penagate
    penagate is offline
    I'm about to be a PowerPoster!
    Join Date
    Jan 2005
    Location
    Everywhere
    Posts
    13,647

    Re: Count rows in tables

    That error means that the column TABLE_NAME doesn't exist in the table.
    If you just want the table name to be returned in the result set then you can place it table name in the field list (in quotes, this time).
    Code:
    strSQL = "SELECT '" & t & "', COUNT(*) FROM " & t
    Should work (haven't ever really used Oracle)
    Reply With Quote Reply With Quote
  8. Jul 17th, 2007, 06:49 AM #7
    GaryMazzone
    GaryMazzone is offline
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,493

    Re: Count rows in tables

    First query:

    Select Table_name From All_tables Order By Table_Name

    Bring that back in either a dataset,datatable or datareader (I would perfer the datareader).

    Set a var equal to the objects return value

    Dim strTableName As String = String.Empty

    strTableName = oDT.GetString(0).Trim()

    Second query (do in a while loop)

    "Select Count(*) From & " strTableName

    Return that with ExecuteScaler
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1
    Reply With Quote Reply With Quote
  9. Jul 17th, 2007, 07:18 AM #8
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    OK, I've got this now. Its not pretty and its not working. I am using datatables where I shouldn't be, but am unsure on the correct way of doing this.

    Can anybody help clean this up, so it actually works ! At the moment, the datatable somes back from this with 32 rows, but I cant see any data when I map a datagridview to this datasource.

    Thanks

    Bob
    Code:
      Private Shared mda As OracleDataAdapter
        Public Shared Function GetTableNames() As DataTable
            Dim dt_names As New DataTable
            Dim dt_temp As New DataTable
            Dim dt_data As New DataTable
            dt_data.Columns.Add("TableName")
            dt_data.Columns.Add("RowCount")

            Dim strSQL As String = String.Empty
            strSQL = "Select table_name from All_Tables"

            Dim oda As New OracleDataAdapter
            oda.SelectCommand = New OracleCommand(strSQL, cnn)
            With oda.SelectCommand
                If .Connection.State = ConnectionState.Closed Then
                    .Connection.Open()
                End If
            End With
            oda.Fill(dt_names)

            'Return dt_names

            strSQL = String.Empty

            Dim t As String 'tablename
            For Each r As DataRow In dt_names.Rows
                t = r(0).ToString
                If InStr(t, "ERGO") > 0 Then
                    strSQL = "SELECT '" & t & "', COUNT(*) FROM " & t

                    oda.SelectCommand = New OracleCommand(strSQL, cnn)
                    With oda.SelectCommand
                        If .Connection.State = ConnectionState.Closed Then
                            .Connection.Open()
                        End If
                    End With
                    oda.Fill(dt_temp)
                    dt_data.ImportRow(dt_temp.Rows(0))


                End If
            Next

            Return dt_data

            oda.SelectCommand.Connection.Close()
            cnn.Close()

        End Function
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  11. Jul 17th, 2007, 07:47 AM #9
    FrancisC
    FrancisC is offline
    Addicted Member
    Join Date
    May 1999
    Posts
    161

    Re: Count rows in tables

    What user name are you connecting to your Oracle database?

    Your query "Select table_name from All_Tables" can very well return table names that you do not have select privileges on.
    Reply With Quote Reply With Quote
  12. Jul 17th, 2007, 07:52 AM #10
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    I have permissions to everything, I'm in as the dba.
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  13. Jul 17th, 2007, 08:26 AM #11
    GaryMazzone
    GaryMazzone is offline
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,493

    Re: Count rows in tables

    I went about it a little different. But here is what I did (it did return the proper values):

    Code:
    Option Strict Off
Imports Oracle.DataAccess.Client
Public Class Form1
    Private Structure stTableInfo
        Dim stTable As String
        Dim intCount As Integer
    End Structure
    Private atCount As stTableInfo()
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim atcount(0)
        Me.countTables()
        Dim strSQL As String = "Select Count(*) From "
        Dim strT As String = String.Empty
        For i As Integer = 0 To atCount.GetUpperBound(0)
            If Me.atCount(i).stTable <> String.Empty Then
                strSQL &= Me.atCount(i).stTable
                Me.atCount(i).intCount = Return_Oracle_Int(strSQL)
            End If
        Next

        For i As Integer = 0 To Me.atCount.GetUpperBound(0)
            strT = String.Empty
            strT = "Table: " & Me.atCount(i).stTable & " contains: " & Me.atCount(i).intCount.ToString() & " rows"
            MsgBox(strT)
        Next
    End Sub
    Private Sub countTables()
        Dim strConn As String = "Data Source=test;"
        strConn &= "User ID=gary;"
        strConn &= "Password=gary;"
        Dim oCmd As OracleCommand
        Dim oDR As Oracle.DataAccess.Client.OracleDataReader
        Dim strSQL As String = "Select Table_Name From user_tables"
        oCmd = New Oracle.DataAccess.Client.OracleCommand()
        Try
            With oCmd
                .Connection = New Oracle.DataAccess.Client.OracleConnection(strConn)
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = strSQL
                oDR = .ExecuteReader()
            End With
            Dim i As Integer = -1
            While oDR.Read()
                i += 1
                ReDim Preserve atCount(i)
                atCount(i).stTable = oDR.GetOracleString(0).ToString().Trim()
            End While
            oDR.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        oCmd.Dispose()
    End Sub
    Public Function Return_Oracle_Int(ByVal strSQL As String) As Integer
        Dim strConn As String = "Data Source=test;"
        strConn &= "User ID=gary;"
        strConn &= "Password=gary;"
        Dim oCmd As Oracle.DataAccess.Client.OracleCommand
        Dim oDr As Oracle.DataAccess.Client.OracleDataReader
        Dim intRet As Integer = -1

        oCmd = New Oracle.DataAccess.Client.OracleCommand
        Try
            With oCmd
                .Connection = New Oracle.DataAccess.Client.OracleConnection(strConn)
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = strSQL
                oDr = .ExecuteReader()
            End With
            oDr.Read()
            intRet = Integer.Parse(oDr.GetInt32(0))
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
            oCmd.Connection.Dispose()
        End Try
        oCmd.Dispose()
        Return intRet
    End Function
End Class
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1
    Reply With Quote Reply With Quote
  14. Jul 17th, 2007, 11:04 AM #12
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    Thanks Gary,

    That works, but I just get loads of zeros coming back now where I expected rows. ARGHHHHHHHHH. I'll have another look tomorrow.

    Bob
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  15. Jul 19th, 2007, 08:56 AM #13
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    OK, so after a few pm's, heres where I am now Gary.

    I have run the code, didn't write to the spool file but to the SQL+ window....

    Code:
    SQL> 
SQL> spool d:\temp\testout.sql
SQL>  select 'Select count(*) From ' || table_name || ';'
  2   from user_tables order by table_name;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From ACTIVECLIENTS;
Select count(*) From LOCKS;
Select count(*) From M_ATTRIBUTEVALUESC106P0V0;
Select count(*) From M_MACROPARAMETERSETC230P1V0;
Select count(*) From M_MASKCOLUMNC250P1V0;
Select count(*) From M_MASKROWC250P0V0;
Select count(*) From M_ODATTRIBUTESETC533;
Select count(*) From M_ODATTRIBUTETYPESETC547;
Select count(*) From M_ODCONFOBJEDITORSETC533;
Select count(*) From M_ODGRPSETC533;
Select count(*) From M_ODPAGESETC533;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From M_ODPCINFOSETC551;
Select count(*) From M_ODSETOBJECTSC486P0V8;
Select count(*) From M_PALLOWANCESETC365P1V0;
Select count(*) From M_PALLOWANCESETC380P1V0;
Select count(*) From M_PAUTORELATIONPATHINFC351P0V3;
Select count(*) From M_PAUTORELATIONPATHSETC419P0V3;
Select count(*) From M_PCRCALCMODELSETC168P0V0;
Select count(*) From M_PCRCALCMODELUSAGESETC315P1V0;
Select count(*) From M_PCRPRODPROGCALCMODELC315P2V0;
Select count(*) From M_PCRTOKENSETC284P1V0;
Select count(*) From M_PCURVEPOTDATASETC172P1V0;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From M_PCURVEPOTSETC172P2V0;
Select count(*) From M_PCUSTOMERSITESETC181P1V0;
Select count(*) From M_PDATACARDSETC508P0V11;
Select count(*) From M_PERGOPLANTYPECHILDSEC132P1V0;
Select count(*) From M_PERGOPLANTYPEPARENTSC132P0V0;
Select count(*) From M_PISEGRIMFUNCTIONINFOC215P0V0;
Select count(*) From M_PISEGRIMPARAMETERSETC212P0V0;
Select count(*) From M_PLABELSETC325P1V0;
Select count(*) From M_PLAYOUTSETC260P4V0;
Select count(*) From M_PMAINPRODUCTIONPROGRC390P2V1;
Select count(*) From M_PPPCODERULESETC315P4V1;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From M_PPPCRUSAGESETC315P3V1;
Select count(*) From M_PPRODUCTIONPROGRAMSEC390P1V1;
Select count(*) From M_PPRODUCTIONPROGRAMUSC390P3V1;
Select count(*) From M_PSHIFTMODELDATASETC177P1V0;
Select count(*) From M_PTIMEANALYSISUSAGESEC342P0V8;
Select count(*) From M_PVARIABLESETC474P0V8;
Select count(*) From M_PWORKERSETC146P1V0;
Select count(*) From M_SETAMOUNTPRICEC490P0V8;
Select count(*) From M_SETCOLUMNNAMESC473;
Select count(*) From M_SETDATATYPESC473;
Select count(*) From M_SETEXPOSEDLINKSC493P0V10;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From M_SETRELEASETABLEENTRIESC610;
Select count(*) From M_SETYEARPRICEREDUCTIOC490P1V8;
Select count(*) From M_TIMEANALYSISCOORDINAC470P0V8;
Select count(*) From M_TIMEANALYSISLINESETC375P0V8;
Select count(*) From M_VALUEPAIRSC483P0V8;
Select count(*) From OPTIONREGISTRY;
Select count(*) From POET_CLASSES;
Select count(*) From POET_INDEXES;
Select count(*) From POET_MEMBERS;
Select count(*) From ROOTBLOCK;
Select count(*) From SURROGATES;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XBLOBVALUEC497V11;
Select count(*) From XBOUNDINGBOXC335V0;
Select count(*) From XCHANGEENTRYC304V0;
Select count(*) From XCRUSAGEC385V1;
Select count(*) From XDBTOOLSLOG;
Select count(*) From XDO3DSTATEC519V12;
Select count(*) From XDO3DSTATEPOSITIONOBJECC520V12;
Select count(*) From XDOACTIONPROXY;
Select count(*) From XDOALLOWANCEC361V0;
Select count(*) From XDOALLOWANCESETC365V0;
Select count(*) From XDOATTACHMENTC207V0;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XDOATTRIBUTEVALUEBOOLC101V0;
Select count(*) From XDOATTRIBUTEVALUEC100V0;
Select count(*) From XDOATTRIBUTEVALUEDATETIMC105V0;
Select count(*) From XDOATTRIBUTEVALUEDOUBLEC103V0;
Select count(*) From XDOATTRIBUTEVALUELONGC102V0;
Select count(*) From XDOATTRIBUTEVALUESETC106V0;
Select count(*) From XDOATTRIBUTEVALUESTRINGC104V0;
Select count(*) From XDOAUTORELATIONPATHINFOC419V3;
Select count(*) From XDOBALANCINGC380V0;
Select count(*) From XDOBLOBC318V0;
Select count(*) From XDOCALCRESULTC477V8;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XDOCHANGEORDERC382V0;
Select count(*) From XDOCMCRS;
Select count(*) From XDOCODERULEC290V0;
Select count(*) From XDOCOMPANY;
Select count(*) From XDOCONFIGUREDOBJECTC453V8;
Select count(*) From XDOCONTRACT;
Select count(*) From XDOCOUNTRY;
Select count(*) From XDOCRCALCMODELC284V0;
Select count(*) From XDOCRCALCMODELUSAGEC314V0;
Select count(*) From XDOCRTOKENC287V0;
Select count(*) From XDOCRTOKENL2RC289V0;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'

... SNIP...


'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XDOPROCESSRESTRICTIONC131V0;
Select count(*) From XDOPROCESSRISKC130V0;
Select count(*) From XDOPRODUCTIONMETHODC274V0;
Select count(*) From XDOPRODUCTIONPROGRAMC315V0;
Select count(*) From XDOPRODUCTIONPROGRAMUSAGC389V1;
Select count(*) From XDOPROJECTTARGETC189V0;
Select count(*) From XDOPROJECTTEAMC188V0;
Select count(*) From XDOPROXYCFGELEMENT;
Select count(*) From XDOPROXYPARENTCHILD;
Select count(*) From XDORAWMATERIALC267V0;
Select count(*) From XDOREACHRANGEC139V0;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XDOREGULARTYPE;
Select count(*) From XDORELATIONOBJECTC162V1;
Select count(*) From XDORELATIONSHIPAUTOC351V3;
Select count(*) From XDORELATIONSHIPC149V0;
Select count(*) From XDORELATIONSHIPORDERC457V8;
Select count(*) From XDORELATIONSHIPPLANTPROVC155V0;
Select count(*) From XDORELATIONSHIPPROCPROCEC156V8;
Select count(*) From XDORELATIONSHIPPROCRUNNIC158V8;
Select count(*) From XDORELATIONSHIPPROCUSESPC159V0;
Select count(*) From XDORELATIONSHIPWELDPOINTC353V0;
Select count(*) From XDORELEASETABLE;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XDORSASSOCIATION;
Select count(*) From XDORSPROCESSIMPLREQ;
Select count(*) From XDORSPROCMANAGESVIS;
Select count(*) From XDOSCRIPTACTIONC404V1;
Select count(*) From XDOSCRIPTC339V0;
Select count(*) From XDOSCRIPTCOMMANDC516V12;
Select count(*) From XDOSCRIPTVARIABLEC473V8;
Select count(*) From XDOSDMOBJECTSTORAGEITEMC483V8;
Select count(*) From XDOSECURITYLEVEL;
Select count(*) From XDOSHIFTMODELC177V0;
Select count(*) From XDOSHIFTMODELDATAC176V0;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XDOSIMULATIONWORKCELLC426V3;
Select count(*) From XDOSUBCOMPITEMC218V0;
Select count(*) From XDOSUBCOMPVIEWITEMC221V0;
Select count(*) From XDOSUBCOMPVIEWITEMITEMC228V0;
Select count(*) From XDOSUBCOMPVIEWITEMITEMPRC229V0;
Select count(*) From XDOSUBCOMPVIEWITEMLISTPRC250V0;
Select count(*) From XDOSUBCOMPVIEWITEMLISTPRC252V0;
Select count(*) From XDOSUBCOMPVIEWITEMLISTREC253V0;
Select count(*) From XDOTAILNUMBERC431V3;
Select count(*) From XDOTARGETC302V0;
Select count(*) From XDOTEMPLATEUSAGEC464V8;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XDOTIMEANALYSISC342V0;
Select count(*) From XDOTIMEANALYSISCOORDINATC470V8;
Select count(*) From XDOTIMEANALYSISDESCRIPTIC372V0;
Select count(*) From XDOTIMEANALYSISEVALUATIOC427V3;
Select count(*) From XDOTIMEANALYSISFORC430V3;
Select count(*) From XDOTIMEANALYSISLINECONTAC375V0;
Select count(*) From XDOTIMEANALYSISUASC400V1;
Select count(*) From XDOV5SCRIPT;
Select count(*) From XDOVALUEADDEDENTRY;
Select count(*) From XDOVALUEADDEDGRP;
Select count(*) From XDOVBAPROJECTC466V8;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XDOVERSIONC345V0;
Select count(*) From XDOVERSIONINFOC294V0;
Select count(*) From XDOVISIONFIELDC140V0;
Select count(*) From XDOVISPROCESSGRAPHC259V0;
Select count(*) From XDOWAGEGROUPC190V0;
Select count(*) From XDOWILIBRARY;
Select count(*) From XDOWILIBRARYITEM;
Select count(*) From XDOWORKERC142V0;
Select count(*) From XDOWORKERREPRESENTATIONC141V0;
Select count(*) From XDOWORKPOSITIONC146V0;
Select count(*) From XEXPOSEDLINKSSETC493V10;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XMUFUNCTIONNAMEC498V11;
Select count(*) From XMURIGHTITEMC449V8;
Select count(*) From XMURIGHTSUBJECTC435V8;
Select count(*) From XMURULE;
Select count(*) From XMURULETORIGHTSUBJECT;
Select count(*) From XMUSINGLEUSERC443V8;
Select count(*) From XMUUSERTOGROUPC445V8;
Select count(*) From XPRICESETSC490V8;
Select count(*) From XPTATTRIBUTE;
Select count(*) From XPTATTRIBUTETYPE;
Select count(*) From XPTBROWSERID;

'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
----------------------------------------------------
Select count(*) From XPTCONFOBJEDITOR;
Select count(*) From XPTFORMAT;
Select count(*) From XPTGLOBALATTRIBUTEFORMATSPEC;
Select count(*) From XPTGRP;
Select count(*) From XPTPAGE;
Select count(*) From XPTPARENTCHILD;
Select count(*) From XPTPCINFO;
Select count(*) From XPTTYPE;
Select count(*) From XSIMPLERS;
Select count(*) From XTASKOBJECTC332V0;

252 rows selected.

SQL>
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  16. Jul 19th, 2007, 09:00 AM #14
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    And then..... this is what I expected but not what I see in Visual Studio when connected to the same datasource...

    SQL> Select count(*) From XDOERGOCOMPPRODUCTDEFAULC242V0;

    COUNT(*)
    ----------
    677

    SQL>
    Last edited by staticbob; Jul 19th, 2007 at 09:04 AM.
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  17. Jul 19th, 2007, 09:02 AM #15
    GaryMazzone
    GaryMazzone is offline
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,493

    Re: Count rows in tables

    To test that is OK SQL copy one of the statements and paste it back into SQL*Plus and run it.

    See if that gives the count you would expect.

    IF it does I'll show you how to turn off the headings,feedback and set the pagesize so that the commands are all one after the other with out page breaks.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1
    Reply With Quote Reply With Quote
  18. Jul 19th, 2007, 09:03 AM #16
    GaryMazzone
    GaryMazzone is offline
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,493

    Re: Count rows in tables

    Is the SQL being generated by VS the same as that select statement?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1
    Reply With Quote Reply With Quote
  19. Jul 19th, 2007, 09:05 AM #17
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    I'm not seeing the SQL generated in Visual Studio, just trying to preview the data in that table. I'll manually code something now to see what I get.
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  20. Jul 19th, 2007, 09:12 AM #18
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    OK, this is the code I have now. I have commented out everything in the button click handler and the messagebox is now giving me....... 677 !!!

    But, if I go back to my original code, I get a datatable with 2 colouns, all the table names as expected but the row count is zero on everything apart from the first open sessions table.

    Bob

    Code:
    Imports Oracle.DataAccess.Client
Public Class test

    Private Structure stTableInfo
        Dim stTable As String
        Dim intCount As Integer
    End Structure

    Private atCount As stTableInfo()
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim atcount(0)
        'Me.countTables()
        'Dim strSQL As String = "Select Count(*) From "
        'Dim strT As String = String.Empty
        'For i As Integer = 0 To atcount.GetUpperBound(0)
        '    If Me.atCount(i).stTable <> String.Empty Then
        '        strSQL &= Me.atCount(i).stTable
        '        Me.atCount(i).intCount = Return_Oracle_Int(strSQL)
        '    End If
        'Next

        'Dim dt As New DataTable
        'dt.Columns.Add("TableName")
        'dt.Columns.Add("RecordCount")

       
'THIS WORKS!
        MessageBox.Show(Return_Oracle_Int("Select count(*) From XDOERGOCOMPPRODUCTDEFAULC242V0"))



        'For i As Integer = 0 To Me.atCount.GetUpperBound(0)
        '    dt.Rows.Add(Me.atCount(i).stTable, Me.atCount(i).intCount.ToString())

        '    'strT = String.Empty
        '    'strT = "Table: " & Me.atCount(i).stTable & " contains: " & Me.atCount(i).intCount.ToString() & " rows"
        '    'MsgBox(strT)
        'Next
        'Me.dgv_tablefields.DataSource = dt


    End Sub

    Private Sub countTables()
        Dim strConn As String = "Data Source=pe9i;"
        strConn &= "User ID=e5_database;"
        strConn &= "Password=databaseora;"
        Dim oCmd As OracleClient.OracleCommand
        Dim oDR As System.Data.OracleClient.OracleDataReader
        Dim strSQL As String = "Select Table_Name From user_tables"
        oCmd = New System.Data.OracleClient.OracleCommand()
        Try
            With oCmd
                .Connection = New System.Data.OracleClient.OracleConnection(strConn)
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = strSQL
                oDR = .ExecuteReader()
            End With
            Dim i As Integer = -1
            While oDR.Read()
                i += 1
                ReDim Preserve atCount(i)
                atCount(i).stTable = oDR.GetOracleString(0).ToString().Trim()
            End While
            oDR.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
        End Try
        oCmd.Dispose()
    End Sub
    Public Function Return_Oracle_Int(ByVal strSQL As String) As Integer
        Dim strConn As String = "Data Source=pe9i;"
        strConn &= "User ID=e5_database;"
        strConn &= "Password=databaseora;"
        Dim oCmd As System.Data.OracleClient.OracleCommand
        Dim oDr As System.Data.OracleClient.OracleDataReader
        Dim intRet As Integer = -1

        oCmd = New System.Data.OracleClient.OracleCommand
        Try
            With oCmd
                .Connection = New System.Data.OracleClient.OracleConnection(strConn)
                .Connection.Open()
                .CommandType = CommandType.Text
                .CommandText = strSQL
                oDr = .ExecuteReader()
            End With
            oDr.Read()
            intRet = Integer.Parse(oDr.GetInt32(0))
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            oCmd.Connection.Close()
            oCmd.Connection.Dispose()
        End Try
        oCmd.Dispose()
        Return intRet
    End Function

 
End Class
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  21. Jul 19th, 2007, 09:17 AM #19
    GaryMazzone
    GaryMazzone is offline
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,493

    Re: Count rows in tables

    I don't see in the comment code where you set up a datarow, set the datarowvalues to the values in the array then append the datarow to the datatable.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1
    Reply With Quote Reply With Quote
  22. Jul 19th, 2007, 09:22 AM #20
    staticbob
    staticbob is offline

    Thread Starter
    Fanatic Member staticbob's Avatar
    Join Date
    Jan 2005
    Location
    Manchestershire, UK Cabbage: I do
    Posts
    619

    Re: Count rows in tables

    I'm doing that here aren't I?

    dt.Rows.Add(Me.atCount(i).stTable, Me.atCount(i).intCount.ToString())
    "I dislike 7 am. If 7 am were a person, I would punch 7 am in the biscuits." - Paul Ryan, DailyRamblings
    Reply With Quote Reply With Quote
  23. Jul 19th, 2007, 09:26 AM #21
    GaryMazzone
    GaryMazzone is offline
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,493

    Re: Count rows in tables

    Look at this page:

    http://www.codeproject.com/Purgatory...ndDataGrid.asp
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules


Click Here to Expand Forum to Full Width