how to get the auto generated values from sql using c#?

Question

I have an Stored Procedure like below..

        CREATE PROCEDURE [dbo].[spMYSPDetails]    
 @Id VarChar(6),    
 @Name VarChar(50),    
 @Limit Money,    
 @Status VarChar(1),    
 @Accounts As SmallInt    
AS BEGIN    

 IF(LEN(ISNULL(@strDebtorId, '')) = 0) BEGIN    

  WHILE 0 = 0 BEGIN    

   SET @Id = '9' + dbo.fnGenerateRandomCode(5, '0123456789')    

   IF NOT EXISTS (SELECT 1 FROM mytbl WITH (NOLOCK) WHERE s_Id = @Id) BEGIN    

    INSERT INTO Mytbl     
     (D_Id,    
     D_Name,    
     D_Limit,    
     D_Status,    
     D_Accounts,    
     D_dtmStamp,    
     D_Balance)    
    VALUES    
     (@Id,     
     @Name,    
     @Limit,    
     @Status,    
     @Accounts,    
     GETDATE(),    
     @Limit)    

     BREAK   

   END

I have c# sharp code to call this SP like this...

try
            {
                string Id = "";
                if (hdDId.Value.ToString() != "")
                {
                    Id = hdDId.Value.ToString();
                }

                objDB.blnParamClear();
                objDB.blnParamAdd(ParameterDirection.Input, "@Id", SqlDbType.VarChar, 6, Id.ToString());
                objDB.blnParamAdd(ParameterDirection.Input, "@Name", SqlDbType.VarChar, 50, txtName.Value.ToString());
                objDB.blnParamAdd(ParameterDirection.Input, "@Limit", SqlDbType.Money, 8, decimal.Parse(txtLimit.Value.ToString()));
                objDB.blnParamAdd(ParameterDirection.Input, "@Status", SqlDbType.VarChar, 1, cboStatus.Value);
                objDB.blnParamAdd(ParameterDirection.Input, "@intMaxAccounts", SqlDbType.SmallInt, 4, txtAccnts.Value.ToString());

                blnResult = (objDB.lngExecuteSP("spMYSPDetails") == 0);

  }

Now I want to catch that auto generated(random) ID in C# for my reference. I tried but am unable to Catch.. Is there any way to catch the same id that is generated by SP in SQL.Suggest me something...

"Catch"? Try to use `objDB.blnParamAdd(ParameterDirection.InputOutput, "@Id" ...)`, though your procedure generates ID in a very, very wrong way. — Joker_vD, May 13 '13 at 08:52
Please Try this with little implement http://stackoverflow.com/questions/19868976/randomly-select-a-row-with-sql-in-access/19873357#19873357 — code save, Nov 09 '13 at 07:07

score 2 · Accepted Answer · answered May 13 '13 at 08:59

2

Use your @ID as output variable.

@Id VarChar(6) output

Then set your C# parameter

SqlParameter paramOutput = new SqlParameter("@Output", SqlDbType.nVarChar); paramOutput.Direction = ParameterDirection.Output;

answered May 13 '13 at 08:59

KuldipMCA

3,079
7
28
48

score 1 · Answer 2 · answered May 13 '13 at 08:52

Stored procedures can have return values and output parameters in addition to input parameters.

Use an Output Parameter.

Change ParameterDirection.Input to ParameterDirection.Output when defining a param "@Id".

PS. What are you passing right now to the sproc (Id.ToString()) if the id is generated in the DB? Why aren't you just using autoincrement column?

how to get the auto generated values from sql using c#?

2 Answers2