Stored procedure error when use computed column for ID
- by Hari
I got the error:
Procedure or function usp_User_Info3 has too many arguments specified
When I run the program. I don't know the error in SP or in C# code. I have to display the Vendor_ID after the user clicks the submit button. Where the thing going wrong here ?
Table structure :
CREATE TABLE User_Info3
(
SNo int Identity (2000,1) ,
Vendor_ID AS 'VEN' + CAST(SNo as varchar(16)) PERSISTED PRIMARY KEY,
UserName VARCHAR(16) NOT NULL,
User_Password VARCHAR(12) NOT NULL,
User_ConPassword VARCHAR(12) NOT NULL,
User_FirstName VARCHAR(25) NOT NULL,
User_LastName VARCHAR(25) SPARSE NULL,
User_Title VARCHAR(35) NOT NULL,
User_EMail VARCHAR(35) NOT NULL,
User_PhoneNo VARCHAR(14) NOT NULL,
User_MobileNo VARCHAR(14)NOT NULL,
User_FaxNo VARCHAR(14)NOT NULL,
UserReg_Date DATE DEFAULT GETDATE()
)
Stored Procedure :
ALTER PROCEDURE [dbo].[usp_User_Info3]
@SNo INT OUTPUT,
@Vendor_ID VARCHAR(10) OUTPUT,
@UserName VARCHAR(30),
@User_Password VARCHAR(12),
@User_ConPassword VARCHAR(12),
@User_FirstName VARCHAR(25),
@User_LastName VARCHAR(25),
@User_Title VARCHAR(35),
@User_OtherEmail VARCHAR(30),
@User_PhoneNo VARCHAR(14),
@User_MobileNo VARCHAR(14),
@User_FaxNo VARCHAR(14)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO User_Info3 (UserName,User_Password,User_ConPassword,User_FirstName,
User_LastName,User_Title,User_OtherEmail,User_PhoneNo,User_MobileNo,User_FaxNo)
VALUES (@UserName,@User_Password,@User_ConPassword,@User_FirstName,@User_LastName,
@User_Title,@User_OtherEmail,@User_PhoneNo,@User_MobileNo,@User_FaxNo)
SET @SNo = Scope_Identity()
SELECT Vendor_ID From User_Info3 WHERE SNo = @SNo
END
C# Code :
protected void BtnUserNext_Click(object sender, EventArgs e)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "usp_User_Info3";
System.Data.SqlClient.SqlParameter SNo=cmd.Parameters.Add("@SNo",System.Data.SqlDbType.Int);
System.Data.SqlClient.SqlParameter Vendor_ID=cmd.Parameters.Add("@Vendor_ID",
System.Data.SqlDbType.VarChar,10);
cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = txtUserName.Text;
cmd.Parameters.Add("@User_Password", SqlDbType.VarChar).Value = txtRegPassword.Text;
cmd.Parameters.Add("@User_ConPassword", SqlDbType.VarChar).Value = txtRegConPassword.Text;
cmd.Parameters.Add("@User_FirstName", SqlDbType.VarChar).Value = txtRegFName.Text;
cmd.Parameters.Add("@User_LastName", SqlDbType.VarChar).Value = txtRegLName.Text;
cmd.Parameters.Add("@User_Title", SqlDbType.VarChar).Value = txtRegTitle.Text;
cmd.Parameters.Add("@User_OtherEmail", SqlDbType.VarChar).Value = txtOtherEmail.Text;
cmd.Parameters.Add("@User_PhoneNo", SqlDbType.VarChar).Value =txtRegTelephone.Text;
cmd.Parameters.Add("@User_MobileNo", SqlDbType.VarChar).Value =txtRegMobile.Text;
cmd.Parameters.Add("@User_FaxNo", SqlDbType.VarChar).Value =txtRegFax.Text;
cmd.Connection = SqlCon;
try
{
Vendor_ID.Direction = System.Data.ParameterDirection.Output;
SqlCon.Open();
cmd.ExecuteNonQuery();
string VendorID = cmd.ExecuteScalar() as string;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
string url = "../CompanyBasicInfo.aspx?Parameter=" + Server.UrlEncode(" + VendorID + ");
SqlCon.Close();
}
}
