Only Getting Closed Record set as an output!? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

Only Getting Closed Record set as an output!?

Author

Topic

Aiby
Yak Posting Veteran

71 Posts

Posted - 2004-04-06 : 03:59:12

Only Getting Closed Record set as an output!?

Im using SQL Server 7 as Desktop Edition ( 98)

'My stored procedure goes here
------------------------------------------------------------------
CREATE PROCEDURE [spInsertSales]

@mBillDate [datetime] ,
@mCustomerID [Int] ,
@mCashSales [bit] ,
@mTotal [money] ,
@mDiscount [money] ,
@mNetAmount [money]
--@mNo [int] output

AS

Begin
DECLARE @mBillNo [int]
Update ControlFile Set BillNo = BillNo +1;
SELECT @mBillNo = BillNo From ControlFile;
Select @mBillNo
--Set @mNo = @mBillNo;

INSERT INTO Sales

( BillNo, BillDate, CustomerID, CashSales, Discount, Total, NetAmount )

VALUES
( @mBillNo, @mBillDate, @mCustomerID, @mCashSales, @mDiscount, @mTotal, @mNetAmount )

End
------------------------------------------------------------------

Vb code goes here

Private Sub Command1_Click()
Dim mCn As New ADODB.Connection
Dim Rec As New ADODB.Recordset
Dim adoCmd As New ADODB.Command
Dim mParameters As Parameters
Dim X As Long

Set mCn = gbCnn

adoCmd.ActiveConnection = mCn
adoCmd.CommandType = adCmdStoredProc
adoCmd.CommandText = "spInsertSales"
Set mParameters = adoCmd.Parameters

mParameters(1).Value = DateSerial(2004, 4, 1)
mParameters(2).Value = 1
mParameters(3).Value = 0
mParameters(4).Value = 12.35
mParameters(5).Value = 2.1
mParameters(6).Value = 124.25

For X = 1 To mParameters.Count - 1
Debug.Print mParameters(X).Value & ", ";
Next X

'mParameters(7).Value = 0

Set Rec = adoCmd.Execute '<<<<<<<<< Error Occurs here
On Error GoTo ErrMsg: 'Error Message Closed Recorset
If Not (Rec.EOF And Rec.BOF) Then
MsgBox ("Fetched")
End If
Exit Sub
ErrMsg:
MsgBox Error$
End Sub
--------------------------------------------------------------------

When i try to give "Go" in stored procedure it showing an error as
"Incorrect Syntax Near Go" - in same procedure

Aiby
Decibel Infotech
Aiby@hotmail.com

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-06 : 04:17:06

You need to use SET NOCOUNT ON in your proc (just after the AS). Without it, every update is returning an empty set with the "n rows affected" message.

Damian

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-06 : 04:20:17

Yeah,
Either that or dont return a Recordset,
just perform the command

adoCmd.Execute

Duane.

Aiby
Yak Posting Veteran

71 Posts

Posted - 2004-04-06 : 04:31:49

quote:
Originally posted by ditch

Yeah,
Either that or dont return a Recordset,
just perform the command

adoCmd.Execute

Duane.

To Duane>
I need to get that PrimaryId back to update the child/transaction tables
(Aiby)

Aiby
Yak Posting Veteran

71 Posts

Posted - 2004-04-06 : 04:33:26

quote:
Originally posted by Merkin

You need to use SET NOCOUNT ON in your proc (just after the AS). Without it, every update is returning an empty set with the "n rows affected" message.

Damian

To Damian..> I will check it out and report you very soon Damian

MatrixOne
Starting Member

11 Posts

Posted - 2004-04-06 : 10:24:05

quote:

To Duane>
I need to get that PrimaryId back to update the child/transaction tables
(Aiby)

My advice would be to put the required code to update both the Parent and Child tables within a single Stored Procedure and run it with BEGIN TRANSACTION .... COMMIT TRANSACTION.

use the golbal vairable @@rowcount within your stored proc to view how many rows have been updated by each statment you execute.

This will ensure data integrity and you will have the Primary ID you require exposed within your stored Proc

