Loop problem, i think

[testare]

Error message that i get i

ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been ...

In table1 there is a primary key on ID.
In table2 there is no primary key on ID.

If i remove the loop then it works

Set Conn = Server.CreateObject("ADODB.Connection")
Set rs   = Server.CreateObject("ADODB.Recordset")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/support.mdb")

sSQL = "SELECT A.*, B.* FROM TABLE1 A LEFT JOIN TABLE 2 B ON A.ID = B.ID WHERE A.ID=58" 

Set rs = Conn.Execute(sSQL)

While Not rs.EOF
	Response.Write ("test")
	rs.MoveNext
Wend

I've tried for hours but yet not solved this problem.
What i'm i doing wrong?

 

[DotNetGnat]

how about this:

If rs.EOF AND rs.BOF then
response.write "no records found"
Else
Do Until rs.EOF
Response.Write ("test")
rs.MoveNext
Loop
End if

-DNG

 

[testare]

Still the same problem.

I cant understand why.
What is wrong with the loop.
Completed with info about the tables

Info in TABLE1

ID TEXT STORE PHONE DATE
58 .Net Kungens Kurva 11213213 2005-10-24 09:39:08

Info in TABLE2

ID NAME DATE COMENT
58 TEST 2005-10-24 13:47:53 asdfasdf
58 TEST 2005-10-24 14:22:08 asdfasdf
58 TEST 2005-10-24 14:22:11 asdfasdf
58 TEST 2005-10-24 14:24:30 asdfasdf

 

[Bloke152]

Give this a try, not sure if it will do the job but it might
this is the loop i would use.

do while not rs.BOF and not rs.EOF
Response.Write ("test")
rs.MoveNext
loop

 

[kav123]

Have you checked if the query works when you run it in the database. Try changing the query to:
"SELECT A.*, B.* FROM TABLE1 AS A LEFT JOIN TABLE 2 AS B ON A.ID = B.ID WHERE A.ID=58"

 

[testare]

I sat down this morning and saw that the loop works if i comment out <%'=rs("BUGG")%> like this.
As soon as i do it like this <%=rs("BUGG")%>, i get that error message.

I will paste the hole code

<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
 <tr>
  <td height="386" valign="top">
<%
		Set Conn = Server.CreateObject("ADODB.Connection")     'Connection 
		Set rs   = Server.CreateObject("ADODB.Recordset")     'Recordset
		Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/support.mdb")  'Öppnar db Connection
		
		sSQL = "SELECT A.*, B.* FROM TABELL1 A LEFT JOIN TABELL2 B ON A.ID = B.ID WHERE A.ID=58" '& Request.QueryString("test")
		Set rs = Conn.Execute(sSQL)
		Response.Write(sSQL)
				
		While Not rs.EOF
		  Response.Write("test")		  
		  rs.MoveNext
		Wend
%>	
   <table width="100%" height="0" border="0" cellpadding="0" cellspacing="2">
    <tr>  
     <td width="68%" valign="top"><table width="100%" height="67" border="0" cellpadding="0" cellspacing="1"  bgcolor="#000000">
      <tr>
       <td  valign="top">
	    <table width="100%" border="0" bgcolor="#FFFFFF">
         <tr>
          <td width="100%" valign="top" bgcolor="#EFEFEF">
		<table>
	     <tr>
         </tr>
		 </table>
         <%=rs("BUGG")%><br><%=rs("INSKRIVEN")%> &nbsp; <%=rs("INSDATUM")%>
        </td>
       </tr>
      <tr>
	 <td valign="top" width="100%"><br><%=Replace(rs("BUGGINFO"), vbCrLf, "<br>")%></td>
	</tr>
    </table>
	</td>
    </tr>
    </table> 
    <br>
    <table width="100%" border= "0" cellpadding="" cellspacing="1" bgcolor="#E0E0C2">
     <tr>
      <td>
	   <table cellpadding="2" cellspacing="0" border = "0" width = "100%" bgcolor="#EFEFEF">
        <tr>
         <td width = "71%"><%=rs("BUGG")%></td>
         <td width = "29%" align="right">&nbsp;</td>
        </tr>
        <tr>
         <td colspan = "2"><%=rs("NAMN")%></td>
        </tr>
      </table>
     </td>
    </tr>
  </table>
  <table width="100%" border="0" cellpadding="2" cellspacing="0" bgcolor="#FFFFFF">
   <tr>
    <td><%=rs("KOMMENTAR")%></td>
   </tr>
  </table>
  <br>
  <table width="100%" border="0" cellpadding="0" cellspacing="1" bgcolor="#000000">
   <tr>
    <td>
	 <table width="100%" border="0" bgcolor="#D7D7FF">
	  <form method="post" action="default.asp?action=insert&test=<%=rs("ID")%>">
      <tr>
	   <td><textarea name="txtkomm" cols="65" rows="10"></textarea></td>
      </tr>
	  <tr>
	   <td><input type="submit" name="cmdSubmit" value="Spara">&nbsp;&nbsp;<input type="reset" value="Rensa"></td>
	  </tr>
	   </form>
     </table>
	</td>
   </tr>
  </table>
 </td>
