vb and sql help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

vb and sql help

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-31 : 15:13:17

Hi there,
i have a problem with getting a number of affected row back when i execute an INSERT query through VB.
here is the actual query:

ALTER PROCEDURE [dbo].[InsertComp] 
	-- Add the parameters for the stored procedure here
	@fComp varchar(100),
	@fMachineID varchar(100)  
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	IF NOT exists (select * from hwk_comps where fComps = @fComp and fMachineID = dbo.fx_GetMachineGUID(@fMachineID))
		BEGIN
			INSERT hwk_comps (fComps,fMachineID)
			VALUES (@fComp,dbo.fx_GetMachineGUID(@fMachineID))
		END
	
END

this is what MSDN says about SQLCOMMAND.EXECUTENONQUERY method:
"Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

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."

right now every time I execute this query (i want to insert a new component) it returns -1. How can i write this query to map a result back to a parameter withing the sqlcommand i am using?

thanx

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-07-31 : 15:27:35

ALTER PROCEDURE [dbo].[InsertComp]
-- Add the parameters for the stored procedure here
@fComp varchar(100),
@fMachineID varchar(100),
@RowsAffected int OUTPUT
AS

....

INSERT hwk_comps (fComps,fMachineID)
VALUES (@fComp,dbo.fx_GetMachineGUID(@fMachineID))

select @RowsAffected = @@rowcount
.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-31 : 15:37:30

@@ROWCOUNT will always return 1 in this case.

E 12°55'05.25"
N 56°04'39.16"

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-31 : 15:49:31

i hope it will be either 1 or 0. that is all i need

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-31 : 16:00:23

it is telling me that the SP is expecting @RowsAffected, which was not supplied ??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-31 : 16:11:18

You need to tell it that it is an output parameter. This change needs to occur in both the sproc and in your app code.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-31 : 16:21:17

that was the problem there. I had it set to ReturnValue in my app. However now that I have changed it to OUTPUT, i get the same results as before ... -1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-31 : 16:22:37

You've got something wrong somewhere, obviously. We can't help with the little information that you've posted. We need to see the code.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-31 : 16:27:15

[code]ALTER PROCEDURE [dbo].[InsertComp]
-- Add the parameters for the stored procedure here
@fComp varchar(100),
@fMachineID varchar(100),
@RowsAffected int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
IF NOT exists (select * from hwk_comps where fComps = @fComp and fMachineID = dbo.fx_GetMachineGUID(@fMachineID))
BEGIN
INSERT hwk_comps (fComps,fMachineID)
VALUES (@fComp,dbo.fx_GetMachineGUID(@fMachineID))
SELECT @RowsAffected = @@rowcount
END

END[/code]

[code] SqlConn.Open()
Me.SqlCmdInsert.Parameters("@fComp").Value = Trim(Me.txtComp.Text)
Me.SqlCmdInsert.Parameters("@fMachineID").Value = DSMachines.Tables(0).Rows(Me.cbComps.SelectedIndex).Item(1)
'Debug.WriteLine(Me.SqlCmdInsert.Parameters("@fComp").Value)
'Debug.WriteLine(Me.SqlCmdInsert.Parameters("@fMachineID").Value)
Debug.WriteLine(Me.SqlCmdInsert.ExecuteNonQuery())
SQL.Close()
[/code]

sqlCmdInsert has three parameters: two input and one output

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-07-31 : 16:31:46

never mind... stupidity/confusion on my part...
i was looking for the wrong thing now with all the changes

Debug.WriteLine(Me.SqlCmdInsert.ExecuteNonQuery())
Debug.WriteLine(Me.SqlCmdInsert.Parameters("@rowsaffected").Value)

the second line returns 1 when the insert succeeds and nothing when it does not.
I think this will do it
many thanx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 02:19:04

IF exists (select * from hwk_comps where fComps = @fComp and fMachineID = dbo.fx_GetMachineGUID(@fMachineID))
SELECT @RowsAffected = 0
ELSE
BEGIN
INSERT hwk_comps (fComps,fMachineID)
VALUES (@fComp,dbo.fx_GetMachineGUID(@fMachineID))
SELECT @RowsAffected = 1
END

E 12°55'05.25"
N 56°04'39.16"

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-08-01 : 09:03:05

thanx peso. I think what I have shown above will work for me, but thanx anyway for your suggestion

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 09:21:42

You need that red rows of code too.
In SQL Server, an integer variable does not get instantiated to zero like VB when it is declared. It is NULL.
If the EXISTS part fails, @rowcount is still null (not zero).

E 12°55'05.25"
N 56°04'39.16"

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-08-01 : 09:26:51

i found that out already, but i can take care of that in the app. I just check if the @rowsaffected parameter value is SYstem.DBNull.Value. If so I know nothing had been inserted

X002548
Not Just a Number

15586 Posts

Posted - 2007-08-01 : 09:47:34

quote:
Originally posted by Peso

@@ROWCOUNT will always return 1 in this case.

E 12°55'05.25"
N 56°04'39.16"

Dp Key?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ronin2307
Posting Yak Master

126 Posts

Posted - 2007-08-01 : 09:52:05

what are you referring to?

quote:

Dp Key?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 09:57:46

quote:
Originally posted by X002548

Dp Key?

No, there is not a duplicate key in this case. There is a check with EXISTS right before the insert statement.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources