B4J Question It is not possible to get data and the number of rows in the Database Table

[Darsiar]

It is not possible to get data and the number of rows in the Database Table
None of the listed methods works; the table is created and there is a record there:
If rs.NextRow Then <-- always gives the true, although there is nothing there !!!

Dim rs As ResultSet =SQL.ExecQuery("SELECT MAX(ClientId) FROM Client")
' Dim rs As ResultSet =SQL.ExecQuery("Select COUNT() FROM Client") ' same mistake!!!

Dim id As Int= 1

Try
If rs.NextRow Then
id=rs.GetInt("ClientId")
id = id + 1
End If
Catch
Log(LastException)
End Try
rs.Close

ERROR:
java.sql.SQLException: no such column: 'ClientId'

thanks for the advice

 

[Peter Lewis]

NumberOfRecords = sql1.ExecQuerySingleResult("SELECT count(*) FROM databasetablename")

 

[OliverA]

Ok. You need to help us make sense of you question. What is causing the issue? You actually have two queries here and we don't really know which one is causing issues and where the issue is exactly occurring. So query #1

   Dim rs As ResultSet =SQL.ExecQuery("SELECT MAX(ClientId) FROM Client")
   Dim id As  Int= 1
  
   Try
      If rs.NextRow  Then
       id=rs.GetInt("ClientId")
       id = id  + 1
     End If
   Catch
       Log(LastException)
   End Try
   rs.Close

When you run this, where is the error occurring? It makes a big difference. Is it the line

Dim rs As ResultSet =SQL.ExecQuery("SELECT MAX(ClientId) FROM Client")

than that would indicate that ClientId column does not exist

or is it

id=rs.GetInt("ClientId")

then it could be that the SQL Database you are using is creating a different column name for your MAX(ClientId). So instead of letting the DB decide for you do a

   Dim rs As ResultSet =SQL.ExecQuery("SELECT MAX(ClientId) As MaxClientId FROM Client")
   Dim id As  Int= 1
  
   Try
      If rs.NextRow  Then
       id=rs.GetInt("MaxClientId")
       id = id  + 1
     End If
   Catch
       Log(LastException)
   End Try
   rs.Close

Query #2

   Dim rs As ResultSet =SQL.ExecQuery("Select COUNT() FROM Client")  ' same mistake!!!  

   Dim id As  Int= 1
  
   Try
      If rs.NextRow  Then
       id=rs.GetInt("ClientId")
       id = id  + 1
     End If
   Catch
       Log(LastException)
   End Try
   rs.Close

If that is how you wrote that one, then there is no ClientId returned! Probably should have been:

   Dim rs As ResultSet =SQL.ExecQuery("Select COUNT() AS MaxClientId FROM Client")  ' same mistake!!!  

   Dim id As  Int= 1
  
   Try
      If rs.NextRow  Then
       id=rs.GetInt("MaxClientId")
       id = id  + 1
     End If
   Catch
       Log(LastException)
   End Try
   rs.Close

If rs.NextRow Then <-- always gives the true, although there is nothing there !!!

Actually, there is, otherwise that would not execute. You're just not getting back what you expect.

BTW: Ninja'd by @Peter Lewis, who has another way to get the answer you are looking for. I'm just explaining why you may have the issue you are experiencing.

 

[Darsiar]

Here is my complete program. Error occurs on line 137

 

[Darsiar]

I try your code and it works!
Thank you so much!!!
But I do not understand what this code means, because it is not in the textbook?
AS MaxClientId

---------------------------
Dim rs As ResultSet =SQL.ExecQuery("Select COUNT() AS MaxClientId FROM Client") ' same mistake!!!

Dim id As Int= 1

Try
If rs.NextRow Then
id=rs.GetInt("MaxClientId")
id = id + 1
End If
Catch
Log(LastException)
End Try
rs.Close

 

[Harris]

It is not possible to get data and the number of rows in the Database Table

Not with what you posted... (use code tags as well).

Not to criticize, but you must study and be knowledgeable of SQL for the answer to your question which is quite elementary...
A little more time on your part should have uncovered this problem - and educated you at the same time...
@OliverA took the time to explain this quite well.

 

[Darsiar]

I am very grateful!
This is really impressive in this forum - a good desire to help and teach each of us.
We are really still new to Basic, because before we had experience only with languages such as C #, F # in net.core 2 and net.core 3
Net.core is a little different there.
More flexible, wider, more powerful, more professional.
But here, too, impresses with its simplicity, which captivates the brilliance of ideas of enthusiasts!
It is surprising that such a simple language does so many useful things on people's enthusiasm!
And the enthusiasm and attitude towards people is the most important thing in any project.
Regards and admiration!
Thank.

 

[OliverA]

But I do not understand what this code means, because it is not in the textbook?
AS MaxClientId

AS creates an alias for the before standing column, table, function, etc. It's helpful in our case sense (as I mentioned) databases sometimes create their own column names. So for example, if I use Access to run the following query

SELECT Count(*) FROM dbo_Test

the column name that Access creates to access the result is Expr1000. If I use SQLite, that column name becomes Count(*). So instead having to guess what each DB Engine does, we can create an alias

SELECT Count(*) AS MaxClientId FROM dbo_Test

Now, the column name returned will always be MaxClientId, no matter the DB Engine used. I do have to agree with @Harris on this, that you'll need to learn a little about SQL to use SQL.[/code]

 

[Fabio vega]

is the sql sentence, chage to "SELECT MAX(ClientId) as 'ClientId' FROM Client