Store Procedure Return Value zero

[WordTechinc]

I excuted SP but return value zero. I think the table using in SP is deleting and writing by parameter by another SP.
How to get return value while other SP is deleting and writing same sql table by job number?

Private Function getSavedJobs()
Dim cmdSavedJobs As New SqlCommand
Dim objSqAd As New SqlDataAdapter
Try
con = GetConnection()
dtJobs.Columns.Add("Select", Type.GetType("System.Boolean"))
dtJobs.Columns("Select").DefaultValue = False
With cmdSavedJobs
.Connection = con
.CommandType = CommandType.StoredProcedure
.CommandText = "INRv2_GetAllSavedJobs"
End With
objSqAd.SelectCommand = cmdSavedJobs

objSqAd.Fill(dtJobs) ******* this returns zero *****

Dim d(0) As DataColumn
d(0) = dtJobs.Columns(1)

dtJobs.PrimaryKey = d
Dim dr1 As DataRow
If Not objMergeJob.dtSelJobs Is Nothing Then
For Each dr As DataRow In objMergeJob.dtSelJobs.Rows
dr1 = dtJobs.Rows.Find(dr(1))
dr1(0) = True
Next
dtArr = dtJobs.Select("Select = True")
End If
'objMergeJob.dtSelJobs.Rows.Find
intRowCount = dtJobs.Rows.Count
objtableStyle.MappingName = dtJobs.TableName
fnFormatDataGrid()
objtableStyle.AllowSorting = False
dbgJob.TableStyles.Add(objtableStyle)
dbgJob.DataSource = dtJobs
dbgJob.Focus()
Catch ex As Exception
MessageBox.Show(dtJobs.Rows.Count) '' suk 20121112 wt
Console.WriteLine(ex.ToString)

''MessageBox.Show(ex.ToString) '' suk 20121112
End Try
End Function

 

[simian336]

This some vb code variant... you can't tell much about the sp from here. You would have to modify the sp "INRv2_GetAllSavedJobs" on your sql server and figure out what the code is doing from there.

Simi

 

[WordTechinc]

This is SP... INRV2_getallsavedJobs-- inr_mailinglist and inr_jobschecule are deleting or writing by other SP at the same time.


CREATE PROC [dbo].[INRv2_GetAllSavedJobs]
AS
SELECT plm.jobcode [Job No],si.seminartitle [Seminar Title]
FROM (SELECT DISTINCT pm.jobNo jobcode FROM inr_mailinglist pm) plm
INNER JOIN (SELECT js.jobno, sm.seminartitle
FROM inr_seminarmaster sm
INNER JOIN inr_jobshedule js
ON sm.seminarid =js.seminarid) si
ON plm.jobcode =si.jobno
ORDER BY plm.jobcode
RETURN




GO

 

[gmmastros]

Let's be clear here so that terminology doesn't get in our way.

When you say, "Return Value Zero", what do you mean?

Does this mean there are no rows returned from the procedure?
Does this mean you are checking the return value and it's always zero?

Some people get confused about return values. To illustrate the point, take a look at the last line of code in the stored procedure.

CREATE PROC [dbo].[INRv2_GetAllSavedJobs] 
AS
SELECT plm.jobcode [Job No],si.seminartitle [Seminar Title] 
FROM (SELECT DISTINCT pm.jobNo jobcode FROM inr_mailinglist pm) plm
INNER JOIN (SELECT js.jobno, sm.seminartitle 
FROM inr_seminarmaster sm
INNER JOIN inr_jobshedule js
ON sm.seminarid =js.seminarid) si
ON plm.jobcode =si.jobno
ORDER BY plm.jobcode
[!]RETURN[/!]

Technically, you can use the RETURN statement to return an integer from the stored procedure, for example....

CREATE PROC [dbo].[INRv2_GetAllSavedJobs] 
AS
SELECT plm.jobcode [Job No],si.seminartitle [Seminar Title] 
FROM (SELECT DISTINCT pm.jobNo jobcode FROM inr_mailinglist pm) plm
INNER JOIN (SELECT js.jobno, sm.seminartitle 
FROM inr_seminarmaster sm
INNER JOIN inr_jobshedule js
ON sm.seminarid =js.seminarid) si
ON plm.jobcode =si.jobno
ORDER BY plm.jobcode
[!]RETURN 937[/!]

You can then capture this return value from VB and use it for your purposes. I recommend against this because return values are usually reserved for error statuses, not usually data. Also note that you can only return an integer this way.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WordTechinc]

I try to get rows(job number list)returned from the procedure.

MessageBox.Show(dtJobs.Rows.Count) is show on screen "0"
Is there way to get job number list while the other SP runnint?

 

[djj55]

Try added the line SET NOCOUNT ON; to your stored procedure and removing the RETURN. See if that helps. Also if you run

SELECT plm.jobcode [Job No],si.seminartitle [Seminar Title]
FROM (SELECT DISTINCT pm.jobNo jobcode FROM inr_mailinglist pm) plm
INNER JOIN (SELECT js.jobno, sm.seminartitle
FROM inr_seminarmaster sm
INNER JOIN inr_jobshedule js
ON sm.seminarid =js.seminarid) si
ON plm.jobcode =si.jobno
ORDER BY plm.jobcode

in SSMS does it return multiple rows?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[WordTechinc]

If only one user run this SP, it fill up job number list and correct. If second user run different SP on different PC and different screen same application same time (takes at least 30 minutes and use same table), the first user doesn't get Job Numbers after run this SP. I altered this SP with Print "Test" to see this SP get executed, but I didn't get "Test" return with One user only and Two users . I tryied to see this SP get executed. I am newbie SQL and visual.net.