How to display autogenerated alphanumeric field from SQL to ASP.NET

Question

0.00/5 (No votes)

See more:

this is my table

SQL

create table tbl_SalesOrder (SalesOrder_id int primary key not null identity(1,1),
Voucher_No  AS 'S' + RIGHT('0'+ CONVERT(VARCHAR(5),SalesOrder_id),5),
Account_No int,
Customer_Name varchar(50),
Address varchar(50),
SalesTax_Type varchar(50),
Date varchar(50),
Store_Name int,
Currency int )

i want to link my autogenerated Voucher_No field to front end (asp.net) which is html textbox

What I have tried:

this is my front end code:

C#

protected void btnSubmit_Click(object sender, EventArgs e)
      {
          string strcon = System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ToString();

      
          String insert, VoucherNumber, AccountNumber, CustomerName, Address, SalesTaxType, Date, StoreName, Currency;

      

          SqlConnection con = new SqlConnection(strcon);
          con.Open();
          SqlCommand com = new SqlCommand("spr_SalesOrder", con);
          insert = "insert";
          com.CommandType = CommandType.StoredProcedure;
          com.Parameters.AddWithValue("@abc", insert);

          VoucherNumber = txtVoucherNo1.Value.ToString();
          AccountNumber = ddAccountNumber1.Text;
          CustomerName = txtCustomerName1.Text;
          Address = txtAddress1.Text;
          SalesTaxType = ddSalesTaxType1.Value;
          Date = txtDateTime1.Value;
          StoreName = ddStoreName1.Text;
          Currency = ddCurrency1.Text;

        SqlParameter p1 = new SqlParameter("Voucher_No", VoucherNumber);
     
          SqlParameter p2 = new SqlParameter("Account_No", AccountNumber);
          SqlParameter p3 = new SqlParameter("Customer_Name", CustomerName);
          SqlParameter p4 = new SqlParameter("Address", Address);
          SqlParameter p5 = new SqlParameter("SalesTax_Type", SalesTaxType);
          SqlParameter p6 = new SqlParameter("Date", Date);
          SqlParameter p7 = new SqlParameter("Store_Name", StoreName);
        SqlParameter p8 = new SqlParameter("Currency", Currency);

          com.Parameters.Add(p1).Direction=ParameterDirection.Output;
          com.Parameters.Add(p2);
          com.Parameters.Add(p3);
          com.Parameters.Add(p4);
          com.Parameters.Add(p5);
          com.Parameters.Add(p6);
          com.Parameters.Add(p7);
        com.Parameters.Add(p8);

          com.ExecuteNonQuery();
 
          con.Close();


     
          lblSaleOrder.Text = "Registered Successfully";

Posted 24-Apr-16 20:38pm

Member 12385326

Updated 24-Apr-16 22:24pm

Suvendu Shekhar Giri

v2

Add a Solution

Comments

Suvendu Shekhar Giri 25-Apr-16 2:44am

What is the problem here?

Member 12385326 25-Apr-16 2:47am

it is not displaying autogenerated voucher_no from backend to front end

Karthik_Mahalingam 25-Apr-16 3:14am

post only relevant code for displaying

Suvendu Shekhar Giri 25-Apr-16 3:34am

Seems, you have mixed up many things. This should be a very simple task.
Can you share the stored procedure spr_SalesOrder, then we'll try to provide the error fix and correct approach.

Things to look:
1. Voucher_No AS 'S' + RIGHT('0'+ CONVERT(VARCHAR(5),SalesOrder_id),5)
>> is it a computed column?
2. SqlParameter p1 = new SqlParameter("Voucher_No", VoucherNumber);
>> what is the use of sending to the stored proc if it is a computed column?

Please share the stored proc.
Thanks

Member 12385326 25-Apr-16 3:51am

yes it is computed column.it is computing S01,S02....... like this.

Member 12385326 25-Apr-16 3:55am

This is my stored procedure:
ALTER proc [dbo].[spr_SalesOrder]
(
--@SalesOrder_Id int =null,
@Voucher_No varchar(50)=null,
@Account_No varchar(50)=null,
@Customer_Name varchar(50)=null,
@Address varchar(50)=null,
@PurchaesTax_Type varchar(50)=null,
@Date varchar(50)=null,
@Store_Name varchar(50)=null,
@Currency varchar(50)=null,
@abc varchar(20) =null
)
AS
BEGIN
BEGIN
set nocount on;
DECLARE @SalesOrder_Id int
IF @abc='insert'
insert into tbl_PurchaseRegister(Account_No,customer_Name,Address,SalesTax_Type,Date,Store_Name,Currency)
Values(@Account_No,@Customer_Name,@Address,@SalesTax_Type,@Date,@Store_Name,@Currency)
SET @SalesOrder_Id = SCOPE_IDENTITY();
END
END
select * from tbl_SalesOrder

Member 12385326 25-Apr-16 3:48am

protected void btnSubmit_Click(object sender, EventArgs e)
{
string strcon = System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ToString();

String insert, VoucherNumber, AccountNumber, CustomerName, Address, SalesTaxType, Date, StoreName, Currency;

SqlConnection con = new SqlConnection(strcon);
con.Open();
SqlCommand com = new SqlCommand("spr_SalesOrder", con);
VoucherNumber = txtVoucherNo1.Value.ToString();

SqlParameter p1 = new SqlParameter("Voucher_No", VoucherNumber);
com.Parameters.Add(p1).Direction=ParameterDirection.Output;

com.ExecuteNonQuery();

con.Close();

lblSaleOrder.Text = "Registered Successfully";

Karthik_Mahalingam 25-Apr-16 4:08am

post your code for binding the data to screen( textbox control )

Member 12385326 25-Apr-16 4:20am

void Filltxt()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select voucher_No from tbl_PurchaseRegister", con);
SqlDataReader dap = cmd.ExecuteReader();

while (dap.Read())
{
txtVoucherNo1.Value= dap.GetValue(0).ToString();

}

i guess am making here small mistake.because it is retriving last inserted voucher no from database and displaying it in my asp.net form.an u please help me out

Karthik_Mahalingam 25-Apr-16 4:25am

no need to write this code.. since you are using output value
checkout my solution.

1 solution

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)

Karthik_Mahalingam · Accepted Answer · 2016-04-24T22:24:00

Solution 1

Change your code as

SQL:

SQL

ALTER proc [dbo].[spr_SalesOrder]
(
 
@Voucher_No varchar(50) output , -- output keyword for out params
@Account_No varchar(50)=null,
@Customer_Name varchar(50)=null,
@Address varchar(50)=null,
@PurchaesTax_Type varchar(50)=null,
@Date varchar(50)=null,
@Store_Name varchar(50)=null,
@Currency varchar(50)=null,
@abc varchar(20) =null
)
AS
 
BEGIN
set nocount on;
DECLARE @SalesOrder_Id int
IF @abc='insert' 
insert into tbl_PurchaseRegister(Account_No,customer_Name,Address,SalesTax_Type,Date,Store_Name,Currency) 
Values(@Account_No,@Customer_Name,@Address,@SalesTax_Type,@Date,@Store_Name,@Currency)
SET @SalesOrder_Id =  SCOPE_IDENTITY();
select @Voucher_No  =  Voucher_No from tbl_PurchaseRegister where SalesOrder_id = @SalesOrder_Id
END

c#

C#

SqlParameter paramVoucher = new SqlParameter("@Voucher_No", SqlDbType.VarChar, 50);
           paramVoucher.Direction = ParameterDirection.Output;  //you should provide direction for out param
           com.Parameters.Add(paramVoucher);
           com.ExecuteNonQuery();
           string voucherNumber = paramVoucher.Value.ToString();
           lblSaleOrder.Text = voucherNumber;

Posted 24-Apr-16 22:24pm

Karthik_Mahalingam

Comments

Member 12385326 25-Apr-16 4:38am

thnx for ur help.the code is working if i take lable to display it. but i am taking textbox. and it is not showing text property.what to do?

Karthik_Mahalingam 25-Apr-16 4:41am

write
YourTextBoxName.Text = voucherNumber;

Member 12385326 25-Apr-16 4:43am

i tried this. but it is not suggesting text property.

Member 12385326 25-Apr-16 4:46am

i used textboxname.value :) thnx a lot for ur help

