Have a look at your code and imagine what you are trying to achieve.
SQL
variable is global variable, am i right?
In this line:
SQL = "SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
" WHERE A.AID=B.AID AND BITT='FALSE'"
you set value for variable correctly, but depends on count of listview subitems and values, your
SQL
variable:
SQL = SQL & "'" & ListView1.ListItems(i).ListSubItems(j).Text & "'"
will be looks like:
"SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
" WHERE A.AID=B.AID AND BITT='FALSE''subitem1';"
"SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
" WHERE A.AID=B.AID AND BITT='FALSE''subitem1''subitem2';"
"SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
" WHERE A.AID=B.AID AND BITT='FALSE''subitem1''subitem2'...'subitem99999';"
Use debugger!
Your query is wrong, because of 2 reasons:
1) Not using JOIN's
2) Is not updatable (probably)
Instead
"SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
" WHERE A.AID=B.AID AND BITT='FALSE'"
use
"SELECT A.*, B.* FROM [ASSET] AS A LEFT JOIN [NOTE] AS B " _
" ON A.AID=B.AID WHERE BITT='FALSE'"
More about JOINS you'll find here:
http://www.w3schools.com/sql/sql_join.asp[
^]
Visual Representation of SQL Joins[
^]
If you want to add new record to recordset, your query need to be updatable:
AddNew method[
^]
If
BITT
field is
Bit
data type, your query should looks like:
"SELECT A.*, B.* FROM [ASSET] AS A LEFT JOIN [NOTE] AS B " _
" ON A.AID=B.AID WHERE BITT=FALSE"
or
"SELECT A.*, B.* FROM [ASSET] AS A LEFT JOIN [NOTE] AS B " _
" ON A.AID=B.AID WHERE BITT=0"
Please, follow the below links:
Using ADO with MS Visual Basic[
^]
ADO Code Examples in Visual Basic[
^]