Mixing
SQL Server 2008: Executenonquery not returning number of rows affected
From C# I launch an update query using (a stored procedure):
command.ExecuteNonQuery(...);
Then in the stored procedure, I build a dynamic query and execute using sp_executesql:
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @pParam1 NUMERIC(18,0)
DECLARE @pParam2 BIGINT
SET @ParmDefinition = N'@Param1 numeric(18,0), @Param2 bigint'
SET @pParam1 = @Param1
SET @pParam2 = @Param2
DECLARE @SQLString nvarchar(max)= 'UPDATE ' + @MyServer + @MyDB + '.[dbo].[MyTable] ' +
'SET SomeField= @Param1 ' +
'WHERE F1= @Param2', @rowcnt INT;
EXECUTE sp_executesql @SQLString, @ParmDefinition, @Param1 = @pParam1, @Param2 = @pParam2
SELECT @rowcnt = @@ROWCOUNT
In C# ExecuteNonQuery is returning -1. I want to get the number of rows affected.
What's the problem?
sql-server-2008 executenonquery
edited Nov 21 '18 at 20:28
marc_s
577k12911141259
asked Nov 21 '18 at 18:24
user1624552user1624552
3,051853110
add a comment |
From C# I launch an update query using (a stored procedure):
command.ExecuteNonQuery(...);
Then in the stored procedure, I build a dynamic query and execute using sp_executesql:
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @pParam1 NUMERIC(18,0)
DECLARE @pParam2 BIGINT
SET @ParmDefinition = N'@Param1 numeric(18,0), @Param2 bigint'
SET @pParam1 = @Param1
SET @pParam2 = @Param2
DECLARE @SQLString nvarchar(max)= 'UPDATE ' + @MyServer + @MyDB + '.[dbo].[MyTable] ' +
'SET SomeField= @Param1 ' +
'WHERE F1= @Param2', @rowcnt INT;
EXECUTE sp_executesql @SQLString, @ParmDefinition, @Param1 = @pParam1, @Param2 = @pParam2
SELECT @rowcnt = @@ROWCOUNT
In C# ExecuteNonQuery is returning -1. I want to get the number of rows affected.
What's the problem?
sql-server-2008 executenonquery
edited Nov 21 '18 at 20:28
marc_s
577k12911141259
asked Nov 21 '18 at 18:24
user1624552user1624552
3,051853110
Please, post the C# code that call the SP.... as i understand your question, @rowcnt must be defined as an output parameter.
– MLeblanc
Nov 21 '18 at 18:32
I have already solved. The problem was that at the beginning of the stored procedure I had put below line: SET NOCOUNT ON. By removing this line, now is working, the number of rows affected is working. Thx.
– user1624552
Nov 21 '18 at 18:43
add a comment |
From C# I launch an update query using (a stored procedure):
command.ExecuteNonQuery(...);
Then in the stored procedure, I build a dynamic query and execute using sp_executesql:
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @pParam1 NUMERIC(18,0)
DECLARE @pParam2 BIGINT
SET @ParmDefinition = N'@Param1 numeric(18,0), @Param2 bigint'
SET @pParam1 = @Param1
SET @pParam2 = @Param2
DECLARE @SQLString nvarchar(max)= 'UPDATE ' + @MyServer + @MyDB + '.[dbo].[MyTable] ' +
'SET SomeField= @Param1 ' +
'WHERE F1= @Param2', @rowcnt INT;
EXECUTE sp_executesql @SQLString, @ParmDefinition, @Param1 = @pParam1, @Param2 = @pParam2
SELECT @rowcnt = @@ROWCOUNT
In C# ExecuteNonQuery is returning -1. I want to get the number of rows affected.
What's the problem?
sql-server-2008 executenonquery
edited Nov 21 '18 at 20:28
marc_s
577k12911141259
asked Nov 21 '18 at 18:24
user1624552user1624552
3,051853110
From C# I launch an update query using (a stored procedure):
command.ExecuteNonQuery(...);
Then in the stored procedure, I build a dynamic query and execute using sp_executesql:
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @pParam1 NUMERIC(18,0)
DECLARE @pParam2 BIGINT
SET @ParmDefinition = N'@Param1 numeric(18,0), @Param2 bigint'
SET @pParam1 = @Param1
SET @pParam2 = @Param2
DECLARE @SQLString nvarchar(max)= 'UPDATE ' + @MyServer + @MyDB + '.[dbo].[MyTable] ' +
'SET SomeField= @Param1 ' +
'WHERE F1= @Param2', @rowcnt INT;
EXECUTE sp_executesql @SQLString, @ParmDefinition, @Param1 = @pParam1, @Param2 = @pParam2
SELECT @rowcnt = @@ROWCOUNT
In C# ExecuteNonQuery is returning -1. I want to get the number of rows affected.
What's the problem?
sql-server-2008 executenonquery
sql-server-2008 executenonquery
edited Nov 21 '18 at 20:28
marc_s
577k12911141259
asked Nov 21 '18 at 18:24
user1624552user1624552
3,051853110
edited Nov 21 '18 at 20:28
marc_s
577k12911141259
asked Nov 21 '18 at 18:24
user1624552user1624552
3,051853110
edited Nov 21 '18 at 20:28
marc_s
577k12911141259
edited Nov 21 '18 at 20:28
marc_s
577k12911141259
edited Nov 21 '18 at 20:28
marc_s
577k12911141259
577k12911141259
asked Nov 21 '18 at 18:24
user1624552user1624552
3,051853110
asked Nov 21 '18 at 18:24
user1624552user1624552
3,051853110
asked Nov 21 '18 at 18:24
user1624552user1624552
3,051853110
3,051853110
Please, post the C# code that call the SP.... as i understand your question, @rowcnt must be defined as an output parameter.
– MLeblanc
Nov 21 '18 at 18:32
I have already solved. The problem was that at the beginning of the stored procedure I had put below line: SET NOCOUNT ON. By removing this line, now is working, the number of rows affected is working. Thx.
– user1624552
Nov 21 '18 at 18:43
add a comment |
Please, post the C# code that call the SP.... as i understand your question, @rowcnt must be defined as an output parameter.
– MLeblanc
Nov 21 '18 at 18:32
I have already solved. The problem was that at the beginning of the stored procedure I had put below line: SET NOCOUNT ON. By removing this line, now is working, the number of rows affected is working. Thx.
– user1624552
Nov 21 '18 at 18:43
Please, post the C# code that call the SP.... as i understand your question, @rowcnt must be defined as an output parameter.
– MLeblanc
Nov 21 '18 at 18:32
Please, post the C# code that call the SP.... as i understand your question, @rowcnt must be defined as an output parameter.
– MLeblanc
Nov 21 '18 at 18:32
I have already solved. The problem was that at the beginning of the stored procedure I had put below line: SET NOCOUNT ON. By removing this line, now is working, the number of rows affected is working. Thx.
– user1624552
Nov 21 '18 at 18:43
I have already solved. The problem was that at the beginning of the stored procedure I had put below line: SET NOCOUNT ON. By removing this line, now is working, the number of rows affected is working. Thx.
– user1624552
Nov 21 '18 at 18:43
add a comment |
1 Answer
1
active
oldest
votes
I have solved it.
The culprit was the line I had put at the beginning of the stored procedure:
SET NOCOUNT ON;
By removing above line, now it is working perfectly, the number of rows affected is returned correctly to C#.
answered Nov 21 '18 at 18:44
user1624552user1624552
3,051853110
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53418375%2fsql-server-2008-executenonquery-not-returning-number-of-rows-affected%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
I have solved it.
The culprit was the line I had put at the beginning of the stored procedure:
SET NOCOUNT ON;
By removing above line, now it is working perfectly, the number of rows affected is returned correctly to C#.
answered Nov 21 '18 at 18:44
user1624552user1624552
3,051853110
add a comment |
I have solved it.
The culprit was the line I had put at the beginning of the stored procedure:
SET NOCOUNT ON;
By removing above line, now it is working perfectly, the number of rows affected is returned correctly to C#.
answered Nov 21 '18 at 18:44
user1624552user1624552
3,051853110
add a comment |
I have solved it.
The culprit was the line I had put at the beginning of the stored procedure:
SET NOCOUNT ON;
By removing above line, now it is working perfectly, the number of rows affected is returned correctly to C#.
answered Nov 21 '18 at 18:44
user1624552user1624552
3,051853110
I have solved it.
The culprit was the line I had put at the beginning of the stored procedure:
SET NOCOUNT ON;
By removing above line, now it is working perfectly, the number of rows affected is returned correctly to C#.
answered Nov 21 '18 at 18:44
user1624552user1624552
3,051853110
answered Nov 21 '18 at 18:44
user1624552user1624552
3,051853110
answered Nov 21 '18 at 18:44
user1624552user1624552
3,051853110
answered Nov 21 '18 at 18:44
user1624552user1624552
3,051853110
3,051853110
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53418375%2fsql-server-2008-executenonquery-not-returning-number-of-rows-affected%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please, post the C# code that call the SP.... as i understand your question, @rowcnt must be defined as an output parameter.
– MLeblanc
Nov 21 '18 at 18:32
I have already solved. The problem was that at the beginning of the stored procedure I had put below line: SET NOCOUNT ON. By removing this line, now is working, the number of rows affected is working. Thx.
– user1624552
Nov 21 '18 at 18:43