[RESOLVED] Array value in SQL

[Nizam]

I'm getting this error

Syntax error (missing operator) in query expression 'Upline_ID ='.

Code:
For x = 1 to i
sql2 = "SELECT * FROM [User] WHERE Upline_ID =" & arrLevel1(x)


arrLevel() is an array, already been defined and content. The contents are integer value.


Help anyone....

[chazzy]

what dbms?

[russell]

arrLevel1(x) is empty

[Nizam]

I'm using ASP & Access. The array I've already checked. It has 3 different values :confused:

[chazzy]

surely there is more code here then... what happens if you echo the array values?

[Nizam]

i is recordcount and has 3 records

Code:
For x = 1 to i
response.write(arrLevel1(x) & "<br />")
Next

Result:
4
5
6

So, arrLevel1(x) isn't empty right?

[russell]

As Chazzy said, need to see more of your code. are you executing sql2? is it getting changed later in the code? Did u response.write sql2?

If you are executing a FOR loop, and writing a query, then after the loop executing it, then your iterator is going to be one higher than you expect. cant possibly troubleshoot without writing out the query.

Also, if this does what it looks like it does

For x = 1 to i
sql2 = "SELECT * FROM [User] WHERE Upline_ID =" & arrLevel1(x)

Why bother looping and executing multiple times? How about

sql2 = "SELECT * FROM [User] WHERE Upline_ID <= " & i
Of course this is a wild guess without u showing us the code thats breaking...

[Nizam]

Here is the code for this page. This page is for a member to see his 1st and 2nd downline for MLM page. Table [User] has fields ID,Upline_ID and all other info. Sorry the code is a bit long...


'==========================LEVEL1==========================
sql = "SELECT * FROM [User] WHERE Upline_ID =" & Session("ID")

Set rsLevel1 = Server.CreateObject( "ADODB.Recordset" )
rsLevel1.Open sql, conn, 3
totalrecord_level1 = rsLevel1.recordcount
Dim arrLevel1(100000),i
Dim currentID



'----------Show Level 1 Downline-------------------
if totalrecord_level1 = 0 then
%>
'No Record
<%
else


While Not rsLevel1.EOF
i = 1
currentID = rsLevel1("ID")
arrLevel1(i) = CInt(currentID)

%>

'Level 1 Downline Data <%
i = i + 1
rsLevel1.MoveNext
Wend
end if
%>
'========================END OF LEVEL1==============

'===========================LEVEL 2================
<%
For x = 1 to i

sql2 = "SELECT * FROM [User] WHERE Upline_ID =" & arrLevel1(x)
Set rsLevel2 = Server.CreateObject( "ADODB.Recordset" )
rsLevel2.Open sql2, conn, 3
totalrecord_level2 = rsLevel2.recordcount
response.write(arrLevel1(x) & "<br />")



if totalrecord_level2 = 0 then
%>
'No Record<%
else

While Not rsLevel2.EOF
i = 1
currentID = rsLevel1("ID")
arrLevel1(i) = currentID
%>
'Level 2 Downline Data <%
i = i + 1
rsLevel2.MoveNext
Wend
end if


rsLevel2.close
set rsLevel2 = nothing
Next
%>
'===========================END OF LEVEL 2=============

[russell]

i is one higher than u think it is (i think). What if you response.write i?

back to your original post, i believe you need to change it to this:

For x = 1 to i -1
sql2 = "SELECT * FROM [User] WHERE Upline_ID =" & arrLevel1(x)

let us know if that solves it...

[Nizam]

Thanks mate! Solved! Plus made some logic error in the code above as well. I've already fixed it....

Thank you... ;)