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)
return @idcount
END
this is my code
try { if(NameTxtBx.Text==""||ToTxtBx0.Text==""||DropDownList1.SelectedIndex==0||RadioButtonList2.SelectedIndex==-1||(PatID_NO.Text=="")||(Convert.ToUInt64(PatNo.Text)<=0)||(Convert.ToUInt64(PatNo.Text)>200000000)) { Message("Please check the field u entered",this); return;
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
Abdul Muqeet
Member
82 Points
432 Posts
where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 08:00 AM|LINK
it is not inserting the data where i am doing the mistak... , its showing catch error message
string Patient_name = NameTxtBx.Text, Export_TO = ToTxtBx0.Text, repType = RadioButtonList2.SelectedValue ;
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 idcount;
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
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.Add("@idcount", SqlDbType.Decimal);
//idcount.Precision = 18;
//idcount.Scale = 0;
cmd.Parameters["@idcount"].Direction = ParameterDirection.Output;
cmd.Connection = con;
cmd.ExecuteNonQuery();
con.Close();
TextBox1.Text = cmd.Parameters["@RETURNOUT"].Value.ToString();
ClientScriptManager cs = Page.ClientScript;
cs.RegisterStartupScript(this.GetType(), "IDCount", "alert('"+cmd.Parameters["@RETURNOUT"].Value.ToString()+"');", true);
}
catch
{
Message("block 3:Error", this);
}
bammidi
Member
402 Points
216 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 08:49 AM|LINK
Hi,
While declaring the sqlcommand
as SqlCommand cmd = new SqlCommand();
it is declared as
SqlCommand cmd = new SqlCommand;
Please fix it and check it once again.
And also Please declare the Catch
as
catch(Exception ex)
{
Response.Write(ex.Message.ToString()); // Check error here in this way.
}
}
If you still face error please post the error Message
Abdul Muqeet
Member
82 Points
432 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 09:13 AM|LINK
i did same like what you said . but i have User_ID in Stored procedure. i want to save it in my table with every transaction .
will this line save the user name in the table???????
cmd.Parameters.AddWithValue("User_ID", Label1.Text = Session["name"].ToString ());
bammidi
Member
402 Points
216 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 10:11 AM|LINK
Well,
Please tell me what is the error you are getting when inserting.
Abdul Muqeet
Member
82 Points
432 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 10:35 AM|LINK
the error was i dint use one column for inserting,,.
can you tell me how to display the code in text box or in message box.
my stored procedure
ALTER PROCEDURE [dbo].[Transaction]
(
@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_name nvarchar(50),
@ReportType nvarchar(50),
@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)
return @idcount
END
this is my code
bammidi
Member
402 Points
216 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 11:16 AM|LINK
cmd.Parameters.Add("@idcount", SqlDbType.Decimal); cmd.Parameters["@idcount"].Direction = ParameterDirection.Output; cmd.Connection = con; cmd.ExecuteNonQuery(); con.Close(); TextBox1.Text = cmd.Parameters["@idcount"].Value.ToString();// here mentioned ClientScriptManager cs = Page.ClientScript; cs.RegisterStartupScript(this.GetType(), "IDCount", "alert('" + cmd.Parameters["@idcount"].Value.ToString() + "');", true);//here mentionedPlease mark as answer if it is correct.
Abdul Muqeet
Member
82 Points
432 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 11:36 AM|LINK
its displaying nothing in the box
bammidi
Member
402 Points
216 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 12:00 PM|LINK
Hi
Well I forgot to mention
I have altered u r procedure
This line to be added at the last.
select @idcount =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
ALTER PROCEDURE [dbo].[Transaction]
(
@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_name nvarchar(50),
@ReportType nvarchar(50),
@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())
--print @idcount
return @idcount
END
Check it out and if u still have any issue let me know.
Abdul Muqeet
Member
82 Points
432 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 12:09 PM|LINK
nothing displayed
bammidi
Member
402 Points
216 Posts
Re: where is my mistake ,,. data is not inserting in the database
Jul 01, 2012 12:29 PM|LINK
Well
For me its working fine
I have checked in this way.
try { string strTextBox1 = ""; string Patient_name = "Bammidi", Export_TO = "US", repType = "1"; int PatNoVal; //PatNoVal = Convert.ToInt32("345"); PatNoVal = int.Parse("6777"); decimal PatID = decimal.Parse("1678"); int? replay_To_type = Int16.Parse("5"); string idcount; try { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["vinay"].ConnectionString); 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", "Hero"); cmd.Parameters.Add("@idcount", SqlDbType.Decimal); cmd.Parameters["@idcount"].Direction = ParameterDirection.Output; cmd.Connection = con; cmd.ExecuteNonQuery(); con.Close(); strTextBox1 = cmd.Parameters["@idcount"].Value.ToString(); ClientScriptManager cs = Page.ClientScript; cs.RegisterStartupScript(this.GetType(), "IDCount", "alert('" + cmd.Parameters["@idcount"].Value.ToString() + "');", true); } catch (Exception ex) { // Message("Block 3: Error\nThe Reported fault is:\n" + ex.Message, this); } //PatNo.Text = ""; //NameTxtBx.Text = ""; //ToTxtBx0.Text = ""; //DropDownList1.SelectedIndex = -1; //RadioButtonList2.SelectedIndex = -1; } catch (Exception ex) { // Message(" ERROR 20", this); }Sql related queries
I suggest u to check the Procedure once again which I have altered and check the value also in the sql Messages.
And after that do that debug and check what is the value u r sending.