CODEWITHSUNDEEP
c#asp.netsql-server-2008
Shreyas Achar
Shreyas Achar

Reputation: 1435

Data insertion to the database is not happening using asp.net wih c#

This is my aspx.cs page,these codes should insert the values into the database,but presently its not going good,its not inserting into database,i am not getting any errors but insertion is not happening,i have no idea what is going wrong.IS there an possibility of SqlException? or any other issues?

   protected void btnSkipSubmit_Click(object sender, EventArgs e)
{
    int random = 0;
    bool isValidInt = int.TryParse(txtrandom.Text, out random);
    //string dummmy = "D";
    //int dum = 0;
    Patient p = new Patient();

    //PatientBill pb = new PatientBill();
    myConnection obj1 = new myConnection();
    DateTime sdt = DateTime.Now;
   // string a;
   string str = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
   string cmdString = "";
   SqlConnection con = new SqlConnection(str);
   SqlCommand cmd = new SqlCommand(cmdString, con);
   SqlTransaction transaction;

    if (isValidInt)
    {
        for (int i = 0; i < random; i++)
        {

            //a = obj1.fnSkipPatient(p);

            string dummmy = "D";
            int dum = 0;
            //DateTime sdt = DateTime.Now;
            cmdString = "INSERT INTO Patient_Data(PatientID,PatientName,F_H_G,F_H_GName,AgeOnRegn,Email,ContactNo,Gender,Married,AddressLine1,AddressLine2,City,PinCode,Religion,Occupation,RegTime,VisitDate,CurrDept,NextDept,PayID,PayDet1,PayDet2,PayDet3,PayValidity,Archived,UpdateUser,UpdateShift,UpdateDate,LocID,AddressLine3,Remark) VALUES (@PatientID,@PatientName,@F_H_G,@F_H_GName,@AgeOnRegn,@Email,@ContactNo,@Gender,@Married,@AddrLine1,@AddrLine2,@City,@PinCode,@Religion,@Occupation,@RegTime,@VisitDate,@CurrDept,@NextDept,@PayID,@PayDet1,@PayDet2,@PayDet3,@PayValidity,@Archived,@UpdateUser,@UpdateShift,@UpdateDate,@LocID,@AddrLine3,@Remark)";
            con = new SqlConnection(str);
            try
            {
                //log.Debug("Entering fnAddPatient method-Function to add a new patient into the database,generate registration bill");
                con.Open();
                transaction = con.BeginTransaction();

                cmd = new SqlCommand(cmdString, con, transaction);

                cmd.Parameters.Add("@PatientID", SqlDbType.VarChar, 12);
                cmd.Parameters["@PatientID"].Value = p.HospitalNo;

                cmd.Parameters.Add("@PatientName", SqlDbType.VarChar, 30);
                cmd.Parameters["@PatientName"].Value = dummmy;

                cmd.Parameters.Add("@F_H_G", SqlDbType.VarChar, 1);
                cmd.Parameters["@F_H_G"].Value = dummmy;

                cmd.Parameters.Add("@F_H_GName", SqlDbType.VarChar, 30);
                cmd.Parameters["@F_H_GName"].Value = dummmy;

                cmd.Parameters.Add("@AgeOnRegn", SqlDbType.Int);
                cmd.Parameters["@AgeOnRegn"].Value = dum;

                cmd.Parameters.Add("@Email", SqlDbType.VarChar, 40);
                cmd.Parameters["@Email"].Value = dummmy;

                cmd.Parameters.Add("@ContactNo", SqlDbType.VarChar, 12);
                cmd.Parameters["@ContactNo"].Value = dummmy;

                cmd.Parameters.Add("@Gender", SqlDbType.VarChar, 1);
                cmd.Parameters["@Gender"].Value = dummmy;

                cmd.Parameters.Add("@Married", SqlDbType.VarChar, 1);
                cmd.Parameters["@Married"].Value = dummmy;

                cmd.Parameters.Add("@AddrLine1", SqlDbType.VarChar, 100);
                cmd.Parameters["@AddrLine1"].Value = dummmy;

                cmd.Parameters.Add("@AddrLine2", SqlDbType.VarChar, 100);
                cmd.Parameters["@AddrLine2"].Value = dummmy;

                cmd.Parameters.Add("@AddrLine3", SqlDbType.VarChar, 100);
                cmd.Parameters["@AddrLine3"].Value = dummmy;

                cmd.Parameters.Add("@City", SqlDbType.VarChar, 20);
                cmd.Parameters["@City"].Value = dummmy;

                cmd.Parameters.Add("@PinCode", SqlDbType.Int);
                cmd.Parameters["@PinCode"].Value = dum;

                cmd.Parameters.Add("@Religion", SqlDbType.VarChar, 20);
                cmd.Parameters["@Religion"].Value = dummmy;

                cmd.Parameters.Add("@Occupation", SqlDbType.VarChar, 20);
                cmd.Parameters["@Occupation"].Value = dummmy;

                cmd.Parameters.Add("@RegTime", SqlDbType.DateTime);
                cmd.Parameters["@RegTime"].Value = sdt;

                cmd.Parameters.Add("@VisitDate", SqlDbType.DateTime);
                cmd.Parameters["@VisitDate"].Value = sdt;

                cmd.Parameters.Add("@CurrDept", SqlDbType.Int);
                cmd.Parameters["@CurrDept"].Value = dum;

                cmd.Parameters.Add("@NextDept", SqlDbType.Int);
                cmd.Parameters["@NextDept"].Value = dum;

                cmd.Parameters.Add("@PayId", SqlDbType.VarChar, 2);
                cmd.Parameters["@PayId"].Value = dummmy;

                cmd.Parameters.Add("@PayDet1", SqlDbType.VarChar, 15);
                cmd.Parameters["@PayDet1"].Value = dummmy;

                cmd.Parameters.Add("@PayDet2", SqlDbType.VarChar, 50);
                cmd.Parameters["@PayDet2"].Value = dummmy;

                cmd.Parameters.Add("@PayDet3", SqlDbType.VarChar, 15);
                cmd.Parameters["@PayDet3"].Value = dummmy;

                cmd.Parameters.Add("@PayValidity", SqlDbType.DateTime);
                cmd.Parameters["@PayValidity"].Value = sdt;

                cmd.Parameters.Add("@Archived", SqlDbType.VarChar, 1);
                cmd.Parameters["@Archived"].Value = dummmy;

                cmd.Parameters.Add("@UpdateUser", SqlDbType.VarChar, 20);
                cmd.Parameters["@UpdateUser"].Value = dummmy;

                cmd.Parameters.Add("@UpdateShift", SqlDbType.Int);
                cmd.Parameters["@UpdateShift"].Value = dum;

                cmd.Parameters.Add("@UpdateDate", SqlDbType.DateTime);
                cmd.Parameters["@UpdateDate"].Value = sdt;

                cmd.Parameters.Add("@PatientCount", SqlDbType.Int);
                cmd.Parameters["@PatientCount"].Value = dum;

                cmd.Parameters.Add("@LocId", SqlDbType.VarChar, 2);
                cmd.Parameters["@LocId"].Value = dummmy;

                cmd.Parameters.Add("@Remark", SqlDbType.VarChar, 100);
                cmd.Parameters["@Remark"].Value = dummmy;


                cmd.CommandType = CommandType.Text;

                cmd.Parameters.Clear();
                string result = cmd.ExecuteNonQuery().ToString();

            }
            catch (Exception ex)
            {

            }
            finally
            {
                con.Close();
            }
        }
    }

    }

