Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL .NET SQL-Server , +
i want to insert the data to a table and i want to return scope_identity() and value of inserted voucherNumber or ( UpdatedVoucherNo ) .. i got the query but i want to know how can i implement it on Code (c# code ) .. i cant use execute scalar because it only return a single value
right ??
 
so i want to insert and i need to return a row to code HOW ??? pls help me...
 
below is my query.. it is working correctly but i want to know to to use it in code !!!!!
 

 @ledgerId numeric(18,0) ,
 @voucherNo varchar(MAX) ,
 @invoiceNo varchar(MAX) ,
 @date datetime ,
 @month datetime ,
 @totalAmount decimal(18,5) ,
 @narration varchar(MAX) ,
-- @extraDate datetime ,
 @extra1 varchar(MAX) ,
 @extra2 varchar(MAX) ,
 @suffixPrefixId numeric(18,0) ,
 @voucherTypeId numeric(18,0) 
AS
DECLARE @UpdatedVoucherNo DECIMAL(18,0)
 
SET @UpdatedVoucherNo = (SELECT ISNULL( MAX(CAST (voucherNo AS NUMERIC(18,0))),0) + 1
 FROM tbl_SalaryVoucherMaster
 WHERE voucherTypeId=@voucherTypeId)
 
 IF (@UpdatedVoucherNo = @voucherNo )
 
 INSERT INTO tbl_SalaryVoucherMaster
 (
            /*salaryVoucherMasterId,*/
            ledgerId,
            voucherNo,
            invoiceNo,
            date,
            month,
            totalAmount,
            narration,
            extraDate,
            extra1,
            extra2,
            suffixPrefixId,
            voucherTypeId )
VALUES
 (
            /*@salaryVoucherMasterId, */
            @ledgerId, 
            @voucherNo, 
            @invoiceNo, 
            @date, 
            @month, 
            @totalAmount, 
            @narration, 
            getDate(), 
            @extra1, 
            @extra2,
            @suffixPrefixId,
            @voucherTypeId)
 
            SELECT SCOPE_IDENTITY() AS [Identity],0 AS [UpdatedVoucherNo]  
Posted 22-Apr-13 19:40pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

you can use data-adapter and retrieve data in datatable for SP you have written.
 
OR
 
Write SP with output parameter
see example
http://www.daniweb.com/software-development/csharp/threads/110318/getting-output-values-back-from-sql-stored-procedure#[^]
 
Happy Coding!
Smile | :)
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

String ConnString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password";  
 SqlConnection con = new SqlConnection(ConnString);  
 SqlCommand cmd = new SqlCommand();  
 cmd.CommandType = CommandType.StoredProcedure;  
 cmd.CommandText = "SP_Name";  
 
//Add Your Parameters here

 cmd.Parameters.Add("@FName",SqlDbType.VarChar).Value = txtFName.Text.Trim();  
 cmd.Parameters.Add("@LName", SqlDbType.VarChar).Value = txtLName.Text.Trim();  
 cmd.Parameters.Add("@DateOfBirth", SqlDbType.DateTime).Value = txtDOB.Text.Trim();  
 cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();  
 cmd.Parameters.Add("@State", SqlDbType.VarChar).Value = txtState.Text.Trim();  
 cmd.Connection = con;  
 try  
 {  
      con.Open();  
      int rows = cmd.ExecuteNonQuery();
                if (rows > 0)
                {
                    //do anything
                }
 }  
 catch (Exception ex)  
 {  
      throw ex;  
 }  
 finally  
 {  
      con.Close();  
      con.Dispose();  
 } 
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 304
1 Sergey Alexandrovich Kryukov 255
2 Shweta N Mishra 216
3 Maciej Los 210
4 PIEBALDconsult 184
0 OriginalGriff 7,630
1 Sergey Alexandrovich Kryukov 7,022
2 DamithSL 5,586
3 Manas Bhardwaj 4,946
4 Maciej Los 4,525


Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 23 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100