Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data from
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
to put in an exception to say insert if Dressing.Dressing_ID =
inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
You mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>
|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:

> The trigger below was automatically generated when I transferred my data from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>
|||Jens,
[vbcol=seagreen]
This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü?meyer" wrote:

> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
>
>
|||Why not use a FOREIGN KEY to enforce referential integrity?
David Portas
SQL Server MVP
|||Good question!!!
AMB
"David Portas" wrote:

> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data fro
m
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I wan
t
to put in an exception to say insert if Dressing.Dressing_ID =
inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
ENDYou mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) !=
(SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:

> The trigger below was automatically generated when I transferred my data f
rom
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I w
ant
> to put in an exception to say insert if Dressing.Dressing_ID =
> inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=
> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referentia
l
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Jens,

This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü?meyer" wrote:
[vbcol=seagreen]
> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
>
>|||Why not use a FOREIGN KEY to enforce referential integrity?
David Portas
SQL Server MVP
--|||Good question!!!
AMB
"David Portas" wrote:

> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

Accepting Null values in Trigger

The trigger below was automatically generated when I transferred my data from
Access to SQL. Right now it needs to find a match between
Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
to put in an exception to say insert if Dressing.Dressing_ID = inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
the syntax right. Any thoughts?
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID = inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
ENDYou mean that : ?
SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> The trigger below was automatically generated when I transferred my data
> from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> want
> to put in an exception to say insert if Dressing.Dressing_ID => inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Try,
/* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
IF (SELECT COUNT(*) FROM inserted where Day_Dressing_ID is not null) != (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID =inserted.Day_Dressing_ID))
BEGIN
RAISERROR 44447 'The record can''t be added or changed. Referential
integrity rules require a related record in table ''Dressing''.'
ROLLBACK TRANSACTION
END
AMB
"Can" wrote:
> The trigger below was automatically generated when I transferred my data from
> Access to SQL. Right now it needs to find a match between
> Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I want
> to put in an exception to say insert if Dressing.Dressing_ID => inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> the syntax right. Any thoughts?
> /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> IF (SELECT COUNT(*) FROM inserted) !=> (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => inserted.Day_Dressing_ID))
> BEGIN
> RAISERROR 44447 'The record can''t be added or changed. Referential
> integrity rules require a related record in table ''Dressing''.'
> ROLLBACK TRANSACTION
> END
>|||Jens,
> > inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
This condition is saying to join a row from table Dressing to a row in table
inserted if the column Dressing_ID are equal in both tables or
inserted.Day_Dressing_ID is null, so if Dressing.Dressing_ID = 1 and
inserted.Dressing_ID is null those rows are joined.
This will not give you what you expect, see an example.
use northwind
go
create table t1 (
c1 int unique
)
go
create table t2 (
c1 int null
)
go
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
insert into t2 values(null)
insert into t2 values(2)
go
select *
from
t1
inner join
t2
on t1.c1 = t2.c1
or t2.c1 is null
go
drop table t1, t2
go
AMB
"Jens Sü�meyer" wrote:
> You mean that : ?
> SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => > inserted.Day_Dressing_ID OR inserted.Day_Dressing_ID is null)
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Can" <Can@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:E19D9F15-A048-404F-9610-6826BF9E61D6@.microsoft.com...
> > The trigger below was automatically generated when I transferred my data
> > from
> > Access to SQL. Right now it needs to find a match between
> > Dressing.Dressing_ID and inserted.Day_Dressing_ID, that's great EXCEPT I
> > want
> > to put in an exception to say insert if Dressing.Dressing_ID => > inserted.Day_Dressing_ID or inserted.Day_Dressing_ID is null. I can't get
> > the syntax right. Any thoughts?
> >
> > /* * PREVENT INSERTS IF NO MATCHING KEY IN 'Dressing' */
> > IF (SELECT COUNT(*) FROM inserted) !=> > (SELECT COUNT(*) FROM Dressing, inserted WHERE (Dressing.Dressing_ID => > inserted.Day_Dressing_ID))
> > BEGIN
> > RAISERROR 44447 'The record can''t be added or changed. Referential
> > integrity rules require a related record in table ''Dressing''.'
> > ROLLBACK TRANSACTION
> > END
> >
> >
>
>|||Why not use a FOREIGN KEY to enforce referential integrity?
--
David Portas
SQL Server MVP
--|||Good question!!!
AMB
"David Portas" wrote:
> Why not use a FOREIGN KEY to enforce referential integrity?
> --
> David Portas
> SQL Server MVP
> --
>
>

About TRAN, CURSOR and @@ERROR

Please take a look at my code below:
CREATE PROCEDURE ...
...
@.userId int,
...
AS
BEGIN TRAN
DECLARE @.confKey varchar(20), @.confValue varchar(20)
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
OPEN curs_conf
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
WHILE @.@.FETCH_STATUS = 0 BEGIN
DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
IF @.confValue <> '' BEGIN
INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
(@.userId, @.confKey, @.confValue)
IF @.@.ERROR <> 0 BEGIN
ROLLBACK
CLOSE curs_conf
DEALLOCATE curs_conf
RETURN
END
END
FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
END
CLOSE curs_conf
DEALLOCATE curs_conf
COMMIT
This code works but is the way it catches errors correct?
Do I have to test @.@.ERROR elsewhere?
Is it necessary to close and deallocate the cursor before returning when an
error occurs?
Is it better to rollback before or after closing and deallocating the
cursor?
Thanks for answering my questions.
HenriWhy use a cursor?
Just use one statement for the insert and one for the delete.
"Henri" wrote:
> Please take a look at my code below:
> CREATE PROCEDURE ...
> ....
> @.userId int,
> ....
> AS
> BEGIN TRAN
> DECLARE @.confKey varchar(20), @.confValue varchar(20)
> DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM ...
> OPEN curs_conf
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> WHILE @.@.FETCH_STATUS = 0 BEGIN
> DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key = @.confKey
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> IF @.confValue <> '' BEGIN
> INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> (@.userId, @.confKey, @.confValue)
> IF @.@.ERROR <> 0 BEGIN
> ROLLBACK
> CLOSE curs_conf
> DEALLOCATE curs_conf
> RETURN
> END
> END
> FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> END
> CLOSE curs_conf
> DEALLOCATE curs_conf
> COMMIT
> This code works but is the way it catches errors correct?
> Do I have to test @.@.ERROR elsewhere?
> Is it necessary to close and deallocate the cursor before returning when an
> error occurs?
> Is it better to rollback before or after closing and deallocating the
> cursor?
> Thanks for answering my questions.
> Henri
>
>|||In a transaction you will usually want to test for @.@.ERROR after every
statement that manipulates data. As Nigel has said however, there is no
obvious reason to use a cursor here at all. I'm not clear just what this
code is supposed to do so if you need more help we'll need some more
information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas
SQL Server MVP
--|||Sorry I had removed the part after FROM.
It's
DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
myDB.dbo.getKeyValueTable(@.confText)
I'm using a table variable and it can be long to build it.
I'm using a cursor because if I don't I would have to build the same table
variable twice (once to delete the records, once to insert them again if
needed)
As for SQL SERVER documentation, it's not possible to assign a table (SET
@.myTable = funcFillTable(...)) so I don't know any better way than using a
cursor...
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:F1E11350-C8FE-4572-96CC-7467CB3ADBDB@.microsoft.com...
> Why use a cursor?
> Just use one statement for the insert and one for the delete.
> "Henri" wrote:
> > Please take a look at my code below:
> >
> > CREATE PROCEDURE ...
> > ....
> > @.userId int,
> > ....
> >
> > AS
> >
> > BEGIN TRAN
> >
> > DECLARE @.confKey varchar(20), @.confValue varchar(20)
> > DECLARE curs_conf CURSOR LOCAL FAST_FORWARD FOR SELECT [key], value FROM
...
> > OPEN curs_conf
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> >
> > WHILE @.@.FETCH_STATUS = 0 BEGIN
> >
> > DELETE FROM tab_user_conf WHERE [user_id] = @.userId AND conf_key =@.confKey
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> >
> > IF @.confValue <> '' BEGIN
> > INSERT INTO tab_user_conf ([user_id], conf_key, conf_value) VALUES
> > (@.userId, @.confKey, @.confValue)
> > IF @.@.ERROR <> 0 BEGIN
> > ROLLBACK
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> > RETURN
> > END
> > END
> >
> > FETCH NEXT FROM curs_conf INTO @.confKey, @.confValue
> > END
> >
> > CLOSE curs_conf
> > DEALLOCATE curs_conf
> >
> > COMMIT
> >
> > This code works but is the way it catches errors correct?
> > Do I have to test @.@.ERROR elsewhere?
> > Is it necessary to close and deallocate the cursor before returning when
an
> > error occurs?
> > Is it better to rollback before or after closing and deallocating the
> > cursor?
> >
> > Thanks for answering my questions.
> >
> > Henri
> >
> >
> >
> >
>|||My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
from client
and a function converts it in a table variable of key value records, that my
cursor uses.
The aim of this procedure is to update an "user configuration" table,
removing, updating and inserting configuration keys and values as specified
by the client.
I thought it would be clever to delete the keys and insert them again when
needed with the updated values. That prevents from testing for each key, if
it is already present in the base, and if it has to be inserted or updated.
As I said to nigel, the cursor is used so that I don't have to build the
key-value table variable twice.
Am I missing something?
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> In a transaction you will usually want to test for @.@.ERROR after every
> statement that manipulates data. As Nigel has said however, there is no
> obvious reason to use a cursor here at all. I'm not clear just what this
> code is supposed to do so if you need more help we'll need some more
> information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>|||This is an example of why careful separation of database and application
layers is important. You shouldn't need to pass delimited lists into the
database. You've introduced a non-relational structure (the delimited list),
created some procedural code around it (the function) which has then forced
you to implement another compromise (the cursor) to work around the
function's perceived limitations!
It seems that what you actually need is an UPDATE followed by an INSERT,
something like the following. Call this for each of your key-value pairs.
Loops and string parsing are much easier and more efficient in client-code.
CREATE PROCEDURE usp_conf_insert_update
(@.key VARCHAR(10), @.value INTEGER)
AS
UPDATE tab_user_conf
SET conf_value = @.value
WHERE conf_key = @.key
IF @.@.ROWCOUNT=0
INSERT INTO tab_user_conf (conf_key, conf_value)
SELECT @.key, @.value
GO
Of course, tab_user_conf looks suspiciously like an unnormalized list rather
than a table... but that's another discussion :-)
Hope this helps.
--
David Portas
SQL Server MVP
--|||>> I thought it would be clever to delete the keys and insert them again when
needed with the updated values.
That's usually not a good idea. Only update data when necessary or you can
get into problems with RI, triggers and tr logs.
You can use the function to insert into a temp table for the data updates.
Then delete any records that are not needed then insert any new ones.
"Henri" wrote:
> My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> from client
> and a function converts it in a table variable of key value records, that my
> cursor uses.
> The aim of this procedure is to update an "user configuration" table,
> removing, updating and inserting configuration keys and values as specified
> by the client.
> I thought it would be clever to delete the keys and insert them again when
> needed with the updated values. That prevents from testing for each key, if
> it is already present in the base, and if it has to be inserted or updated.
> As I said to nigel, the cursor is used so that I don't have to build the
> key-value table variable twice.
> Am I missing something?
> Henri
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > In a transaction you will usually want to test for @.@.ERROR after every
> > statement that manipulates data. As Nigel has said however, there is no
> > obvious reason to use a cursor here at all. I'm not clear just what this
> > code is supposed to do so if you need more help we'll need some more
> > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
> >
>
>|||Thanks for your help David
I had always thought that multiple client-server access was slower than 1
access with a string parsed on the server
Is string parsing that slow in SQL SERVER?
Thanks again
Henri
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
message de news:ibCdnWWyc6JvOi7cRVn-qw@.giganews.com...
> This is an example of why careful separation of database and application
> layers is important. You shouldn't need to pass delimited lists into the
> database. You've introduced a non-relational structure (the delimited
list),
> created some procedural code around it (the function) which has then
forced
> you to implement another compromise (the cursor) to work around the
> function's perceived limitations!
> It seems that what you actually need is an UPDATE followed by an INSERT,
> something like the following. Call this for each of your key-value pairs.
> Loops and string parsing are much easier and more efficient in
client-code.
> CREATE PROCEDURE usp_conf_insert_update
> (@.key VARCHAR(10), @.value INTEGER)
> AS
> UPDATE tab_user_conf
> SET conf_value = @.value
> WHERE conf_key = @.key
> IF @.@.ROWCOUNT=0
> INSERT INTO tab_user_conf (conf_key, conf_value)
> SELECT @.key, @.value
>
> GO
> Of course, tab_user_conf looks suspiciously like an unnormalized list
rather
> than a table... but that's another discussion :-)
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>
>|||So creating a temp table is better that using a table variable or a cursor?
It's sometime difficult to know what is faster and what is not.
Lucky you were here, thanks again :-)
Henri
"Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> >> I thought it would be clever to delete the keys and insert them again
when
> needed with the updated values.
> That's usually not a good idea. Only update data when necessary or you can
> get into problems with RI, triggers and tr logs.
> You can use the function to insert into a temp table for the data updates.
> Then delete any records that are not needed then insert any new ones.
> "Henri" wrote:
> > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > from client
> > and a function converts it in a table variable of key value records,
that my
> > cursor uses.
> >
> > The aim of this procedure is to update an "user configuration" table,
> > removing, updating and inserting configuration keys and values as
specified
> > by the client.
> > I thought it would be clever to delete the keys and insert them again
when
> > needed with the updated values. That prevents from testing for each key,
if
> > it is already present in the base, and if it has to be inserted or
updated.
> >
> > As I said to nigel, the cursor is used so that I don't have to build the
> > key-value table variable twice.
> > Am I missing something?
> >
> > Henri
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > In a transaction you will usually want to test for @.@.ERROR after every
> > > statement that manipulates data. As Nigel has said however, there is
no
> > > obvious reason to use a cursor here at all. I'm not clear just what
this
> > > code is supposed to do so if you need more help we'll need some more
> > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> > >
> >
> >
> >
> >
>|||It is certainly better than using a cursor.
"Henri" wrote:
> So creating a temp table is better that using a table variable or a cursor?
> It's sometime difficult to know what is faster and what is not.
> Lucky you were here, thanks again :-)
> Henri
> "Nigel Rivett" <sqlnr@.hotmail.com> a écrit dans le message de
> news:B34C01FB-45DE-4A64-9FA1-DE66A293008A@.microsoft.com...
> > >> I thought it would be clever to delete the keys and insert them again
> when
> > needed with the updated values.
> >
> > That's usually not a good idea. Only update data when necessary or you can
> > get into problems with RI, triggers and tr logs.
> >
> > You can use the function to insert into a temp table for the data updates.
> > Then delete any records that are not needed then insert any new ones.
> >
> > "Henri" wrote:
> >
> > > My procedure is getting a key value string like KEY:VALUE;KEY:VALUE;etc.
> > > from client
> > > and a function converts it in a table variable of key value records,
> that my
> > > cursor uses.
> > >
> > > The aim of this procedure is to update an "user configuration" table,
> > > removing, updating and inserting configuration keys and values as
> specified
> > > by the client.
> > > I thought it would be clever to delete the keys and insert them again
> when
> > > needed with the updated values. That prevents from testing for each key,
> if
> > > it is already present in the base, and if it has to be inserted or
> updated.
> > >
> > > As I said to nigel, the cursor is used so that I don't have to build the
> > > key-value table variable twice.
> > > Am I missing something?
> > >
> > > Henri
> > >
> > >
> > > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a écrit dans le
> > > message de news:za2dncKkk8fDBC7cRVn-qA@.giganews.com...
> > > > In a transaction you will usually want to test for @.@.ERROR after every
> > > > statement that manipulates data. As Nigel has said however, there is
> no
> > > > obvious reason to use a cursor here at all. I'm not clear just what
> this
> > > > code is supposed to do so if you need more help we'll need some more
> > > > information. Refer to: http://www.aspfaq.com/etiquette.asp?id=5006
> > > >
> > > > --
> > > > David Portas
> > > > SQL Server MVP
> > > > --
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
>
>|||Performance is always something you should test out for yourself with your
data - it's rarely possible to generalize. For strings of trivial size the
efficiency of client code vs TSQL is probably negligible but your comments
on the performance of your function suggested this wasn't a negligible
problem. My point was that you should start from the assumptions of correct
design - performance optimization comes later.
--
David Portas
SQL Server MVP
--

