Rows Affected From Insert,update,delete Using SQLDMO
Feb 8, 2000
I am using SQLDMO to implement a tool to do basic management/script running for a MSDE database. The problem i am having is getting the number of rows affected from insert,update,delete statements. Help and MSDN seem to imply that this info is returned in a QueryResults object, but it is always empty unless the query is a select statement. I tried using ExecuteImmediate, ExecuteWithResults, and ExecuteWithResultsAndMessages methods and the info is not in any resultset, message, or property.
mikem
View 2 Replies
ADVERTISEMENT
Jun 1, 2004
Hi,
I've added multiple records with same info during practice. Now I"m trying to delete those records from SQL Server DB. but it says
"Key column information is insufficient or incorrect. To many rows were affected by update."
What to do, to delete these records?
Appreciated..
View 2 Replies
View Related
May 28, 2004
Hello all,
Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?
Any help would be appreciated!
Brian
View 1 Replies
View Related
Oct 20, 2007
I 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.
View 1 Replies
View Related
Dec 27, 2007
Hello,
Is it possible to insert rows into a view, update rows in a view and delete rows in a view?
If so, how does T-SQL handle it when a VIEW is the result of following query?
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2 ON table_name1.col = table_name2.col
WHERE condition
Thanks in advance,
Erke.
View 4 Replies
View Related
Mar 15, 2004
I have a mixed mode account setup with exec permissions granted on my stored procedures. I am running an sp that is editing a member, and if i run it using my query analyzer with the same login, my sp writes to the db. however, if i'm calling it through my web app (asp.net) it doesn't. here is my sp code:
USE DATABASE
--DROP PROC sp_EditMember
GO
--Create the stored procedure
CREATE PROCEDURE sp_EditMember
@member_id smallint,
@last_name nvarchar(50), --not nullable
@first_name nvarchar(50),
@spouse_name nvarchar(50),
@street_address nvarchar(50),
@city nvarchar(35),
@state nvarchar(5),
@zip_code nvarchar(15),
@zip_4 nvarchar(4),
@area_code nvarchar(10),
@phone_number nvarchar(20),
@email nvarchar(50),
@child_1 nvarchar(30),
@child_2 nvarchar(30),
@child_3 nvarchar(30),
@child_4 nvarchar(30),
@child_5 nvarchar(30),
@member_status nvarchar(20),
@member_exp nvarchar(10),
@plaques_st nvarchar(10)
AS
BEGIN TRAN
UPDATE Members
SET last_name = @last_name, first_name = @first_name,
spouse_name = @spouse_name, street_address = @street_address,
city = @city, state = @state, zip_code = @zip_code, zip_4 = @zip_4,
area_code = @area_code, phone_number = @phone_number, email = @email,
child_1 = @child_1, child_2 = @child_2, child_3 = @child_3,
child_4 = @child_4, child_5 = @child_5,
member_status = @member_status, member_exp = @member_exp,
plaques_st = @plaques_st
WHERE member_id = @member_id
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE COMMIT TRAN
GO
--------------------------------------
on my app side- i've already ensured the datatypes match up and the sizes are all ok (except for member_id, the variable is an int and i pass it as a small_int- the number is ranging from 1000-10000).
any troubleshooting on this?
thanks in advance,
sudeep.
View 1 Replies
View Related
May 15, 2015
I have a file which has some wind data that i am trying to import into a sql data base through bulk insert. if the script works as it supposed i should see 144 rows impacted but i see 0 rows affected.
BULK INSERT TOWER.RAWINTERFACE_1058 FROM 'C:Temp900020150427583.txt'
WITH(CHECK_CONSTRAINTS,CODEPAGE='RAW',DATAFILETYPE='char',FIELDTERMINATOR=' ',ROWTERMINATOR='
',FIRSTROW=172)
The code works if the file is large but if its small 0 rows are affected. and also if i remove the header rows then the file works again. want to understand what is going on here. i am including the screen shot of the file in notepad++. I have tried changing the row terminator to ' ' , ' ' and also tried to change the codepage but nothing seems to work. No error file is being generated either, if i give a error file option.
View 7 Replies
View Related
Nov 7, 2007
Hi,
Using VS.NET 2008 Beta2, and SQL Server 2005.
I have a gridview bound to a linq data source, and when trying to update a row, I get an exception that no rows were modified.
The query generated is:
UPDATE [dbo].[package]
SET [owner_id] = @p5
WHERE ([package_id] = @p0) AND ([title] = @p1) AND ([directory] = @p2) AND ([owner_id] = @p3) AND ([creation_date] = @p4)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [20006]
-- @p1: Input String (Size = 22; Prec = 0; Scale = 0) [Visual Studio.NET 2005]
-- @p2: Input String (Size = 26; Prec = 0; Scale = 0) [MSI_Visual_Studio.NET_2005]
-- @p3: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10000]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/07/2007 12:00:00 a.m.]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10001]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
If I run it manually on sql server, it fails until the directory column is removed. The type is varchar(50), with a uniqueness constraint. However, this is same type as the title column, which doesn't have this problem.
Thanks,
Jessica
View 5 Replies
View Related
Apr 11, 2004
Hi,
i read from help files that "For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. " Anyone know how to get the return value from the query below?
Below is the normal way i did in vb.net, but how to check for the return value. Please help.
========
Public Sub CreateMySqlCommand(myExecuteQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myExecuteQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub 'CreateMySqlCommand
========
Thank you.
View 12 Replies
View Related
Oct 2, 2007
what is wrong with this statement?
Update services
Delete
where ticketnum = '0'
error message: incorrect syntax near keyword delete
View 6 Replies
View Related
Jun 4, 2007
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
View 1 Replies
View Related
Dec 5, 2006
I use business logic layer, TableAdapter(middle tier) and StoredProcedure(backend). In my stored procedure,I set as follows:set nocount offWhat and where should I do in order to get the rows affected count in the business logic layer(C#/VB code)?Thank you.
View 2 Replies
View Related
Dec 20, 2006
I'm doing an executenonquery() and the value I'm getting back is -1 This is confusing me, as sometimes the row has updated when I look at it in sql manager and then I look at it again and it seems to have reverted.Would it be possible to point me in the right direction for the cause and how to rectify / debug this issue. I'm using a stored procedure and I'm slightly uncertain how to check exactly what sql server has been told to insert ;) Thanks!
View 1 Replies
View Related
May 6, 2008
After executing a restore command. I got the following result.
Anyone know why is displaying rows affected. I saw the executiong plan and it shows several execution plans? Its the first time i see it. Can anyone explain what is going on?
restore database test_GG_ATRECORDING_QAT2
from disk = 'f:a.bak'
with move 'a' to 'f:MSSQL$INAQATdataa2.mdf',
move 'a_log' to 'f:MSSQL$INAQATdataa2_log.LDF'
Processed 11016 pages for database 'test_GG_ATRECORDING_QAT2', file 'a' on file 1.
Processed 1 pages for database 'test_GG_ATRECORDING_QAT2', file 'a_log' on file 1.
(1 row(s) affected)
(7 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(11 row(s) affected)
(7 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
(5 row(s) affected)
RESTORE DATABASE successfully processed 11017 pages in 2.330 seconds (38.732 MB/sec).
View 3 Replies
View Related
Dec 13, 2007
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
View 3 Replies
View Related
Jan 8, 1999
I 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
View 2 Replies
View Related
Jan 22, 2008
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
View 6 Replies
View Related
Oct 16, 2007
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
View 10 Replies
View Related
Jul 24, 2007
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 Related
Nov 13, 2005
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
View 3 Replies
View Related
Jan 3, 2006
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?
View 4 Replies
View Related
Apr 7, 2006
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!!
View 3 Replies
View Related
Aug 15, 2001
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
View 2 Replies
View Related
Dec 6, 2007
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!
View 2 Replies
View Related
Jul 10, 2006
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).
View 1 Replies
View Related
Feb 18, 2014
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
View 5 Replies
View Related
Dec 2, 2014
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].....
View 1 Replies
View Related
Mar 20, 2006
I am still learning SSIS.
What is the best way to do delete/insert/update in a database in SSIS. I am looking to achieve something similar to what was there in a Data Driven Query in SQL Server 2000. I would like to delete/insert/update the destination table based on the condition that my lookuptable returns
For ex
If "the user in my source table has changed the status" Then
Update the user row in the destination table
Else skip the row
If "the user in my source table has delete flag =1 " Then
Delete the user in the destination table
Else skip the row
If "the user in my source table is new(has a new ID) " Then
Insert the user in the destination table
Else skip the row
Any suggestions ....
Cheers
Siaj
View 6 Replies
View Related
Sep 2, 2006
hi,
I have a table which contained 5 columns and with 2 primary keys
Col 1 | Col 2 | Col 3 | Col 4 | Col 5 |
ab | 1 | abc | null | null
ab | 2 | def | null | null
Col 1 and Col 2 both are primary keys.
How do I update Col2 from 1 to 2 and from 2 to 1 in a single transaction statement and commit it?
Thanks
View 3 Replies
View Related
Mar 13, 2006
I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...
SQL Server 2005 likes to put the SET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application.
So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.
View 2 Replies
View Related
Dec 29, 2004
Hello,
I am using a cursor to navigate on data...of a table....
inside the while @@fetch_status = 0 command
I want to delete some rows from the table(temporary table)
in order to not be processed...
The problem is that I want this deletion to affect the rows the cursor has.
I declared a dynamic cursor but it does not work.
Does anyone know how I can do this??
Thanks :)
View 7 Replies
View Related
May 20, 2007
Hi,
I used with Execute SQL Task for update a table in Oracle DB.
I saw that when the command has no rows for updeting, the task fails.
Here is my command:
update tableName set fieldA=sysdate where fieldB is Null
and again, when there are some rows that fieldB is Null then the command succeed, but when the fieldB in all the rows is not null the command fails.
I tried to play with the ResultSet with no success.
Please your advice.
Thank you in advance
Noam
View 4 Replies
View Related
Aug 31, 2015
I have a database table where upon inserting a new record, I get 1 row(s) affected twice. But when deleting the record, I only get one instance of that message. I'm not really an expert, neither have I designed the database, but how can I retrieve the actual affected rows or determine what table does the other row pertain to?
View 2 Replies
View Related
