Okay..i have this problem ...i am using SQL server 2005 standard ,C#,VS2005
--i am inserting some record in DB .. using ExecuteNonQuery...i want to know how many records are getting inserted..so in my DB class i did something like this : numRecords = commandObject.ExecuteNonQuery() ,assuming that ExecuteNonQuery returns the number of affected records.i am retriving this numOfRecords in my code behind and printing it but it always prints 1,even though more then one records are inserted.What is wrong here?
-i also have returnValue defiend like this.Could this tell me anything about how many records are inserted or affected during update,select ?if so,how?
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 ========
Hello,If I run an action SP from MS Access using ADO:...cmd.executewhere the SP is something like Create...Update tbl1 set fld1 = 'something' where...how can I retrive the count of records affected like from Queryanalyzer?Thanks,Rich*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm sure this is a simple question, but I haven't had any luck finding an answer.
With a trigger, how do I access the modified/updated/deleted rows? The point of all this is I need to a database that maintains lockout procedures to email certain people when one of these procedures gets updated/added. I want to be able to build a link that points to our webserver (the interface for the lockout db) and to do this, I need certain fields of the affected records.
Let me know if I need to clarify anything. Any help is greatly appreciated.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
BEGIN DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' ) END BEGIN INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22' END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 ) SELECT 'abc', 'data11', 'data12' UNION ALL SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
HI EVERYBODY This is my procedure " CREATE PROCEDURE SP_SAMPLE_SEARCH @Title nvarchar(256) AS
SELECT ID,Title,Price FROM [tbl_Sim] WHERE ([Title] LIKE '%' + @Title + '%') Order by Price desc GO " I exec procedure and it returns 12 results with diffirents ID and want to get these values 1,2,3....12 How do I get these... I am a beginner. Thanks for help..
hi can anyone tell me how to count number of records(rows) in a table without using "COUNT" function.for practise iam trying to implement it through queries.
Hi... I just begining to use the asp.net 2.0 and have tow littel problems... in my project the user makes a "list of problems on a house" when hi post the house number the page shoud to generete a master recod with a keynumber, the date an hour and the state of problems... and a form to insert a list of problems... but if the house dosn´t exist this forms are hidden... then i need to know the nomber of records that gets a recordsource if its more than cero show the forms else hiden it... if show the forms i need to store in a variable the keynumber to store it in the problem details table... ¿How i can khow the number of records of a RecordSource gets?... and ¿How I can store in a variable a field value retrived for a RecordSource?... I´m using VWD and SQL Express that means ADO.NET 2.0... cheers.
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
I want find out Total number records in one table without using select statment. Some body as told to me there is system table you can find total number of records. Any body give me systable name. Thanks Jack
My manager ask me to provide him with the total number of records which have been added, deleted or modified on a certain database in SQL Server 2000 during the month of September. Is there away to get that information from the transaction log or by any how?
Why is it my number of records are different from my view. this are the following code that that i used in my code and in my view.
my code in my program: this code show all the records i know
If iConn.State = ConnectionState.Open Then iConn.Close() iConn.Open() Dim rsBills As New Data.DataSet rsBills = New DataSet Dim daBills As New SqlDataAdapter daBills = New SqlDataAdapter rsBills.Clear() Dim cmBills As New SqlCommand cmBills = iConn.CreateCommand ' "Select U_Code, U_Name, U_Level, P_Word From EPassword ", cmBills.CommandText = "Select OR_no, Billing_mo From dbo.vwBilling Where Month(dbo.vwBilling.Billing_mo)= " & Month(Me.dtto.Value) & " And Year(dbo.vwBilling.Billing_mo) = " & Year(Me.dtto.Value) & " ORDER BY OR_no "
With this algorithm you can sum up an unkown number of records, so that an aggregation matches a fixed value. If there is not an exakt match available, the algorithm returns the nearest possible value!-- Initialize the search parameter DECLARE@WantedValue INT
SET@WantedValue = 349
-- Stage the source data DECLARE@Data TABLE ( RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, MaxItems INT, CurrentItems INT DEFAULT 0, FaceValue INT, BestUnder INT DEFAULT 0, BestOver INT DEFAULT 1 )
-- Aggregate the source data INSERT@Data ( MaxItems, FaceValue ) SELECTCOUNT(*), Qty FROM( SELECT 899 AS Qty UNION ALL SELECT 100 UNION ALL SELECT 95 UNION ALL SELECT 50 UNION ALL SELECT 55 UNION ALL SELECT 40 UNION ALL SELECT 5 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 250 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 90 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 350 UNION ALL SELECT 450 UNION ALL SELECT 450 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 1 UNION ALL SELECT 10 UNION ALL SELECT 1 ) AS d GROUP BYQty ORDER BYQty DESC
-- Declare some control variables DECLARE@CurrentSum INT, @BestUnder INT, @BestOver INT, @RecID INT
-- If productsum is less than or equal to the wanted sum, select all items! IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) <= @WantedValue BEGIN SELECTMaxItems AS Items, FaceValue FROM@Data
RETURN END
-- Delete all unworkable FaceValues DELETE FROM@Data WHEREFaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)
-- Update MaxItems to a proper value UPDATE@Data SETMaxItems =CASE WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue ELSE MaxItems END
-- Update BestOver to a proper value UPDATE@Data SETBestOver = MaxItems
-- Initialize the control mechanism SELECT@RecID = MIN(RecID), @BestUnder = 0, @BestOver = SUM(BestOver * FaceValue) FROM@Data
-- Do the loop! WHILE @RecID IS NOT NULL BEGIN -- Reset all "bits" not incremented UPDATE@Data SETCurrentItems = 0 WHERERecID < @RecID
-- Increment the current "bit" UPDATE@Data SETCurrentItems = CurrentItems + 1 WHERERecID = @RecID
-- Get the current sum SELECT@CurrentSum = SUM(CurrentItems * FaceValue) FROM@Data WHERECurrentItems > 0
-- Stop here if the current sum is equal to the sum we want IF @CurrentSum = @WantedValue BREAK ELSE -- Update the current BestUnder if previous BestUnder is less IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue BEGIN UPDATE@Data SETBestUnder = CurrentItems
SET@BestUnder = @CurrentSum END ELSE -- Update the current BestOver if previous BestOver is more IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver BEGIN UPDATE@Data SETBestOver = CurrentItems
SET@BestOver = @CurrentSum END
-- Find the next proper "bit" to increment SELECT@RecID = MIN(RecID) FROM@Data WHERECurrentItems < MaxItems END
-- Now we have to investigate which type of sum to return IF @RecID IS NULL IF @WantedValue - @BestUnder < @BestOver - @WantedValue -- If BestUnder is closer to the sum we want, choose that SELECTBestUnder AS Items, FaceValue FROM@Data WHEREBestUnder > 0 ELSE -- If BestOver is closer to the sum we want, choose that SELECTBestOver AS Items, FaceValue FROM@Data WHEREBestOver > 0 ELSE -- We have an exact match SELECTCurrentItems AS Items, FaceValue FROM@Data WHERECurrentItems > 0With references to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505
I get this with this query which might be altered somehow:
SELECT COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies
Of course this is not normalized but I can't change this.
Nevertheless I need to get the full number of each Hobby and not only the combination of them.
So instead or in addition to the existing recordset I need e.g
357 Essen which ist the sum of all records containing 'Essen' in the above example
The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.
SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_a UNION SELECT row_number() over (ORDER by a.empid) as rec_num, empname FROM employee_b
the problem is the rec_num repeat for each statement like this :
rec_num empname
1 john 2 maggy 1 lee 2 mary 3 louis
How do i make the rec_num continue for the next statement after union.
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a UNION SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a
1 777 Mike HR 2 888 Susy HR 1 111 Smith TECH 2 222 John TECH 3 333 Lenny TECH
How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :
What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!
Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny.