Karthik_Mahalingam 25-Apr-16 4:47am

welcome :)

Member 12385326 25-Apr-16 7:12am

again i am in trouble. the code u suggested is working fine. but i want that voucher_no should be generated when page is loaded. and should get submited when on button clicked. i tried this by writing it in page_load but it is inserting data twice or sometimes it is null :(

Karthik_Mahalingam 25-Apr-16 8:06am

write inside if(!page.ispostback)

Member 12385326 25-Apr-16 8:42am

i wrote inside if(!page.ispostback) only.
protected void Page_Load(object sender, EventArgs e)
{

if (!Page.IsPostBack)
{
Filltxt();
}
}

void filltxt()
{
string strcon = System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ToString();
String insert, AccountNumber, VoucherNumber,VendorName, Address, PurchaseTaxType, Date, StoreName, Currency;
SqlConnection con = new SqlConnection(strcon);
con.Open();
SqlCommand com = new SqlCommand("spr_PurchaseRegister", con);
insert = "insert";
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@abc", insert);

SqlParameter p1 = new SqlParameter("@Voucher_No", SqlDbType.VarChar, 50);

p1.Direction = ParameterDirection.Output;

com.ExecuteNonQuery();
con.Close();

string voucherNumber = p1.Value.ToString();
txtVoucherNo1.Value = voucherNumber;

lblPurchaseRegister.Text = "registered successfully";

}

Karthik_Mahalingam 25-Apr-16 8:47am

why you writing inside page load.

write that in button click event only..

Member 12385326 25-Apr-16 8:52am

becasue i want voucher_no should get generate when we open webpage. if i write it inside button click then it displays voucher_no when we submit our data

Karthik_Mahalingam 25-Apr-16 8:53am

after submitting what is the issue?
the value will be still in the page control only.

Member 12385326 25-Apr-16 9:01am

when we submit that time only it displays voucher_no. user should get to see voucher_no as soon as he opens the web page

Karthik_Mahalingam 25-Apr-16 9:06am

the same page right?
or he will open another page ??
what is the requirement, i didnt get stil.

Member 12385326 26-Apr-16 1:17am

i have menu "sales order" .when user clicks on that he should see sales order page with voucher_no generated on it automatically. then he will fill the details and will click on submit button

Karthik_Mahalingam 26-Apr-16 2:28am

pass the voucher number as query string to the sales order page..
since you have voucer number in the current page.