db datatypes: update

Showing posts with label update. Show all posts

Monday, March 26, 2012

Problem with INSTEAD OF UPDATE trigger

Maybe this is a silly question, but I am not used to SQL Server.

I have a view where I put an INSTEAD OF UPDATE trigger. In this trigger, I have to deal with the two trigger tables: inserted and deleted. My problem is that I don't know what to do when an UPDATE command changes more than one row in my view. In this case, both inserted and deleted tables have more than one row. How can I know which inserted record correspond to a given deleted record?join them on the primary key.|||Originally posted by nigelrivett
join them on the primary key.

But what should I do when the primary key is changing? In this case the PKs in 'inserted' and 'updated' will not match.|||...yet another reason to use artificial primary keys...|||I am used to Oracle triggers. In Oracle, you can create a FOR EACH ROW trigger, which enables you to match the :new and :old records. Is is very easy, and doesn't require an artificial PK.

I don't want to add a new column to my view just to deal with this limitation of SQL Server. Is there another alternative? Maybe there is some function that helps us to match the records, but I still could not find one...|||Originally posted by blindman
...yet another reason to use artificial primary keys...

Not another "surrogate" for the use of IDENTITY...

Oh the humanity...

Why do you need know? What's the code doing...why can;t this be done the procedure that makes the update? (because you allow dynamic sql, right)

If you post the trigger, maybe we can come up with some ideas...|||That may be easier, but it is essentially a cursor and could not be as efficient as a set-based operation against a true primary key.

You could try to match on another set of columns that constitues a natural key.

...but you should avoid updates to views anyway. It is better programming practice (for many reasons) to handle input and output from tables through stored procedures. Most of the views I've seen created were made by developers who had stepped up from MS Access and its saved querys. Most experienced dbas I've met strongly perfer to use stored procedures.

blindman|||>> But what should I do when the primary key is changing? In this case the PKs in 'inserted' and 'updated' will not match.

