Advanced SQL Problem

[monfu]

Dear All,

I have 2 retreive some data from 2 tables that are linked together, however I cannot manage to do it.

My first table has three fields that are relevant to me and these are NAME,USERPASS and ACCOUNT, and the second table has CODE and EMAIL. ACCOUNT from the first table and CODE from the second table are linked together.

Now what I want to do is retreive the NAME AND USERPASS from the first table where the ACCOUNT is the same as the CODE and then send out an e-mail with the username and password.

I tried this query, however it is not working:-

SELECT XERSITES.ACCOUNT, STKCST.CODE, XERSITES.NAME, XERSITES.USERPASS, STKCST.EMAIL
FROM XERSITES INNER JOIN STKCST ON XERSITES.ACCOUNT = STKCST.CODE;

Can you please help me out?

Thanks for your time

[monfu]

Ok i have discovered that I cannot use an inner join since the driver does not support it! I have to do a nested loop

[monfu]

I have come up with this code:-

Set rsSelect=Server.CreateObject("ADODB.Recordset")
rsSelect.ActiveConnection = cnnDFlex

Set rsSelectSTK=Server.CreateObject("ADODB.Recordset")
rsSelectSTK.ActiveConnection = cnnDFlex

sqlSelect="SELECT name, userpass, account FROM xersites WHERE EMAIL <> ''"
rsSelect.Open (sqlSelect)
rsSelect.movefirst
do
sqlSelectSTK="SELECT code, email FROM stkcst WHERE EMAIL <> ''"
rsSelectSTK.Open (sqlSelectSTK)
rsSelectSTK.movefirst
do
if rsSelect("account") = rsSelectSTK("code") then
sqlInsertIntoTemp="insert into temp(name,userpass,email) values(" & rsSelect("Name") & ",'" & rsSelect("userpass") & "','" & rsSelectSTK("Email") & "')"
set rsInsertIntoTemp=cnnDFlex.execute(sqlInsertIntoTemp)
rsSelectSTK.movenext
loop until rsSelectSTK.eof
end if
rsSelect.movenext
loop until rsSelect.eof

but it is giving me an error:-

Error Type:
Microsoft VBScript compilation (0x800A040E)
'loop' without 'do'
/isl/admin/createtemptable.asp, line 42
loop until rsSelectSTK.eof


Please can you help me out?

[vass0922]

Set rsSelect=Server.CreateObject("ADODB.Recordset")
rsSelect.ActiveConnection = cnnDFlex

Set rsSelectSTK=Server.CreateObject("ADODB.Recordset")
rsSelectSTK.ActiveConnection = cnnDFlex

sqlSelect="SELECT name, userpass, account FROM xersites WHERE EMAIL <> ''"
rsSelect.Open (sqlSelect)
rsSelect.movefirst
do
  sqlSelectSTK="SELECT code, email FROM stkcst WHERE EMAIL <> ''"
  rsSelectSTK.Open (sqlSelectSTK)
  rsSelectSTK.movefirst
  do
    if rsSelect("account") = rsSelectSTK("code") then
      sqlInsertIntoTemp="insert into temp(name,userpass,email) values('" & rsSelect("Name") & "','" & rsSelect("userpass") & "','" & rsSelectSTK("Email") & "')"
      set rsInsertIntoTemp=cnnDFlex.execute(sqlInsertIntoTemp)
      rsSelectSTK.movenext
    end if

  loop until rsSelectSTK.eof
  rsSelect.movenext
loop until rsSelect.eof

1. You should use the code tags, otherwise you lose the indenting... if you don't indent DO, it will help you find problems like this.
2. You had your End If outside of the inner do loop, while your IF statement was Inside the Do Loop = borked loop
3. You also missed the single quotes around the name field in the recordset

[monfu]

Ok thanks mate, I appreciate a lot!