Click here to Skip to main content
15,040,212 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello All

I am new in .NET and create a project Demo to insert new record in MS Access using ADO.NET.
Following is my code.
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database1.mdb");
                con.Open();
                string str = "insert into FormA_Table(Name1,Fathers_Name,Husband_Name,DOB,Address_Of_Res,Address_of_practice,Medical_qualification,Degree_Qualification,year_of_passing,Name_of_institute,Reg_no,Reason_Name_Date_Rule,Revised_date,ReEntry_date,Additional_qualification,Additional_Qualification1,Additional_Qualif_Date,emp_info,Address_of_Employer,Remarks,Sign_Registrar,Reg_date,Intern_From,Intern_To,Revision_Varified)values('" + txtName.Text + "',@fname,@hname,@dob,@res_add,@p_add,@m_qualif,'" + txtQualification.Text + "',@year,'" + txtInstitute.Text + "','" + txtRegistration.Text + "','" + txtReason.Text + "',@revdate,@reentrydate,@add_qualif,'" + txtAddQualification.Text + "',@q_date,@e_type,@e_add,'" + txtRemarks.Text + "',@na,@reg_date,@i_from,@i_to,@rev)";
             
                OleDbCommand cmdInsert = new OleDbCommand(str, con);
                cmdInsert.Parameters.Add("@fname", OleDbType.VarChar);
                cmdInsert.Parameters["@fname"].Value = FName;
                cmdInsert.Parameters.Add("@hname", OleDbType.VarChar);
                 cmdInsert.Parameters["@hname"].Value = HName;
                
                cmdInsert.Parameters.Add("@dob", OleDbType.VarChar);
                cmdInsert.Parameters["@dob"].Value = DOB1;
                
                cmdInsert.Parameters.Add("@res_add", OleDbType.VarChar);
                cmdInsert.Parameters["@res_add"].Value = Res_Address; ;
                
                cmdInsert.Parameters.Add("@p_add", OleDbType.VarChar);
                cmdInsert.Parameters["@p_add"].Value = Pra_Address;
                cmdInsert.Parameters.Add("@m_qualif", OleDbType.VarChar);
                cmdInsert.Parameters["@m_qualif"].Value = med_qualif;
                cmdInsert.Parameters.Add("@year", OleDbType.Integer);
                cmdInsert.Parameters["@year"].Value = year;
                cmdInsert.Parameters.Add("@rev_date", OleDbType.VarChar);
                cmdInsert.Parameters["@rev_date"].Value = RevisedDate1;
                cmdInsert.Parameters.Add("@reentrydate", OleDbType.VarChar);
                cmdInsert.Parameters["@reentrydate"].Value = reEntry1;
                cmdInsert.Parameters.Add("@add_qualif", OleDbType.VarChar);
                cmdInsert.Parameters["@add_qualif"].Value = add_qualType;
                cmdInsert.Parameters.Add("@q_date", OleDbType.VarChar);
                cmdInsert.Parameters["@q_date"].Value = qualifDate1; ;
                cmdInsert.Parameters.Add("@e_type", OleDbType.VarChar);
                cmdInsert.Parameters["@e_type"].Value = emp_type;
                cmdInsert.Parameters.Add("@e_add", OleDbType.VarChar);
                cmdInsert.Parameters["@e_add"].Value = Emp_add;
                cmdInsert.Parameters.Add("@na", OleDbType.VarChar);
                cmdInsert.Parameters["@na"].Value = NA;
                cmdInsert.Parameters.Add("@reg_date", OleDbType.VarChar);
                cmdInsert.Parameters["@reg_date"].Value = Reg_Date1;
                cmdInsert.Parameters.Add("@i_from", OleDbType.VarChar);
                cmdInsert.Parameters["@i_from"].Value = InternFrom;
                cmdInsert.Parameters.Add("@i_to", OleDbType.VarChar);
                cmdInsert.Parameters["@i_to"].Value = InternTo;
                cmdInsert.Parameters.Add("@rev", OleDbType.VarChar);
                cmdInsert.Parameters["@rev"].Value = revised1;
               int i= cmdInsert.ExecuteNonQuery();
                if (i > 0)
                    MessageBox.Show("New Record Added successfuly");
                else
                    MessageBox.Show("Fail to save new record");
                con.Close();
                cmdInsert.Dispose();


There is no any problem and it aslo shows a message "Record added successfuly" but it does not add any record in database.
please help me to so that I can complete my project.



Thanks.
Posted
Updated 25-Nov-10 0:40am
v2
Comments
Sandeep Mewara 25-Nov-10 7:10am
   
Did you closed and opened the database to re-verify if it was added or not? Having return value of 'i' > 0 means it worked.
RaginiSinha 1-Dec-10 6:44am
   
Yes, i tried it many time but still there is no new rercord added in database..

1 solution

It seems like there is nothing really wrong with your code.

I would however suggest to add all the parameters properly instead of concatenate some of them into the querystring directly.

You also might want to move the actual open to the point where it is actually needed instead of opening it while you are still busy with other stuff. This specially is important in concurrent situations where it could be more noticeable.

Handling the proper creation and disposing of objects can be done nicely with using. Below some example code.

C#
using (OleDbConnection conn = new OleDbConnection(ConnString))
{
  using (OleDbCommand cmd = new OleDbCommand(SqlString, conn))
  {
    cmd.CommandType = CommandType.Text;
    // Add the input parameter and set its properties.
    SqlParameter parameter = new SqlParameter();
    parameter.ParameterName = "@Parameter";
    parameter.SqlDbType = SqlDbType.NVarChar;
    parameter.Direction = ParameterDirection.Input;
    parameter.Value = categoryName;
    conn.Open();
    cmd.ExecuteNonQuery();
  }
}


Good luck!
   
Comments
RaginiSinha 1-Dec-10 6:43am
   
Thank you very much for your usefull suggestions.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900