Showing posts with label write. Show all posts
Showing posts with label write. Show all posts

Friday, March 23, 2012

Hidden expression with dates?

Hi! How to write the syntax when you try to compare the dates. In this case I try to check if the date is empty(then it's 1/1/1900) then the field should be hidden. This is what I tried but didn't succeed:

=iif(Fields!ModifiedDate.Value="1/1/1900",true,false)

You can convert your string value ('1/1/1900') to a date with the CDate() function. With this you can compare the to value's with each other.|||

Jan Pieter Posthuma wrote:

You can convert your string value ('1/1/1900') to a date with the CDate() function. With this you can compare the to value's with each other.

Thanks for the post but unfortunately I didn't get it. The main problem is syntax - I don't know where to use Cdate() -function. I tried this:

=iif(Cdate(Fields!modifiedDate).Value) > "1/1/1900",false,true)

the compiler didn't like it :)

Can you give me an example?

|||

It just the otherway round: =iif(Fields!modifiedDate.Value=CDate("1/1/1900"), false, true)

Wednesday, March 21, 2012

hi i want to know how to write stored procedure ..then i have to include (IF condition ) w

hi i want to know how to write stored procedure ..then i have to include (IF condition ) with SP ..

let me this post ..............anybody ????

CREATEPROCEDURE name_of_procedure
-- Add the parameters for the stored procedure here
@.passedparam1 int,
@.passedparam2 smalldatetime

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNTON;

-- Insert statements for procedure here

SELECT [COLOFINTS], [OTHERCOL] FROM MYDBNAME WHERE ([COLOFINTS] = @.passedparam1 AND [DATEENTERED] > @.passedparam2

END

|||

CREATEPROCEDURE name_of_procedure
-- Add the parameters for the stored procedure here
@.passedparam1 int,
@.passedparam2 smalldatetime

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNTON;

-- Insert statements for procedure here

IF @.passedparam2 < GETDATE()

SELECT [COLOFINTS], [OTHERCOL]FROM MYDBNAMEWHERE ([COLOFINTS] = @.passedparam1 AND [DATEENTERED] > @.passedparam2)

ELSE

SELECT [COLOFINTS], [OTHERCOL]FROM MYDBNAMEWHERE [COLOFINTS] = @.passedparam1

END

|||

Just to clarify something that clevesteve wrote, if the IF has > 1 statement, you must wrap it in BEGIN END, for example,

IF @.passedparam2 < GETDATE()
BEGIN
SELECT [COLOFINTS], [OTHERCOL] FROM MYDBNAME WHERE ([COLOFINTS] = @.passedparam1 AND [DATEENTERED] > @.passedparam2)
...some other statement
END
ELSE
BEGIN
SELECT [COLOFINTS], [OTHERCOL] FROM MYDBNAME WHERE [COLOFINTS] = @.passedparam1
..some other statemetn
END

|||

hi thaka for reply

please send me a any tutorial of these stored procudre ...bcz am a beginner of these

|||

You can find many such on the web but here's a start on using T-SQL http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

For more, just google "sql server how to write stored procedures"

Sql server 2005 also supports writing procs in .net but I have no experience myself with this

Monday, March 12, 2012

Hepl with a stored procedure

I want to write a stored procedure that takes two params and merges records
whose id match the two params.
For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
rows:
"c1"," i2", 3
"c1", "i4", 2
"d1", "i2", 2
"d1", "i3", 1
After I call the sp and pass "c1" and "d1" as arguments, the table should
contains only 3 rows:
"d1", "i2", 5
"d1", "i3", 1
"d1", "i4", 2
Note: the sp should also take into account that only records with "c1" id
are guaranteed to exist in the table but the records with "d1" is are not.
I could use two cursors to fetch records that match the given col1
arguemtns, do some comparison as I step through the cursors, write the
results into a temp table, drop the records in TableX, and finally select
records from temp table into TableX.
I think there must be an elegant & efficient way that uses only subqueries
and maybe a Table variable. Could any one help me with this?.... a complex (or simple) update statement that has the same logic as your
query.
I'd most likely use executeSQL within my sProc that allows output params fro
m an
exec string to do further validation processing
Then start a transaction, first do the update and then the delete then close
the
transaction.
I would also be concerned about record locks if users are in these same tabl
es
during this update/merge.
HTH
JeffP....
"VC" <vutha@.mailblocks.com> wrote in message
news:eAM7%238tEFHA.464@.TK2MSFTNGP15.phx.gbl...
> I want to write a stored procedure that takes two params and merges record
s
> whose id match the two params.
> For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
> rows:
> "c1"," i2", 3
> "c1", "i4", 2
> "d1", "i2", 2
> "d1", "i3", 1
> After I call the sp and pass "c1" and "d1" as arguments, the table should
> contains only 3 rows:
> "d1", "i2", 5
> "d1", "i3", 1
> "d1", "i4", 2
> Note: the sp should also take into account that only records with "c1" id
> are guaranteed to exist in the table but the records with "d1" is are not.
> I could use two cursors to fetch records that match the given col1
> arguemtns, do some comparison as I step through the cursors, write the
> results into a temp table, drop the records in TableX, and finally select
> records from temp table into TableX.
> I think there must be an elegant & efficient way that uses only subqueries
> and maybe a Table variable. Could any one help me with this?
>|||On Mon, 14 Feb 2005 14:49:28 -0700, VC wrote:

>I want to write a stored procedure that takes two params and merges records
>whose id match the two params.
>For example, I have a TableX(col1 char(10), col2 char(5), col3 int) with 4
>rows:
>"c1"," i2", 3
>"c1", "i4", 2
>"d1", "i2", 2
>"d1", "i3", 1
> After I call the sp and pass "c1" and "d1" as arguments, the table should
>contains only 3 rows:
>"d1", "i2", 5
>"d1", "i3", 1
>"d1", "i4", 2
>Note: the sp should also take into account that only records with "c1" id
>are guaranteed to exist in the table but the records with "d1" is are not.
Hi VC,
This can be done with three queries. Your sp should enclose them in a
procedure and add proper error handling.
-- Handle c1 without matching d1
-- (these are simply "renamed" to d1)
UPDATE c
SET col1 = 'd1'
FROM TableX AS c
LEFT JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
-- Handle c1 with matching d1
-- (col3 in the d1 row gets increased; the c1 row is left unchanged)
UPDATE d
SET col3 = d.col3 + c.col3
FROM TableX AS c
INNER JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
-- Remove remaining c1 rows
DELETE TableX
WHERE col1 = 'c1'
(untested)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank for the suggestion of using the transaction to protect the db
intergrity in case of errors. In my case, record locks should not be a
problem because col1 contains guid variables that are supposed to be
uniquely created for a user session. So the operation would affect, if any,
just a few rows that belong to one user.
BTW, check out the solution provided by Hugo Kornelis. It is much more
efficient than the one I had in mind.
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23QhIYQuEFHA.1396@.tk2msftngp13.phx.gbl...
> ... a complex (or simple) update statement that has the same logic as
> your
> query.
> I'd most likely use executeSQL within my sProc that allows output params
> from an
> exec string to do further validation processing
> Then start a transaction, first do the update and then the delete then
> close the
> transaction.
> I would also be concerned about record locks if users are in these same
> tables
> during this update/merge.
> HTH
> JeffP....
> "VC" <vutha@.mailblocks.com> wrote in message
> news:eAM7%238tEFHA.464@.TK2MSFTNGP15.phx.gbl...
>|||Your solution works nicely. I just make a small change to your block of code
so that only rows with unmatched col2 are renamed.
UPDATE c
SET col1 = 'd1'
FROM T1 AS c
WHERE c.col1 = 'c1'
AND c.col2 NOT IN
(SELECT d.col2 FROM T1 AS d
WHERE d.col1 = 'd1')
Thank you very much. I now only need to create a sp out of these codes :)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:aib2111n1uigknubv8kk4c1lmk1j0qc02m@.
4ax.com...
> On Mon, 14 Feb 2005 14:49:28 -0700, VC wrote:
>
> Hi VC,
> This can be done with three queries. Your sp should enclose them in a
> procedure and add proper error handling.
> -- Handle c1 without matching d1
> -- (these are simply "renamed" to d1)
> UPDATE c
> SET col1 = 'd1'
> FROM TableX AS c
> LEFT JOIN TableX AS d
> ON d.col1 = 'd1'
> AND d.col2 = c.col2
> WHERE c.col1 = 'c1'
> -- Handle c1 with matching d1
> -- (col3 in the d1 row gets increased; the c1 row is left unchanged)
> UPDATE d
> SET col3 = d.col3 + c.col3
> FROM TableX AS c
> INNER JOIN TableX AS d
> ON d.col1 = 'd1'
> AND d.col2 = c.col2
> WHERE c.col1 = 'c1'
> -- Remove remaining c1 rows
> DELETE TableX
> WHERE col1 = 'c1'
> (untested)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 15 Feb 2005 11:54:58 -0700, VC wrote:

