如何在SQL到ASP.NET中显示自动生成的字母数字字段 [英] How to display autogenerated alphanumeric field from SQL to ASP.NET

问题描述

这是我的表

this is my table

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希望将我自动生成的Voucher_No字段链接到前端（asp.net）这是html文本框



我尝试过：



这是我的前端代码：

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:

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";

推荐答案

将您的代码更改为



SQL：

Change your code as

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;

这篇关于如何在SQL到ASP.NET中显示自动生成的字母数字字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！