on update catch 22

How do you make an update cascade opperation if you want to set the PK to an excisting one?
I have a table where to post are the same only the PK differs, now I want to delete one but transfer it's dependecies. i.e redirect its references to the other post.
How can I do that using on update cascade/delete. I see a catch 22 coming up!

/Jonneponne

Hi

I would redirect all dependencies to a single post you want to keep and then delete a duplicate post.

ON UPDATE CASCADE works only if you are trying to update the KEY references by Foreign Key , not the other way round.

NB.

on Insert, then Update Tigger Help

when a record is inserted into tNTS, I need the columnseq_numupdated with the value of the columnid(it's the PK)

can someone help me with my tigger:

CREATE TRIGGER updateNewSeq
ON dbo.tNTS
FOR INSERT
AS

update tNTS
(
set seq_num =tNTS
)
SELECT
NTS_id
FROM INSERTED

cheersYour update statement looks wierd.

Use as an example:

 update titleauthor
 set title_id = inserted.title_id
 from titleauthor, deleted, inserted
 where deleted.title_id = titleauthor.title_id

|||I tried:

CREATE TRIGGER [updateSeqNum] ON [dbo].[tNTS]
FOR INSERT
AS
update tNTS
set seq_num = NTS_id
from inserted

got error: error 209 ambigious column name: NTS_id

NTS_id is the pk i'm trying to copy to column seq_num after the record is inserted|||You need a where statement for starters so it knows where to actually update.|||

update tNTS

set seq_num=i.NTS_id

from tNTS

JOIN inserted i ON (tNTS.id=i.id)

|||thanks motley, it worked

On DELETE On UPDATE Cascade syntax error

Hello

I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data. However, I have having problems.

I have already created the tables with primary keys on each table and foreign keys linking each table to the next.

I tried to delete a row from the parent table and was given this error:

DELETE FROM [dbo].[DomNam]
WHERE [DomNam]=N' football '

Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DomNam'. The conflict occurred in database 'DomDB', table 'Dom_CatA', column 'DomNam'.

I tried to insert an alter table query:

ALTER TABLE dbo.DomNam
ADD CONSTRAINT FK_Dom_ID

REFERENCES dbo.Dom_CatA (Dom_ID)

ON DELETE CASCADE ON UPDATE CASCADE

But on Execute I saw this error:

Error]
Incorrect syntax for definition of the 'TABLE' constraint

What is wrong with the above syntax?

Or would it be better if I used a trigger instead because I already have foreign keys set within the tables?
If so please give an example of the syntax for the trigger I would need to update and cascade data from all tables.

I would be grateful for any advice. Thanks.

Hi

Have you missed something like this:

ALTER TABLE dbo.DomNamADD CONSTRAINT FK_Dom_IDFOREIGN KEY (Dom_ID)REFERENCES dbo.Dom_CatA (Dom_ID)ON DELETE CASCADE ON UPDATE CASCADE

Trigger could result such an error( DELETE statement conflicted with COLUMN REFERENCE constraint ..... ) either.

|||

(I need to be able to regularly, update or delete data from my parent table and subsequent child tables from A to Z, each table contains data.)

The above means what you are looking for require a trigger because Cascade On DELETE and UPDATE is DRI(declarative referential integrity), that means if A references B then B must exist, it is very clean simple relational Algebra, anything outside that you need a trigger. But you may have some columns that qualify for it, try the threads below for how to enable it. Hope this helps.

http://forums.asp.net/thread/1315554.aspx

http://forums.asp.net/thread/1120122.aspx

|||

Hi

Thanks guys for your reply and help.

I think that my problem is that I have created a many to many relationship without foreign key restraints.

On the parent table I have used the primary key from the first child table "A" as the foreign key.

The child tables are from A to Z.

With the first child table "A" I have used the Primary key from the parent as the foreign key.

With the child table "B" I have used the Primary key from the previous "A" table as the foreign key in "B" table. I have used the primary key as the foreign key in all subsequent tables without declaring referential integrity.

I know that in the very near future I will need to update and delete much of the records in these tables.

How do I remove the foreign keys so that I might be able to create a junction table, which I have since found that I need for a many to many table relationship. Or is there another easier way to do this?

Thanks

Lynn

|||

Hi guys

I have since learned how to delete the foreign keys.

I am viewing my SQL 2000 database using Aqua Data,

* Click on the last sibbling table.

* Click Alter table

* Select constrains tab

* Select FK + delete + OK

I have kept my Primary Keys in tact.

Now I need to relink my tables and ensure that I am able to cascade update or delete. All tables contain similar content, which will require updating or deleting often. Tables are from A to Z.. What is the best method to do this?

Thanks

Lynn

|||I have told you that all you can do is one to one referencing for DRI(declarative referential integrity) to work so if you have figured out the tables that qualify for if A references B then B must exist at the top of Enterprise Manager you will see enable relationship. And those that did not qualify you need a trigger. I hope I am very clear it is not DRI for A to Z but A to B. Hope this helps.|||

I just remembered I had a conversation with a database person who did not understand DRI(declarative referential integrity) so let me explain you have tables A to Z meaning 26 tables it means you can have only 13 DRIs that is 13 Cascade DELETE and Cascade UPDATE because it is a location based way to delete and update related data. So although foreign key is required to enable DRI not all foreign keys can be DRI.

You said the data is related that is DDL(data definition language) where relationship is determined by Upper and Lower bound Cardinality while DRI is DML(data manipulation language) keeping track of child data through one to one mapping if A references B then B must exist. I hope this makes it clear. You have to be careful with the ALTER TABLE ADD or DROP CONSTRAINT in 2000 because most ALTER must be done with Enterprise Manager or SQL Server 2000 will tell you it is wrong. One more thing you can use trigger to do it the way you want but triggers are resource intensive and may not fire all the time. Hope this helps.

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part10/c3761.mspx?mfr=true

|||

Hi Caddre

Thanks for the reply, as you have gathered I am not a sql database expert, I am trying to understand DRI.

The whole think would be easier if I had everything in the one table (but it would be too wide to manage) because the same column of data is repeated on every table A to Z., therefore data row of "John Smith" exists in all tables. But in the child tables "A" table for example "John Smith" has extra columns e.g. art, apples, auto, etc.,

Consequently if I remove or insert a row in parent table, the children need the same edit.

Would it be just as complicated if I created a new table "juntion table" and include all the primary key's from the parent and children tables as foreign keys in the junction table. Or would that not work?

Thanks

Lynn

|||

I am sorry I did not come to this thread to confuse you but what you are saying now is not relational, so I have found you two solutions you can add to valid DRI. I also think it is Microsoft documentation that confused you because in 2005 multiple Cascade is allowed but one table cannot be repeated twice that is the same thing I am saying. Hope this helps.

http://support.microsoft.com/kb/142480

http://msdn2.microsoft.com/en-us/library/aa224818(sql.80).aspx

On delete cascade

Hi,
1. To my knowledge from sql 2k, there is an option "On delete cascade" and
"On update cascade". But in almost all databases which I have seen till date
,
user defined SP's have been created for deleting table content instead of
checking this "On delete cascade" option. Is there any specific reason for
this? i.e., will there be any performance or anyother reason behind this?
2. Lets assume that I have enabled "On delete cascade" in my DB. If I delete
a record in master table it should delete corresponding details in 4 to 5
tables. What would happen if it fails for some reasons while deleting the 3r
d
table content. Will it rollback?
Regards
Pradeep1. "will there be any performance or anyother reason behind this?"
-No, some guys really want to have their own hand on the deleting of
the data, due to extra issues with logging business rules checking etc.
2. "Will it rollback?"
Sure, thats what a transactional database is for.
HTH, Jens Suessmeyer.|||If can be a safety issue too.
For example, if you use Enterprise Manager to delete a parent row,
referential integrity will prevent you from deleting it if there are child
rows. With cascade delete turned on, the parent and all related children ar
e
deleted. If it was deleted by mistake, it can be quite a complex task to
restore the parent and all the children.
Joe
"Jens" wrote:

> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>|||There are technical reasons behind this as well. Cascading referential
actions complicate deadlock minimization because there isn't any way to
determine with certainty the order in which locks will be obtained. If you
issue the DELETE or UPDATE statements in a procedure, you have control over
the order in which locks are obtained and thus can prevent most deadlocks.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133783057.518482.200520@.g47g2000cwa.googlegroups.com...
> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>

On any change or update

