|
Here you go, this is really simple and you should consider doing better date arith with DAY using CONVERT
<br />
create proc myproc <br />
<br />
@Type as varchar(10)<br />
<br />
AS<br />
BEGIN<br />
<br />
if @Type = 'DAY'<br />
BEGIN<br />
select * <br />
from table<br />
Where YEAR(DATE) = YEAR(getdate())<br />
and MONTH(DATE) = MONTH(getdate())<br />
and DAY(DATE) = DAY(getdate()) <br />
END<br />
<br />
if @Type = 'MONTH'<br />
BEGIN<br />
select * <br />
from table<br />
Where YEAR(DATE) = YEAR(getdate())<br />
and MONTH(DATE) = MONTH(getdate())<br />
END<br />
<br />
<br />
if @Type = 'YEAR'<br />
BEGIN<br />
select * <br />
from table<br />
Where YEAR(DATE) = YEAR(getdate())<br />
<br />
END<br />
<br />
<br />
<br />
END<br />
go<br />
<br />
|
|
|
|
|
Hi Friend'z...
I need to update the database tables though datagrid only.(in windows form c#).
How can I update the database through datagrid ? pl help me in doing this.
thanks in advance,
|
|
|
|
|
is it a datagrid or datagridView?
Visual Studio can't evaluate this, can you?
public object moo<br />
{<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}
|
|
|
|
|
It is Datagrid,
i'm using Visual Studio.Net 2003
pl help me...
|
|
|
|
|
Hi,
I have a sp where in few values would be passed as input parameters, now using these values insertion opertion should be done for 11 tables.
Now if anyone insert statement fails it need not perform the remaining insert statements.
What i've done:
begin transaction
insert
insert
insert
insert...
....
...
11statements total
if(@@error<>0)
rollback transaction
else
commit transaction
Is this correct! this is leading to some problems, like other users if trying to use any of the tables i used it is giving errors
Gautham
|
|
|
|
|
it seems correct to me, I always use
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION TRANS
END
COMMIT TRANSACTION TRANS
Visual Studio can't evaluate this, can you?
public object moo<br />
{<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}
|
|
|
|
|
The value of @@ERROR is reset after every SQL statement. You should either use the new SQL-Server 2005 Try-Catch mechanism, or have checks on @@ERROR after each of your insert statements (in which case I would normally use a GOTO to jump to a rollback at the end of the procedure).
What errors are you getting?
|
|
|
|
|
Hi,
My name is srinivasan and I am new to this DTS concept.
I have to execute the DTS package from the C#.Net Code behind.
I managed to create a small DTS Package in SQL server and execute it from SQL Server Enterprise Manager.
But I can not execute that Package from C#.Net.
Can any one help?
I’ve written a small Class for Executing the DTS but it's not working.
CLASS: Class1
Function: DtsPackage()
---------------------------------------
public void DtsPackage()
{
try
{
Package2Class Package = new Package2Class();
object pVarPersistStgOfHost = null;
Package.LoadFromSQLServer
("<server name>","<user name>","<password>",DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection,"","","","Test Package",ref pVarPersistStgOfHost);
Package.Execute();
Package.UnInitialize();
System.Runtime.InteropServices.Marshal.ReleaseComObject(Package);
Package = null;
}
--------------------------------------------------
And when I execute this I am getting an error.
__________________________________________________________________
ERROR: 
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
__________________________________________________________________
What could be the reason?
The 'server name', 'user name' and the 'password' was also correct.
Server name is SQL Server's Name.
User Name is SQL Server's User Name.
Password is SQL Server's Password.
Thank you in advance,
Seenu.
SrinivasanPrabakaran.
Trainee Software Engineer.
Metrica Systems.
Bangalore.
|
|
|
|
|
Hi All
Perhaps someone can advise on a better solution to my problem ?
I have a table full of call (telephone) records, each record has the duration of the call made
the duration is in seconds
i.e
Call table
Call id, duration, telephonenumber
1 89 0122222222222
2 135 0192929292929
3 31 0121122211111
4 5....etc etc
what i need to do i go through each record and convert the duration to a time format
so
for call id 1, the result would be 01:29,
for 2, result would be 02:15
for 3, result would be 00:31
for 4, result would be 00:05
at the moment, below is what i am doing within a cursor for each row, and although it works fine
it is a really slow way of doing things, and when i have to go through a few million records you can imagine this takes quite a while
Can anyone suggest a quicker alternative ?
Hope that all makes sense ?
Simon
set @CounterVariable = 1
set @hours = 0
set @mins = 0
set @time_counter = 0
WHILE (@CounterVariable <= @dur)
BEGIN --- count the hours
set @CounterVariable = @CounterVariable + 1
set @time_counter = @time_counter +1
IF @time_counter = 60
BEGIN
set @hours = @hours + 1
set @time_counter = 0
END
END
--- mins is remainder (seconds)
set @mins = @time_counter
IF @hours < 10 and @time_counter < 10 -- build the time string
BEGIN
set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2))
END
ELSE IF @hours >= 10 and @time_counter < 10
BEGIN
set @dur_time = CAST(@hours as varchar(2)) + ':' + '0' + CAST(@mins as varchar(2))
END
ELSE IF @hours < 10 and @time_counter>= 10
BEGIN
set @dur_time = '0'+ CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2))
END
ELSE IF @hours >=10 and @time_counter>= 10
BEGIN
set @dur_time = CAST(@hours as varchar(2)) + ':' + CAST(@mins as varchar(2))
END
|
|
|
|
|
Hi,
Here some sql to calculate Hours, Minutes, seconds using only built in sql functions and in one sql select statement:
select floor(duration / 3600) as h, floor( (duration - floor (duration /3600) )/60) as m, duration - floor(duration / 3600) * 3600 - floor( (duration - floor (duration /3600) )/60) * 60 as sec
then u can concatenate all these fields
HTH.
Hayder marzouk
|
|
|
|
|
I may be too simplistic here, but I tend to agree with Hayder marzouk
(presuming I'm understand what you're trying to accomplish here).
I used a slightly different formula than he did: his didn't work quite right when I tried it in Access .. probably my fault. In MSAccess, this works:
CREATE PROCEDURE pcSeconds2FormattedTime( seconds INTEGER ) AS
SELECT (@seconds\3600) & ':' & ((@seconds - (@seconds \3600)*3600 )\60) & ':' & @seconds - (3600*(@seconds\3600) + 60*((@seconds - (@seconds \3600)*3600 )\60))
FROM dual;
(presuming you have something called "dual").
If you don't have the '\' operator (integer division), then use the FLOOR function as suggested. Also, in a real db system, use the real concatenation operator '||', not the '&' ampersand.
Depending on whether the SQL compiler will do "common subexpression elimination", this may or may not run any faster than your method.
|
|
|
|
|
thats great, thanks guys
Simon
|
|
|
|
|
Hi,
After i have back up and restore the database to another server on another computer, the mirror option under tasks after right clicking on the database does not appear .
So, the problem is with my software or others problems?
Thanks in advance.
|
|
|
|
|
|
Dear all,
We have a solution which makes use of the mirroring feature of SQL Server 2005 for high availability and automatic failover. But, because of the high cost of SQL Server this is not always an option for all our clients.
My question is, what is the best approach or architecture to implement a custom replication / mirroring feature between two SQL Server Express databases. I have read about a trigger-log approach on the master database with some service synchronizing to the slave? But how to go about when the master and slave switches roles? What about replicating structural changes also? Automatic failover is also totally something else, but not that important for now?
Any ideas, resources, etc. would be appreciated.
Kind regard!
|
|
|
|
|
I am using MS Access DB With MFC as programming platform. I have question about WHERE clause in SELECT query
"SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = 'get_cat' "
get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
i removed qoutes and tried still it doesnt work it throws exception like “Too few parameters, Expected 1” I think it might be ignoring the unquoted get_cat
Quesion is--->
Is there any method of enclosing variables in the SQL queries while using MS Access as database?
Like any syntax like ---> ' ". variable ." '
In other words:: I want to use variable in my SQL query and there must be some syntax provided by SQL or MS Access to identify such variable which is been used in the query and use the value inside that variable and not the variable itself as a string.
Plz help me out with this problem
San
San
|
|
|
|
|
i am new to sql and such stuff....
did you tried concatination?? or something like
"SELECT field FROM table WHERE field='".$var['var']."'"
hope this works!!
|
|
|
|
|
madeelch1986 wrote: did you tried concatination??
Not wise. You're setting yourself up to be attacked through a SQL Injection attack by concatinating values into a SQL String.
|
|
|
|
|
May be you are true but this is not what i needed actually
My requirement is, i am trapping some long value at runtime and i storing it into some long variable and i want to fetch the data from MS Access DB which corrosponds to this value only . So i want to use this variable in my SQL in WHERE clause for searching.
If you can help then please tell me how to do it.
Thanks
san
|
|
|
|
|
Yes thanks for you help
but i am sorry to say that it was not useful
I used to write this when i was working on PHP
i think i need to check some different way to write and use variables in my SQL query
Thanks
San
sazsas
|
|
|
|
|
santoshsan wrote: get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
That is because the database has no idea of the variables you have in your C++ applicaiton. They are separate applications, running in separate processes. The only way they communicate is through SQL. You have to tell the SQL the value you want. It cannot peer into another process and pull the value out.
It has been over 7 years since I did MFC and C++ so your best bet is to find out how to generate parameterised queries in MFC and put the value in as a parameter to the SQL query.
|
|
|
|
|
To Colin Angus Mackay
The para you wrote that is very true. One process cant predict about the variables which are generated in some other process unless there is some intermediate one like SQL to do it.
I used to write SQL queries which uses variables created in PHP. When I was working on PHP and MYSQL. It was like -->
“ SELECT field FROM table WHERE field = ‘”.$variable.”’ “
I tried to quote like this but MFC does not take the above statement as one whole string but it counts it upto-->
“ SELECT field FROM table WHERE field = ‘ ”
There must be some way that MS Access has provided to specify that the value which is been used in SELECT query is a variable. E.g. it has #date# to include date value.
I am still searching for it, if you find something helpful please help me out with this problem.
san
|
|
|
|
|
santoshsan wrote: "SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = 'get_cat' "
get_cat is a long variable, but when i enclose it in ' ' qoutes SQL or ODBC treats it as string and does not uses the value inside it which I wanted to.
You probably need to do something like this:
"SELECT subcat_id,subcat_name FROM temp_subcat WHERE cat_id = " + get_cat
Do keep in mind the SQL injection attacks warning from Colin, though.
Hope this helps.
Chandra Ram
|
|
|
|
|
I tried to create database independant using new feature of visual .net(factories)the problem happen when passing parameters for orcale and sql server the shape of this passed parms differs that will genrate error when passing parameter or i will have to write code for diffrent diffrent database cases & that will be on the contrary of the reason of factories and exisance.
thanks alot
Hassan amaar.
Hassan Amaar
|
|
|
|
|
I think you're maybe missing the point of the factory pattern - in what way do the passed parameters change?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
