?
Solved

Simple sql query

Posted on 2006-07-11
9
Medium Priority
?
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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

801 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