Let's say I have the following SQL string:

"SELECT * FROM tbl_companies"

how can I select all records from the db which the company name is not empty?

ex:

"SELECT * FROM tbl_companies WHERE tbl_companies.company_name <> Null" ???????

___________________
microdesign² | www.microdesign2.com

You don't mention which db you use, but let's give it a go anyways....


I think something like

strSQL = "SELECT * FROM tbl_companies WHERE company_name NOT NULL"

should do it.

A couple of pointers, as far as I have learned:

<ul><li>It's bad practice to use "SELECT *", because you also include coloumns you often don't need in that particular recordset (performance loss), and you loose track of which coloumns are in the recordset.</li><li>With both Access and MySql, and probably most other db's, you don't need the table name in front of coloumn names, except when the coloumn occures in several tables which you select from, for instance foreign keys.</li></ul>

Edited by - Stanly on 05 Feb 2003 15:16:30

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
You don't mention which db you use, but let's give it a go anyways....
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Sorry - I'm using Access

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
A couple of pointers, as far as I have learned:[list]<li>It's bad practice to use "SELECT *", because you also include coloumns you often don't need in that particular recordset (performance loss), and you loose track of which coloumns are in the recordset.</li><li>With both Access and MySql, and probably most other db's, you don't need the table name in front of coloumn names, except when the coloumn occures in several tables which you select from, for instance foreign keys.</li>/list]
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I know - that SQL string was just an example - but thanks anyway! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

___________________
microdesign² | www.microdesign2.com

Create a recordset and filter with this!

&lt;% If Not Recordset1.EOF Or Not Recordset1.BOF Then %&gt;
&lt;%=(Recordset1.Fields.Item("tbl_companies.company_name".Value)%&gt;
&lt;% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %&gt;


if you want you can also look here.

www.aspfaq.com/show.asp?id=2150

Edited by - asp on 05 Feb 2003 01:30:59

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Create a recordset and filter with this!

&lt;%=(Recordset1.Fields.Item("tbl_companies.company_name".Value)%&gt;

<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

I might be mistaking here, but wouldn't this extract more data into the recordset than acctually needed? My personal experience tells me you should always filter the data in the SQL-query to avoid extra large recordsets, without needing it.

Thanks for your reply - but I wanted to know how to filter in the sql statement, not after it. <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Create a recordset and filter with this!

&lt;% If Not Recordset1.EOF Or Not Recordset1.BOF Then %&gt;
&lt;%=(Recordset1.Fields.Item("tbl_companies.company_name".Value)%&gt;
&lt;% End If ' end Not Recordset1.EOF Or NOT Recordset1.BOF %&gt;


if you want you can also look here.

www.aspfaq.com/show.asp?id=2150

Edited by - asp on 05 Feb 2003 01:30:59
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

___________________
microdesign² | www.microdesign2.com