I am wiriting a C# application that stores availabilty information in a tabl
e
ie UserName, UserAvailable (just to keep it simple)
I am looking for a recommendation for the best way to have the SQL database
notify my c# client that any data in this table has been changed.
In my old non .net application I used a client/server model where the
server watched for changes and sent messages to each registered client but n
o
data was ever saved. Two of my goals with the new application are eliminatin
g
the server application and saving the data to a SQL db.
Currently I have my application re-examine the table every n-seconds but
this is far from optimal. Any suggestions would be highly appreciated.
Regards,
SeanHi
What is your SQL Server vesion?
If it is SQL Server 2000 , I think you may want to consider using triggers
to examine "deleted" and "inserted" virtual tables
Does your App make changes too?
"Codesmith" <Codesmith@.discussions.microsoft.com> wrote in message
news:8F93298C-EBD9-4CE4-A7C9-8DA9E9731908@.microsoft.com...
>I am wiriting a C# application that stores availabilty information in a
>table
> ie UserName, UserAvailable (just to keep it simple)
> I am looking for a recommendation for the best way to have the SQL
> database
> notify my c# client that any data in this table has been changed.
> In my old non .net application I used a client/server model where the
> server watched for changes and sent messages to each registered client but
> no
> data was ever saved. Two of my goals with the new application are
> eliminating
> the server application and saving the data to a SQL db.
> Currently I have my application re-examine the table every n-seconds but
> this is far from optimal. Any suggestions would be highly appreciated.
> Regards,
> Sean|||If you are on, or can upgrade to, SQL Serer 2005, read up on Query Notificat
ions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Codesmith" <Codesmith@.discussions.microsoft.com> wrote in message
news:8F93298C-EBD9-4CE4-A7C9-8DA9E9731908@.microsoft.com...
>I am wiriting a C# application that stores availabilty information in a tab
le
> ie UserName, UserAvailable (just to keep it simple)
> I am looking for a recommendation for the best way to have the SQL databas
e
> notify my c# client that any data in this table has been changed.
> In my old non .net application I used a client/server model where the
> server watched for changes and sent messages to each registered client but
no
> data was ever saved. Two of my goals with the new application are eliminat
ing
> the server application and saving the data to a SQL db.
> Currently I have my application re-examine the table every n-seconds but
> this is far from optimal. Any suggestions would be highly appreciated.
> Regards,
> Sean|||There are many approaches for implementing that, the best and sexiest
solution is to use Query Notification as Tibor said. There are other
mechanisms for doing this (including calling Message Queues...), but
not as easy to use as QN :-)
HTH, jens Suessmeyer.|||Tibor,
Thanks for your responce. After reviewing the documentaion on the MSDN web
page this sounds like just what I had in mind.
Do you know if the service broker feature is available in the 2005 Express
edition of SQL?
"Tibor Karaszi" wrote:

> If you are on, or can upgrade to, SQL Serer 2005, read up on Query Notific
ations.
>|||Hi,
http://www.microsoft.com/sql/prodin...e-features.mspx
Service Broker (Subscriber only) --means yes, you can.
HTH, Jens Suessmeyer.|||Hi Jens!
the way I read the part "subscriber only" is that you cannot insert into a q
ueue on Express. To me,
this sounds like Express does not support query notifications. But I happily
admit that I'm only
guessing here. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1136912680.595505.22710@.g43g2000cwa.googlegroups.com...
> Hi,
> http://www.microsoft.com/sql/prodin...e-features.mspx
> Service Broker (Subscriber only) --means yes, you can.
>
> HTH, Jens Suessmeyer.
>|||You are right I just read down the white paper, you can subscribe
within broker queues outside the SQL Server Express Editions, so you
just can be notified. So even default queues are not creaetable. Sorry
for the wrong information to the original poster, thanks to Tibor.
-Jens.|||Service Broker is available in SQL Express 2005.
The only restriction is that when exchanging messages between separate SQL
instances, the message has to pass trough a higher version. That is, you
cannot send a message from a SQL Express to another SQL Express directly.
Since you're interested in Query Notifications, you will probably keep the
messages local within the SQL Express instance, so this limitation won't
affect you whatsoever.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Codesmith" <Codesmith@.discussions.microsoft.com> wrote in message
news:7DF1F9B5-19F0-4E41-AA75-D2B19DA5EE7B@.microsoft.com...
> Tibor,
> Thanks for your responce. After reviewing the documentaion on the MSDN web
> page this sounds like just what I had in mind.
> Do you know if the service broker feature is available in the 2005 Express
> edition of SQL?
>
> "Tibor Karaszi" wrote:
>|||Sorry, not true Tibor. The short story is that as long as you are working
within the same instance (which is the only way query notifications work
with the default listener) SQL Express Service Broker is 100% equivalent to
all the other SKU's. If you are talking between sku's, there is a small
restriction as Remus pointed out.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uiQOc2gFGHA.3280@.TK2MSFTNGP10.phx.gbl...
> Hi Jens!
> the way I read the part "subscriber only" is that you cannot insert into a
> queue on Express. To me, this sounds like Express does not support query
> notifications. But I happily admit that I'm only guessing here. ;-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1136912680.595505.22710@.g43g2000cwa.googlegroups.com...
>