Upvotes: 0

Views: 469

Answers (4)

Ram
Ram

Reputation: 49

1)Make Sure that the connection string is valid.

2)If you close and open the sql server management studio the database will be switched over to "master", you have to change the control to your DB.You can see a dropdownbox in the sql editor tool bar where you can select the DB that where the current query should executed.

Upvotes: 0

Sandy
Sandy

Reputation: 11687

You must commit changes in transaction and also cosider transaction.Rollback(); in the catch

I think you are missing transaction.Commit(); after cmd.ExecuteNonQuery();.

        try
        {
            con.Open();
            transaction = con.BeginTransaction();
            cmd = new SqlCommand(cmdString, con, transaction);

            cmd.Parameters.AddWithValue("@PatientID", p.HospitalNo);
            // Continue your usual work
            cmd.Parameters.AddWithValue("@Remark", dummmy);

            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();
            transaction.Commit();
            cmd.Parameters.Clear();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
        }
        finally
        {
            con.Close();
        }

Something similar to above code should work for you.

Hope it helps.

Upvotes: 0

Karthik_SD
Karthik_SD

Reputation: 633

execute the following code in server management studio.... 

GRANT INSERT ON [dbo].[Patient_Data] TO public

hope it works...

Upvotes: 0

Linga
Linga

Reputation: 10545

Move cmd.Parameters.Clear(); after cmd.ExecuteNonQuery()

Try with cmd.ExecuteNonQuery() only.

Also note that if your isValidInt is false, your query will not be executed.. So, first check whether your isValidInt is true. Better you change your logic

Upvotes: 1

Related Questions