B4J Question Error getting a record count from SQL

Peter Lewis

Active Member
Licensed User
Longtime User
Hi

I am looping through an sql table of customer names and placing it in a table. I wanted to add a second column in the Table that showed how many items belonged to that customer from a second table.

The problem I am having is getting the server to recognise the name of the customer. I have converted it to a string and tried all options using that. At the moment there are about 250 customers which it goes through and loads up.

Here is the code, any advice would be appreciated.. Thank you

B4X:
Sub FillTableCust
    TableView2.Items.Clear
    Sql1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://162.215.252.76:3306/himelcoz_diamonds", "himelcoz_testing", "ZXNb^iVIP1_=")
    Dim rt As ResultSet = Sql1.ExecQuery("SELECT Name FROM Customer")
    Do While rt.NextRow
    Dim rowc(2) As Object
    rowc(0) = rt.GetString("Name")
    Dim Cust As String
        Cust = rt.GetString("Name")
       
    Dim NumberOfMatches As Int = Sql1.ExecQuerySingleResult("SELECT count(*) FROM tracking WHERE location ="&Cust)
   
   
    rowc(1) = NumberOfMatches
   
    TableView2.Items.Add(rowc)
    TableView2.SetColumnSortable(0, True)
    TableView2.SetColumnWidth(0,250)
    TableView2.SetColumnWidth(1,50)
    Loop
    rt.Close
   
   
       
End Sub
 

Daestrum

Expert
Licensed User
Longtime User
Have you tried putting the Cust in quotes ie,
B4X:
Dim NumberOfMatches As Int = Sql1.ExecQuerySingleResult("SELECT count(*) FROM tracking WHERE location ='"&Cust&"'")
 
Upvote 0

atiaust

Active Member
Licensed User
Longtime User
Hi Peter,

I think you need to read the second table using another resultset then loop thru that resultset to get the records.
 
Upvote 0

Peter Lewis

Active Member
Licensed User
Longtime User
Have you tried putting the Cust in quotes ie,
B4X:
Dim NumberOfMatches As Int = Sql1.ExecQuerySingleResult("SELECT count(*) FROM tracking WHERE location ='"&Cust&"'")
Hi

I tried it and this was the result

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Jewellers'' at line 1
 
Upvote 0

Daestrum

Expert
Licensed User
Longtime User
Does your data contain a single quote? (looks like it can from the error)
If so try
B4X:
Dim NumberOfMatches As Int = Sql1.ExecQuerySingleResult("SELECT count(*) FROM tracking WHERE location ="&chr(34) & Cust & chr(34))
 
Upvote 0

Peter Lewis

Active Member
Licensed User
Longtime User
I also tried a direct sql query using amn SQL program and this string does work , so the syntax is correct for this type of SQL

B4X:
SELECT count(*) FROM tracking WHERE location = "Durban Depot"
 
Upvote 0

Peter Lewis

Active Member
Licensed User
Longtime User
Does your data contain a single quote? (looks like it can from the error)
If so try
B4X:
Dim NumberOfMatches As Int = Sql1.ExecQuerySingleResult("SELECT count(*) FROM tracking WHERE location ="&chr(34) & Cust & chr(34))

Thank you , Daestrum

That worked perfectly

Attached is the result
dashboard.png
 
Upvote 0
Top