B4J Question Error getting a record count from SQL

[Peter Lewis]

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

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]

Have you tried putting the Cust in quotes ie,

Dim NumberOfMatches As Int = Sql1.ExecQuerySingleResult("SELECT count(*) FROM tracking WHERE location ='"&Cust&"'")

 

[atiaust]

Hi Peter,

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

 

[Peter Lewis]

Have you tried putting the Cust in quotes ie,

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

 

[Daestrum]

Does your data contain a single quote? (looks like it can from the error)
If so try

Dim NumberOfMatches As Int = Sql1.ExecQuerySingleResult("SELECT count(*) FROM tracking WHERE location ="&chr(34) & Cust & chr(34))

 

[Peter Lewis]

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

SELECT count(*) FROM tracking WHERE location = "Durban Depot"

 

[Peter Lewis]

Does your data contain a single quote? (looks like it can from the error)
If so try

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