OleDbCommand with Parameters

Hi,

I have application connected to MS Access DB using OleDB. When creating commands (Insert/Update/Select) I use OleDbParamater class to insert data into command. Examples :

Select ::

OleDbCommand select_cmd = new OleDbCommand("SELECT * FROM " + ObjectTable.TableName + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Update ::

OleDbCommand update_cmd = new OleDbCommand("Update " + ObjectTable.TableName + " SET " +
ObjectTable.idParent + "=@." + ObjectTable.idParent + " , " +
ObjectTable.idParentUnder + "=@." + ObjectTable.idParentUnder + " , " +
ObjectTable.License + "=@." + ObjectTable.License + " , " +
ObjectTable.Type + "=@." + ObjectTable.Type + " ," +
ObjectTable.Language + "=@." + ObjectTable.Language + " , " +
ObjectTable.Name + "=@." + ObjectTable.Name + " , " +
ObjectTable.Checksum + "=@." + ObjectTable.Checksum + " , " +
ObjectTable.VText + "=@." + ObjectTable.VText + " , " +
ObjectTable.VInt + "=@." + ObjectTable.VInt + " WHERE " +
ObjectTable.idObject + "=@." + ObjectTable.idObject + " AND " +
ObjectTable.idObjectUnder + "=@." + ObjectTable.idObjectUnder);

Parametes:: (Adding in separate method -> AddParameters(OleDbCommand command); )

command.Parameters.Add("@." + ObjectTable.idObject, OleDbType.BigInt).Value = this.IDUpper;
command.Parameters.Add("@." + ObjectTable.idObjectUnder, OleDbType.BigInt).Value = this.IDUnder;
command.Parameters.Add("@." + ObjectTable.Name, OleDbType.VarChar).Value = this.Name;
command.Parameters.Add("@." + ObjectTable.idParent, OleDbType.BigInt).Value = GetUpper(this.IDParent);
command.Parameters.Add("@." + ObjectTable.idParentUnder, OleDbType.BigInt).Value = GetUnder(this.IDParent);
command.Parameters.Add("@." + ObjectTable.License, OleDbType.BigInt).Value = this.License;
command.Parameters.Add("@." + ObjectTable.Language, OleDbType.BigInt).Value = this.Language;
command.Parameters.Add("@." + ObjectTable.Type, OleDbType.BigInt).Value = (int)this.Type;

command.Parameters.Add("@." + ObjectTable.VText, OleDbType.VarChar).Value = String.IsNullOrEmpty(this.VText) ? null : this.VText;
command.Parameters.Add("@." + ObjectTable.VInt, OleDbType.BigInt).Value = this.VInt;
command.Parameters.Add("@." + ObjectTable.Checksum, OleDbType.BigInt).Value = this.Checksum;

Question: Does the order of adding parameters to command matter? Because allways when the order of parameters added is diffrent from order in command text, I get weird Exceptions . I thought that the name matters, not the order, but it seems that system doesn't care about the parameter's name, it just picks next parameter in command.Parameters when putting values. How is it?Do you mean that if it could matter during the addition of the parameters ? It does not, as the .add method only puts the parameter in the collection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||You know what? It does. And it is the only thing that matters (talking about OleDb) -> ORDER. Despite of using names (I could questionmarks instead of parameter's names). I've done this using Oracle DB and (of cource) namespace and everything worked fine, but OleDb looks to handle thing in it's own weird way. Example :

OleDbCommand selectCmd = new OleDbCommand( "Select * from Customers where CustomerName=@.name AND CustomerAge=@.age);

selectCmd.Parameters.Add("@.age", OleDbType.Integer).Value = 20;

selectCmd.Parameters.Add("@.name", OleDbType.VarChar).Value = "Michael";

this is NOT going to work !!! If the order of parameters added to command's parameters collection is diffrent from order of parameters used in command itself, it won't work.

OLEDBcommand is too slow

Hello,

I'm using an OLEDB Command in a DataFlow which performs a parametric query to update thousands of rowsets but it is very slow.

Is there an alternative ?

it's not the component's fault it is slow. SQL faster at set based operations.

You can insert all the rows into a temporary table and then using a SQL task, run the update by joining the two tables together.|||

Thanks!

I've tested this solution, but it seems to persisted a certains slowness.

My dataflow uses these components:

OLEDB Source --> Lookup --

|||As with anything, you have to find out where the bottleneck is. Is it the source? is it the lookup? is it the dest?

Start simple. How quickly does the source get the records? Dump everything into Trash Destination. Then add the lookup? is the lookup taking a while to cache the rows? Are you selecting the whole table or just the keys that you need? etc etc.

Finally, having a poor query in SQL source will result in a slow data flow. Are the tables correctly indexed in the source query. The dest? Two many indexes? Lookup? Indexes etc etc.

Is the final SQL update correctly indexed?

Listing two components in your data flow and saying they slow is the vaguest statement you could say.

Many reasons, more possible solutions.sql

OLEDB performance and Script task

Hi enquiring whether is it more efficient to use OLEDB destination to update and extract all records from table A to tabl e B or use script task? And whether OleDB destination perform row by row update? Finally will data integrity be affected if i run delete, update and insert operations in the same data flow but extracting different sets of data each time from the 2 tables?

Unfortunately the answers to your first question is, it depends... Try testing them out for yourself as the speeds will vary by system and by the transforms that you are trying to push the data through.

OleDB destination will NOT preform a row by row UPDATE. The destination transformation is for inserting new rows, to do the update you will use the OLE DB Command Transformation.

Your data integrity should be fine for doing delete update and insert operations in the same data flow. Of course, this will vary depending on what transformations you do, when, how you get your data, etc... Try watching the Kimball video which is linked to on the front page for a good overview of the various ways of handling update / insert. Also, look at the thread for "check to see if a row exists, if so update, else insert" (also stickied on the front page).

|||

Thanks for the quick reply, but using OLE DB Command from what i understand is too expensive, so comparing OLE DB Command and Script task, script task would be a better choice in this aspect, can i say that at first glance?

|||If you have a large number of updates, you should write those updates to a temporary table and then once the data flow is done, issue an Execute SQL task to perform a batch update. That's the *best* option.|||

Hi,

Oh thanks for the advice, but just to verify this 'batch update', is it using a sql statement something like this

"update tablea set tablea.rows = tableb.rows from tablea, tableb (with the critriea)" and the temporary table contains only the records that need to be updated.

|||

garynkill23 wrote:

Hi,

Oh thanks for the advice, but just to verify this 'batch update', is it using a sql statement something like this

"update tablea set tablea.rows = tableb.rows from tablea, tableb (with the critriea)" and the temporary table contains only the records that need to be updated.

Yes

oledb command

I am transfering large data.

I use oledb command to insert and update as i need to make some modifications to incoming data.I do my modifications in the procedure.

But the command does not insert as the data is huge at one shot.

if i try to send small data it works fine

Its shows warning(yellow color)

How can i achive inserting huge data effeciently please help.

Yellow just indicates that the task is still working, not that there is a problem. Red indicates a problem.

The OLEDB command is not the best for moving large amounts of data. When you need to perform an update, you might try writing the update data to a temporary table, then using an Execute SQL task to perform a batch update, as that usually has better performance.

|||

Even if i had to transfer to temporary table i need to using OLEDB connection or oledb command etc to store my update data and then use execute sql task in the control flow.

As i said earlier i am using oledb command as i try making some modifications in my procedure (updating several table at one time)

How can this be done?

|||you need to insert data with an OLE DB Destination, not the command transformation. Or the SQL Server destination. The OLE DB Command is not really designed for INSERTS as it executes for every row of data going through it.

OLE DB Command

Hi All.
I'm using OLE DB Command in order to update my oracle DB. I use MS provider to connect to oracle (I tried the oracle provider too).

I try to execute the simple query as

UPDATE TABLE1
SET
VALID_DATE_TO = to_date(?,'dd/mm/yyyy')
WHERE UNIT_CODE=?
AND VALID_DATE_TO = to_date('01/01/9999','dd/mm/yyyy')

I declare the appropriate parameters.
So, the error is fell:

[OLE DB Command [1247]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01861: literal does not match format string ".

[OLE DB Command [1247]] Error: The "input "OLE DB Command Input" (1252)" failed because error code 0xC020906E occurred, and the error row disposition on "input "OLE DB Command Input" (1252)" specifies failure on error. An error occurred on the specified object of the specified component.

[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command " (1247) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

What is this?!!

Any ideas ...

Thanks in advance.

Eli

What type is your first parameter? It must be a string judging by the SQL, and that string must already be formatted in dd/mm/yyy format. Is that so?|||

yes, the first parameter is string in 'dd/mm/yyyy' format.

|||

Maybe you just cannot use a parameter in this way. We are now pushing my Oracle limits, but perhaps the equivalent of a SQL Profiler trace might shed some light on what is really happening to that parameter. For SQL Server I would expect all sorts of calls preparation and passing paramaters that would make some sense in this context.

An alternative is to convert the string column to a date in the pipeline, Derived Column or Data Conversion, and dispense with the to_date alltogether.

OldValuesParameterFormatString SqlDataSource Update

OK guys, I'm sure I'm doing something stupid here but after 2 days I find myself extremely frusted... Any help would be appreciated

I'm tring to write an update procedure for an SqlDataSource control that will allow me to chage the primary key values of a record in the table. Everything seems to work fine with the exception of obtaining the old values (the primary key before its changed by the end user) during the update. I've read several articles on the web an in the forums on this topic. Much of it has to do with the .NET beta version alteration from "original_{0}" to "{0}". I believe those conversations do not describe my problem.

Anyhow, onto some source code. Here's the ASP source code for a grid view and sql data source.

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="productName,productURL" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display."> <Columns> <asp:CommandField ShowSelectButton="True" /> <asp:BoundField DataField="productName" HeaderText="productName" ReadOnly="True" SortExpression="productName" /> <asp:BoundField DataField="productURL" HeaderText="productURL" ReadOnly="True" SortExpression="productURL" /> <asp:BoundField DataField="productSupportURL" HeaderText="productSupportURL" SortExpression="productSupportURL" /> <asp:BoundField DataField="description" HeaderText="description" SortExpression="description" /> <asp:BoundField DataField="modified" HeaderText="modified" SortExpression="modified" /> <asp:BoundField DataField="userid" HeaderText="userid" SortExpression="userid" /> <asp:BoundField DataField="useridDomain" HeaderText="useridDomain" SortExpression="useridDomain" /> <asp:BoundField DataField="sortOrder" HeaderText="sortOrder" SortExpression="sortOrder" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SBAAdminConnectionString1%>" DeleteCommand="DELETE FROM [homePageList] WHERE [productName] = @.old_productName AND [productURL] = @.old_productURL" InsertCommand="INSERT INTO [homePageList] ([productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder]) VALUES (@.productName, @.productURL, @.productSupportURL, @.description, @.modified, @.userid, @.useridDomain, @.sortOrder)" ProviderName="<%$ ConnectionStrings:SBAAdminConnectionString1.ProviderName%>" SelectCommand="SELECT [productName], [productURL], [productSupportURL], [description], [modified], [userid], [useridDomain], [sortOrder] FROM [homePageList]" UpdateCommand="UPDATE homePageList SET productSupportURL = @.productSupportURL, description = @.description, modified = @.modified, userid = @.userid, useridDomain = @.useridDomain, sortOrder = @.sortOrder, productName = @.productName, productURL = @.productURL WHERE (productName = @.original_productName) AND (productURL = @.original_productURL)" OldValuesParameterFormatString="original_{0}"> <DeleteParameters> <asp:Parameter Name="old_productName" Type="String" /> <asp:Parameter Name="old_productURL" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productName" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="GridView1" Direction="InputOutput" Name="original_productURL" PropertyName="SelectedValue" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="productName" Type="String" /> <asp:Parameter Name="productURL" Type="String" /> <asp:Parameter Name="productSupportURL" Type="String" /> <asp:Parameter Name="description" Type="String" /> <asp:Parameter Name="modified" Type="DateTime" /> <asp:Parameter Name="userid" Type="String" /> <asp:Parameter Name="useridDomain" Type="String" /> <asp:Parameter Name="sortOrder" Type="Single" /> </InsertParameters> </asp:SqlDataSource>

I added a text box for every field in the grid to the form and update the fields when the user selects a record in the GridView. I then added an Update button to the form and tied the event to this procedure: 

Protected Sub bUpdate_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles bUpdate.Click'if the user has not entered key fields!If missingKeyFields()Then Exit Sub End If Dim count, soAs Integer Dim uDomain, uidAs String'parse out the user id and domain.Try If HttpContext.Current.User.Identity.Name =""Then uid ="unknow" uDomain ="unknown"Else uid = HttpContext.Current.User.Identity.Name uid = uid.Substring(uid.IndexOf("\") + 1) uDomain = uid.Substring(0, uid.IndexOf("\") - 1)End If Catch exAs Exception uid ="unknow" uDomain ="unknown"End Try'fill in the parameters '@.productName, @.productURL, @.productSupportURL, @.description, @.modified, @.userid, @.useridDomain, @.sortOrderMe.SqlDataSource1.UpdateParameters.Item("productName").DefaultValue =Me.tbProductName.TextMe.SqlDataSource1.UpdateParameters.Item("productURL").DefaultValue =Me.tbProductURL.TextMe.SqlDataSource1.UpdateParameters.Item("productSupportURL").DefaultValue =Me.tbProductSupportURL.TextMe.SqlDataSource1.UpdateParameters.Item("description").DefaultValue =Me.tbProductDescription.TextMe.SqlDataSource1.UpdateParameters.Item("modified").DefaultValue = NowMe.SqlDataSource1.UpdateParameters.Item("userid").DefaultValue = uidMe.SqlDataSource1.UpdateParameters.Item("useridDomain").DefaultValue = uDomainMe.SqlDataSource1.UpdateParameters.Item("sortOrder").ConvertEmptyStringToNull =True If Integer.TryParse(Me.tbSortOrder.Text, so)Then Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue = soElse Me.SqlDataSource1.UpdateParameters.Item("sortOrder").DefaultValue =""End If'insert the record. count =Me.SqlDataSource1.Update()Me.lCommandInfo.Text = count.ToString +" record was updated."End Sub
 
The net result of the procedure is the message "0 record was updated." when I select a record in the gridView and then edit the "productName" field and click Update.

In an attempt to trace down the problem I added this code:

Protected Sub SqlDataSource1_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlDataSource1.Updating Message.Text = e.Command.CommandText Message.Text +="<br>"For lpAs Integer = 0To e.Command.Parameters.Count - 1 Message.Text += e.Command.Parameters(lp).ParameterName +"=" + e.Command.Parameters(lp).Value Message.Text +="<br>"Next End Sub

The message label displays the following text:

UPDATE homePageList SET productSupportURL = @.productSupportURL, description = @.description, modified = @.modified, userid = @.userid, useridDomain = @.useridDomain, sortOrder = @.sortOrder, productName = @.productName, productURL = @.productURL WHERE (productName = @.original_productName) AND (productURL = @.original_productURL)@.description=interesting!@.modified=3/15/2007 4:07:31 PM@.userid=unknow@.useridDomain=unknown@.sortOrder=@.productName=ok22@.productURL=http@.productSupportURL=http@.original_productName=ok@.original_productURL=ok

Notice how the "@.original_productURL" has the same value as the "@.original_productName"? This should not be the case. The @.original_productURL should equal "http" since I didn't update the field's value. It seems like the second field of the primary key is not getting updated or passed to the @.original_productURL parameter. Since .NET handles the @.original_{0} parameters I don't know why the field isn't being properly updated.

Any thoughts on how to track down the root cause of my problem... Or better yet any thoughts on a work around to my problem?

Thanks,
Johnny

way back in school i was taught update is the combination of select ,delete & insert....since u r changing the primarykey........select the row in temp variables,make the changes in the varioable,delete original from database & insert the modified row.....

this can be done by stored procedure or a datareader object easily.

|||

That does sound like a work around for my current situation, but I would hate to re-invent the wheel by creating temp variables that mimic the functionality of the OldValues parameter array.

Any other thoughts?

Johnnny

|||if u find another way let me know|||

OK here's the easiest work around I could find.

1) I removed the control binding for the parameters "original_productName" and "original_productURL". This was set to "GridView1.SelectedValue".
2) I manually set the parameters in my button click subroutine:
Me.SqlDataSource1.UpdateParameters.Item("original_productName").DefaultValue =Me.GridView1.SelectedDataKey.Item(0)
Me.SqlDataSource1.UpdateParameters.Item("original_productURL").DefaultValue =Me.GridView1.SelectedDataKey.Item(1)

I hope this helps others that run into issues with using more then 1 field as a primary key.

Now onto my complaint. While I'm willing to be called a newbie at ASP.NET and will bow to anyone who can show me the proper way to use OLDValues, this problem appears to be a bug in the GridViews control or SqlDataSource control. If I had the source code for these controls I would debug them for Microsoft. To summorize the issue...

1) Create a table with 2 fields as the primary key.
2) Add an SqlDataSource control for the table to an ASP.NET page. Be sure to add SQL code for the Insert, Update, Delete methods with parameters that correspond to the OldValuesParameterFormatString.
3) Add a GridView to the ASP.NET page and set its datasource to the SqlDataSource control.
4) In the Insert, Update, and Delete queries of the SqlDatasource control, bind the old value parameters to the GridView's SelectedValue.
5) Add a label to the ASP.NET page.
6) Add a Button to the ASP.NET page.
7) In the Button click procedure add code to update the record and execute the update method of the SqlDataSource. i.e. Sqldatasource1.updateParameters.item(0).defaultValue = "new value". updatedCount = Sqldatasource1.Update().
8) Display the updated count in the label.

