Insert Record into MS Access Database

Question

0.00/5 (No votes)

See more:

.NET2.0

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 25-Nov-10 0:36am

RaginiSinha

Updated 25-Nov-10 0:40am

RaviRanjanKr

v2

Add a Solution

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

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)

E.F. Nijboer · Accepted Answer · 2010-11-25T07:04:00

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!