MatrixOne
Starting Member

11 Posts

Posted - 2004-04-06 : 10:30:17

I mean Use BEGIN TRANSACTION and COMMIT TRANSACTION within the Stored proc

Eg:

CREATE PROCEDURE dbo.UP_Get_TCSD_From_SSTAT001
(@DaysToReg int = -2)

AS
--DATE CREATED: 30-Mar-2004

DECLARE @RowsUpdated bigint

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON

BEGIN

BEGIN TRANSACTION

--Delete the rows from the table if existing
DELETE FROM Table 2

--Insert the Rows into Destination table
INSERT INTO Table 2
SELECT *
FROM table1

--Get no of Rows updated
SELECT @RowsUpdated = @@Rowcount

IF @@Error <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Transfer failed. Transaction Rolled Back', 16, 1)
END
ELSE
BEGIN
COMMIT TRANSACTION

END
GO

(diddnt explain that very well sorry)

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-06 : 16:46:54

Agreed that putting it into one sproc/one transaction is a good idea. Also want to add that when you want to return a single value from a stored procedure, and not a real recordset, I would suggest using either OUTPUT Parameters or a RETURN VALUE instead of a recordset.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Aiby
Yak Posting Veteran

71 Posts

Posted - 2004-04-11 : 06:58:27

quote:
Originally posted by Merkin

You need to use SET NOCOUNT ON in your proc (just after the AS). Without it, every update is returning an empty set with the "n rows affected" message.

Damian

Thank you Damian, its working... Thank you so much...

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-12 : 22:12:09

Hi gals and guys, i was about to post a new topic but i read this and thought it might help me a quick sec if I just continued this post. I have recently moved from ACCESS to SQL and wanted to start using OUTPUT parameters. I have not read much on them, but thought I'd get a quick human response while I was here. keeping the current example, how would I implement let's say the @@RowCount to go out the an OUTPUT parameter in the application? Do I have to make it the last parameter in the parameter list on the vb code? can it be anywhere? what is I wanted more than one output, not a recordset but just more than one value like maybe @@RowCount and @@DBTS, not sure i could use a RETURN VALUE if I wanted more than one value thrown out, but I might be wrong.

Thanks.

- RoLY roLLs

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-04-15 : 19:12:18

Roly, you're right, you can't have more than one RETURN VALUE. But you can have as many OUTPUT parameters as you want. I believe they can be in any order in the list of parameters, but it makes sense to me to usually put them last. To assign them in your sproc, just do something like:

SELECT @MyOutputParm1 = @@ROWCOUNT, @MyOutputParm2 = @@DBTS

or you can also use SET statements

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-15 : 21:30:57

Thank you AjarnMark. Listen I've got another question that no one seems to be able to answer, yet. If u can, would you take a look at it. Hope u can help, if not, it's ok. The more people view it, the better chance of it getting answered, no? :)

I know it's on another forum, but I posted it here before and was told to post it at ASP.NET, but then I figured out it was an SQL thing. Thanks for looking.

[url]http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=537188[/url]

- RoLY roLLs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-15 : 21:41:09

Could you summarize what the problem is from the other forum?

If the problem is query related, run the query inside Query Analyzer and verify that it retrieves the correct result set. Then move the query into your application.

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-15 : 21:59:28

yes, in QA it does give me the correct results. But what i'll do to keep the post clean is start a new one instead, with details.

- RoLY roLLs

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-04-15 : 22:02:29

Roly, who told you not to post it here ? We have a forum for ASP and ASP.NET.
It's also probably to start a new thread for a new question.

Anyway, I hate sideways scrolling, but from what I can gather you are getting some of your fields not showing up in ADO. Do you have any "Text" fields ? From memory a lot of Access fields get converted to Text.

Text fields are for very long blobs of text (more than 8k) and cause a little bit of havoc with ADO. Text fields have to be last in the select list, or you get problems.
You should look at making them VarChar fields.

Also, I notice in your parameter, you have made it the length of the input. While that won't hurt, it is meant to be the length of the parameter.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources