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

Question

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

• Sandy: Not an answer but a side note. Consider Entity framework for DB use.
• Carlos Landeras: is thrown some kind of exception?
• polin: can you tell us what is the exception?
• Shreyas Achar: NO nothing no exception
• polin: There are a lot of problems. please google for how to use transaction and execute a query. Debug your code. Have you even opened connection?

Answer 1

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

Answer 2

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.

Answer 3

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

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

hope it works...

Answer 4

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.