Hi,
i want to create a sqldatasource in VWD also with advanced (insert, delete, update) option.
This works fine for 'normal' tables, but when i try to do that with e.g. aspnet_roles table (indicated as vw_aspnet_Roles in dropdownlist of VWD), or aspnet_users table, that advanced option is not available, even when i select all fields.
Why, and how can i solve that?
Thanks
Tartuffe
Hello,
Where can i give on the sql server Management studio that i can insert and update, delete.
Thenks for you help
I want to run a query overnight through the SQL agent using osql. However I need to turn off the print header option that you normally turn off through the advanced options tab in the Query Analyzer. Does anyone know if this is possible?? Many Thanks in advance..
View 1 Replies View RelatedI have to load and unload many datatabases for testing. So I wanted to quickly remove a database, its devices and respective dat files
all at once.
Is there a way of completely dropping a database so that all trace of it is quickly gone?
Generally bulk insert is used to insert bulk data from flat files or csv files into database tables. Thats good. But while inserting the data into table, if the schema of the table changes (say a column is added or removed) then are there any options for inserting the same data, with some changes, into the database table...
My work is to take backup of the tables and insert them to a new database with some changes in schema like adding a column or removing a column.
Dear,
i got solve for my problem later.But I meet with new difficulty,my the following gives example script
------------------------------------------
CREATE TRIGGER Niru_data ON dbo.trans_r
FOR INSERT
AS
DECLARE
@Code_Month char(4),
@name_table char(10),
@noreg char(7),
@tr_date char(6),
@Rec_contain char(20)
SELECT
@Code_Month = substring(tr_date,1,4) from inserted
SELECT
@name_table = "trans_"+@code_Month
SELECT
@noreg = a.noreg,
@tr_date = a.tr_date,
@rec_contain = a.rec_contain
FROM
trans_r a INNER JOIN inserted b
ON
a.noreg=b.noreg
insert into @name_table values(@noreg,@tr_date,@isi)
GO
--------------------------------------------------
That's trigger not succesfull cause i try to insert table with variable declaration (@name_table).
some body can help me ????
Thank you before that
I?m getting an error when I execute a stored procedure which is try to insert a row to a table.The error is:Server: Msg 1934, Level 16, State 1, Procedure SRV_SP_IS_EMRI_SATIRI_EKLE, Line 32INSERT failed because the following SET options have incorrect settings: 'ANSI_NULLS.'.In my sp, I insert an row to a table. But also I created a view which is select some fields from this table.(Note: Some fields are calculated fields in this view. To see if calculated fields cause this error , I didnt selec calculated fields in the view, but I?m still getting this insert error.One more note: My indexed field is an identity field also.) I think, index causes this error. But I cant find my error.Thank you for your help...To do this view, I execute this code:drop view SRV_V_GARANTILI_IS_EMRI_SATIRLARI_TgoCREATE VIEW dbo.ViewTwith SCHEMABINDING ASSELECTS.FieldA,....S. FieldHFROMdbo.TABLE SWHERE(S.FieldA = 1) AND (S. FieldB IS NULL) AND (S.FieldH IN (0,1))goSET NUMERIC_ROUNDABORT OFFGOSET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ONGOcreate unique clustered index ViewT_IX_FieldA on ViewT (FieldA)go****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...d4cddba67ade8b6*****************************************
View 7 Replies View RelatedHi everyone In my SqlServer Management Studio Express, on start up it shows the server type option, but greyed.So that value is fixed to database engine. ( I'm trying to work on an SqlServer Compact Edition database through the SSMStudiothat's why I'm trying to get this to change.)Besides, after I connect i go to the Object Explorer, expand the server node, and go to Replication.When i expand replication, i get the "Local Subscription" option, but nothng for Publication.( I want to work on Merge Replication, that's why I desparately need Publication to work)Am i missing something here? I did not install SqlServer separately, I only have what comes bundled with the Visual Studio 2005 Setup.
View 2 Replies View RelatedUsing OrderRebate Table.
Have two records getting inserted. All fields are identical except for cd_tp.
If 2 records are inserted I would like the record with cd_tp 3 deleted.
Code below doesn't seem to be working
delete orderrebate
FROM OrderRebate inner join inserted on inserted.ord_type = OrderRebate.ord_type and
inserted.ord_no = OrderRebate.ord_no and inserted.line_seq_no = OrderRebate.line_seq_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no
and inserted.line_seq_no = OrderRebate.line_seq_no and inserted.cd_tp = '3'
END
Here's my sp. Before I insert my new values I want to delete the existing records from the table. Can I do this in the same sp ? If so, could someone tell me the syntax please.
CREATE PROCEDURE [spRB_AddBlockBookingDateRef]
** here Delete * from tblRB_BlockBookingDates
@strBookingDateRef nvarchar(100),
@strRoomRef nvarchar (50),
@strDateRequired datetime
AS
INSERT INTO tblRB_BlockBookingDates(
BB_BookingDateRef,
BB_RoomRef,
BB_DateRequired)
VALUES
(@strBookingDateRef,
@strRoomRef,
@strdateRequired
)
GO
Hi All,
I have this project I'm working on it's Product Content Management System rewrite. I got to the point of updating the Product By Sku and not sure if I should use UPDATE statement or I should DELETE sections assosiated with the ProductContentID and then re-insert them again? I'm not sure which is more afficient?
I can really do both and it's really not that complicated, the only problem I see with DELETE then INSERT is the ProductContentSectionId in the Sections table is going to grow very fast and I'm a bit concerned about it.
We use SQL Server 2000 and we have about 4 bound tables where we keep the data. The one I'm talking about is the sections table where we keep the actual types of product content like a BoxShot, Description, Key Features and so on...
Thank you in advance!
Tatyana Hughes
hi all
iwant to make feedback after insert data or delet date Like( the data is sucssfuly way) or(data is delete sucssuse way)
but not alert iwant feedback
thank you
hello all i have a problem in deleting and updating records when i configuring my data source i click on advanced button in the configuration datasource and then i check the two boxes to generate insert and update and optimistic concurrency but the problem is these two choices are not active unless there was a primary key in the table i have some tables doesnt have a primary key how i can fix that what should i do to allow insert and delete in these tables.thanks in advance
View 4 Replies View RelatedI am trying to update a SQL database with data from a Wang system. The Wang data is dumped to a txt file. I then import it into an update table in SQL via Access. Some of the data is new and some of the data is updated records. At this point I have been trying to create a script to update and add data to a table via the query tool in SQL Then delete data from the update table.
I was able to get the UPDATE and DELETE to work but I have not figured out how to insert new records at the same time? Can I use an IF statement? I will apreciate any help or sample code, Thanks.
UPDATE MemberList
Set Name = NameUpd, Address1 = Address1Upd, Address2 = Address2Upd, City = CityUpd, State = StateUpd, ZipCode = ZipCodeUpd, MemberStatus = MemberstatusUpd
FROM MemberList, MemberListUpd
WHERE MemberList.MemberNumber = MemberListUpd.MemberNumberUpd
INSERT ?
DELETE MemberListUpd
i am creating an insert based on a select statement -- i need to delete the row from the select statment table after it has been inserted
something like
insert into table_insert(value1, value2)
(select table_exclude_id, value1, value2 from table exclude)
delete from table_exclude where table_exclude_id in "the select statement"
can you do this?
Im trying to keep a mirror image of some data Im getting from Quickbooks.
As the records are inserted into the database I need to check if a record exists and either update or insert a new one.
it seems easier just to delete using the tnxid and reinsert vs updating
my question is if I go
begin
INSERT INTO QBInvoicesQue(100s of feilds)
end
begin
delete from QBInvoices where txnid = @TxnID
end
and there is not matching txnid to delete from will it cause any problems? before going to the insert statement?
begin
INSERT INTO QBInvoices(100s of feilds)
end
I like to empty my table before I insert a xml. How can this be done in SSIS?
I vaguely recall reading an article that I can no longer find that an update statement is executed as a combination of a Delete and an Insert by SQL server. Does anyone know if this a still a true statement in SQL Server 2005?
Thanks,
-shl
I want to do insert and delete in a cursour but it's not working for me can any one check the below code and help me out to solve the problem,
DECLARE @LINKID AS INT
DECLARE @INITCOUNT AS INT
DECLARE @NumberOfDuplicates AS INT
SELECT @InitCount = (SELECT COUNT(*) FROM [SSDTemp])
Declare DedupeDataInSSDTempTableNewCur Cursor For
SELECT LINKID FROM SSD WHERE LINKID IS NOT NULL
OPEN DedupeDataInSSDTempTableNewCur
FETCH NEXT FROM DedupeDataInSSDTempTableNewCur
into @LINKID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @CNT AS INT
SELECT @CNT = COUNT(LINKID) FROM SSD WHERE LINKID=@LINKID
if (@cnt =1) begin
INSERT INTO SSD (
[CallID],
[LinkID], [FirstName], [MiddleInit], [LastName],
[Suffix], [DateOfBirth], [SSN], [PhoneNumber],
[Addr1], [Addr2], [City], [State], [Zip] )
SELECT top 1
[CallID], [LinkID], [FirstName], [MiddleInit],
[LastName], [Suffix], [DateOfBirth], [SSN],
[PhoneNumber], [Addr1], [Addr2], [City], [State], [Zip] from SSDTemp
where linkid = @LINKID
DELETE top 1 from ssdtemp where linkid=@LINKID
end
if (@cnt =0) begin
INSERT INTO SSD (
[CallID],
[LinkID], [FirstName], [MiddleInit], [LastName],
[Suffix], [DateOfBirth], [SSN], [PhoneNumber],
[Addr1], [Addr2], [City], [State], [Zip] )
SELECT top 2
[CallID], [LinkID], [FirstName], [MiddleInit],
[LastName], [Suffix], [DateOfBirth], [SSN],
[PhoneNumber], [Addr1], [Addr2], [City], [State], [Zip] from SSDTemp
where linkid = @LINKID
DELETE top 2 from ssdtemp where linkid=@LINKID of DedupeDataInSSDTempTableNewCur
End
SELECT @NumberOfDuplicates = @InitCount - (SELECT COUNT(*) FROM [SSDTemp])
FETCH NEXT FROM DedupeDataInSSDTempTableNewCur
INTO @LINKID
END
CLOSE DedupeDataInSSDTempTableNewCur
DEALLOCATE DedupeDataInSSDTempTableNewCur
for now, doing a small school project, i find doing SPs for Insert useful, like checking for existing data and not inserting, that might not be the best method, i had advice from here i can use unique constraints instead, then what about update and delete? SPs also? the pros make SPs for everything? currently use dynamically generated SQL from SqlDataSources. for Update / delete. some delete are SPs too...
View 2 Replies View RelatedI have recently started an ASP.Net application and am having some issues updating, inserting and deleting rows. When I started working with it, I was getting errors because it could not find any update command. Eventually, I figured out how to automatically generate the commands, by configuring my SQLDataSource control and clicking the "advanced" button. Right now though, I have generated the commands, but I still can not insert, update or delete rows. When I attempt to update anything, I recieve an error that says "The data types text and nvarchar are incompatible in the equal to operator." Nowhere in my table do I have any rows that use the datatype "nvarchar", only "text" and "int". I tried switching all of my text columns to "nvarchar(500)", which did not help. I am led to believe that the auto generated SQL procedures are trying to do something behind the scenes that is making my database act up, because even when I delete rows, I get the same exception, so the datatypes cannot be messed up there, because all that the datasource is doing is deleting rows, therefore there is no need to worry about data types. I only get the error when I check the "Use optimistic concurrency" box. When I do not use optimistic concurrency, I can delete, insert, and update rows... but nothing happens. There are no errors, but nothing is deleted, updated or inserted either. Upon postback, nothing has changed. I may upload a copy of the exact exception page, if someone thinks that it may help. Here is the update command that was generated: UPDATE [Record Information] SET [Speed] = @Speed, [Recording Company] = @Recording_Company, [Year] = @Year, [Artist] = @Artist, [Side 1 Track Title] = @Side_1_Track_Title, [Side 1 Track Duration] = @Side_1_Track_Duration, [Side 2 Track Title] = @Side_2_Track_Title, [Side 2 Track Duration] = @Side_2_Track_Duration, [Sleeve Description] = @Sleeve_Description WHERE [Record Database ID] = @original_Record_Database_ID
Apparently no stored procedures exist for any of these operations, and I am unsure why. The "Record Database ID" is my identity column, and is the only field that is (and is supposed to be) uneditable.
Hi,I just upgraded my ASP.NET 2.0 BETA 2 environment to the final release of ASP.NET 2.0 VWD.Once the update was finished, I could open my website without any problems..... Now, I noticed that in the final release, some modifications have been included in the Membership Stored Procedure and other stored procedures. So I created a new database (SQL Express) and added my data again.After re-creating my SQLDataSources, I tryed to enable the Editing and Deleting option in VWD and once I run my web application, it seems when selecting editing and then update, it doesn't work anymore....This is my code :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IMMOASPNETDBConnectionString %>"
DeleteCommand="DELETE FROM aspnet_test WHERE (testID = @Original_testID)" SelectCommand="SELECT BuyID, BuyNL, BuyFR, Lastupdated FROM aspnet_Buy"
UpdateCommand="UPDATE aspnet_Buy SET BuyNL = @BuyNL, BuyFR = @BuyFR WHERE (BuyNL = @original_BuyID)">
<DeleteParameters>
<asp:Parameter Name="Original_testID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="BuyNL" />
<asp:Parameter Name="BuyFR" />
<asp:Parameter Name="original_BuyID" />
</UpdateParameters>
</asp:SqlDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataKeyNames="BuyID" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowEditButton="True" />
<asp:BoundField DataField="BuyID" HeaderText="BuyID" InsertVisible="False" ReadOnly="True"
SortExpression="BuyID" />
<asp:BoundField DataField="BuyNL" HeaderText="BuyNL" SortExpression="BuyNL" />
<asp:BoundField DataField="BuyFR" HeaderText="BuyFR" SortExpression="BuyFR" />
<asp:BoundField DataField="Lastupdated" HeaderText="Lastupdated" SortExpression="Lastupdated" />
</Columns>
</asp:GridView>Can someone help me with this ? What is wrong with the Update command ?Thanks to all,Bart
Hello Guys,
I’m trying to create a sqlDataSource using the wizard, I can get the select statement, the here clause and the order by clause but when I click on the Advanced button the options to generate the insert, update and delete statements are not available. Does anyone know what am I doing wrong?
I've got four pages with in the first page a insert, in the second a select, in the thirth a update and in the fourth a delete statement. First the values of a textbox will be inserted in the database, then the values will be shown in labels and than it is possible to edit or delete the values inserted. Every inserted item belonging to each other has one ID. The follwing values has a second ID etc.
How can I make that possible?? I think that I should pass the ID's between the pages so I'm sure that I edit or delete the values that I want. So insert value 1 in page 1, show with select value 1 in page 2, edit or delete value 1 in page 3 and 4.
Maybe I didn't explain it good enough for you, please tell me then!!
Thanks!!
When granting INSERT, DELETE permissions, is this done for
the logon-id OR for the user associated with the logon-id?
I know you can do this for roles.
I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.
DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?
PLEASE SUGGEST ME..ITS URGENT.
THANKS IN ADVANCE
HARISH
Hello,
What is faster / better for performance?
Check if something already excist and update the changed values? or delete the whole table and then insert everything?
Thanks!
I am having problems with a trigger that is designed to audit changes to a particular field in a table. If that field is updated, then the old record is inserted into an audit table.
This trigger never fails when I run test data against it from Query Analyzer. It works some of the time when the web application updates it, fails other times.
Typically, multiple records are updated at the same time. Any ideas?
Here is the Trigger:
create trigger t_u_product_rate_detail
on product_rate_detail
for insert, update, delete
as
/--Local variable
declare
@auditdate datetime,
@audituser sysname
--Set values so function isn't executed a bunch of times
select
@auditdate = getdate(),
@audituser = suser_sname()
if exists (select * from inserted)
begin
if exists (select * from deleted)
begin
insert into product_rate_detail_audit_log
select d.product_rate_detail_id,
d.product_rate_id,
d.day_of_week_id,
d.ad_size_id,
d.rate,
d.plan_vol,
d.plan_freq,
@auditdate, @audituser, 'U'
from deleted d
join inserted i on i.product_rate_detail_id = d.product_Rate_detail_id
where (d.rate <> 0 and d.rate is not null)
and i.rate <> d.rate -- this determines if the rate has changed.
end
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SQL2K sp4
Howdy all. I opened a 200 mb. file in Query Analyzer that is full of Inserts/ Updates/ and Deletes. I tried just to parse it, and killed it after 18 hours. There is no blocking. All of the appropriate indexes exist. I even removed them and retried JIC. The box is plenty powerful for this task. Does anyone have any ideas?
I've tried several times with no luck. At the top of the file is SET IMPLICIT_TRANSACTIONS ON and then every 10,000 statements is COMMIT WORK. I've tried adjusting the number of commits to a lower number with no luck. This works fine on smaller files (3 - 20 mb).
i've read the transact-sql command,
i known that the select command use to retrieve many fields from many tables with one command
select * from table1,table2
yes,
but i ' ve not seen the way to add,delete or update those fields from those tables with one command...
Is it possible? why?
I don't have any idea , can u help me
I want to know the sql commands , if it's possible
thanks for reply,
mochi
How to Insert,Update and delete through script without using merge.I have simple requirement of Deleting,Inserting and Update from one table to other table...Here is the Sample Data
CREATE TABLE #Table1
(ID INT ,Name VARCHAR(30),DATEKEY INT)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (1,'Mohan',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (2,'Raj',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (3,'Majja',20131231)
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (4,'Majjaa',20131231)
[code]...
So now i need to update 1st record and add another new record..So i need to update as well as delete the existing data in Target table.
INSERT INTO #Table1 (ID,Name,DATEKEY)VALUES (5,'Macha',20131231)
My output should come like this one :
IDNameDATEKEY
1Mohan20131231
5Macha 20131231
with out using merge how can i handle Update,insert and Delete through TSQL
I have to create a trigger that will log who changed information on a table and when (NOT what they have changed).
My idea is to get the users name and see if it is in a table if not create it and get the associated ID, also get the ID of table that was accessed along with the ID of the type of task that was performed. Take this data and insert it into a table.
Here is the SQL I have so far.
-- Primary Database Tables --
CREATE TABLE Physician (
Physician_ID int not null identity(1,1) primary key,
First_Name varchar(100),
Last_Name varchar(100),
Mobile_Number varchar(15),
Pager_Number varchar(15)
[code].....
hi,
how can I use stored proc to delete all rows from a table (table1) and insert new ones.
for instance.
my query is:
-------------------------------------
select field1, two, three
from table1 innerjoin table2 on etc...
where condition1 and condition2
-------------------------------------
I want to place the result in my table (table1) and everything I run the stored pro I want to delete the content and place the new dataset in the table1?
I hope I'm clear what I'm trying to do.
thank you very much in advance.
P.S please be detailed when explaining.