Problem on insertion data to table using storedprocedure

Question

0.00/5 (No votes)

See more:

Hi friends
I insert data to sqltable with storedprocedure using c# windows application. when getting result from storedprocedurte on sqlcommand.ExecuteNonQuery() it return -1 for stored procedure.I think this means data successfully inserted. but when checking table there is no data present in table. what could be the problem on this issue?

Posted 28-Oct-12 23:06pm

baskaran chellasamy

Add a Solution

Comments

VIPR@T 29-Oct-12 5:12am

Please show your query here...

BlackMilan 29-Oct-12 5:12am

My fortune teller crystal sphere must be repaired, so post your code ...

Shanalal Kasim 29-Oct-12 5:14am

first check stored procedure using Execute stored procedure option

satz_770 29-Oct-12 5:17am

ExecuteNonQuery() returns number of rows affected and when it reurns -1, it means that something like rollback has occurred. Check for any triggers in your side.

More explanation :
http://msdn.microsoft.com/en-us/library/80x06z3b%28v=vs.80%29.aspx

_Amy 29-Oct-12 5:18am

This question is unclear.. You should provide your dummy codes also.

baskaran chellasamy 29-Oct-12 5:36am

The Query is
ALTER procedure [dbo].[sp_studadmissioninsert](@admissionnumber int,@admissiondate datetime,@fromyear smallint,@toyear smallint,@classname varchar(30),@studname varchar(50),@dob datetime,@gender char(1),@age smallint,@fathername varchar(50),@fatheroccupation varchar(50),@mothername varchar(50),@motheroccupation varchar(50),@medium varchar(25),@studenttype varchar(25),@identimark1 varchar(300),@identimark2 varchar(300),@religion varchar(15),@cast varchar(50),@castcategory varchar(10),@phonenumber varchar(20),@email varchar(80),@previousschool varchar(max),@needtransport char(1), @street varchar(200),@city varchar(50),@state varchar(50),@country varchar(50),@zipcode varchar(50),@street1 varchar(200),@city1 varchar(50),@state1 varchar(50),@country1 varchar(50),@zipcode1 varchar(15),@datetime datetime,@adminid int,@applicationnumber int )
as
set nocount on
begin
declare @cassid int,@academicid int
select @academicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear
select @cassid=Standered_id from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)and Standered_name=@classname

if exists (select StudentName from StudentApplication_details where ApplicationNumber=@applicationnumber)
begin
update StudentAdmission_details set Admissiondate=@admissiondate,Academic_year_id=@academicid,ClassId=@cassid,StudentName=@studname,Age=@age,FatherOccupation=@fatheroccupation,MotherName=@mothername,MotherOccupation=@motheroccupation,Medium=@medium,StudentType=@studenttype,IdentificationMark1=@identimark1,IdentificationMark2=@identimark2,Religion=@religion,Caste=@cast,CastCategory=@castcategory,Phone_number=@phonenumber,EmailId=@email,Previous_School=@previousschool,Transport_status=@needtransport,Updateddatetime=@datetime,adminid=@adminid where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber
end
else
begin
--declare @cassid int,@academicid int
--select @academicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear
--select @cassid=Standered_id from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)and Standered_name=@classname
insert into StudentAdmission_details(AdmissionNumber ,ApplicationNumber ,Admissiondate,Academic_year_id,ClassId,StudentName,DOB,Gender,Age,FatherName,FatherOccupation,MotherName,MotherOccupation,Medium,StudentType,IdentificationMark1,IdentificationMark2,Religion,Caste,CastCategory,Phone_number,EmailId,Previous_School,Transport_status,Createddatetime,adminid)values(@admissionnumber, @applicationnumber,@admissiondate,@academicid,@cassid,@studname,@dob,@gender,@age,@fathername,@fatheroccupation,@mothername,@motheroccupation,@medium,@studenttype,@identimark1,@identimark2,@religion,@cast,@castcategory,@phonenumber,@email,@previousschool,@needtransport,@datetime,@adminid)
end
if exists(select Street,City,State,Country,Zipcode from TemporaryAddress_details where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber)
begin
update TemporaryAddress_details set Street=@street,City=@city,State=@state,Country=@country,Zipcode=@zipcode,Updateddatetime=@datetime,adminid=@adminid where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber
end
else
begin
insert into TemporaryAddress_details(Street,City,State,Country,Zipcode,Createddatetime,adminid,ApplicationNumber,AdmissionNumber)values(@street,@city,@state,@country,@zipcode,@datetime,@adminid,@applicationnumber,@admissionnumber)
end
if exists(select Street,City,State,Country,Zipcode from PermenentAddress_details where StudentApplicationNumber=@applicationnumber and StudentAdmissionNumber=@admissionnumber )
begin
update PermenentAddress_details set Street=@street,City=@city,State=@state,Country=@country,Zipcode=@zipcode

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Street Racers · Answer 1 · 2012-10-28T23:15:00

Solution 1

it's difficult to tell without any code. But still i will try to help you to solve the issue."Usually,for UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." So please check, if any transaction rollback occured.
Refer the following link for more details.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx[^]

Posted 28-Oct-12 23:15pm

