sql server - Using stored procedure output parameters in C# -

-

i having problem returning output parameter sql server stored procedure c# variable. have read other posts concerning this, not here on other sites, , cannot work. here have. trying print value comes back. following code returns null value. trying return primary key. have tried using @@identity , scope_indentity() (i.e. set @newid = scope_identity()).

stored procedure:

create procedure usp_insertcontract     @contractnumber varchar(7),      @newid int output begin      insert [dbo].[contracts] (contractnumber)         values (@contractnumber)      select @newid = id [dbo].[contracts] contractnumber = @contractnumber end

opening database:

pvconnectionstring = "server = desktop-pc\\sqlexpress; database = pvdatabase; user id = sa;     password = *******; trusted_connection = true;";  try {     pvconnection = new sqlconnection(pvconnectionstring);     pvconnection.open(); } catch (exception e) {     databaseerror = true; }

executing command:

pvcommand = new sqlcommand("usp_insertcontract", pvconnection);  pvcommand.transaction = pvtransaction; pvcommand.commandtype = commandtype.storedprocedure;      pvcommand.parameters.clear(); pvcommand.parameters.add(new sqlparameter("@contractnumber", contractnumber));  sqlparameter pvnewid = new sqlparameter(); pvnewid.parametername = "@newid"; pvnewid.dbtype = dbtype.int32; pvnewid.direction = parameterdirection.output; pvcommand.parameters.add(pvnewid);  try {     sqlrows = pvcommand.executenonquery();      if (sqlrows > 0)         debug.print("new id inserted =  ",              pvcommand.parameters["@newid"].value.tostring());      }     catch (exception e)     {         debug.print("insert exception type: {0}", e.gettype());         debug.print("  message: {0}", e.message);     } }

i modified stored procedure (to use scope_identity) , looks this:

create procedure usp_insertcontract     @contractnumber varchar(7),     @newid int output begin     insert [dbo].[contracts] (contractnumber)     values (@contractnumber)      select @newid = scope_identity() end

i tried , works fine (with modified stored procedure):

// define connection , command, in using blocks ensure disposal using(sqlconnection conn = new sqlconnection(pvconnectionstring )) using(sqlcommand cmd = new sqlcommand("dbo.usp_insertcontract", conn)) {     cmd.commandtype = commandtype.storedprocedure;      // set parameters     cmd.parameters.add("@contractnumber", sqldbtype.varchar, 7);     cmd.parameters.add("@newid", sqldbtype.int).direction = parameterdirection.output;      // set parameter values     cmd.parameters["@contractnumber"].value = contractnumber;      // open connection , execute stored procedure     conn.open();     cmd.executenonquery();      // read output value @newid     int contractid = convert.toint32(cmd.parameters["@newid"].value);     conn.close(); }

does work in environment, too? can't why original code won't work - when here, vs2010 , sql server 2008 r2, works flawlessly....

if don't value - suspect table contracts might not have column identity property on it.


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more