Solved

Simple sql query

Posted on 2006-07-11
9
233 Views
Last Modified: 2009-07-29
I wanted to see if there was a record already in the timein row that matched the current date and the employeeid.  I realized I have my logic messed up.  Thanks for your help

objCmd.CommandText = "select recordid from employeehours where employeeid = '" & stremployeeid & "' and timein =  & CONVERT(datetime, '" & Date.Today  & "', 102)"
0
Comment
Question by:running32
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17083850
I am not good on VB,

objCmd.CommandText = "select recordid from employeehours where employeeid = '" & stremployeeid & "' and CONVERT(varchar(8),timein,112) =  & CONVERT(datetime, '" & Date.Today  & "', 112)"
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 150 total points
ID: 17083867
objCmd.CommandText = "select recordid from employeehours where employeeid = '" & stremployeeid & "' and CONVERT(varchar(8),timein,112) =   CONVERT(datetime, GETDATE(), 112)"
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 300 total points
ID: 17083968
try:

objCmd.CommandText = "select recordid from employeehours where employeeid = '" & stremployeeid & "' and datediff(dd,timein,  GETDATE())=0"
---employeeid is integer or string
or if int:
objCmd.CommandText = "select recordid from employeehours where employeeid = " & stremployeeid & " and datediff(dd,timein,  GETDATE())=0"
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17084036
Whats the datatype of employeeid?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:running32
ID: 17084067
time  is stored in the database as date and time.  I only want to compare the date 7/11/2006 10:00 AM .  I have a record in the database that matches but I keep getting no records returned.

Thanks
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 17084095
Did you look to see the parameters you are actually passing in?  Maybe stremployeeid is empty.
0
 

Author Comment

by:running32
ID: 17084191
Tested that stremployeeid is populated.
0
 
LVL 29

Assisted Solution

by:leonstryker
leonstryker earned 50 total points
ID: 17084221
Try running a simple SQL to make sure its not a VB issue:

objCmd.CommandText = "select COUNT(recordid) from employeehours where employeeid = " & stremployeeid
0
 

Author Comment

by:running32
ID: 17084414
Thanks so much got it now.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now