c# - Executing Stored Procedure and returning row count back to code via output and sql parameters -

i have following code matches user input via session variables. stored procedure returns row count if data in session variable matches data in database.

everything works except want return row count single row: in nutshell, visit form, add info , hit submit. data stored in session , stored procedure returns data when matched.

even though program works intreccount variable 0 rather row count.

stored procedure:

create procedure [dbo].[uspconfirmation]      @recordid char(36),     @lname varchar(30),     @fname varchar(30),     @minit char(1),     @recordcount int output  select * registration recordid = @recordid ,       lname = @lname ,       fname = @fname ,       minit = @minit  set @recordcount = @@rowcount  return 

method/code:

public static dataset confirmation() {  sqlcommand cmdsql = new sqlcommand("uspconfirmation", connection); cmdsql.commandtype = commandtype.storedprocedure;  cmdsql.parameters.add(new sqlparameter("@recordid", sqldbtype.varchar, 36)); cmdsql.parameters["@recordid"].direction = parameterdirection.input; cmdsql.parameters["@recordid"].value = recordidsession;  cmdsql.parameters.add(new sqlparameter("@lname", sqldbtype.varchar, 30)); cmdsql.parameters["@lname"].direction = parameterdirection.input; cmdsql.parameters["@lname"].value = lnamesession;  cmdsql.parameters.add(new sqlparameter("@fname", sqldbtype.varchar, 30)); cmdsql.parameters["@fname"].direction = parameterdirection.input; cmdsql.parameters["@fname"].value = fnamesession;  cmdsql.parameters.add(new sqlparameter("@minit", sqldbtype.char, 1)); cmdsql.parameters["@minit"].direction = parameterdirection.input; cmdsql.parameters["@minit"].value = mnamesession;  cmdsql.parameters.add(new sqlparameter("@recordcount", sqldbtype.int)); cmdsql.parameters["@recordcount"].direction = parameterdirection.output; 

... variable hold row count via output variable ...

int32 intreccount = convert.toint32(cmdsql.parameters["@recordcount"].value);  sqldataadapter da = new sqldataadapter(cmdsql); dataset ds = new dataset(); da.fill(ds);  try {     connection.open();     cmdsql.executenonquery(); } catch (exception ex) {        dbmsg = ex.message;  } {     connection.close();     cmdsql.dispose();     cmdsql.parameters.clear(); } return ds; } 

you need access value of output parameter after executing query not before. move line after execution of query, , before clearing parameters, like:

//variable hold row count via output viarable  int32 intreccount = convert.toint32(cmdsql.parameters["@recordcount"].value); 

so code method be:

public static dataset confirmation() {  sqlcommand cmdsql = new sqlcommand("uspconfirmation", connection); cmdsql.commandtype = commandtype.storedprocedure;  cmdsql.parameters.add(new sqlparameter("@recordid", sqldbtype.varchar, 36)); cmdsql.parameters["@recordid"].direction = parameterdirection.input; cmdsql.parameters["@recordid"].value = recordidsession;  cmdsql.parameters.add(new sqlparameter("@lname", sqldbtype.varchar, 30)); cmdsql.parameters["@lname"].direction = parameterdirection.input; cmdsql.parameters["@lname"].value = lnamesession;  cmdsql.parameters.add(new sqlparameter("@fname", sqldbtype.varchar, 30)); cmdsql.parameters["@fname"].direction = parameterdirection.input; cmdsql.parameters["@fname"].value = fnamesession;  cmdsql.parameters.add(new sqlparameter("@minit", sqldbtype.char, 1)); cmdsql.parameters["@minit"].direction = parameterdirection.input; cmdsql.parameters["@minit"].value = mnamesession;  cmdsql.parameters.add(new sqlparameter("@recordcount", sqldbtype.int)); cmdsql.parameters["@recordcount"].direction = parameterdirection.output;    sqldataadapter da = new sqldataadapter(cmdsql); dataset ds = new dataset(); da.fill(ds); int32 intreccount = 0; try {     connection.open();     cmdsql.executenonquery();     //variable hold row count via output viarable     intreccount = convert.toint32(cmdsql.parameters["@recordcount"].value);  } catch (exception ex) {     dbmsg = ex.message; } {     connection.close();     cmdsql.dispose();     cmdsql.parameters.clear(); }   return ds; }