Sarah

1. SQL Syntax 2 Guest

## Sarah

Hi All,<BR>I&#039;m having a devil of a time with this code and I keep getting an "unterminated string constant" Any and all help is appreciated! Ghost was helping me this morning and got me this far. <BR><BR>rs1= conn.Execute("Select LotCode as Expr1, LotClose from dbo.Lots ORDER BY LotClose")<BR><BR>Do While Not rs1.EOF<BR><BR>set rs2=conn.Execute ("SELECT Bidder, Amount, Date FROM dbo.Bids WHERE Amount = (SELECT MIN(Amount) AS Minimum FROM dbo.Bids WHERE BLotCode=" & "&#039;" & rs1.Fields("Expr1").value & "&#039;" )")<BR><BR>rs1.movenext<BR>Loop<BR><BR>Please help!<BR>Thanks in advance,<BR>Sarah

2. sarah total newbie Guest

## SQL syntax 2

Obviously so newbie-ish I can&#039;t put my name in the right place!

3. Senior Member
Join Date
Dec 1969
Posts
723

## SQL Syntax 2

Use this,<BR><BR>set rs2=conn.Execute ("SELECT Bidder, Amount, Date FROM dbo.Bids WHERE Amount = (SELECT MIN(Amount) AS Minimum FROM dbo.Bids WHERE BLotCode=" & "&#039;" & rs1.Fields("Expr1").value & "&#039;)")

4. Senior Member
Join Date
Dec 1969
Posts
369

## RE: SQL Syntax 2

Thanks Moshe!<BR>With that code- can I write a function so if rs2.EOF=true then print "no bids yet"? Where would I put it in the code? After rs2=conn.execute ("etc")?<BR>Thanks again!<BR>Sarah

5. Senior Member
Join Date
Dec 1969
Posts
723

## RE: SQL Syntax 2

Yes you can do this,<BR><BR>Set rs2 = conn.execute(...)<BR><BR>if not(rs2.eof and rs2.bof) then response.write "no bids yet"<BR><BR>By the way, what database are you using?

6. Senior Member
Join Date
Dec 1969
Posts
369

## RE: SQL Syntax 2

SQL Server 7. I was trying to write a function to do the "no bids yet" thing and it just burps all over itself. I am going to give your code a try- you&#039;ve led me on the right path so far! <BR>Thanks!<BR>Sarah

7. Sarah (fist meets screen) Guest

## RE: SQL Syntax 2

Hey Moshe-<BR>I am still getting that stupid ADODB.Field error: &#039;800a0bcd&#039; <BR>Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. <BR>How can this work? not all the lots will have bids and if someone wants to see a summary of Current high bids they&#039;ll get the ones that have bids then a big fat error. ARGH!<BR>Thanks!<BR>Sarah<BR>

8. Senior Member
Join Date
Dec 1969
Posts
723

## RE: SQL Syntax 2

You can combine your SQL into a single statment like so,<BR><BR>Select Lots.LotCode, Lots.LotClose,<BR>(<BR>SELECT Bidder, Amount, Date FROM Bids WHERE Amount = (SELECT MIN(Amount) AS Minimum FROM Bids WHERE Bids.BLotCode = Lots.LotCode<BR>) As total<BR>from Lots<BR>ORDER BY LotClose<BR><BR>if you need more help, email me: moshesol@hotmail.com

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•