[SOLVED]Querry problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

[SOLVED]Querry problem

Author

Topic

nechtmarrie
Starting Member

24 Posts

Posted - 2013-08-23 : 05:24:28

Hi,

I currently got a querry that generates a list of staff that is left available. 2 other tables with assinged staff and a table with sick or absent staff are check in it.

This is my querry:
SELECT Naam, Rijbewijs, VierVijfdag FROM Chauffeurs WHERE NOT EXISTS (SELECT Naam FROM Planlijst WHERE Naam = Chauffeurs.Naam AND Datum = @Datum)
AND NOT EXISTS (SELECT 1
FROM Ziektes_Verlof
WHERE ((@Datum BETWEEN Start_Datum AND Eind_Datum) OR
(Start_Datum <= @Datum) AND (Eind_Datum IS NULL))
AND Naam = Chauffeurs.Naam)

When i run it in sql management studio it works like a charm, however when i try to use it in my visual basic report it just bugs out totally...

Dous anyone knows why this is?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 05:51:01

Are you getting any error? or slow performance

--
Chandu

nechtmarrie
Starting Member

24 Posts

Posted - 2013-08-23 : 06:04:06

When i use the querry solo in sql management it works perfectly, when i use it in my vs report it just shows the report empty, without any of the other tables.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 06:12:50

Check the following things:
1) what is the value passing to @Datum param....
In SQL Server what is the default DATEFORMAT? means in which format you are passing @Datum value; where as in VS how you passed...
In the Debug mode of VS check what is the value of @Datum
2) Are you connecting to the same database ( In real-time we can have separate databases for both Test & Development purpose)...
if you have separate database, check whether you have connected to correct database or not? whether data exist or not ?

--
Chandu

nechtmarrie
Starting Member

24 Posts

Posted - 2013-08-23 : 06:46:07

The Value for today for example is passed as: 28/08/2013

I am connecting to the correct database

And for the formats i have alot of ofher querry's also using the @Datum parameter and the same input and they all work just fine.
Thats why this is so confusing for me :s

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 07:00:00

Have you checked @datum value in VS?
Are you passing DATE along with TIME portion?
If yes, WHERE NOT EXISTS (SELECT Naam FROM Planlijst WHERE Naam = Chauffeurs.Naam AND Datum = @Datum) gets failed....
check thoroughly..

--
Chandu

nechtmarrie
Starting Member

24 Posts

Posted - 2013-08-23 : 07:55:40

No, there is no time portion, just the date.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 08:16:56

try with the following modification


SELECT Naam, Rijbewijs, VierVijfdag 
FROM Chauffeurs 
WHERE NOT EXISTS (SELECT Naam FROM Planlijst WHERE Naam = Chauffeurs.Naam AND Datum = @Datum)
AND NOT EXISTS (SELECT 1 FROM Ziektes_Verlof 
				WHERE ((@Datum BETWEEN Start_Datum AND Eind_Datum)
					 OR ((Start_Datum <= @Datum) 
						AND (Eind_Datum IS NULL))
					    ) AND Naam = Chauffeurs.Naam)

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 08:19:08

Check once whether dataset has getting records by putting Break Point on where you assigned dataset to gridview/something else...?

Can you post the code of the following part..
how you declared @Datum variable and assign value to it....
How you got DD/MM/YYYY format for @datum variable

Refer once
http://stackoverflow.com/questions/11085040/different-result-running-a-query-in-sql-server-and-from-c-sharp
--
Chandu

nechtmarrie
Starting Member

24 Posts

Posted - 2013-08-23 : 09:08:37

Thnx bandi for your help, ill take a look when i'm at my home pc, on my way home from work atm.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-23 : 09:37:06

quote:
Originally posted by nechtmarrie

Thnx bandi for your help, ill take a look when i'm at my home pc, on my way home from work atm.

Welcome

Let us know if you need any help

--
Chandu

nechtmarrie
Starting Member

24 Posts

Posted - 2013-09-04 : 06:36:00

Hi Chandu,

I ended up making my own code for getting the data and printing it out. This works just fine.
I still down't know why it did not work the first way tho :s

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources