MySQLPreparedStatement fails using JOIN

General
1

I have data in two tables that are related using VRCNUM. The following code prepares the MySQL prepared statement

Properties are:
DatabaseConnection As MySQLCommunityServer
RequestPathComponents() As String

[code]Dim my_str As String = (“SELECT *” + _
" FROM " + APIRequest.SQLGetTable + _
" LEFT JOIN Contributions_" + APIRequest.SQLGetTableYear + _
" ON " + APIRequest.SQLGetTable + “.VRCNUM = Contributions_” + APIRequest.SQLGetTableYear + “.VRCNUM” + _
" WHERE LD = ‘" + APIRequest.SQLLDPrepare + _
"’ AND (Party = ‘" + APIRequest.SQLParty + _
“) AND Election_District = '” + APIRequest.SQLEDPrepare + _
"’ AND Street = ‘" + APIRequest.SQLGetStreet + _
"’ ORDER BY (Street) ASC, (House_Number+0) ASC, Apartment ASC")

APIRequest.SQLStatement = APIRequest.DatabaseConnection.Prepare(my_str)

APIRequest.SQLStatement.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
APIRequest.SQLStatement.Bind(0, APIRequest.RequestPathComponents(2))
[/code]

The problem is VRCNUM does not return a value which is needed. When I run the data contained in my_str in MySQL Workbench all is good.

I need data from the other table to be included when VRCNUM are in each Table and always returning APIRequest.SQLGetTableYear data

Not certain what is not correct but something is not right. Any thoughts on how to use a JOIN in a MySQL prepared statement? Thanks

2

That looks more like a constructed query as opposed to a Prepared Statement… by “constructed” I mean you are concatentaing values to create an SQL statement instead of using “?” and Bind… since myStr contains no “?” characters

3

Try “Select *, “ + ApiRequest.SqlGetTable + “.VRCNUM AS myvrcnum …

Then have a look for the value in myvrncnum

4

Thanks to both for your responses. @Marius Dieter Noetzel that did resolve the problem

@Dave S - I looked at doing that at first but could not figure out the syntax. After Marius post I went back and was able to create the Bind sequence of statements. So in the end I had both working.

I am so into sql statements not using BIND et al that I go back and forth all the time. Thanks again for your response