|
Hi all,
Is there any way to write an SQL SELECT statement that will tell me whether a column is NULL or not, without having to download the column's content?
I'm working on a content-management system that stores HTML pages as text records in a MS SQL Server table. Some pages have a 'default' page which is displayed by setting the text column to NULL. For the web interface I'd like a way to summarize the state of the content (e.g. whether it has been supplied or not), but it seems ridiculous to download the entire HTML content of a section just to see if it's NULL or not.
Oh, and I can't use Stored Procedures for reasons I won't go into 
TIA,
Pete
|
|
|
|
|
Well, you could always do something like this:
SELECT id
FROM tblContent
WHERE ...
AND Content IS NULL
Modify it as you want to, the most important thing is the condition "column IS NULL" can be used to find out whether a column is null or not without having to download it.
Hope it helps,
Edbert
|
|
|
|
|
Hi Edbert, thanks for the reply.
I'd thought of that, but I'm not sure how well it works if I'm displaying info about /every/ row in the table. I'd have to do a SELECT for each row. Plus, each row has about 5 different text fields (each 'page' of content has 5 'sub-pages'). So that would mean 5*num_rows SELECTs!
I guess a similar approach would be to initially do the SELECT you suggest (for each of the 5 content columns), and then programmatically compare each id to the results. Hmmm, maybe that's what you meant in the first place.
It's annoying that there doesn't seem to be a 'cleaner' way to do it, but then I guess SQL isn't really the most powerful thing for this kind of problem...
Thanks for you help,
Pete
|
|
|
|
|
If you post the database structure and how you want your query to be I might be able to help you build the query.
|
|
|
|
|
You could use ISNULL( column, valueIfNull ) although you will still end up with the data if the column is not null. Your best bet would be to use a case e.g. CASE WHEN col IS NULL THEN 1 ELSE 0 END AS IsColNull. Please note this is not checked for syntax.
|
|
|
|
|
Hi guys,
I ended up re-normalising the database schema, a side-effect of which was that my NULL-checking problem is no longer an issue. Which is nice. I just wanted to say thanks for your help.
Cheers,
Pete
|
|
|
|
|
hii all...
i'm facing a problem ...
i wanna insert a new name into my table (like ADAM SMITH)
but if there is an existing (ADAM SMITH) ...with two balnks it will produce a duplication in the table..(it's the same ADAM SMITH but one time with one balnk and one time with two balnks...so all a wanna do is to make the SQL treat (ADAM SMITH) like(ADAM SMITH)like(ADAM SMITH)
to prevent the duplication
how can i do it by SQL or by access or by SQL server...??
thx in advance
|
|
|
|
|
As far as I know, there is no SQL function to trim the spaces in between words.
Why don't you separate the name field into FirstName and LastName? I usually separate them into FName,MName and LName.
It's easier to use that way.
If you can't modify the table, then you have to write your own function to separate and trim the name you want to insert
(i.e. 'ADAM' and 'SMITH') and produce an SQL like this to check whether it exists:
SELECT COUNT(*)
FROM tblName
WHERE name LIKE('ADAM%SMITH')
but this may not be 100% correct, as names like 'ADAM SAMUEL SMITH' will be counted too.
You may also want to create a function to trim the names and put them back together before inserting them to the database, which is safer.
|
|
|
|
|
I have a dataset displayed in a particular view with some sorting. For some specific reasons I want to create a new dataset from this dataview so that the new dataset will have it's rows in actual physical sorted sequence just as in the view. How do i do it?
One solution will be to iterate through the view's rows and create a new dataset of your own in the same sequence within a foreach loop. But I feel it's not that 'graceful'.
Rakesh
|
|
|
|
|
Hi,
I want to get a boolean variable from a stored procedure into .aspx page. Can you send me a piece of code for this?(C# will be better)
Best wishes,
-
When in doubt, push a pawn!
-
|
|
|
|
|
You can write the code yourself but here is the idea.
Create a SqlConnection.
Create a SqlCommand, assing the connection to it and give it a command-text equal to your SPs name.
Set the CommandType of the command to StoredProcedure.
Add an output SqlParameter to the command, make it's name equal to the output param in your SP.
Execute the command
The parameter will then be assigned the value.
Use Convert.ToBoolean to get the value.
regards,
Paul Watson
Bluegrass
South Africa
Miszou wrote:
I have read the entire internet. on how boring his day was.
Crikey! ain't life grand?
|
|
|
|
|
I run an application in my computer with the sqlserve installed in my computer too.
But when my code goes to :
SqlConnection conn = new SqlConnection("...");
conn.Open();
It's very strange that sometime it took so long to open the connection, about 4 seconds. But sometime it's fast, it took no more than 1s to finish.
I tried it many times. All the tests are done after restarting the computer, so the environment should be very clean.
Any one know why? Please kindly tell me.
Thanks in advance!
|
|
|
|
|
Post your connection string (hash the server ip, password and username first) so we can see if there is a problem in there.
Otherwise where is this SQL server you are connecting to in relation to the box running the code?
regards,
Paul Watson
Bluegrass
South Africa
Miszou wrote:
I have read the entire internet. on how boring his day was.
Crikey! ain't life grand?
|
|
|
|
|
How is your computer resolving the location of the server? If your connection string uses the server name, it must be resolved to an IP address, which might take a bit of time if you are depending on netbios (windows workgroup) to get the job done. Will be worse if you have multiple newtork adapters...
Iry putting the servers IP info in LMHOSTS.
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
|
|
|
|
The first time a connection is made to the server, it has to perform authentication, set up various parameters, and perform other initialisation.
When using connection pooling, a 'closed' connection (from the application's perspective) is not in fact closed. It's kept in the pool waiting to be reused. When you 'open' a connection and there's at least one spare connection in the pool, the provider simply resets the connection ("EXEC sp_reset_connection" in SQL Profiler trace output) and hands it out. This explains the sub-second connection time.
When there aren't any connections, but the pool can hold more (maximum not yet reached), it will have to do a proper connection in the same way as the first one. This will take about the same amount of time as the first did.
|
|
|
|
|
How do I do this?
I need to receive the data this way:
12 10
I fI have the following structure.
select sum(total)as total1
from table1
12
select sum(total)as total2
from table1....
10
|
|
|
|
|
Anonymous wrote:
I need to receive the data this way:
12 10
If you are referring to having the items both returned by the query in one record instead of two sets, you use a union.
select sum(total) as total1, null as total2
from table1
union
select null, sum(total)
from table2
Rocky <><
www.GotTheAnswerToSpam.com
|
|
|
|
|
How can I check when the last time db was restore?
|
|
|
|
|
Do you have access to the server utilities? or are you asking about how to do it programatically?
|
|
|
|
|
Hi !
In my application, I use a SQL command to create a table in an Access database (with ODBC).
I want ot have a text field, and I create it like this :
Name VARCHAR(128)
My problem is that in Access, this text field is set to : Empty String Not Allowed, but I want to allow empty string. How can I specify that in my SQL command ?
Thanks !
Jerome
|
|
|
|
|
Hello!
You should be able to simply modify your CREATE statement to something that looks like this:
Name VARCHAR(128) NULL
to explicitly allow null strings. Then again, writing
Name VARCHAR(128) NOT NULL
will explicitly require NON-NULL strings.
I hope this helps.
Cypher.
|
|
|
|
|
Thanks for answering, but the problem remains. Your solution does allow to have a NULL string, but doesn't allow to have an empty string (which is not a null string !).
Any ideas ?
Jerome
|
|
|
|
|
Jerome Conus wrote:
but doesn't allow to have an empty string
And you get some error message telling you this?
Someone correct me if I'm wrong, but I think that any attempt to store an empty string in a varchar type column will result in that column being set to NULL.
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
|
The thing is that in Access, there are two properties on a text field :
'NULL allowed'
and
'Empty string allowed'
But, with the SQL language, I don't know how to set these two properties.
This command :
CREATE T_Dummy (Label VARCHAR(30) NOT NULL);
Will change the first property (NULL allowed) but not the second one, which by default says 'no empty string allowed' and which is the one I'd like to change.
I couldn't find in the SQL language reference a way to change the second property and I wonder if it is something only Access understands.
Jerome
|
|
|
|
|
Jerome Conus wrote:
I wonder if it is something only Access understands
You are probably hitting the nail on the head.  You'll have to dig through some Access specific programming reference books to likely find any mention of it. Good Luck
Chris Meech
It's much easier to get rich telling people what they want to hear. Chistopher Duncan
But for a man, barbecuing eggplant and portobello mushrooms is a sure way to have people question your sexual orientation. Kuro5hin
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
