SQL Statements

[JGallo]

Back again...

I'm trying to insert a blank into a drop down box, but I keep on getting the following error:

Warning: User supplied query returns no records for display
Select FullName from PersInfo UNION Select ' ' from PersInfo

I tried running the query on the server itself, and it worked fine. Also, I am able to get the drop down list to display without the blank, but as soon as I add the UNION it blows up.

Help please!

[John]

Hi,

I'm assuming you're using the dbEditDropFlds or dbFilterDropFlds parameter and want the droplist to contain a blank at the top?

Are you using the DISTINCT option? If not, shouldn't you?

Here's a simple nwind program that displays a dropdown list on the filter screen of all the employee titles. Note the blank that's at the top. See if there's something wrong or different in your syntax. Finally, confirm which product you're using and what type of database.

Thanks,
John

<%
Set X= Server.CreateObject("ASP.DB")
X.dbUnit = "999"
X.dbFilterDropFlds = "(;~/+|)Title~DSN=NWIND~~~SELECT DISTINCT Title FROM Employees~~~~"
X.dbDSN = "DSN=NWIND"
X.dbNavigationItem = "Top,Prev,Next,Bottom,GridRow,Reload,Filter,ResetF ilter"
X.dbSQL = "SELECT EmployeeID,LastName,FirstName,Title FROM Employees"
X.ASPdb
%>

[JGallo]

The drop down list is being displayed as part of the add template I'm using. I think part of the problem is that the query is accessing an outside table called PersInfo (i.e. not the table that the code accesses directly and saves information to - Suggestions). I'm able to get the blank to appear when I query items from the Suggestions table itself. Here is my code:

Set Invdb7 = Server.CreateObject("Asp.DB")
Invdb7.dbUnit = 20009
Invdb7.dbDSN="Driver={SQL Server};Server=h01ny_nt3;DataBase=NYDivision;UID=u sername;PWD=password"
Invdb7.dbDBType="SQL"
Invdb7.dbGridInc=99999
Invdb7.dbStatusBar=false
Invdb7.dbNavigationItem="top,bottom,next,prev,Add"
SQL = "Select FullName from PersInfo UNION select ' ' from PersInfo"
Invdb7.dbSQL=SQL
Invdb7.dbMagicLayout="(|,#)11|BLOCK,1,,<SELECT NAME=""Suggest_EID"">,</SELECT>||<OPTION " &_
"Value=""#0#"">#0#"
Invdb7.ASPdb

[John]

The best way for us to reproduce (and fix) your problem, is to give us an example that we can run on our systems. We all have the nwind database (juse use dbDSN="nwind", so if you can reproduce this with that database, we'll be able to see the problem first-hand and offer suggestions to get it to work.

Please post a short nwind example here. Take a look at the template examples at: http://www.aspdb.com/Site/classic/examples/default.shtm (such as App-19, and all the Gold template examples below that) first.

Thanks,
John