|
Thanks , I will try to find another solution without using t-sql in ms access.
Regards
I Love SQL
|
|
|
|
|
Access doesn't support true stored procedures like this. Anything you do like this has to be implemented in your own application code.
|
|
|
|
|
Access doesn't support T-SQL, not only stored procedures.
|
|
|
|
|
SELECT dbo.tblEmployeeMaster.empId, dbo.tblEmployeeMaster.empName, dbo.tblTimeCardmain.carddate,
dbo.tblTimeCardmain.reportingtime, dbo.tblTimeCardmain.leavingtime, dbo.tblsalarydetails.Whrs,
dbo.tblTimeCardmain.shift, dbo.tblTimeCardmain.projectid FROM dbo.tblEmployeeMaster INNER JOIN
dbo.tblTimeCardmain ON dbo.tblEmployeeMaster.empId = dbo.tblTimeCardmain.empid AND
dbo.tblEmployeeMaster.projectId = dbo.tblTimeCardmain.projectid INNER JOIN
dbo.tblsalarydetails ON dbo.tblTimeCardmain.empid = dbo.tblsalarydetails.EmpId INNER JOIN
dbo.tblMonthHourDetails ON dbo.tblTimeCardmain.empid = dbo.tblMonthHourDetails.EmpId AND
dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate AND
dbo.tblTimeCardmain.projectid = dbo.tblMonthHourDetails.ProjectId
WHERE (dbo.tblTimeCardmain.carddate = CONVERT(DATETIME, '2007-06-04 00:00:00', 101)) AND
(dbo.tblTimeCardmain.app1 = 1) AND (dbo.tblTimeCardmain.projectid = 100)
Hi i amn't well in sqlserver so hlp me...,
See the above query in that i am checkng "dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate"...,
if both dates are equal then those dates cant display how to do that?
same time it want to check other conditions also...,
Plz hlp me...,
Mag
|
|
|
|
|
the table structure is not fully understood.
however, from remove join on date ...
"dbo.tblTimeCardmain.carddate = dbo.tblMonthHourDetails.SDate"
and use it in where with not equal
"dbo.tblTimeCardmain.carddate <> dbo.tblMonthHourDetails.SDate"
check whether is solves
Regards
KP
|
|
|
|
|
Hi all,
I need to know if this is possible, and if it is, whats would a stored proc like that look like.
What I'm looking for is a query that the stored proc will run, which will like the following :
SELECT * FROM myTable <br />
WHERE<br />
(<br />
SELECT COUNT(*) FROM myOtherTable<br />
WHERE (myOTID = @param1 OR<br />
myOTID = @param2 OR<br />
myOTID = @param3 OR [could me more ORs]) AND (myOtherTable.myID= myTable.myID)<br />
) = 3
Any help would be much appreciated...
Jubjub
"If you're too careful, your whole life can become a f---in' grind." - Mike McD ( Rounders)
|
|
|
|
|
Yes, it is possible.
There are 3 ways to do this as mentioned here :
Click Here[^]
Regards,
Arun Kumar.A
|
|
|
|
|
Thanx arul, that helps a lot.
That helps more than a lot. I tweaked the code that Mark J. Miller wrote in that article to suit my needs and it works a treat. I appreciate the help Arul.
Looks like I have malfunctions with my web surfing abilities  .
-- modified at 20:38 Saturday 2nd June, 2007
"If you're too careful, your whole life can become a f---in' grind." - Mike McD ( Rounders)
|
|
|
|
|
I've built some strongly typed data sets, they work pretty good, but they didn't do everything I wanted them to do.
So I make a class Library and called it my DAL(Data access layer) in it, I had the data adapters, and would do some processing and then make some calls to the db through the functions set in the strongly typed datasets.
The whole system seems to be working pretty good, but now I have someone else working with me on the page stuff. At first he created his own instance of the data adapters and bypassed my DAL.
Does anyone know a good way of preventing the DAL from being bypassed? I don't know if there's a way to change the scope of a strongly typed data set so that only one class could access it.
Kris
|
|
|
|
|
Drathmar wrote: Does anyone know a good way of preventing the DAL from being bypassed?
It is called discipline.
|
|
|
|
|
Colin Angus Mackay wrote: Drathmar wrote:
Does anyone know a good way of preventing the DAL from being bypassed?
It is called discipline.
Sometimes the short answers are the best 
|
|
|
|
|
im using this query
select date from table where date='5/7/2007'
it returns '0' rows, but when I run this follwoing query it returns 2 rows
select date from table where date='5/7/2007 1:30:00 PM'
i don't want to search with time how can i do that?
i even try this
select date from table where date='5/7/2007%'
but it gives error as it cant convert smalldatetime to string any solution.
|
|
|
|
|
|
Everyone seems to be doing this today!
Firstly - use ISO 8601 format. This format - yyyyMMdd - is not subject to misinterpretation by different locale settings.
Secondly, dates have a time part. If you want any time on 7 May 2007, use:
WHERE date >= '20070507' AND date < '20070508' If you don't specify a time part for a date literal, the time part is set to 00:00:00 (i.e. 12am).
|
|
|
|
|
Thanks Mike,
I'll be much oblige if you can tell me how to set that format? n where should set this ISO format either in OS or MSSQL Management Studio?
Thanks Again
|
|
|
|
|
gamzun wrote: n where should set this ISO format either in OS or MSSQL Management Studio?
YOU must employ the format in YOUR application / queries. This is not an OS or Server task. It is up to the application to format user entered dates properly before submitting them to the server in queries....
|
|
|
|
|
Use convert statement to the format required and use style 105 to cut date...
Remember to convert date to the table format while selecting... '%' Sign can be used only with a string and if ur date format in the table is datetime then that cant be done
PPK
|
|
|
|
|
Hello
SQL Server 2005
I have tried and cannot return any rows either though there are records that should be returned, using the sql below.
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') <br />
AND scheduleDate BETWEEN 20/5/2007 AND 30/5/2007
In the database scheduleDate is a dateTime datatype.
In the database I have copied and pasted.
23/05/2007 00:00:00
I tried the following
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') <br />
AND scheduleDate BETWEEN '20/5/2007' AND '30/5/2007'
And got an error message:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Many thanks for any help with this,
Steve
|
|
|
|
|
Your connection is probably set to US English. This seems to be the default.
Never use a local time format with SQL Server. They're just too prone to confusion and different interpretation depending on the locale. Instead, use the ISO 8601 date format - yyyyMMdd.
So I would write
SELECT * FROM Calls WHERE
clientName IN ('Bankside Engineering Limited')
AND
scheduleDate BETWEEN '20070520' AND '20070530'
|
|
|
|
|
Hello,
Thanks for your response.
I tried this
SELECT * FROM Calls WHERE clientName IN ('Bankside Engineering Limited') <br />
AND ScheduleDate BETWEEN '20070521' AND '20070531'
No error messages but no rows returned.
I have this date below stored in the database, but this should have been returned.
23/05/2007 00:00:00
Many thanks,
Steve
|
|
|
|
|
Well then I would guess that record doesn't have a clientName = 'Bankside Engineering Limited'
Why did you use the IN since there is only one name?
Ben
|
|
|
|
|
Hello Ben,
In my visual basic code the user will select from a list of companies. They could select all or just select a couple of them. My query in my visual basic will string format the array of companies. Just to keep it simple I just put one in there.
The problem is corrected with the date between. I now know that the sql server uses US formatting by default.
Thanks,
Steve
|
|
|
|
|
Try this out
BETWEEN CONVERT(DATETIME, '2004-06-21 00:00:00', 102) AND CONVERT(DATETIME,
'2004-06-25 00:00:00', 102))
http://msdn2.microsoft.com/en-us/library/aa226054(sql.80).aspx
|
|
|
|
|
Hi,
currently tryin 2 find a connection from .net that can work with both sql server and windows ce as the application i am creating will need to access both? any words of wisdom would be great, was told maybe an ole db one might work but not too sure now,
thanks
gav
|
|
|
|
|
hi friends
i have two tables like t1,t2
In t1,i set primary key
In t2,i set foreign key
now both tables are related.
now i am update ,delete primary key column but it is not affected foreginkey table
how to write query affcted primarykey table as well as foregin key table
can anybody help me
regards
saravan
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
