|
Hi.
In my application I have to select some records from a table for which the value of a date/time columns is within a certain range. So it comes down to comparing some dates in a SQL command.
How do you do that for Access and Sql Server databases? I also have to do it for MySql, but I understand that this is not the best place to ask MySql stuff.
So for Access and Sql Server what's the easiest and safest way to compare dates?
Thanks.
|
|
|
|
|
Never compare your dates when they are present; they'll get jealous and not date you anymore. 
WHERE SomeDateField BETWEEN LowDate AND HighDate
should work for pretty much any SQL database.
|
|
|
|
|
Hi
I want to assign a xml field for the insertion like
insert into table1(t1,t2,t3)
select SequenceItem.query('t1').value('.','INT') AS t1
,SequenceItem.query('t2').value('.','Datetime') AS t2
,SequenceItem.query('t3').value('.','Datetime') AS t3
FROM @xmlJobSequence.nodes('/NewDataSet/Table1') AS xmlJobSequence(SequenceItem)
instead of assigning each and every field with the help of .QUERY, i want to assign all the fields like our usual query select * from table..
is it possible.. how to go about it.
|
|
|
|
|
Hi all i have developed an asp.net 2.0 application with sql2005. it is running ok at my pc. When i tried to deploy it at the customer site i am receiving an error when i hit login. I can acccess the login page but when i enter username and password and press login i get the following error:
System.Data.SqlClient.SqlException: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
any ideas regaring this.
my connection string is as follows:
<connectionStrings><br />
<add name="conString" connectionString="Data Source=kassem;Initial Catalog=CDC;persist security info=False;user id=sa; password=kassem255;" providerName="System.Data.SqlClient"/><br />
<add name="AccessCon" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\current bdl Europe Schd\bdl_results.mdb" providerName="system.data.oledb"/><br />
</connectionStrings>
|
|
|
|
|
hmmm... well your first problem is your connection string referancess an access database, not sql2005.
MrPlankton
|
|
|
|
|
Actually i need both and i have put both SQL & OLEDB. Check well 
|
|
|
|
|
Good Morning All
i have a Procedure that returns Nothing, i dont know why.
<br />
Create Proc Check_Geocode<br />
(<br />
@num_key varchar(10),<br />
@Extension int, <br />
@OUTRES varchar(10) oUTpUT<br />
)<br />
as <br />
set @OutRes = (select Geocode from sde.Boundary_Codes<br />
where Extension = @Extension And Numkey = @Num_key)<br />
<br />
And i will run this Procedure like this
<br />
declare @OutRes varchar(10)<br />
exec Check_Geocode 3,'0400',@OutRes Out<br />
select @OutRes as Geocode<br />
When i Execute the Procedure i dont get anything, but when i go to the table and Query it i get results.
<br />
<br />
select * from Boundary_Codes<br />
where Numkey = '0400' and Extension = 3<br />
i get results what is wrong with my Procedure
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Vuyiswa wrote: And i will run this Procedure like this
declare @OutRes varchar(10)
exec Check_Geocode 3,'0400',@OutRes Out
select @OutRes as Geocode
I believe you'll have to execute it the following way:
Declare @DisplayOutput VARCHAR(10)
Exec Check_Geocode '3',0400,@OUTRES = @DisplayOutput OUTPUT
Select @DisplayOutput AS [GeoCode]
Please do ensure that the input params are passed with their appropriate entries because, I see a discrepancy here:-
Vuyiswa wrote: exec Check_Geocode 3,'0400',@OutRes Out
As your proc accepts only Varchar,Int,Output
|
|
|
|
|
Thanks ,
my mistake was the Order of i declared my variables in the Procedure. i never knew that it matters, but you opened my eyes, each an everyday we learn.
thank you very much my friend.
thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Pleasure is mine 
Glad that it worked. Good Luck ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
Hi,
I have a converted rdlc file that uses 3 datasets, all of which takes the same two parameters being passed from the requesting page. But when I run it - I get "parameter 'Parameter1' is missing a value" error. FYI, I set default values for both of them.
Any ideas?
Thanks.
Ekjon
|
|
|
|
|
private void button1_Click(object sender, System.EventArgs e)
{
OdbcConnection myconn = new OdbcConnection("DSN=test");
SqlConnection conn = new SqlConnection("server=.;database=techpaymaster;uid=sa;password=;Pooling=false;Connect Timeout=55;");
OdbcCommand da = new OdbcCommand("select * from tinfo",myconn);
myconn.Open();
OdbcDataReader dr=da.ExecuteReader();
while(dr.Read())
{
string i =(string)dr["Name"];
int j =(int)dr["Age"];
conn.Open();
SqlCommand cmd = new SqlCommand("insert into sadd(Namee,Agee)values('"+i+"',"+j+")",conn);
cmd.ExecuteNonQuery();
}
it is showing the message
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.
modified on Thursday, January 24, 2008 1:28:54 AM
|
|
|
|
|
Which line of code is producing the error?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
It wouldn't be, by any chance a name in your database with a comma in it? You should parameterise your insert query.
|
|
|
|
|
I have a float column in a database that I want to add to, not set the value to. How do I do that?
Example:
Current value: 5.1
Add: 1.3
New value: 6.4
I know I can read the data, add the values, and then update, but that doesn't seem right.
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
|
|
|
|
|
Expert Coming wrote: I know I can read the data, add the values, and then update, but that doesn't seem right.
It is.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
UPDATE Table1 SET X = (X + 1.3)
|
|
|
|
|
I have a query which I am running accross several DB's to get information on several of our store's oulet. The query Below provided the info I need but I am not sure if it is the best way to do it and how it will be affected when we have millions of record in our DB. Or can I make it into a single query so when executed in C# I do do get two table in my dataset. Any comment or suggestion is appreciated.
Select 'My Business' AS BusName, sum(PassVer.PassedVerifier) AS 'Passed Verifiers', Sum(PassVer.CashFunded)AS 'Cash Funded', SUM(PassVer.OtherFunding) AS 'Other Funding', Sum(PassVer.CompletedApplications) AS 'Completed Applications', SUM(PassVer.CompleteFundedCash) AS 'Complete - Funded Cash', SUM(Passver.CompleteNotFundedCash) AS 'Complete-Not Cash', SUM(PassVer.Total) AS 'Total Applications',
cast((Sum(PassVer.PassedVerifier) * 100.00) / sum(PassVer.CompletedApplications) AS Float) AS '% Completed and verified',
cast((SUM(PassVer.CompletedApplications)* 100.00) / Sum(PassVer.Total) AS Float) AS '% of Completed from Total'
FROM (
--Selects Applications which Passed Verifier
Select 1 AS 'PassedVerifier', 0 As 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Pass Validation'
UNION ALL
--Selects Applications which passed the verifier and are funded by Cash.
SELECT 0 AS 'PassedVerifier', 1 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where A.fundingMethodId = 'Cash' and Q.QueueId= 'Pass Validation'
UNION ALL
--Selects Applications which passed the verifier and are funded by methods other than Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 1 As 'OtherFunding', 0 AS 'CompletedApplications',0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where A.fundingMethodId != 'Cash' and Q.QueueId= 'Pass Validation'
UNION ALL
--Selects Applications which are completed.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 1 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed'
UNION ALL
--Selects Applications which are completed and are funded by Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 1 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed' and A.fundingMethodId = 'Cash'
UNION ALL
--Selects Applications which are completed and are not funded by Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 1 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed' and A.fundingMethodId != 'Cash'
UNION ALL
--Total amount of applications.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 1 AS 'Total'
FROM dbo.BK_Account) PassVer
SELECT 'My Business' AS BusName,
CASE WHEN GROUPING (CAST(MONTH(A.CreationDate) AS VARCHAR))= 1 THEN 'Grand Total'
else (CAST(MONTH(A.CreationDate) AS VARCHAR)) end as MonthCreated,
Count(*) AS TotalCompletedApplications,
SUM(A.FundingAmount) AS SumTotalCompletedApplications
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
WHERE (A.Visible = 1) AND (Q.QueueId= 'Pass Validation' )
GROUP BY (CAST(MONTH(A.CreationDate) AS VARCHAR))
With rollup
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
Try using case statements instead?
...
CASE WHEN Q.QueueId= 'Pass Validation' THEN 1 ELSE 0 END AS 'PassedVerifier',
CASE WHEN A.fundingMethodId = 'Cash' and Q.QueueId= 'Pass Validation' THEN 1 ELSE 0 END AS 'CashFunded',
...
|
|
|
|
|
I'm trying to allow control over events/records displayed on a calendar using checkboxes.
Here is what I currently have:
http://propeller.head.home.comcast.net/~propeller.head/page1.html
It's just your run-of-the-mill asp calendar with some added AJAX so that when you mouse-over the date's, it displays the list of events for that day.
What I am attempting to add is the ability to control the SELECT (or display) of events by way of some checkboxes. Something that looks like this:
http://propeller.head.home.comcast.net/~propeller.head/page2.html
But im confused about how to update/change SELECT statements on the fly - or if it's possible... Any hints, pointers, samples?
Here is the calendar script:
http://propeller.head.home.comcast.net/~propeller.head/new/calendar.txt
Here's my AJAX event:
http://propeller.head.home.comcast.net/~propeller.head/new/ajaxevent.txt
Thanks!
-swo
|
|
|
|
|
I am creating a user defined type for time only. I have a book called pro sql server 2005 database design and optimization that has the code to create an assembly to create a user defined type for date only, but I need to create one for time only.
Does anyone know what the difference is between creating a UDT for date vs. time?
Has anyone here ever created one before that works?
Do you have the code?
I read somewhere that sql server 2008 has a time only type included in it. Does anyone know if there is a way to get the code that microsoft used to create the assembly in 2008 for the time only data type?
|
|
|
|
|
Hi all,
I am executing the following SP. But when i execute it, I get the error
Server: Msg 16929, Level 16, State 1, Procedure BicRank, Line 34.
The cursor is readonly. The Statement has been terminated.
Please help me to come out from this problem. Its very urgent for me.
This is the SP.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.BicRank /
CREATE PROCEDURE BicRank
AS
-- determine mo3 bic and m03 ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS')
-- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007
--rankings are completed per dmoq, service, bicuniverse combination
--100% is considered best
Declare BicRank_DESC Cursor
for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel
where (Mo3_Percent < 101) and dmoq in ('TTR_1','TTR_2','TTR_3','TTR_6','TTR_12','TTR_24','PROGRS')
order by compare, Mo3_percent desc
declare @3mo as float, @rank as int, @heldcompare as varchar(60), @bic as float, @firstbic as float, @compare as varchar(60)
declare @dmoq as varchar(20), @service as varchar(20), @bicuniverse as varchar(6), @RCD_CNT INTEGER
OPEN BicRank_DESC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_DESC into @compare, @3mo
while (@@fetch_status <> -1)
begin
IF (@heldcompare <> @compare)
begin
set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare
end
ELSE IF (@3mo <> @bic)
begin
set @rank = @rank + 1 set @bic = @3mo
end
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_DESC
FETCH NEXT FROM bicrank_DESC into @compare, @3mo
END
commit transaction
CLOSE BicRank_DESC
DEALLOCATE BicRank_DESC
--determine mo3 bic and m03 ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3')
--rankings are completed per dmoq, service, bicuniverse combination
--0% is considered best
Declare BicRank_ASC Cursor
for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel
where ( Mo3_Percent < 101 AND MO3_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F')
order by compare, Mo3_percent asc
OPEN BicRank_ASC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_ASC into @compare, @3mo
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare
end
ELSE IF (@3mo <> @bic)
begin
set @rank = @rank + 1 set @bic = @3mo
end
Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_ASC
FETCH NEXT FROM bicrank_ASC into @compare, @3mo
END
CLOSE BicRank_ASC
DEALLOCATE BicRank_ASC
--determineYTD bic andYTD ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3')
--rankings are completed per dmoq, service, bicuniverse combination
--0% is considered best
Declare BicRank_YTD_DESC Cursor
for Select dmoq + service + bicuniverse as compare, YTD_percent from mtce_toexcel
where ( YTD_Percent < 101 AND YTD_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F')
order by compare, YTD_percent asc
declare @YTD float
OPEN BicRank_YTD_DESC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_YTD_DESC into @compare, @YTD
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @YTD set @firstbic = @YTD set @heldcompare = @compare
end
ELSE IF (@YTD <> @bic)
begin
set @rank = @rank + 1 set @bic = @YTD
end
Update MTce_toExcel set YTD_bic = @firstbic,YTD_rank = @rank where current of BicRank_YTD_DESC
FETCH NEXT FROM bicrank_YTD_DESC into @compare, @YTD
END
CLOSE BicRank_YTD_DESC
DEALLOCATE BicRank_YTD_DESC
--determineYTD bic andYTD ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS')
--rankings are completed per dmoq, service, bicuniverse combination
--100% is considered best
-- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007
Declare BicRank_YTD_ASC Cursor
for Select dmoq + service + bicuniverse as compare, YTD_percent from mtce_toexcel
where (YTD_Percent < 101 ) and dmoq in ('TTR_1', 'TTR_2', 'TTR_3','TTR_6','TTR_12','TTR_24','PROGRS')
order by compare, YTD_percent desc
OPEN BicRank_YTD_ASC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_YTD_ASC into @compare, @YTD
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @YTD set @firstbic = @YTD set @heldcompare = @compare
end
ELSE IF (@YTD <> @bic)
begin
set @rank = @rank + 1 set @bic = @YTD
end
Update MTce_toExcel set YTD_bic = @firstbic,YTD_rank = @rank where current of BicRank_YTD_ASC
FETCH NEXT FROM bicrank_YTD_ASC into @compare, @YTD
END
CLOSE BicRank_YTD_ASC
DEALLOCATE BicRank_YTD_ASC
--
--Populate MO3_BIC on records where MO3_BIC is null
--This is due to the MO3_PERCENT being 101, 102 or NULL
--
Declare BicRank_null Cursor
for Select distinct dmoq, service, bicuniverse from mtce_toexcel where mo3_bic is null
OPEN BicRank_null
FETCH NEXT FROM bicrank_null into @dmoq, @service, @bicuniverse
while (@@fetch_status <> -1)
begin
Declare
mo3_bic Cursor For
Select DISTINCT MO3_BIC from mtce_toexcel where mo3_bic is NOT null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
SET @RCD_CNT = @@ROWCOUNT
-- get the mo3_bic
Open mo3_bic
Fetch Next From mo3_bic Into @FIRSTBIC
if @RCD_CNT > 0
BEGIN
UPdate MTce_toExcel set Mo3_bic = @firstbic where mo3_bic is null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
END
Close mo3_bic
Deallocate mo3_bic
FETCH NEXT FROM bicrank_null into @dmoq, @service, @bicuniverse
END
CLOSE BicRank_null
DEALLOCATE BicRank_null
--
--Populate YTD_BIC on records where YTD_BIC is null
--This is due to the YTD_PERCENT being 101, 102 or NULL
--
Declare BicRank_null_ytd Cursor
for Select distinct dmoq, service, bicuniverse from mtce_toexcel where YTD_bic is null
OPEN BicRank_null_ytd
FETCH NEXT FROM bicrank_null_ytd into @dmoq, @service, @bicuniverse
while (@@fetch_status <> -1)
begin
Declare
YTD_bic Cursor For
Select distinct ytd_bic from mtce_toexcel where YTD_bic is not null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
SET @RCD_CNT = @@ROWCOUNT
-- get theYTD_bic
Open YTD_bic
Fetch Next From YTD_bic Into @firstbic
if @RCD_CNT > 0
BEGIN
Update MTce_toExcel set YTD_bic = @firstbic where YTD_bic is null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
END
Close YTD_bic
Deallocate YTD_bic
FETCH NEXT FROM bicrank_null_ytd into @dmoq, @service, @bicuniverse
END
CLOSE BicRank_null_ytd
DEALLOCATE BicRank_null_ytd
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks and Regards,
|
|
|
|
|
This is probably a permissions issue. Check that you have the appropriate permissions to execute the updates. BTW, you could probably rewrite your code without using cursors.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
would u please give me database only with fields too , so that i could run and help u by running this procedure. actually it is really hard to understand as like. u can use my mail id. 
manoj Kumar Jha
"Learn to smile at every situation. See it as an opportunity to prove your strength and ability."
|
|
|
|
|
Your update statement is using "where current of BicRank_DESC". This is only allowed if the cursor was declared with the "for update" clause.
Regards
Andy
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
