SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 [SOLVED]Querry problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 08/23/2013 :  05:24:28  Show Profile  Reply with Quote
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?


Edited by - nechtmarrie on 09/04/2013 06:37:24

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/23/2013 :  05:51:01  Show Profile  Reply with Quote
Are you getting any error? or slow performance

--
Chandu
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 08/23/2013 :  06:04:06  Show Profile  Reply with Quote
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.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/23/2013 :  06:12:50  Show Profile  Reply with Quote
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
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 08/23/2013 :  06:46:07  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/23/2013 :  07:00:00  Show Profile  Reply with Quote
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
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 08/23/2013 :  07:55:40  Show Profile  Reply with Quote
No, there is no time portion, just the date.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/23/2013 :  08:16:56  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/23/2013 :  08:19:08  Show Profile  Reply with Quote
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

Edited by - bandi on 08/23/2013 08:37:09
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 08/23/2013 :  09:08:37  Show Profile  Reply with Quote
Thnx bandi for your help, ill take a look when i'm at my home pc, on my way home from work atm.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 08/23/2013 :  09:37:06  Show Profile  Reply with Quote
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
Go to Top of Page

nechtmarrie
Starting Member

Belgium
24 Posts

Posted - 09/04/2013 :  06:36:00  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000