473,372 Members | 1,070 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Could I use SQL "Select Case .. When..." in ADODB.Recordset().open

Is there any SQL Error?

Or I have to use Select case in VB code to control SQL instead.
Thank you for any ans.

Nuno
Jul 20 '05 #1
4 12462
Nuno (ra*****@chula.com) writes:
Is there any SQL Error?

Or I have to use Select case in VB code to control SQL instead.
Thank you for any ans.


I'm afraid that the question you have posted provides far too little
of information to be useful. Please post the code you are having problem
with.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2


Thank you Erland Sommarskog. I use VS.net webservice to connect SQL
server. Could I try with the following code (I'm not sure that I can use
"SELECT CASE" sql with this or not)
'Dim objADORS_ As New ADODB.Recordset()
'objADORS_.Open("SELECT CASE
DateDiff('d',[tblHeader].[start_Weekend]," & to_date & ")" & _
' " WHEN 0 THEN sum([tblTimeSheet].[sat]) +
sum[tblMiscellenous].[sat]" & _
' " WHEN 1 THEN sum([tblTimeSheet].[sun]) +
sum[tblMiscellenous].[sun]" & _
' " WHEN 2 THEN sum([tblTimeSheet].[mon]) +
sum[tblMiscellenous].[mon]" & _
' " WHEN 3 THEN sum([tblTimeSheet].[tue]) +
sum[tblMiscellenous].[tue]" & _
' " WHEN 4 THEN sum([tblTimeSheet].[wed]) +
sum[tblMiscellenous].[wed]" & _
' " WHEN 5 THEN sum([tblTimeSheet].[thu]) +
sum[tblMiscellenous].[thu]" & _
' " WHEN 6 THEN sum([tblTimeSheet].[fri]) +
sum[tblMiscellenous].[fri]" & _
' " ELSE 0 " & _
' " END as HrsUsed" & _
'" FROM(tblHeader, tblTimeSheet, tblMiscellenous)" & _
'" WHERE(tblHeader.HID = tblTimeSheet.HID and
tblHeader.HID=tblMiscellenous.HID) " & _
'" and ('" & to_date & "' between
[tblHeader].[start_weekend] and [tblHeader].[end_weekend])" & _
'" and tblHeader.initial = '" & ini & "'" & _
'" GROUP BY [tblHeader].[start_Weekend]")

thank you for your kindness ans.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3
A Rugchatjaroen (ra*****@chula.com) writes:
Thank you Erland Sommarskog. I use VS.net webservice to connect SQL
server. Could I try with the following code (I'm not sure that I can use
"SELECT CASE" sql with this or not)
'Dim objADORS_ As New ADODB.Recordset()
'objADORS_.Open("SELECT CASE
DateDiff('d',[tblHeader].[start_Weekend]," & to_date & ")" & _
' " WHEN 0 THEN sum([tblTimeSheet].[sat]) +
sum[tblMiscellenous].[sat]" & _
' " WHEN 1 THEN sum([tblTimeSheet].[sun]) +
sum[tblMiscellenous].[sun]" & _
' " WHEN 2 THEN sum([tblTimeSheet].[mon]) +
sum[tblMiscellenous].[mon]" & _
' " WHEN 3 THEN sum([tblTimeSheet].[tue]) +
sum[tblMiscellenous].[tue]" & _
' " WHEN 4 THEN sum([tblTimeSheet].[wed]) +
sum[tblMiscellenous].[wed]" & _
' " WHEN 5 THEN sum([tblTimeSheet].[thu]) +
sum[tblMiscellenous].[thu]" & _
' " WHEN 6 THEN sum([tblTimeSheet].[fri]) +
sum[tblMiscellenous].[fri]" & _
' " ELSE 0 " & _
' " END as HrsUsed" & _
'" FROM(tblHeader, tblTimeSheet, tblMiscellenous)" & _
'" WHERE(tblHeader.HID = tblTimeSheet.HID and
tblHeader.HID=tblMiscellenous.HID) " & _
'" and ('" & to_date & "' between
[tblHeader].[start_weekend] and [tblHeader].[end_weekend])" & _
'" and tblHeader.initial = '" & ini & "'" & _
'" GROUP BY [tblHeader].[start_Weekend]")


SELECT CASE is OK, but it seems you have an error with to_date. It's
not quoted in the SQL string.

I would suggest that it is better to use a parameterized query instead,
because then you don't have to bother about nested quotes, and the
embedded SQL code becomes cleaner.

I'm only an occassional ADO programmer, so this syntax may not be
entirely correct, but would do something like:

Dim cmd AS new ADODB.Command
cmd.CommandText = _
" SELECT CASE DateDiff('d', h.[start_Weekend], ?)" & _
" WHEN 0 THEN SUM(ts.[sat]) + ? " & _
" WHEN 1 THEN SUM(ts.[sun]) + ? " & _
" WHEN 2 THEN SUM(ts.[mon]) + ? " & _
" WHEN 3 THEN SUM(ts.[tue]) + ? " & _
" WHEN 4 THEN SUM(ts.[wed]) + ? " & _
" WHEN 5 THEN SUM(ts.[thu]) + ? " & _
" WHEN 6 THEN SUM(ts.[fre]) + ? " &_
" ELSE 0 " & _
" END as HrsUsed" & _
" FROM tblHeader h, tblTimeSheet ts, tblMiscellenous m " & _
" WHERE h.HID = ts.HID " & _
" AND h.HID = m.HID " & _
" AND ? between th.[start_weekend] AND th.[end_weekend] " & _
" AND h.initial = ? " & _
" GROUP BY h.[start_Weekend]"
cmd.Parameters.Append cmd.CreateParameter(, adDateTime,,, to_date)
cmd.Parameters.Append cmd.CreateParameter(, adInteger,,, & _
sum[tblMiscellenous].[sat])
...
cmd.Parameters.Append cmd.CreateParameter(, adDateTime,,, to_date)
cmd.Parameters.Append cmd.CreateParameter(, adChar,, 1, ini)
rs.Open(cmd)

In the SQL I have also introduced aliases to make it less verbose.

I should add that the query looks a little funny, but since I don't
what result you are looking for, I cannot tell whether it returns
the desired result or not.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Erland Sommarskog,

That's so cool. Thank you so much..... ^_^
Nuno
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Phil Powell | last post by:
Has anyone here ever done a case where you have a select multiple form element and you have to do both server-side and client-side validation? I am honestly not sure how to do it in Javascript (I...
3
by: Radu | last post by:
Hi. I have lots of processing to do on the server - from the client (Access) I call a sproc which returns a recordset (the sproc is essentially a big "select"). With the obtained data , I need to...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
5
by: eliffman | last post by:
I'm trying to populate an array with the records from a recordset. But I'm getting an error. Here's the code: Dim db As Database, qd As QueryDef, rs As Recordset Dim MyArray() As Double, i As...
6
by: GSteven | last post by:
(as formerly posted to microsoft.public.access.forms with no result) I've created a continuous form which is based on a straightforward table (ex - customers - 100 records). On the form there is...
2
by: Dave Markle | last post by:
Good afternoon. I was just going through my code, analyzing it with FXCop, and FxCop gave me the following error on this code: MY CODE: Select Case termYears Case 5 : retVal.Append("1") Case...
5
by: Henning M | last post by:
Hi all, I having some problems with Access and selecting records between dates.. When I try this in access, it works fine!! "Select * from Bilag Where Mdates Between #1/1/2006# And...
3
by: divya | last post by:
Hi, I have a table tblbwday with 2 fields Name and Birthday.I have written this script for displaying evryday names of the people on that day. <% set objConn...
2
by: JanP | last post by:
Hello I'm new here and hope someone can help solve this problem. I built a database in MS Access 2000 and split it. Since I was upgraded to 2003, (the other user still has 2000) we cannot...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.