I want to insert a record in DB and then need to return a row . I created the query but how to use that in .cs (C# code )

Question

0.00/5 (No votes)

See more:

, +

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 !!!!!

SQL

 @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 18:40pm

Cop Cybrosys

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Answer 1 · 2013-04-22T19:14:00

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!
:)

Posted 22-Apr-13 19:14pm

Aarti Meswania

Naz_Firdouse · Answer 2 · 2013-04-22T19:17:00

Solution 2

Here is a sample...
http://msdn.microsoft.com/en-US/library/37hwc7kt(v=vs.80).aspx[^]

http://stackoverflow.com/questions/4492536/how-run-a-stored-procedure-with-parameters-has-a-return-value-from-code-behi[^]

Posted 22-Apr-13 19:17pm

Naz_Firdouse

Updated 22-Apr-13 19:21pm

v2

TrushnaK · Answer 3 · 2013-04-22T19:38:00

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();  
 }