When you test the page you'll find that 0 records are updated. This problem seesm to stem from the fact that only the first column of the GridView is used as the selectedValue. Meaning both key fields for the database table have been set to the first column's value of the GridView. Its almost enough to make me start a blog! lol Good luck people :)

Johnny

|||

what is the tbProductName...

is it the id of the some control.

Old Users / New Domain - Any way to directly update SYSXLOGINS

FIRST - I already know I it is not recommended to directly update systems
tables - no need to remind me. That being said, here is the scenario. I
have a bunch of users who are switching from one domain to another. EXAMPLE
login -
myco.us.east\joe should NOW be myco.us.west\joe
So, is there any easy way to just change the LOGIN name. I know that
SYSLOGINS is a view and that SYSXLOGINS is the root table. Problem is that
NAME in SYSXLOGINS doesnt seem to reflect the login name, rather the user
name. It would be great is I could just change the login name via SQL - Any
ideas on this' Exactly what columns and where?What is the version of SQL Server?
Take a look at sp_change_users_login in the BOL
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:CC69F7B5-2B63-4C12-AF93-D883BB692B28@.microsoft.com...
> FIRST - I already know I it is not recommended to directly update systems
> tables - no need to remind me. That being said, here is the scenario. I
> have a bunch of users who are switching from one domain to another.
> EXAMPLE
> login -
> myco.us.east\joe should NOW be myco.us.west\joe
> So, is there any easy way to just change the LOGIN name. I know that
> SYSLOGINS is a view and that SYSXLOGINS is the root table. Problem is
> that
> NAME in SYSXLOGINS doesnt seem to reflect the login name, rather the user
> name. It would be great is I could just change the login name via SQL -
> Any
> ideas on this' Exactly what columns and where?|||hi,
i feel it will not do what you want. Could you do it from EM ?
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"MSSQLServerDeveloper" wrote:

> FIRST - I already know I it is not recommended to directly update systems
> tables - no need to remind me. That being said, here is the scenario. I
> have a bunch of users who are switching from one domain to another. EXAMP
LE
> login -
> myco.us.east\joe should NOW be myco.us.west\joe
> So, is there any easy way to just change the LOGIN name. I know that
> SYSLOGINS is a view and that SYSXLOGINS is the root table. Problem is tha
t
> NAME in SYSXLOGINS doesnt seem to reflect the login name, rather the user
> name. It would be great is I could just change the login name via SQL - A
ny
> ideas on this' Exactly what columns and where?|||Hi,
FYI - The undocumented sysxlogins table doesn't exist in SQL 2005. The
syslogins view is preserved for backwards compatibility, but it's
deprecated.
Unfortunately, I think you will need to drop and recreate the logins.
I think it should be reasonably straightforward to generate the code to
do that. Since you have NT authenticated users, you won't have to
worry about passwords, which simplifies the task somewhat.
Good luck.
Regards,
Clifford Dibble [MSFT]
Andy Davis wrote:[vbcol=seagreen]
> hi,
> i feel it will not do what you want. Could you do it from EM ?
>
> Regards
> --
> Andy Davis
> Activecrypt Team
> ---
> SQL Server Encryption Software
> http://www.activecrypt.com
>
> "MSSQLServerDeveloper" wrote:
>