?
Solved

Simple sql query

Posted on 2006-07-11
9
Medium Priority
?
277 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 600 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 43

Accepted Solution

by:
Eugene Z earned 1200 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 29

Expert Comment

by:leonstryker
ID: 17084036
Whats the datatype of employeeid?
0
 

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 200 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

840 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