How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View Complete Forum Thread with Replies
Related Forum Messages:
Can I Read Some Rows From The Middle Of Rows In DataReader?
helo.. I have 100,000 rows in the database and I want to read results for eg: from 5000 to 5050 by DataReader. I wrote this code to do this but its too slow: Dim SlctStr As String = "select * from topicstbl where partID like '" & PagePartID & "'" Dim ReadCom As New SqlClient.SqlCommand ReadCom.CommandText = SlctStr ReadCom.Connection = MainLib.MyConnection Dim MyReader As SqlClient.SqlDataReader = ReadCom.ExecuteReader() Dim StartTNum As Long = 5000 For IR As Long = 0 To StartTNum - 1 MyReader.Read() Next Do While MyReader.Read StartTNum += 1 If StartTNum > 5500 Then Exit Do ''''''''''''''''''' Loop MyReader.Close() is there another way to do the same thing better off than this code?
View Replies !
DataSet Rows Being Deleted, But After The Update , The Sql Database Is Not Updated. The Delete Rows Still In The Database.
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 Replies !
How To Run Delete Query / Delete Several Rows Just By One Click ?
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
View Replies !
Delete Doesn't Delete Rows, But @@ROWCOUNT Says It Did
I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005: DELETE FROM sql2005.production.dbo.products WHERE vendor='Foo' AND productId NOT IN ( SELECT productId FROM sql2000.staging.dbo.fooProductList ) The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate. I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble. Does anyone have suggestions on what might cause this sort of behavior?
View Replies !
How To Read A Set Of Rows Into Session Variables? C#
Hello ASP.NET C# and SQL gurus I want to read the results of a set of rows into session variables -- how is it possible? Let me try explain. I have a query which returns multiple rows, e.g. the following query SELECT PROFILE_ID, PROFILE_NAME FROM USER_PROFILES returns 5 rows i.e 5 sets of profile_ids and profile_names. Now, I want to capture these and store them in session variables thus. Session["PROFILEID_1"] = Session["PROFILEID_2"] = Session["PROFILEID_3"] = Session["PROFILEID_4"] = Session["PROFILEID_5"] = Session["PROFILENAME_1"] = Session["PROFILENAME_2"] = Session["PROFILENAME_3"] = Session["PROFILENAME_4"] = Session["PROFILENAME_5"] = Thanks in advance! Fouwaaz
View Replies !
Join Only Returns The Read Rows :|
Hi all, I am trying to build a association table (t2) to store a list of users have viewed an item in my records table (t1). My goal is to send the UserID parameter to the query and return to the user a read / not read marker from the query so I can handle the read ones differently in my .net code. The problem is that I cannot work out how to return anything but the read data to the client. So far my stored proc looks like this DECLARE @UserID AS Int -- FOR TESTING SET @UserID = 219 -- FOR TESTING SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID FROM t1 LEFT OUTER JOIN t2 ON t1.MemoID = t2.MemoID WHERE t2.UserID = @UserID GROUP BY t1.MemoID, t1.strTitle,t2.UserID It works fine but only returns those records from t1 that are read. I need to return the records with null values also! I may have built the assoc table wrong and would really appreciate some pointers on what I am doing wrong. (assoc table has rID, MemoID and UserID columns) Please help! Many thanks
View Replies !
How To Read The Rows In A Cursor Variable
Hi, I have a dynamic query that returns its values in a cursor variable. How do I read each row from this cursor in a loop ? Eg.: use AdventureWorks go DECLARE @sqlnvarchar(4000), @paramsnvarchar(4000), @tables_cursorcursor, @db_namenvarchar(50), @table_namenvarchar(4000), @schema_namenvarchar(50); set @db_name = 'AdventureWorks'; set @schema_name = 'Production'; set @table_name = 'BillOfMaterials, Product'; set @sql = ' select a.name table_name ' + ' from ' + @db_name + '.sys.tables a join ' + @db_name + '.sys.schemas b ' + ' on (a.schema_id = b.schema_id) ' + ' where b.name= @schema_name1 ' + ' and @table_name1 is null ' + ' order by 1; ' SELECT @params = N' @table_name1 nvarchar(3000) ,' + N' @schema_name1 nvarchar(100) ,' + N' @cursor cursor output' EXEC sp_executesql @sql, @params, @table_name,@schema_name , @tables_cursor OUTPUT
View Replies !
Read CSV File - Save Columns Into Rows
I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table --CSV file format State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 600 GA,2,'John Doe',14.00,'Roger Smith',15.00 FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00 SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00 Data in SQL table from csv file should look like this State,Name,Total GA,John Doe,14.00 GA,Roger Smith,15.00 FL,John Doe,14.00, FL,Roger Smith,15.00 FL,Sally Cox,16.00 I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.
View Replies !
How To Read Block Of Rows From Database Tables
have created a Database Application in Java and display all the records in tabular format of one Table. This table have Millions of Rows, If I run Select * from Table, then my Machine not responding, so Now I wants to add paging of 1000 rows at one time. Is there are any option/query to read block of rows at one time and then query again for next page ? i.e In MYSQL have LIMIT clause with Select Statement Please let me know.. Database : SQL Server 2000/2005, Thanks in Advance Laxmilal
View Replies !
Read CSV File - Save Columns Into Rows
I want to import CSV file and convert columns into rows depending on Customer count(2nd record in each row of CSV file) and save to SQL table --CSV file format State, Customer_Count, Name_1, Total_1,Name_2, Total_2,Name_3, Total_3..can go upto 350 GA,2,'John Doe',14.00,'Roger Smith',15.00 FL,3,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00 SC,5,'John Doe',14.00,'Roger Smith',15.00,'Sally Cox',16.00,'James Brown',17.00,'Rick Davis',18.00 Data in SQL table from csv file should look like this State,Name,Total GA,John Doe,14.00 GA,Roger Smith,15.00 FL,John Doe,14.00, FL,Roger Smith,15.00 FL,Sally Cox,16.00 I have multiple CSV files with millions of records. How can i achieve this using Integration Services or Bulk Data Import.
View Replies !
Insert > 100 Rows Per Second From Application
Does anyone have experience of geting SQL server to accept > 100 inserts per second? We use a stored procedure to insert data and this has increased throughput from 30/sec with ODBC to 100-110 /sec but we desperately need to write to the DB faster than this!! There doesnt seem to be any I/O backlog so i am assuming this "ceiling" is due to network overhead. (BCP can insert 3-4000 rows quite happily) Is there any way to batch up these inserts or process them differently in order to improve throughput? Much appreciated, Damon
View Replies !
Delete Many Rows
Hi All,I am designing a purge process for a db that has grown to almost 200GB.My purge process will remove about 1/3 of the 500 million rows spreadover seven tables. Currently there are about 35 indexes defined onthose seven tables. My question is will there be a performance gain bydropping those indexes, doing my purge, and re-creating the indexes. Iam afraid that leaving those indexes in place will create a lot ofextra overhead in my delete statements by having to maintain theindexes. I know that it could take many hours to rebuild the indexesafterward, but I am planning on doing that anyway. The reason that Iwant to know whether I should drop the indexes ahead of time, is I maynot be able to do the entire purge at once and the tables may need tobe accessed between purges. If this occurs, I will need to have thoseindexes in place.So do I drop the indexes before the purge and re-create them later ordo I leave them in place and re-index them afterward?Thanks In Advancep.h.
View Replies !
Delete Rows
Hi I am trying to delete rows in temp1 which are exist in temp2 delete from dbo.temp1 as a inner join temp2 as b on a.regionname=b.regionname and a.servicecode=b.servicecode and a.directioncode=b.directioncode and a.destorigflag=b.destorigflag I am getting error : Incorrect syntax near the keyword 'as'. thanks in advance subash
View Replies !
Delete Rows Where...
Hi there, I have two tables a,b, with the same columns. How do I delete all the data from a that has the same entry as b? Thanks for all the answers
View Replies !
SQL Query - Delete All Rows Bar The Last 10? Help Please!
Hi, I'm really not very good at SQL sadly, so would really appreciate any help. I'm basically working on a website that has a chatroom. I want it so the chatroom table only holds, say 15 lines of chat, then once a new line of chat is entered the last line in the table is deleted, so the table always has a maximum of just 15 rows. The fields are: MessageID Poster Message DateTime I've tried my best, but just don't know how to do it. I suppose it's something like: SELECT TOP 15 * FROM Chatroom ORDER BY MessageID DESC - this gives me the last 15 rows. Then I need a delete statement to delete the rest?! Sorry, I am very bad at SQL, so any help would be great. This is written in a stored procedure. Can the stored procedure pick up whether there are 15 or more rows in the table, and if so then delete all bar the newest 15 rows of chat? To summarise: I want a stored procedure that checks if 15 or more rows exist, if they do then delete all bar the newest 15 rows. Thanks, Ricky
View Replies !
Delete A Row And All Other Rows Thats Linked To It
Hii want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it. I have to delete all the rows thats linked to the row i wanna delete first.Is there a easier way to delete the row and all the rows thats linked to it? i wanna code it to do it.an suggestions?
View Replies !
DELETE Rows In MSDE
Hi.. DELETE FROM table1 WHERE projektID=5 there are 500000 rows that has projektID=5.. and when i run the query the hardrive is working for a couple of minutes and then stops. and NOTHING has happened. not a single row has been deleted?.. cant the DELETE statement handle that many rows or?. or is there another way i can delete these rows?.
View Replies !
Scripted Delete Rows
Hi,I need to delete rows from my user tables dependant upon there nonexistence from another table:delete studentwhere student_id not in (select student_id from tblStudent)The reasons is convoluted, simplest explanation is that our operationalsystem allows the change of business keys. This wreaks havoc in thedata warehouse.So, I'm look for help on how I can delete rows from tables that have acolumn STUDENT_ID. I'd like the script to search for the tables, thenperform the delete.I don't know where information about user tables are stored, nor how toloop through the results to do the delete.Any Ideas are appreciated.
View Replies !
Delete Of Mirrored Rows
hi.I've seen ways to delete duplicate rows.Can someone give me some sql to do this?I have a table with varchar table_name_start, varchar column_name,varchar table_name_end;it has rows like this:table1 col1 table2table1 col2 table 3table2 col1 table1I'd lke to delete the rows if they exist with the names swappedaround, i.e. like above since the first and third share a column nameand the table_name_start/end matches the others table_name_end/start,I'd like to delete one and leave the other.I'm scratching my head trying to figure this out.thanks
View Replies !
Delete Particular Rows In A Table
hi alli hava eetable that is ,eename sal_______ ________suresh 100000ramsesh 100000raja 100000susjssj 100000dkddkd 100000jfdjfdjfd 100000so i want to delete from second to Fourth row by using Rownumber..Rownumber is not a column of eetable give some example
View Replies !
Delete Duplicate Rows
Suppose that we have the following rows in a MSSQL table : 1administrateur1NULLNULL 2administrateur2NULLNULL 1administrateur1NULLNULL 2administrateur2NULLNULL How to remove duplicates (leave only the 2 first rows) ?
View Replies !
Delete Duplicate Rows
Hi, I have the following query to select duplicate rows from the table. How can i delete them with out using temp table. select UserName, Title, Name, ColWidth, Sequence from table1 (nolock)) Group by UserName, Title, Name, ColWidth, Sequence Having count(*) >1 Any help would be greately appreciated. Thanks
View Replies !
DELETE DUPLICATE ROWS
CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW TO GET YESTERDAY DATE BY USING ISQL WINDOW. THANKS JK
View Replies !
Delete Duplicate Rows
I have a table which looks as follow: field1 field2 field3 field4 field5 ...... A B C A X ...... A B C B Y ...... A B C C Z ...... A B C A Y ...... . . . . . ...... I want to delete all the rows except one row. Anybody can help? Thank you very much.
View Replies !
Delete Rows From A View...
Hi, I have a requirement to delete rows from a view linked to another table. eg: Dups is the name of the view and t02 is the name of a table. Delete Dups from Dups, t02 where Dups.id = t02.id and Dups.run = t02.run When I run this querry I get the following message: View 'DUPS' is not updatable because the FROM clause names multiple tables. Does anyone know of a way that I might get around this or another possible solution to my requirement? Thanks in advance, Darrin Wilkinson
View Replies !
Delete Only 2 Out Of 4 Identical Rows
I am a beginner in Ms-Sql,so kindly help me with this query:- Following is the table:- Name Phone email John 4564 john@abc.com John 4564 john@abc.com John 4564 john@abc.com John 4564 john@abc.com How can i manage to delete only 2 rows out of these 4 rows
View Replies !
DELETE Rows In Excel
How to delete rows in an excel worksheet? I have the following code in the exeutesqltask but i keep getting a syntx error. 'DELETE FROM 4DialMeters' Remeber that this is an Excel source and I need to find out what the correct syntax is for the DELETE Statement in Excel.
View Replies !
Delete Rows In Oracle From Sql Dts
hi everyone ,How are you all? i'm working on sql DTS to transfer data from oracle to MS SQL2000 i did the transfer but i need to delete transfered data from oracle in DTS How i could do that? regards, Firas azzi
View Replies !
To Delete Rows Of Within The ADO RecordSet
Hi! I have a Foreach loop container where I populate a Ado recordset with some ids. Then I use another Foreach loop container to shred the Recordset and perform some task. Once Second Foreach Loop container is completed, my Recordset is getting populated again by the parent Foreach loop, except it is not discarding the data from before. Is there a way I can reset or delete rows from recordset after my second Foreach Loop container so that I don't duplicate rows in the Recordset.
View Replies !
Replication. Cant Delete Rows
Hi All. I have the folling replication issue: BD1 ------------------------> BD0 Replicate with filter BD2 ------------------------> BD0 Replicate with filter BD3 Four data bases BD0,BD1,BD2,BD3 In BD1 y BD2 there are two merged publications subscribed continuous in BD0. The publications are defined as insert only and allow changes in the subscriber. Everything works perfectly. The records replicate immediately. In BD0 you can insert and modify records, however when I attemept to delete a record I get the following message: error: You can't insert a row with a duplicate key in the object 'dbo.MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone' Note: This is a translation of the original error message in Spanish below. No se puede insertar una fila de clave duplicada en el objeto 'dbo.MSmerge_tombstone' con Ãndice único 'uc1MSmerge_tombstone' Cheers,
View Replies !
Delete Multiple Rows With 2 Ids
Hello All, I have a table: idSurrogate int identity id1 int id2 int id1 + id2 is a unique index I need to delete multiple rows from the table given a list of id1 and a list of id2 In other words @id1List = '10,20,30' @id2List = '1,3,5' I need to delete these 3 rows from the table 1) @id1=10 and @id2=1 2) @id1=20 and @id2=3 3) @id1=30 and @id2=5 I am a bit lazy today - can anyone help out with a delete sql stmt Thanks!
View Replies !
To Delete Last N Inserted Rows
I have a table in which i have to delete last n inserted rows, how should i approach this , Sql server does not provide any ROWGUID BASED ON TIMESATAMP i dnt think Rank() wil work either Any suggestions?
View Replies !
How To Delete Rows In Tables...
I need to delete some rows in some of my tables after tranfering data from my OLTP to SQL database. Im using SQL 2000 I have tried with the following: Delete from fsalesinvoiceline Join dsalesinvoiceheader on Fsalesinvoiceline.salesid= dsalesinvoiceheader.salesid and Fsalesinvoiceline.company= dsalesinvoiceheader.company Where dsalesinvoiceheader.billtocustomerno=€™INDTAST DEBITORNUMMER€™ Go Delete from dsalesinvoiceheader Where dsalesinvoiceheader.billtocustomerno=€™INDTAST DEBITORNUMMER€™ I get the following error message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'JOIN' What am I doing wrong? /Søren D. Jensen
View Replies !
How Do You Delete Duplicate Rows......
How do you delete duplicate rows when there are constraints or like in professional terms as we say "Referential Integrity". Mr.Madhivanan i hope you remember me. Because it is after all purpose that binds us all together...purpose...it is inevitable Mr.Madhivanan.
View Replies !
Delete Rows From A Table
Hi, This is an easy one. How do delete rows from a table? I want to delete all the rows from the last month of a table so i have to be able to pass the month parameter to the delete statement. Help.. plz.. i think this is an easy one
View Replies !
Want Insert 1000 Rows Into SQL Tables Through Application
Hi I have an PL/SQL procedure @ Oracle database which extracts 10000 rows from a table and Now I have load all the 1000 rows into SQL 2005 tables. I have extracted the data from oracle into DataAdapter/dataset , Now I want to load all the rows to SQL 2005 tables. Please help how I can load.. If I use insert statement everytime , it makes server busy and takes much time for 10000 inserts to complete(even using Procedure goes heavy since for every insert have to call this). Is there any possibility that i can pass the REF Cursor / Dataset/dataAdapter into SQL stored so that inserts will have happen all together ?? Thanks in advance for ur help. Regards: Nanjappa
View Replies !
Stored Procedure Not Returning Rows In Web Application
Hi, I Have created a stored procedure to use full text search and return the results back, When i run this procedure in the query analyzer it is working fine, but when i run this procedure from asp.net application, it is returning zero rows. I have checked all the parameters and everything in my web application, there is nothing wrong in there. Another stored procedure which almost do the samething with some different parameters is working fine on both ends. I am using Sql server 2005 (express) + VS.NET 2005. and using ASPNET to connect to database. Thanks in advance for any suggestions.
View Replies !
Delete Rows From One Table Using Rows From Another Table
Using the the NumId from TitleData, I would like to delete thecorresponding row in Bookdata using pure SQL. I want it to delete allrows in bookdata where the Titledata.NumID is a match to bookdata.idThe two tables are linked in that the NumId of table Titledata isidentical to the Id of table bookdata. I can, using ADO, loop thrudeleting one by one but I would like to do this in a pure SQLstatement. Is this possible? Any help is appreciated.I was thinking something like this way :"Delete from Bookdata where Titledata.NumID = Bookdata.id"But of course it will error.My current code is:(frmlogon.tablename is really Titledata)Dim rstry As New ADODB.RecordsetDim values As VariantSQLQuery = "Select Numid from " & frmLogon.TablenameSet rstry = frmLogon.cnConnection.Execute(SQLQuery)values = rstry.GetRowsSet rstry = Nothing'now loop thruDim xx As Integerxx = 0Do Until xx > UBound(values, 2)SQLQuery = "Delete from Bookdata where bookdata.Id = '" & values(0,xx) & "'"frmLogon.cnConnection.Execute (SQLQuery)xx = xx + 1Loop'create statements for 2 tables involved areconn.Execute "CREATE TABLE TitleData" & _"(Id INT IDENTITY (1, 1) NOT NULL PRIMARY KEY," & _"NumId INT DEFAULT 0 )"conn.Execute "CREATE TABLE BookData" & _"(Id INT IDENTITY (1, 1) NOT NULL," & _"Titles TEXT DEFAULT ''," & _"GeneralNote TEXT DEFAULT ''," & _"Author VARCHAR(100) DEFAULT ''," & _"Imprint VARCHAR(100) DEFAULT ''," & _"ISBN VARCHAR(100) DEFAULT ''," & _"Description VARCHAR(100) DEFAULT ''," & _"CallNumberPre VARCHAR(5) DEFAULT ''," & _"CallNumber VARCHAR(25) DEFAULT '',LOCNumber VARCHAR(30) DEFAULT '',"& _"Accession VARCHAR(25) DEFAULT ''," & _"Bibliography VARCHAR(100) DEFAULT ''," & _"Series VARCHAR(100) DEFAULT ''," & _"MyStatus VARCHAR(70) DEFAULT ''," & _"Barcode VARCHAR(50) DEFAULT ''," & _"LocalData VARCHAR(100) DEFAULT ''," & _"CheckoutPeriod VARCHAR(10) DEFAULT ''," & _"CatalogCard TEXT DEFAULT ''," & _"Summary TEXT DEFAULT ''," & _"MyCount VARCHAR(10) DEFAULT ''," & _"ItemDate DATETIME DEFAULT ''," & _"MyUser VARCHAR(50) DEFAULT ''," & _"MarcData TEXT DEFAULT ''," & _"SdlsRecord TEXT DEFAULT '', LOSC VARCHAR(5) DEFAULT '', LOSNDecimal(14,6) DEFAULT 0," & _"Edits Char(1) DEFAULT '', TitleDuplicate VARCHAR(50) DEFAULT '')"
View Replies !
Read Rows AND An Output Parameter From Codebehind Returned By Stored Proc?
I have a stored procedure that returns a resultset AND an output parameter, pseudocode:myspGetPoll@pollID int,@totalvoters int outputselect questionID,question from [myPoll] where pollID=@pollID @totalvoters=(select count(usercode) from [myPoll] where pollID=@pollID)1. In my code behind I'd like to read both the rows (questionID and question) as well as total results (totalvoters) How could I do so?2. what would be the signature of my function so that I can retreive BOTH a resultset AND a single value?e.g.: private function getPollResults(byval pollID as integer, byref totalvoters as integer) as datasetwhile reader.read dataset.addrow <read from result>end whiletotalvoters=<read from result>end functionThanks!
View Replies !
How Do You Delete (x) Number Of Rows From Database
I am setting up a database which schedules production and tracks inventory of items on a daily basis. The scheduler may put in 100 identical entries (apart from the identity column) of an item with its corresponding quantity. My problem is, if there is a shipment of product (a subtraction of quantity from the database), how can I delete a specified number of rows where the inventory listing is 100,000 pcs? I think the DELETE TOP(r) command will work but I don't know how make the command into an actual variable. Maybe there is another way too... My current not-working try; I look at the product desired to delete, figure out how many rows to delete, and since it is not always an integer, figure out a quantity to add back in. The addition part works fine but delete command needs work. Any help is appreciated. int InvRows = 0; decimal RealInvRows = 0; decimal AddQty = 0; int preAddAmount = 0; protected void DelInv_Click(object sender, EventArgs e) { Label TotProdSum = (Label)DetailsView2.FindControl("TotProdSum"); Label RowQty = (Label)DetailsView3.FindControl("RowQty"); int SubQty = Convert.ToInt32(ShipQty.Text); InvRows = SubQty / Convert.ToInt32(RowQty.Text) + 1; RealInvRows = SubQty / Convert.ToDecimal(RowQty.Text); AddQty = (InvRows - RealInvRows) * Convert.ToInt32(RowQty.Text); IntLbl.Text = Convert.ToString(InvRows); RealLbl.Text = Convert.ToString(RealInvRows); preAddAmount = Convert.ToInt32(AddQty); AddAmount.Text = Convert.ToString(preAddAmount); for (int r = 0; r <= InvRows; r++) { forWhile.DeleteCommand = "DELETE TOP (r) FROM Inventory WHERE (Inventory = @Inventory)"; forWhile.DeleteParameters.Add("Inventory", RowQty.Text); forWhile.Delete(); forWhile.DeleteParameters.Clear(); } forWhile.InsertCommand = "INSERT INTO Inventory(Dte, Product, Inventory) VALUES (@Dte, @Product, @Inventory)"; forWhile.InsertParameters.Add("Inventory", AddAmount.Text); forWhile.InsertParameters.Add("Product", InvProdDDL.Text); forWhile.InsertParameters.Add("Dte", Date.Text); forWhile.Insert(); forWhile.InsertParameters.Clear(); }
View Replies !
Can't Update, Insert, Or Delete Rows
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 Replies !
Delete Rows In Excel From DTS Package
I have a DTS package that needs to refresh data in 3 separate Excelspreadsheets on a daily basis. The problem is that unless I manuallydelete the previous day's data, it appends rather than replaces.I can't delete the excel files on a daily basis, as they have to bethere for the DTS package to be able to export to Excel. What I wantto do is create a VBScript (ActiveX Control) to delete all the rows ofdata except the first row within each spreadsheet as the first step ofthe DTS package. Then the remaining steps would run and thespreadsheets would only have the current day's data at the end of theprocess.Thanks for any help offered.
View Replies !
How Do I Delete Many Rows Without Monopolizing Server?
Hello,I am working on a project using SQL Server 2000 with a database containingabout 10 related tables with a lot of columns containing text. The totalcurrent size of the database is about 2 Gig. When I delete data from thedatabase, it takes a lot of system resources and monopolizes the database sothat all other query requests are slow as mud!Ideally, I would like to be able to issue delete commands to the database ona primary table and get a fast response back. Then, it doesn't matter to mehow long the actual deletion operation takes as long as its priority is lowcompared to the other query requests coming in. Typically, removing asingle row from the primary table results in a deletion of up to 300 rowsfrom related tables.Questions:1. Can I create a trigger on the primary table that will delete the rowsfrom that table, issue a delayed/low priority delete for all of the othertables, and return to the application quickly?2. Can a trigger be run in an asynchrous mode? (that is, issue the command,return immediately, and then go about its business on its own time).3. Can the priority of an SQL statement be specified?4. Is there a Transact-SQL "sleep" command that would allow you to do somework -- sleep for a little bit -- do some more work -- etc?Any help in this area would be greatly appreciated.....Thanks in advance...--Bob GangerGeneral DynamicsJoin Bytes!
View Replies !
|
TRACKER