Street Racers

Updated 28-Oct-12 23:18pm

v3

Comments

baskaran chellasamy 29-Oct-12 5:37am

The Query is
ALTER procedure [dbo].[sp_studadmissioninsert](@admissionnumber int,@admissiondate datetime,@fromyear smallint,@toyear smallint,@classname varchar(30),@studname varchar(50),@dob datetime,@gender char(1),@age smallint,@fathername varchar(50),@fatheroccupation varchar(50),@mothername varchar(50),@motheroccupation varchar(50),@medium varchar(25),@studenttype varchar(25),@identimark1 varchar(300),@identimark2 varchar(300),@religion varchar(15),@cast varchar(50),@castcategory varchar(10),@phonenumber varchar(20),@email varchar(80),@previousschool varchar(max),@needtransport char(1), @street varchar(200),@city varchar(50),@state varchar(50),@country varchar(50),@zipcode varchar(50),@street1 varchar(200),@city1 varchar(50),@state1 varchar(50),@country1 varchar(50),@zipcode1 varchar(15),@datetime datetime,@adminid int,@applicationnumber int )
as
set nocount on
begin
declare @cassid int,@academicid int
select @academicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear
select @cassid=Standered_id from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)and Standered_name=@classname

if exists (select StudentName from StudentApplication_details where ApplicationNumber=@applicationnumber)
begin
update StudentAdmission_details set Admissiondate=@admissiondate,Academic_year_id=@academicid,ClassId=@cassid,StudentName=@studname,Age=@age,FatherOccupation=@fatheroccupation,MotherName=@mothername,MotherOccupation=@motheroccupation,Medium=@medium,StudentType=@studenttype,IdentificationMark1=@identimark1,IdentificationMark2=@identimark2,Religion=@religion,Caste=@cast,CastCategory=@castcategory,Phone_number=@phonenumber,EmailId=@email,Previous_School=@previousschool,Transport_status=@needtransport,Updateddatetime=@datetime,adminid=@adminid where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber
end
else
begin
--declare @cassid int,@academicid int
--select @academicid=Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear
--select @cassid=Standered_id from Standered_details where Acadamic_year_id=(select Acadamic_year_id from Acadamic_year where Acadamic_year_from=@fromyear and Acadamic_year_to=@toyear)and Standered_name=@classname
insert into StudentAdmission_details(AdmissionNumber ,ApplicationNumber ,Admissiondate,Academic_year_id,ClassId,StudentName,DOB,Gender,Age,FatherName,FatherOccupation,MotherName,MotherOccupation,Medium,StudentType,IdentificationMark1,IdentificationMark2,Religion,Caste,CastCategory,Phone_number,EmailId,Previous_School,Transport_status,Createddatetime,adminid)values(@admissionnumber, @applicationnumber,@admissiondate,@academicid,@cassid,@studname,@dob,@gender,@age,@fathername,@fatheroccupation,@mothername,@motheroccupation,@medium,@studenttype,@identimark1,@identimark2,@religion,@cast,@castcategory,@phonenumber,@email,@previousschool,@needtransport,@datetime,@adminid)
end
if exists(select Street,City,State,Country,Zipcode from TemporaryAddress_details where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber)
begin
update TemporaryAddress_details set Street=@street,City=@city,State=@state,Country=@country,Zipcode=@zipcode,Updateddatetime=@datetime,adminid=@adminid where AdmissionNumber=@admissionnumber and ApplicationNumber=@applicationnumber
end
else
begin
insert into TemporaryAddress_details(Street,City,State,Country,Zipcode,Createddatetime,adminid,ApplicationNumber,AdmissionNumber)values(@street,@city,@state,@country,@zipcode,@datetime,@adminid,@applicationnumber,@admissionnumber)
end
if exists(select Street,City,State,Country,Zipcode from PermenentAddress_details where StudentApplicationNumber=@applicationnumber and StudentAdmissionNumber=@admissionnumber )
begin
update PermenentAddress_details set Street=@street,City=@city,State=@state,Country=@country,Zipcode=@zipcod

baskaran chellasamy 29-Oct-12 5:39am

today some hours before data actually successfully inserted for same query.but now only it doesnt insert data.

fjdiewornncalwe 31-Oct-12 11:56am

If it worked earlier in the day and no changes were made to the procedure, then it is likely that you are simply sending in bad data that causes the data to not be inserted.

BlackMilan · Answer 2 · 2012-10-28T23:59:00

Solution 3

You can try to run the code inside the stored procedure in management studio in an editor window and playing around. It's a little bit tricky, but may help to understand the code and find out the problem.

Posted 28-Oct-12 23:59pm

BlackMilan

ram salunke · Answer 3 · 2012-10-31T05:24:00

Solution 4

check table,sp fields and there values which u sending...
or fire your query in sqlserver

Posted 31-Oct-12 5:24am

ram salunke

ravithejag · Answer 4 · 2012-10-28T23:35:00

Solution 2

it may help you
http://www.daniweb.com/web-development/aspnet/threads/412058/insert-data-to-sql-server-using-stored-procedure-and-aspnet-textboxes

Posted 28-Oct-12 23:35pm

ravithejag