about SQL String (security question)

I have a SQL Query String like below..

string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";

in my program it does work perfectly ... but now I just recalled my teacher have told.. it may cause security problem in that way...

maybe I need to change 【UPDATE Member SET Member_pwd = '" + pwd +】 to【UPDATE Member SET Member_pwd = '" + @.pwd +】

is there any difference between pwd and @.pwd?

thank you very much

I think what your teacher suggested is to use Parameterized Queries. There are a few benefits to it - primarily - security, maintenance/ease of coding. Please google for more info.I can type up a couple of lines but you will learn more from the articles already published.|||

Using this dynamic query building with strings is bad. Users can potentially inject malicious code into your queries. I have included a helpful link for you to review:http://www.4guysfromrolla.com/webtech/092601-1.shtml

Good Luck!

|||

thanks for you all (very much)

do you have a example for C#... shame on me I'm not similiar with VB...

I just trying to fix my original code . can you give me some suggestion?

except I have to change pwd to @.pwd.. is something else I've to add or edit?

thank you very much

================start of original code ============================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

================end of original code ============================

================fixed code ===================================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + @.pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

|||The post is marked as answered. So I am assuming you got your solution?|||

no no no... I'm sorry

should I post a new topic for extra question?

I still have many wonder on this filed...

thank you

