where is my mistake ,,. data is not inserting in the database : The Official Microsoft ASP.NET Forums

Reply

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

                            }

Moved by Mikesdotnetting on Jul 01, 2012 08:41 AM

Reply

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

Edited by bammidi on Jul 01, 2012 08:57 AM

Reply

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

Reply

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.

Reply

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

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 = "";
                        NameTxtBx.Text = "";
                        ToTxtBx0.Text = "";
                        DropDownList1.SelectedIndex = -1;
                        RadioButtonList2.SelectedIndex = -1;
                 }
                    catch
                    {
                        Message(" ERROR 20", this);
                    }

                }
            }
            catch
            {
                Message("Please enter all the fields ", this);
            }
        }
      protected void DropDownList1_DataBound(object sender, EventArgs e)
        {
            DropDownList1.Items.Insert(0, "--Select One--");
        }

    }
}

Reply

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

For displaying the code you are returning it as @idcount so mention as @idcount in place of @RETURNOUT

remaining code is working fine

 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 mentioned

Please mark as answer if it is correct.

Edited by bammidi on Jul 01, 2012 11:17 AM
Edited by bammidi on Jul 01, 2012 11:34 AM

Reply

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

Reply

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.

Marked as answer by Abdul Muqeet on Jul 01, 2012 01:47 PM

Reply

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

Reply

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

EXEC [Transaction]

@Patient ='5677',
@E_TO ='your',
@R_type =45,
@User_name ='sample'
@ReportType ='type1',
@Patient_no =5556,
@Patient_ID_NO =234234,
@idcount =0



select * from Transactions


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

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.

Edited by bammidi on Jul 01, 2012 12:30 PM
Edited by bammidi on Jul 01, 2012 12:30 PM
Edited by bammidi on Jul 01, 2012 12:30 PM

where is my mistake ,,. data is not inserting in the database RSS

23 replies

Abdul Muqeet

where is my mistake ,,. data is not inserting in the database

bammidi

Re: where is my mistake ,,. data is not inserting in the database

Abdul Muqeet

Re: where is my mistake ,,. data is not inserting in the database

bammidi

Re: where is my mistake ,,. data is not inserting in the database

Abdul Muqeet

Re: where is my mistake ,,. data is not inserting in the database

bammidi

Re: where is my mistake ,,. data is not inserting in the database

Abdul Muqeet

Re: where is my mistake ,,. data is not inserting in the database

bammidi

Re: where is my mistake ,,. data is not inserting in the database

Abdul Muqeet

Re: where is my mistake ,,. data is not inserting in the database

bammidi

Re: where is my mistake ,,. data is not inserting in the database

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft