This has got me puzzled... Executed SQL Query RESOLVED

[hkhalil]

Hi everyone,

I have an SQL query that I am executing in my program. The query doesn't work in the application, but works when executed by itself using SQL Query Analyser.

Here is the query :

strSQL = "UPDATE Stock_Details SET SDInputDate = (SELECT SDInputDate FROM Stock_Details WHERE ObjectID = 4887) WHERE ObjectID = 504"

If I execute this query in the application, it says that "SELECT SDInputDate FROM Stock_Details WHERE ObjectID = 4887" returns a NULL value, and it cannot perform the update since the table doesn't allow nulls in the column i'm updating.

But, if I execute it in SQL Query Analyser, the query works perfectly fine, and no NULL values are returned.

I am completely puzzled, and I have been toying with this for the last 2 days.

Any tips ?

Thanks!

[Chubby]

What comes back from the DB if you simply use the inner select (SELECT SDInputDate FROM Stock_Details WHERE ObjectID = 4887) as the SQL source ?? Is it Null???

Chub..

[hkhalil]

Hi,

The returned value is not null, that's why I'm really lost.

[szlamany]

What you have is a totally legitimate query, I just tried something like it in QA myself and it worked just fine.

You must be typing something wrong or have something very silly going on.

Can you post the actual code / copy+paste it into a [CODE] tagged block in the forum here?

[Chubby]

What putting a query something like this into the String and executing it ? (Check the syntax as this is done without checking).

--- Not quite sure if you need this line but put it in anyway.
SET NOCOUNT ON

DECLARE @SDInputDate Integer

SET @SDInputDate = SELECT SDInputDate FROM Stock_Details WHERE ObjectID = 4887

--- Switch it Off
SET NOCOUNT OFF

UPDATE Stock_Details SET SDInputDate = @SDInputDate WHERE ObjectID = 504

Chubby.

[szlamany]

If I execute this query in the application, it says that "SELECT SDInputDate FROM Stock_Details WHERE ObjectID = 4887" returns a NULL value, and it cannot perform the update since the table doesn't allow nulls in the column i'm updating.

How are you getting it to say an error message like this?

It would not give you a message about 4887, but about the column not accepting nulls...

[hkhalil]

Sorry guys, I forgot something very silly in the code. Everything works now so it's all good

Thanks again, and sorry for the trouble

[szlamany]

I'm glad it worked out - must have been a psychic moment for me (or typo's are just that common)

[Mc Brain]

Sorry guys, I forgot something very silly in the code. Everything works now so it's all good

Thanks again, and sorry for the trouble

Care to share with all of us?