July 30, 2012 at 5:46 am
my stored procedure. its used to insert the data and increment idcount by one till the end of the year when the next year starts it will change to 1 again.
then i got requirement to add 301A before idcount number so that it should save and display like this
301A1
301A2
301A3
so on till the year ends
.
.
301A100000
when next year starts
301A1
.
.
.
[font="Comic Sans MS"]i am not getting how should i add 301A before idcount number and save it to new column to display in message box with every transaction [/font]
ALTER PROCEDURE [dbo].[Transaction]
(
@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_name nvarchar(50),
@ReportType int,
@Patient_no int,
@Patient_ID_NO numeric(18,0),
@idcount numeric(18,0) output
)
AS
BEGIN
declare @tempid numeric(18,0)
set @tempid = 0;
declare @idcnt numeric(18,0)
select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
if (@idcnt =0)
set @tempid=1
else
set @tempid = @idcnt +1
INSERT INTO dbo.Transactions (Patient,E_TO,R_date,R_from,User_name,report_type,Patient_no,Patient_ID_NO,idcount)
values
(@Patient,@E_TO,getdate(),@R_type,@User_name,@ReportType,@Patient_no,@Patient_ID_NO,@tempid)
select @idcount =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
return @idcount
end
C# code
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Transaction";
cmd.Parameters.AddWithValue("@Patient", Patient_name);
cmd.Parameters.AddWithValue("@E_TO", Export_TO);
cmd.Parameters.AddWithValue("@R_type", reptype);
cmd.Parameters.AddWithValue("@ReportType", replay_To_type);
cmd.Parameters.AddWithValue("@Patient_no", PatNoVal);
cmd.Parameters.AddWithValue("@Patient_ID_NO", PatID);
cmd.Parameters.AddWithValue("@User_name", Label1.Text = Session["name"].ToString ());
cmd.Parameters.Add("@idcount", SqlDbType.NvarChar, 50);
cmd.Parameters["@idcount"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@idcount", SqlDbType.NVarChar, 50);
cmd.Parameters["@idcount"].Direction = ParameterDirection.Output;
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();
TextBox1.Text = cmd.Parameters ["@idcount"].Value.ToString();
ClientScriptManager cs = Page.ClientScript;
cs.RegisterStartupScript(this.GetType(), "IDCount", "alert('YourID is ID :" + cmd.Parameters["@idcount"].Value.ToString() + "');", true);
July 30, 2012 at 6:01 am
Total guess, as I can't see the app code you have in front of you, nor the data, nor do I fully understand your requirements. You may be better off asking one of the more senior developers that you work with,
ALTER PROCEDURE [dbo].[Transaction] (
@Patient NVARCHAR(50), @E_TO NVARCHAR(50), @R_type INT, @User_name NVARCHAR(50), @ReportType INT,
@Patient_no INT, @Patient_ID_NO NUMERIC(18, 0), @idcount VARCHAR(22) OUTPUT)
AS
BEGIN
DECLARE @tempid NUMERIC(18, 0);
SET @tempid = 0;
DECLARE @idcnt NUMERIC(18, 0);
SELECT @idcnt = isnull(max(idcount), 0)
FROM dbo.Transactions
WHERE R_date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()), 0) AND
R_date < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0);
IF (@idcnt = 0)
BEGIN
SET @tempid = 1;
END
ELSE BEGIN
SET @tempid = @idcnt + 1;
END
INSERT INTO dbo.Transactions (Patient, E_TO, R_date, R_from, User_name, report_type, Patient_no, Patient_ID_NO, idcount)
VALUES (@Patient, @E_TO, getdate(), @R_type, @User_name, @ReportType, @Patient_no, @Patient_ID_NO, @tempid);
SELECT @idcount = isnull(max(idcount), 0)
FROM dbo.Transactions
WHERE R_date >= DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()), 0) AND
R_date < DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0);
RETURN '301A' + @idcount
END
July 30, 2012 at 6:33 am
the way i've always handled this situation is to continue to use an identity field, but adding a persisted calculated field that creates the desired alphanumeric based on a concatenation of the formula + ID.
I'm not sure if it was just a copy paste error, but are you saying the first value each year is 301A1 so it is not unique?
Lowell
July 30, 2012 at 6:44 am
getting this error in ExecuteNonQuery();
Conversion failed when converting the varchar value '301A1' to data type int
idcount ic numeric(18,0) in table
C# CODE
try
{
string Patient_name = NameTxtBx.Text, Export_TO = ToTxtBx0.Text ;
int PatNoVal;
PatNoVal = Convert.ToInt32(PatNo.Text);
PatNoVal = int.Parse(PatNo.Text);
decimal PatID = decimal.Parse(PatID_NO.Text);
int? replay_To_type = Int16.Parse(DropDownList1.SelectedValue);
int? reptype = Int16.Parse(RadioButtonList2.SelectedValue);
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Transaction";
cmd.Parameters.AddWithValue("@Patient", Patient_name);
cmd.Parameters.AddWithValue("@E_TO", Export_TO);
cmd.Parameters.AddWithValue("@R_type", reptype);
cmd.Parameters.AddWithValue("@ReportType", replay_To_type);
cmd.Parameters.AddWithValue("@Patient_no", PatNoVal);
cmd.Parameters.AddWithValue("@Patient_ID_NO", PatID);
cmd.Parameters.AddWithValue("@User_name", Label1.Text = Session["name"].ToString ());
cmd.Parameters.Add("@idcount", SqlDbType.Decimal);
cmd.Parameters["@idcount"].Direction = ParameterDirection.Output;
cmd.Connection = con;
cmd.ExecuteScalar();
con.Close();
TextBox1.Text = cmd.Parameters ["@idcount"].Value.ToString();
ClientScriptManager cs = Page.ClientScript;
cs.RegisterStartupScript(this.GetType(), "IDCount", "alert('YourID is ID :" + cmd.Parameters["@idcount"].Value.ToString() + "');", true);
}
catch (Exception ex)
{
Message("Block 3: The Reported fault is:" + ex.Message, this);
}
PatNo.Text = "";
NameTxtBx.Text = "";
ToTxtBx0.Text = "";
DropDownList1.SelectedIndex = -1;
RadioButtonList2.SelectedIndex = -1;
}
catch
{
Message("ERROR ", this);
}
}
}
catch
{
Message("Enter all fields ", this);
}
July 30, 2012 at 6:54 am
This is looking for a decimal -->
cmd.Parameters.Add("@idcount", SqlDbType.Decimal);
You want to return a string.
cmd.Parameters.Add("@idcount", SqlDbType.VarChar, 22);
I'd like to suggest again that you talk to one of the more senior developers in your workplace. You're going to run into problems if you attempt to guess like this.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply