Linq to SQL - Problem in Stored Procedure

Question

I have storeProcesdure name called as prc_GetID, it's return value (ClientID)

Here thr proc Code:

Create PROCEDURE [dbo].[prc_GetID] (@ID VARCHAR(20)) 
AS
SET NOCOUNT ON;
SELECT ClientID FROM dbo.Clients WHERE [ID]=@ID

I use above STORED PROCEDURE with linq to sql, here's Code,

Public Class Form2
   Dim dcClients As New dcDataContext(CNString)

         Private Sub btnClick_Click(ByVal sender As Object, _
                          ByVal e As System.EventArgs) Handles btnClick.Click

           Dim CID = dcClients.prc_P(txtID.Text)
           txtClientID.Text = CID.ReturnValue

        End Sub
End Class

---

Here's My dcDataContext Code

---

     <FunctionAttribute(Name:="prc_GetID")>  _
 Public Function prc_GetID(<Parameter(Name:="ID", DbType:="VarChar(20)")> _
                   ByVal ID As String) As ISingleResult(Of prc_GetIDResult)
  Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, _
                         CType(MethodInfo.GetCurrentMethod,MethodInfo), ID)
  Return CType(result.ReturnValue,ISingleResult(Of prc_GetIDResult))
 End Function

but it's return value 0 Why?,

Answer 1

You probably should be examining CID.ClientID, not CID.ReturnValue. ReturnValue is the response code of the stored procedure, not the returned data you're looking to find. If you don't have a ClientID property you may need to remap your stored procedure to get the proper return type visible to your app.

Correction applied to account for multiple results (wasn't thinking):

Your stored procedure will return multiple records. Do you want the first record returned? If so and you will always have at least one row returned, try this:

txtClientID.Text = CID.First().ClientID.ToString()

In reality you need to think about your multiple records or no records returned and deal with it appropriately. Also, please don't continue to do your data access from within a form; it's good practice to create a library/business layer project to keep business logic out of your UI.

Answer 2

Are you confusing the return value with the result set?