A PK is a record identifier. If you update it (possibly an indication of poor choice of PK) then this is a logical delete/insert so failing to match rows is correct.
If you carry out the physical delete and insert for this then you won't have this problem otherwise anything unmatched as a deleted record and and inserted record in the trigger.|||My problem is that I am dealing with legacy code, that uses a table that does not exist anymore. The old table was replaced by my view. I can't (and don't want to) change the legacy code. It uses (yes) a lot of dynamic SQL, that makes changes also in the PK. I wanted to make the view work transparently, just like the old table. The view has a lot of joins, so I cannot just create an updateable view. When the user changes a record in the view, he is really changing records in two or three distinct tables.

My application uses both Oracle and SQL Server databases. I could solve the problem easily in Oracle, but don't know how to do it in SQL Server.|||Unfortunately sql server has less features that contravene rdbms rules so is more suseptable to problems due to bad design.

Don't think there is much you can do about this other than correct the implementation.

You could traverse the inserted and deleted tables row by row (or insert the recs into temp tables with identities) and hope that the record order obtained corresponds to the matching records but there is no guarantee that this will always work even if it happens to on some instances.|||I understand your use of the temporary table to avoid having to rewrite a ton of code. That makes sense in many instances.

You will still need to add a unique column to your table, which other procedures do not even need to be aware of, and which you can use for matching up records. This is your best option.

blindman|||Yes. I created a new column in my view, a copy of the PK that is not changed by the legacy code. This will be my new unique ID in the trigger.

Quick and dirty, but it solves the problem. Thanks a lot for the suggestion.sql

Friday, March 23, 2012

problem with INSERT on ExecuteNonQuery

I'm trying to INSERT a new record into a SQL database using the command object. It works if I use an UPDATE, but every time I try an INSERT, I get "ExecuteNonQuery: Connection property has not been initialized". The query has been checked in enterprise manager direct and works fine. Has anybody got any ideas, please?


 Dim cmdSql As New SqlCommand("INSERT INTO table(blah) VALUES ('blah'), ConUsers")
 ConUsers.Open()
 cmdSql.ExecuteNonQuery()
 ConUsers.Close()

What are you using for your connection/connection string? is it exactly the same as the sub that works for the update statement?|||> is it exactly the same as the sub that works for the update statement?

yes, it is. The only thing that changes is the query itself and I've checked that on the database direct. I've even checked that servername/ASPNET has INSERT permission on that table. So I'm a bit stumped.|||Is that code copied and pasted? If so, you have a misplaced double quote. Should be like this:

 Dim cmdSql As New SqlCommand("INSERT INTO table(blah) VALUES ('blah')", ConUsers)

Terri

Wednesday, March 21, 2012

Problem with ID update after deleting rows

Hi,

I hope some of you can help me with that

I made an application that insert some data to MS SQL 2005 DB Express Edition. One of columns in my database store text. The content of that field must, beyond the existing text, append the current id to its text string. Practically, it means that if on row nr 15 I store text value "15text", on the next row i will store "16text". I figured out that I can get value of max ID by creating following stored procedure.

USE [tracking_db]

/****** Object: StoredProcedure [dbo].[spMaxId] Script Date: 07/18/2007 09:31:44 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[spMaxId]

@.maxId integer output

SELECT @.maxId= MAX(id) FROM FILES_TABLE

Once i get the maxID value i simply concat max id and string like:

string.Concat(max_id.ToString(), file_name);

where "max_id is integer return value from stored procedure and "file_name" is a string to rename like "max_id+file_name"

Two problems occur!!!

Problem nr 1.

Since I use to insert 10 new rows each time, the values from 0-9 are appended to text like "(0-9)text"

Problem nr 2.

If I delete some rows, ID does not get update. It means that after deleting all rows from table, next inserted item gets last existed ID before delting +1. New inserted item should get value 1 since table is empty after deleting all rows from it!!!

Hope some of you has any idea what to do

Muris

i think this table ID coulumn is Identity column. In that case identity value has to be reset using DBCC Checkident command to reset the value. Otherwise remove the Identity column and change the insert logic.

select COLUMNPROPERTY (object_id('yourtablename'),'yourcolumnname' , 'IsIdentity')

if the above statement returns 1 it means its a identity column. So you should use DBCC Checkident command to reset the identity.

Madhu

|||

Hi Madhu,

You are right. My ID column is an identity column and it is also autoincremental. I wil now try to solve problem using your idea.

Muris

|||

Hi Madhu,

I find your answer logical and helpful, but I have difficulties to implement it.

Where should I put this DBCC Checkident to reset the identity. I use a stored procedure to find the max value of my identity column.

Simply, where to add the statement your suggested?

Muris

|||

Execute Mahdu's suggestion one time only -in a empty query window.

DBCC CHECKIDENT ( 'YourTable', RESEED )

|||

Suppose u have 1000 rows in the table and you are deleting rows where ID>900 . ie 900 to 1000 - 100 rows. After deletion when you insert what i understand is you should have the ID as 900 but you will get it as 1001. If this is the case. After deletion you need to reset the indentity. in this particular scenario

Delete from yourtablename where id>900 -- It will delete 100 rows

DBCC CHECKIDENT ( 'YourTable', 900) -- REset the value to 900 , if you have not run this

statement your next id will be 1001. You can

do this in sp also. read about this BOL

Insert the new value

Madhu

|||

HI,

I was away from my computer for several days so my reply is a bit late. Anyway I solved the problem with ID update using your suggestions with small modification.

Typing DBCC CHECKIDENT('YourTable', new value of ID) does not help unless you do not specify command RESEED so following statement is fully functional:

DBCC CHECKIDENT('YourTable', RESEED,new value of ID) .

In terms of deleting all rows from your table you should write

DBCC CHECKIDENT('YourTable',RESEED,1) .

The number 1 will be the ID of the newly inserted row in your previously cleaned table.

Thank you all for your help!

Regards

Muris

Problem with ID 17981

Hello,

I′m trying to do an update through a system but this message display a problem that I didn′t get to identify exactly the problem to looking for a solution.

"Table error: Object ID 17981, index ID 1, page (0:871674153). Test (offset + len < PAGESIZE) failed. Slot 10, offset 0x1a8 is i
Error while undoing logged operation in database "

Could anyone help me please?

Thanks,

Maurício

Try checking the table with "dbcc checktable" and the db with "dbcc checkdb".

AMB

Friday, March 9, 2012

Problem with DTS

I am using a DTS package to import data from primary server to secondary
server to update the changes occured in few tables and both located in a
different location. The problem is that whenever this DTS fails to execute i
will loose even the existing datas in secondary server since it delete the
existing data before inserting, how to overcome this? OR Is there any better
ways to implement this?
Thanks in advanceHi
Well, I'd transfer the OLD data before deleting into a temporary table and
in case of failure ( in order to not loose the data) nove the data back.
What's error do you get when you run the DTS and it failed?
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
>I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
> i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any
> better
> ways to implement this?
> Thanks in advance
>|||It just showing "Job Failed". It happens whenever if there any problem with
internet or network.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> Hi
> Well, I'd transfer the OLD data before deleting into a temporary table
and
> in case of failure ( in order to not loose the data) nove the data back.
> What's error do you get when you run the DTS and it failed?
>
>
> "imtiaz" <Imtiaz@.microsoft.com> wrote in message
> news:%23%23mR2iKfGHA.2188@.TK2MSFTNGP05.phx.gbl...
execute
the
>|||Ok, so you can specify an OUTPUT file under Advanced Tab in the Step's
definition.It will give the error desciption
You will have to introduce some logic behind like if the job's step is
failed go to the next step and do soemthing
"imtiaz" <Imtiaz@.microsoft.com> wrote in message
news:uFH027KfGHA.2456@.TK2MSFTNGP04.phx.gbl...
> It just showing "Job Failed". It happens whenever if there any problem
> with
> internet or network.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eQqtExKfGHA.3588@.TK2MSFTNGP02.phx.gbl...
> and
> execute
> the
>|||Can you put Delete and Insert into a single transaction? If one step fails,
just roll back the whole transaction.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>|||Try www.sqlscripter.com to transfer your data.
"imtiaz" wrote:

> I am using a DTS package to import data from primary server to secondary
> server to update the changes occured in few tables and both located in a
> different location. The problem is that whenever this DTS fails to execute
i
> will loose even the existing datas in secondary server since it delete the
> existing data before inserting, how to overcome this? OR Is there any bett
er
> ways to implement this?
> Thanks in advance
>
>

Wednesday, March 7, 2012

Problem with Deletecomand attribute of asp:sqldatasource

Greetings everyone. I'm trying to set up a GridView that makes use of an SQL Datasource. All commands (update,select,delete) are set within the tag's attributes but when i hit the Delete Trigger it returns that the id variable is undeclared.

<asp:SqlDataSource
ID="KevinDataSource"
ConnectionString="Server=overmind;Database=kevin_bonis;User=sa;Password="
SelectCommand="SELECT * FROM Accesorii"
UpdateCommand="Update Accesorii SET nume_accesoriu=@.nume_accesoriu,
articol=@.articol,
cantitate=@.cantitate,
trimis_bolla=@.trimis_bolla,
bolla=@.bolla,
data_trimitere=@.data_trimitere,
intors=@.intors,
data_intoarcere=@.data_intoarcere WHERE id=@.id"
DeleteCommand="DELETE FROM Accesorii WHERE id = @.id"
runat="server" /
What could be the problem with this? I'll appreciate any help i can get.
Thanks alot, Allyrion

Hi Allyrion,

did you set the datakeynames property for Gridview?

Lan

Saturday, February 25, 2012

Problem with Delete function within a Gridview

I've got an issue that when I update a record in the gridview it works fine. When I click the delete link to remove the record from the database, I get the following error, "System.FormatException: Input string was not in a correct format". Part of the Stack Trace refers to "String oldValuesParameterFormatString". This parameter is in my SqlDataSource. It was dynamically created when I originally created the SqlDataSource with VWD 2005 Express Edition. The delete function will work if I remove "OldValuesParameterFormatString="original_{0}"ProviderName="System.Data.SqlClient",and any reference to "original_" in the DeleteCommand the SqlDataSource. But if I do, the update function doesn't work. Anyway, here's the SqlDataSource: Any help would be greatly appreciated!!!!!

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConflictDetection="CompareAllValues"

ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"

DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.original_intID AND [street] = @.original_street AND [city] = @.original_city AND [state] = @.original_state AND [zip] = @.original_zip AND [status] = @.original_status AND [pDate] = @.original_pDate AND [sPrice] = @.original_sPrice AND [asPrice] = @.original_asPrice AND [actSalePrice] = @.original_actSalePrice AND [cToDate] = @.original_cToDate AND [rehabBudget] = @.original_rehabBudget AND [tDay] = @.original_tDay AND [eDate] = @.original_eDate AND [loDate] = @.original_loDate AND [rsDate] = @.original_rsDate AND [flooringDate] = @.original_flooringDate AND [estCompDate] = @.original_estCompDate AND [actCompDate] = @.original_actCompDate AND [coe] = @.original_coe AND [lDate] = @.original_lDate AND [credits] = @.original_credits AND [agent] = @.original_agent AND [insComplete] = @.original_insComplete AND [cEscrowDate] = @.original_cEscrowDate AND [bidValue] = @.original_bidValue AND [thomGuideNumber] = @.original_thomGuideNumber AND [locksmith] = @.original_locksmith AND [notes] = @.original_notes AND [hoa] = @.original_hoa"

InsertCommand="INSERT INTO [houses] ([street], [city], [state], [zip], [status], [pDate], [sPrice], [asPrice], [actSalePrice], [cToDate], [rehabBudget], [tDay], [eDate], [loDate], [rsDate], [flooringDate], [estCompDate], [actCompDate], [coe], [lDate], [credits], [agent], [insComplete], [cEscrowDate], [bidValue], [thomGuideNumber], [locksmith], [notes], [hoa]) VALUES (@.street, @.city, @.state, @.zip, @.status, @.pDate, @.sPrice, @.asPrice, @.actSalePrice, @.cToDate, @.rehabBudget, @.tDay, @.eDate, @.loDate, @.rsDate, @.flooringDate, @.estCompDate, @.actCompDate, @.coe, @.lDate, @.credits, @.agent, @.insComplete, @.cEscrowDate, @.bidValue, @.thomGuideNumber, @.locksmith, @.notes, @.hoa)"

OldValuesParameterFormatString="original_{0}"ProviderName="System.Data.SqlClient"

SelectCommand="SELECT * FROM [houses] WHERE ([intID] = @.intID)"

UpdateCommand="UPDATE [houses] SET [street] = @.street, [city] = @.city, [state] = @.state, [zip] = @.zip, [status] = @.status, [pDate] = @.pDate, [sPrice] = @.sPrice, [asPrice] = @.asPrice, [actSalePrice] = @.actSalePrice, [cToDate] = @.cToDate, [rehabBudget] = @.rehabBudget, [tDay] = @.tDay, [eDate] = @.eDate, [loDate] = @.loDate, [rsDate] = @.rsDate, [flooringDate] = @.flooringDate, [estCompDate] = @.estCompDate, [actCompDate] = @.actCompDate, [coe] = @.coe, [lDate] = @.lDate, [credits] = @.credits, [agent] = @.agent, [insComplete] = @.insComplete, [cEscrowDate] = @.cEscrowDate, [bidValue] = @.bidValue, [thomGuideNumber] = @.thomGuideNumber, [locksmith] = @.locksmith, [notes] = @.notes, [hoa] = @.hoa WHERE [intID] = @.original_intID">

If intID is your primary key fo the table, you can do it in a simple way. Please set DataKeyNames="intID" in your gridview and try:

DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.intID"

<DeleteParameters>
<asp:ParameterName="intID"Type="Int32"/>

</DeleteParameters>

|||

limno:
If intID is your primary key fo the table, you can do it in a simple way. Please set DataKeyNames="intID" in your gridview and try:
DeleteCommand="DELETE FROM [houses] WHERE [intID] = @.intID"
<DeleteParameters>
<asp:ParameterName="intID"Type="Int32"/>

</DeleteParameters> 

Hey limno, thanks for the help. Unfortunately, it doesn't work. The record is not deleted. When I click the delete link, the page simply refreshes with the same data fields.

|||

Something strange happened to the reply above. Here it is again...

Hey limno, thanks for the help. Unfortunately, it doesn't work. The record is not deleted. When I click the delete link, the page simply refreshes with the same data fields.

|||

Hi tobias,

Please check in your Page_Load event handler to see if you have reloaded the DataSource by calling DataBind() method again. You need to check if it is a postback by using IsPostBack property.

If DataBind() is called, the operation will have no effect.

Monday, March 26, 2012

Problem with INSTEAD OF UPDATE trigger

Friday, March 23, 2012

problem with INSERT on ExecuteNonQuery

Wednesday, March 21, 2012

Problem with ID update after deleting rows

Problem with ID 17981

Friday, March 9, 2012

Problem with DTS

Wednesday, March 7, 2012

Problem with Deletecomand attribute of asp:sqldatasource

Saturday, February 25, 2012

Problem with Delete function within a Gridview

db datatypes

Blog Archive

About Me