>Your solution works nicely. I just make a small change to your block of cod
e
>so that only rows with unmatched col2 are renamed.
>UPDATE c
>SET col1 = 'd1'
>FROM T1 AS c
>WHERE c.col1 = 'c1'
>AND c.col2 NOT IN
> (SELECT d.col2 FROM T1 AS d
> WHERE d.col1 = 'd1')
Hi VC,
This statement is actually equivalent to the statement I intened to use,
but I now see that I forgot to include one important line. This is the
statement as I meant it to write:
UPDATE c
SET col1 = 'd1'
FROM TableX AS c
LEFT JOIN TableX AS d
ON d.col1 = 'd1'
AND d.col2 = c.col2
WHERE c.col1 = 'c1'
AND d.col1 IS NULL -- This line is added
The extra line is there to test that the LEFT JOIN did not find a matching
row in TableX.
The main advantage of my version over yours is that NOT IN will produce
unexpected results if any of the rows in your table can have a NULL value
for col2. That's why I always use either the LEFT JOIN technique, or a
subquery with EXISTS.
Of course, the problem with the LEFT JOIN technique is that it goes
dramatically wrong if you forget to include the IS NULL test... <g>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, February 24, 2012

Help: JDBC 1.22 and SQL 2000

HI, all

I need to write a program, which using VM 1.1.8.

So I need to find the JDBC driver version 1.22, Can anyone help me?

Please Reply, a link for download jdbc 1.22 or any other solutions .

Thank you!

You can take a look at the following links for Microsoft SQL Server 2005 JDBC Driver (recommended)

http://msdn.microsoft.com/data/ref/jdbc/

http://www.microsoft.com/downloads/details.aspx?FamilyId=6D483869-816A-44CB-9787-A866235EFC7C&displaylang=en

For SQL Server 2000 Driver for JDBC Service Pack 1

http://www.microsoft.com/downloads/details.aspx?FamilyID=4F8F2F01-1ED7-4C4D-8F7B-3D47969E66AE&displaylang=en