|||

Hi,

You will need to add the parameters for all the criterias that is used in the query. Not only the pwd.

A typical sample might look like

SqlCommand cmd = new SqlCommand("Select * from Table WHERE [name]=@.name", this.connection);
cmd.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
cmd.Parameters.Value = "Kevin";
SqlDataReader sdr = cmd.ExecuteReader();

About an error message " Could not find stored procedure dbo.U_Login ".

Hi All,I am having an error message when running the program below, the message says " Could not find stored procedure 'dbo.U_Login ". I have tried a inline Sql statement to match if user exists and then redirects to another page but when using a Stored Procedure what I get is the above mentioned error. I am logged onto my PC as Administrator but I don't know why this error appaears. Can anyone help me with this ? I am using SQL Server Express with Visual Studio. here the application I am trying to run:

1using System;2using System.Data.SqlClient;3using System.Data;4using System.Configuration;5using System.Web;6using System.Web.Security;7using System.Web.UI;8using System.Web.UI.WebControls;9using System.Web.UI.WebControls.WebParts;10using System.Web.UI.HtmlControls;111213public partialclass _Default : System.Web.UI.Page14{15protected void Page_Load(object sender, EventArgs e)16 {1718 }19protected void btnSubmit_Click(object sender, EventArgs e)20 {2122//SqlDataSource LoginDataSource = new SqlDataSource();23 //LoginDataSource.ConnectionString =24 // ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;25 ////ConfigurationManager.ConnectionStrings["LoginConnectionString"].ToString();26 ////// if (LoginDataSource != null)27 //// // Response.Redirect("DebugPage.aspx");2829 //Sql connection = LoginDataSource.ConnectionString;3031string UserName = txtUserName.Text;32string Password = txtPassword.Text;3334// string query = "SELECT * FROM Users WHERE userName = @.UserName " + "AND Password = @.Password";35 //SqlCommand Command = new SqlCommand(query, new SqlConnection(GetConnectionString()));3637 SqlCommand Command =new SqlCommand("dbo.U_Login",new SqlConnection(GetConnectionString()));383940//SqlConnection SqlConnection1 = new SqlConnection(GetConnectionString()); //added41 //SqlCommand Command = new SqlCommand(); //added42 //Command.Connection = SqlConnection1; //added43 // Command.Connection = new SqlConnection(GetConnectionString());44 //SqlConnection_1.Open();//added4546 Command.CommandType = CommandType.StoredProcedure;//added47 //Command.CommandText = "dbo.U_Login"; //added4849505152 Command.Parameters.AddWithValue("@.UserName", UserName);5354 Command.Parameters.AddWithValue("@.Password", Password);5556 Command.Connection.Open();5758 SqlDataReader reader;596061//reader = Command.ExecuteReader(CommandBehavior.CloseConnection);116263 reader = Command.ExecuteReader();646566if (reader.Read())6768 Response.Redirect("DebugPage.aspx");69else70 Response.Write("user doesn't exist");717273//SqlConnection_1.Close();//added747576 }77private static string GetConnectionString()78 {7980return ConfigurationManager.ConnectionStrings["LoginConnectionString"].ConnectionString;8182 }838485 }86878889

Thanks in advance

Hello my friend,

Try it without the "dbo." prefix in the code.

Kind regards

Scotty