data is not inserting in data base ,using c# , where is the mistake..

Question

0.00/5 (No votes)

See more:

it is not inserting the data where i am doing the mistak... , its showing catch error message

C#

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

                          }

[edit]Code block added - OriginalGriff[/edit]

Posted 30-Jun-12 22:06pm

Mohammed Abdul Muqeet

Updated 30-Jun-12 22:11pm

OriginalGriff

v2

Add a Solution

2 solutions

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)

OriginalGriff · Answer 1 · 2012-06-30T22:15:00

Solution 2

We can't tell for the same reason you can't: Your code hides the error.
Change the catch block:

C#

catch (Exception ex)
   {
   Message("Block 3: Error\nThe Reported fault is:\n" + ex.Message, this);
   }

That way, if the error message does not give you enough information, you can put a break point on the exception and view the detail.

Posted 30-Jun-12 22:15pm

OriginalGriff

Comments

Mohammed Abdul Muqeet 1-Jul-12 4:27am

@sandeep...i have changed the line to
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Transaction";
and i hae

Mohammed Abdul Muqeet 1-Jul-12 4:29am

@OriginalGriff ..
i have pasted your lines in my code it is not showing any message , or error ,from this lines its going to catch cmd.ExecuteNonQuery();
con.Close();

Mohammed Abdul Muqeet 1-Jul-12 4:33am

This is my error in execption ...
Procedure or function 'Transaction' expects parameter '@Patient', which was not supplied.

my stored procedure
ALTER PROCEDURE [dbo].[Transaction]
(

@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_id uniqueidentifier,
@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_id,report_type,Patient_no,Patient_ID_NO,idcount)values (@Patient,@E_TO,getdate(),@R_type,@User_id,@ReportType,@Patient_no,@Patient_ID_NO,@tempid)

END

OriginalGriff 1-Jul-12 4:45am

Take the spaces out!
The Parameter names are not trimmed before being passed to SQL:
cmd.Parameters.AddWithValue(" @Patient ", Patient_name);
Becomes:
cmd.Parameters.AddWithValue("@Patient", Patient_name);
You will need to do this for the others as well.

Mohammed Abdul Muqeet 1-Jul-12 4:51am

yes i did .. brother i have one more issue .. when a user login , how can i save the user name in my table , you can see in my stored procedure there is @User_id , i want to save the user name in my table with every transaction ...
because its showing this error now..
Procedure or function 'Transaction' expects parameter '@User_id', which was not supplied.

OriginalGriff 1-Jul-12 5:08am

You would have to add it as a parameter to the list in your C# code - at the moment you don't set it at all. So SQL (rightly) complains. :laugh:

Mohammed Abdul Muqeet 1-Jul-12 5:03am

?

Mohammed Abdul Muqeet 1-Jul-12 5:15am

it is a uniqueidentifier , i will change it to nvchar ,.. will this line of code save the username ? cmd.Parameters.AddWithValue("User_ID", Label1.Text = Session["name"].ToString ());

OriginalGriff 1-Jul-12 5:22am

NO! Don't change Guids to strings! Pass it the Guid - there is a constructor which takes a string if you only have it as a string in your C# for some strange reason.

Never convert data away from a sensible type to a generic one (particularly string) unless it is for presentation and you need a specific format.

And no, I suspect it won't - a "name" is unlikely to be a Guid or anything resembling a Guid. Look for where you use the userID elsewhere in your code, and get the code from that.

Mohammed Abdul Muqeet 1-Jul-12 5:30am

or should i change datatype of User_ID uniqueidentifier to User_name nvarchar in my table??

Mohammed Abdul Muqeet 1-Jul-12 5:37am

tell me brother waiting for your comment .. should i try it like that by changing datatype to nvarchar???

Mohammed Abdul Muqeet 1-Jul-12 5:49am

??????????

Mohammed Abdul Muqeet 1-Jul-12 6:10am

i tried and i can save the name in the table

OriginalGriff 1-Jul-12 6:18am

Yes, but that doesn't mean it works. If there is other code which uses the UserID to refer to other data, then putting a name in where it should be a unique ID that matches a user in a different table may mean you have just broken other code, but don't know it yet...:laugh:

Mohammed Abdul Muqeet 1-Jul-12 6:21am

i dint use that id any where ,,, previously i was using but i change the code for that that id....

how can i display the column in message box ?????? i want to display "idcount" in my message box or textbox with every transaction.

OriginalGriff 1-Jul-12 6:23am

If you aren't using it, get rid of it. Don't fill it with "wrong" information, and it will come back to bite you later on.
Use the MessageBox.Show method.

Mohammed Abdul Muqeet 1-Jul-12 6:37am

can you show me .... how to display it from sql table
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
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;

}
else
{
try
{

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);
string 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.AddWithValue("@User_name", Label1.Text = Session["name"].ToString ());
cmd.Parameters.Add("@idcount", SqlDbType.Decimal);
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 (Exception ex)
{
Message("Block 3: Error\nThe Reported fault is:\n" + ex.Message, this);
}

PatNo.Text = "";

OriginalGriff 1-Jul-12 6:48am

This is the last query on this that I will answer - you really should open new queries for each of these, as tehy are not related to the original.

Looking at your SP, it returns the value you want - so replace ExecuteNonQuery with ExecuteScalar and it should return an integer value you can display.

I think you need to go back and look at what you are doing - these are very basic questions for the level of code you are using.

Sandeep Mewara · Answer 2 · 2012-06-30T22:15:00

Solution 1

Is it even compiling?

For instance, this line:

C#

SqlCommand cmd = new SqlCommand;

Posted 30-Jun-12 22:15pm

Sandeep Mewara

Comments

Mohammed Abdul Muqeet 1-Jul-12 5:03am

?