</tr>
  </table></td>
    </tr>
  </table>

 

[DotNetGnat]

are you sure you have the field BUGG in either of the tables...if it is both the tables then even that is a problem...

-DNG

 

[testare]

Hi DNG

The field KOMMENTAR is only in Table1, If i remove
<%'=rs("KOMMENTAR")%> to <%=rs("KOMMENTAR")%> then i get the error message

 

[DotNetGnat]

what is the actual error message that you are getting...and also i would suggest you to change your query to the following...

sSQL = "SELECT A.field1,A.field2, B.field1,B.field2 FROM TABLE1 A LEFT JOIN TABLE 2 B ON A.ID = B.ID WHERE A.ID=58"

if suppose you have field names same in both the tables then...do this...

sSQL = "SELECT A.field1,A.field2, [red]A.field as Afield[/red] B.field1,B.field2, [red]B.field as Bfield[/red] FROM TABLE1 A LEFT JOIN TABLE 2 B ON A.ID = B.ID WHERE A.ID=58"

and then when you display...you have to either specify Afield or Bfield depending on what you want

-DNG

 

[testare]

I've changed the sql to this

sSQL = "SELECT A.INSDATUM, B.KOMMENTAR FROM JINFO A LEFT JOIN KOMMENTAR B ON A.ID = B.ID WHERE A.ID=58"

The column INSDATUM exist only in table1 and column KOMMENTAR exist only in table2

When want to show the Kommentar field i typed <%=rs("B.KOMMENTAR")%>

Now i recieve the error

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal

 

[DotNetGnat]

sSQL = "SELECT A.INSDATUM, B.KOMMENTAR as mykommentar FROM JINFO A LEFT JOIN KOMMENTAR B ON A.ID = B.ID WHERE A.ID=58"

then use like this:

<%=rs("mykommentar")%>

you have the field name and table name...may be its getting confused with that and when you display you dont need to specify the table name

-DNG

 

[testare]

Now i get
ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been ...

 

[testare]

I've changed so that you wouldn't be confused
This works

sSQL = "SELECT A.INSDATUM, B.KOMMENTAR as mykommentar FROM JINFO A LEFT JOIN FORUM B ON A.ID = B.ID WHERE A.ID=58"
Set rs = Conn.Execute(sSQL)
Response.Write(sSQL)

If rs.EOF AND rs.BOF then
response.write "no records found"
Else
Do Until rs.EOF
 Response.Write("<br>")
 Response.Write(rs("MYKOMMENTAR"))
 rs.MoveNext
Loop
End if

But with this code it doesn't.
I recive this message

ADODB.Field (0x80020009)
Either BOF or EOF is True, or the current record has been deleted. ...

sSQL = "SELECT A.INSDATUM, B.KOMMENTAR as mykommentar FROM JINFO A LEFT JOIN FORUM B ON A.ID = B.ID WHERE A.ID=58" 
Set rs = Conn.Execute(sSQL)
Response.Write(sSQL)

If rs.EOF AND rs.BOF then
response.write "no records found"
Else
Do Until rs.EOF
 Response.Write("<br>")%>
<%=rs("MYKOMMENTAR")%>
<% Response.Write(rs("MYKOMMENTAR"))
 rs.MoveNext
Loop
End if