|
Hi all,
I have written an insert query in a Stored Proc,
It works fine and inserts an record in the table, but
it returns -1 when i execute it thru C# Code.
Can anyone tell me why this happens?
Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"
|
|
|
|
|
first paste your stored procedure code here.
do you return any thing in your procedure code?
|
|
|
|
|
i dont return any thing from the proc, but the default value returned from an insert proc is 1,
in my case donno y it returns -1.
I have done some calculations into some variables and a simple insert statement is used to
insert the record in the table,
I have also used a transaction for this and
after the insert statment i have checked if the @intErrorCode is not 0 then rollback transaction,
The insert works fine but it returns -1.
Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"
|
|
|
|
|
if you call another stored procedure for insert into this procedure?
if this is true:
call to another procedures into one procedure cause returning -1
|
|
|
|
|
Which function you are using
ExecuteScaler, executenonquery, etc
Please specify
|
|
|
|
|
dbcommand.ExecuteNonQuery();
Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"
|
|
|
|
|
Do you mean ExecuteNonQuery returns -1? If yes, see what the documentation says
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.
|
|
|
|
|
yes, tht's a problem, i can the values in the table that means the insert statement was executed, and if an error has occured the insert should be rolledback rite, but this rollback is not happening and m not able to find what the error is.
wht r the ways to find the error that has caused the rollback?
Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"
|
|
|
|
|
In your stored procedure you can use RAISERROR[^] to generate an error and to inform client what has happened.
|
|
|
|
|
Since you are using Stored procedures, you can handle the errors inside the procedure and rollback if necessary. Add an output parameter which can be used to inform the client program about the process status. Something like,
CREATE PROCEDURE Foo
@FooColumn int = NULL
@ProcessStatus varchar(100) = NULL
AS
IF @@ERROR <>0
BEGIN
ROLLBACK
SET @ProcessStatus = 'Rollbacked'
END
ELSE
BEGIN
SET @ProcessStatus = 'Completed'
END
|
|
|
|
|
Thanks guys for all you suggestions,
I found the problem with my stored proc,
I had written SET NOCOUNT ON;
which was causing the -1 to return.
Now i have switched oFF that and it returns me 1.
Thanks & Regards,
Pramod
"Everyone is a genius at least once a year"
|
|
|
|
|
Hi experts 
I've created a database ( named database1.sdf )
Then , I've created two tables in it. ( date[^] & text[^] )
Now I'm going to create a relation between them with VS2008 SP1.
But this error[^] has occurred 
What's wrong with it ?
How can I resolve it ?
Thank you in advance
|
|
|
|
|
Most likely you have a mismatch in the code behind of xsd itself. Click DateID column in your dataset designer and check the DataType property from properties window.
|
|
|
|
|
Wow, thanks
|
|
|
|
|
You're welcome
|
|
|
|
|
Hello Friends,
I have create an trigger on an table insert and update event :
CREATE TRIGGER sp_Kp1 ON [dbo].[OEORDD]
for insert, update
AS
DECLARE @x DECIMAL(18,2);
DECLARE @y varchar(10);
DECLARE @z DECIMAL(18,2);
SELECT @x = xx FROM inserted;
SELECT @y = yy FROM inserted;
SELECT @z = zz FROM table1 where xx= @x and yy = @y;
INSERT INTO table2 (tx,ty,tz) VALUES @x,@y,@z);
This trigger is working fine only if one row is updated
but it fails if i update more than one row using update query.
Thanks n regards,
rahul saini
|
|
|
|
|
Can you simply use one insert into...select from query:
CREATE TRIGGER sp_Kp1 ON [dbo].[OEORDD]
for insert, update
AS
BEGIN
INSERT INTO table2 (tx,ty,tz) VALUES @x,@y,@z)
SELECT i.xx,
i.ii,
t.zz
FROM inserted i,
table1 t
WHERE t.xx = i.xx
AND t.yy = i.yy
END;
|
|
|
|
|
monuSaini wrote: This trigger is working fine only if one row is updated
but it fails if i update more than one row using update query.
Expanding Milka's answer,
SQL server don't have row level trigger like oracle. SQL server trigger will be executed only after the whole statement is completed. So when multiple rows are updated, the trigger will be executed only after all the updations are complete. You can use the INSERTED or UPDATED virtual tables to get the data affected.
|
|
|
|
|
I am using this query to see if any key is already in the table and returns 0 rows.
select * from dbo.Table1
where AccountCode IN(SELECT
[AccountCode] FROM #TempTable)
But when I do this it fails due to violation of primary key constraint:
INSERT INTO
dbo].[Table1]
(
[col1]
, [col2]
, [AccountCode]
, [col4]
, [col5]
, [colDate]
)
SELECT
[col1]
, AccountCode
, '12345'
, '12345'
, 100
, '01/16/2009'
FROM #TempTable
Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it.
|
|
|
|
|
CodingYoshi wrote: Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it.
you cannot use above query till there is identity column in destination table.
Remove identity for a while, execute your query and then put identity on column
|
|
|
|
|
CodingYoshi wrote: Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it
So you should remove the identity column from the insert statement since it's autogenerated. You cannot define value to it
INSERT INTO
dbo].[Table1]
(
[col1]
, [col2]
, [AccountCode]
, [col4]
, [col5]
, [colDate]
)
SELECT
[col1]
, AccountCode
, '12345'
, '12345'
, 100
, '01/16/2009'
FROM #TempTable
If you must insert a value to it, you can temporarily disable it:
SET IDENTITY_INSERT [Table1] ON
INSERT...
SET IDENTITY_INSERT [Table1] OFF
But if you insert new values to the identity column, you may end up in a situation where the next insert generates a duplicate identity value.
modified on Saturday, January 17, 2009 12:01 PM
|
|
|
|
|
Hi All
I'm a newbie to SQL coding, and I have a problem which I'm hoping you can help with regarding the Division by zero error.
I have a calculated field in a sql statement where the results from 1 case statements is divided by another. Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception?
Thank you in advance.
|
|
|
|
|
Unsy wrote: Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception
Most likely, but could you post the statement. Without that it's quite impossible to say how to implement it.
|
|
|
|
|
Hi Mika
I dont have the exact code with me at the moment. It was similar to this...
SELECT
Field1
,Field2
,SUM(
SUM(CASE WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 END))
/
SUM(CASE WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 TotalPaid END))
)
FROM
Table1
GROUP BY
Field1
,Field2
The idea is to get a total of how many Term - 20's are over 0 which were representing a loan term. Anything less than 0 would mean it has gone over term and should not be counted.
This was to be divided by the sum of total paid to calculate performance.
Please excuse the SQL code if it is full of errors as I am 250 miles from home and have no way of testing if it is right. 
Thanks
|
|
|
|
|
Unsy wrote: Please excuse the SQL code if it is full of errors as I am 250 miles from home
No problem.
And the problem was that the divisor is equal to zero in some cases. If it's zero, you wanted the result of the division to be zero. In that case you could for example use inline view for intermediate results and make the statement something like:
SELECT
Field1
,Field2
,SUM( CASE Divisor
WHEN 0 THEN 0
ELSE Amount / Divisor
END)
FROM (
SELECT Field1,
Field2,
SUM(CASE
WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0
THEN 0
ELSE 1
END
) amount,
SUM(CASE
WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0
THEN 0
ELSE 1
END
) divisor
FROM
Table1
GROUP BY
Field1
,Field2) alias
GROUP BY
Field1
,Field2
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
