[Aboch]

I am attempting to run a little applicaiton that tells me how many rows are in each table of a database. I am dealing with a little over a thousand tables. I have a schema so I Loop through the schema table hoping to then export the row counts to another datatable. I don't know much about sql so the majority of this is a result of me jumping through tutorials on using SQL in .net

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myConnection As OdbcConnection = New OdbcConnection()
        myConnection.ConnectionString = "Dsn=" + DSNBOX.Text + ";Userid=" + USERIDBOX.Text + ";Password=" + PasswordBox.Text + ";Database=" + Databasebox.Text


        Try
            myConnection.Open()
            MsgBox("win")
        Catch ex As Exception
            MsgBox("Fail")
        End Try

        Dim Sychmacount As Integer
        Dim i As Integer
        Dim CountCMD As String
        Dim Mycommand As New OdbcCommand()
        Dim myAdapter As New OdbcDataAdapter
        Dim Rowcounter As String
        Mycommand.Connection = myConnection

        Sychmacount = SchemaDataSet.admin_syschema.Rows.Count
        For i = 0 To Sychmacount

            CountCMD = "select count(" + Trim(SchemaDataSet.admin_syschema.Rows(i).Item(3)) + ") from admin." + Trim(SchemaDataSet.admin_syschema.Rows(i).Item(0))

            MsgBox(CountCMD)'Example text: "Select count(accause.number) from admin.accause"
            Mycommand.CommandText = CountCMD
            myAdapter.SelectCommand = Mycommand

            'this is were I get the error
            Rowcounter = Mycommand.ExecuteScalar
            Me.The_ProductTableAdapter.Insert(SchemaDataSet.admin_syschema.Rows(i).Item(0), SchemaDataSet.admin_syschema.Rows(i).Item(3), Rowcounter)


        Next i
    End Sub

When I run the code I get a "Win" and when it reaches the command.executescaler i get the error
"ERROR [2a504] [FairCom][ODBC FairCom Driver 9.2.41014(Build-100505_110424)][ctreeSQL] -20003 Syntax error"

I assume my syntax is wrong per the message but I am too new to SQL to understand what i am missing. I was also concerned with the CtreeSql which I looked at faircoms site and they were talking about an interface for VB but they didn't offer it on thier site. As a side not I don't have access to the server as an admin only through the CtreeaceODBC datasource set up on my machine. Any insight or work around would be greatly appreciated. Thank you for your time.

[Martyr2]

Set a breakpoint on the ExecuteScalar line. Then hover over the CountCMD variable. Does it read correctly and what you expect it too? Does it fill in the table name and field names properly? The first job here is to make sure that the SQL is what is expected and then you can work from there. Instead of count(accause.number) what if you just use count(*)? Have you tried a select like....

select count(*) from accause

Here we have just simplified things a little. See if this works for you.

[Aboch]

Martyr2, on 24 August 2012 - 08:02 PM, said:

Set a breakpoint on the ExecuteScalar line. Then hover over the CountCMD variable. Does it read correctly and what you expect it too? Does it fill in the table name and field names properly? The first job here is to make sure that the SQL is what is expected and then you can work from there. Instead of count(accause.number) what if you just use count(*)? Have you tried a select like....

select count(*) from accause

Here we have just simplified things a little. See if this works for you.

After an initial run that seemed to do the trick. I really need to get myself a SQL bible. Thank you very much