Collect a returned value from SP

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)

Collect a returned value from SP

Author

Topic

Amar Gupta
Starting Member

5 Posts

Posted - 2005-09-14 : 18:38:33

Hi

I have a SP without a output variable that returns a integer value. How do i collect it from vb.net app. Following is the code i am using...

 
CREATE PROCEDURE [dbo].[ins_Template]
@vchar_TemplateName varchar(100),		-- Template Name
@int_CustomerId int,			-- Customer Id

-- INSERT
INSERT INTO dbo.map_Templates (TemplateName, CustomerId)
VALUES	(@vchar_TemplateName, @int_CustomerId)

SET @int_TemplateId = @@IDENTITY

RETURN @int_TemplateId
GO


SQL = "EXEC ins_Template " & _
                      "@vchar_TemplateName = '" & Name & "'," & _
                      "@int_CustomerId = " & CustomerID

Dim UCommand As SqlClient.SqlCommand = New SqlCommand(SQL, BlitzKarte4.GlobalConnection)

UCommand.ExecuteNonQuery()

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-14 : 18:52:26

Dim i As Int16
i = UCommand.ExecuteNonQuery()

Tara

Amar Gupta
Starting Member

5 Posts

Posted - 2005-09-14 : 19:01:32

Hi Tara

thanks for your reply. But this is not giving me correct result. Everytime I run the proc the returned value is 8..dont know why 8?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-14 : 19:14:05

Could you post your exact code, your stored procedure and the VB.NET code as it appears you've left stuff out of your initial post? Post what you've changed it to with my recommendation.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-14 : 19:28:06

Don't you need to use ExecuteScalar?

Tara

Amar Gupta
Starting Member

5 Posts

Posted - 2005-09-14 : 19:51:38

Hi

The code is little lengthy but simple. I tried executeScalar also...but I got returned value 0.

<code>
CREATE PROCEDURE [dbo].[ins_Template]

@vchar_TemplateName varchar(100), -- Template Name
@int_CustomerId int, -- Customer Id
@int_TemplateTypeId int, -- Template Type Id
@vchar_Title varchar(100), -- Title
@int_OrientationId int, -- Orientation Id
@vchar_BasedataCSV varchar(1000) = NULL, -- Basedata CSV (optional)
@vchar_LayoutOptionsCSV varchar(1000) = NULL, -- Layout Options CSV (optional)
@vchar_AncillaryDataXML varchar(1000) = NULL, -- Ancillary Data XML (optional)
@vchar_AncillaryDataXMLMajorElement varchar(20) = NULL, -- Ancillary Data XML Major Element
@vchar_AncillaryDataXMLMinorElement varchar(20) = NULL -- Ancillary Data XML Minor Element

AS

DECLARE @int_TemplateId int

-- CHECK IF THIS TEMPLATE EXISTS FOR SPECIFIED CUSTOMER
IF EXISTS (SELECT TOP 1 * FROM dbo.map_Templates WHERE (CustomerId = @int_CustomerId) AND (TemplateName = @vchar_TemplateName)) RETURN -1

-- INSERT
INSERT INTO dbo.map_Templates
(TemplateName, CustomerId, TemplateTypeId, Title, OrientationId)
VALUES (@vchar_TemplateName, @int_CustomerId, @int_TemplateTypeId, @vchar_Title, @int_OrientationId)

SET @int_TemplateId = @@IDENTITY

-- BASEDATA
IF (@vchar_BasedataCSV IS NOT NULL) BEGIN -- Basedata Comma,Sep,Values

EXEC dbo.ins_Templates_Basedata
@int_TemplateId = @int_TemplateId,
@vchar_BasedataCSV = @vchar_BasedataCSV
END

-- LAYOUT OPTIONS
IF (@vchar_LayoutOptionsCSV IS NOT NULL) BEGIN -- Layout Options comma,sep,values

EXEC dbo.ins_Templates_LayoutOptions
@int_TemplateId = @int_TemplateId,
@vchar_LayoutOptionsCSV = @vchar_LayoutOptionsCSV
END

-- ANCILLARY DATA
IF (@vchar_AncillaryDataXML IS NOT NULL) BEGIN -- Ancillary Data XML

EXEC dbo.ins_TemplatesAncillaryData
@int_TemplateId = @int_TemplateId,
@vchar_XML = @vchar_AncillaryDataXML,
@vchar_MajorElement = @vchar_AncillaryDataXMLMajorElement,
@vchar_MinorElement = @vchar_AncillaryDataXMLMinorElement
END

RETURN @int_TemplateId
GO

Public Function ADD_Template(ByVal oMapTemplate As BlitzKarte4.Map) As Boolean
Dim SQL As String
Dim NewTemplateID As Integer
Dim i As Integer
Try
SQL = "EXEC ins_Template " & _
"@vchar_TemplateName = '" & oMapTemplate.Name & "'," & _
"@int_CustomerId = " & oMapTemplate.CustomerID & "," & _
"@int_TemplateTypeId = " & oMapTemplate.MapTypeId & "," & _
"@vchar_Title = '" & oMapTemplate.Title & "'," & _
"@int_OrientationId = " & oMapTemplate.OrientationID & "," & _
"@vchar_BasedataCSV = '" & "1,2,3,4" & "'," & _
"@vchar_LayoutOptionsCSV = '" & "1,2,3" & "'"

i = ExecuteNonQuery(SQL) ' tried int16 also but same result
Catch ex As Exception
MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, "Error")
Return False
End Try
End Function

Private Function ExecuteNonQuery(ByVal SQL As String) As Boolean
' ------------------------------
' Executes a non-query statement
' ------------------------------
Try
Dim UCommand As SqlClient.SqlCommand = New SqlCommand(SQL, BlitzKarte4.GlobalConnection)
Dim i As Integer
i = UCommand.ExecuteNonQuery()
UCommand.Dispose()
Return True

Catch ex As SqlException
MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace & vbCrLf & "Query: " & SQL)
Return False
End Try
End Function

</code>

Amar Gupta
Starting Member

5 Posts

Posted - 2005-09-15 : 10:47:25

Hi Tara

Did you figure the problem.

Thanks for help.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-15 : 12:20:40

You are returning True from the function and not returning i. When you call your ExecuteNonQuery function, you are setting the returned value to i. But i doesn't equal your i inside the function though because you aren't returning it. You are returning True. So i = True.

Tara

Amar Gupta
Starting Member

5 Posts

Posted - 2005-09-15 : 13:41:34

Hi

I was looking the value of i in debug mode. The returned value is not correct. ignore the function return error.
i = UCommand.ExecuteNonQuery() is not giving the correct value.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-15 : 13:42:53

Run the stored procedure in Query Analyzer. Does the output parameter get set properly? Let me know if you need help with the code required for Query Analyzer.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources