Hi Guys,
I am using access (.mdb) database in my windows application. When i am inserting values to db it gives me an error "syntax error in insert into statement" but it will work in SQL and access query analyzer.
here is my code

string StrInsertToServer1 = "insert into Registration_master (stud_id, stud_name, add1, add2, add3, phone_no, mobile_no, dob, college_name, college_no, tuition_name, tuition_no, username, password, machinecode,recharge_code) values('" + txtstud_id.Text + "','" + txtstudName.Text + "','" + txtAddress1.Text + "','" + txtAddress2.Text + "','" + txtAddress3.Text + "','" + txtPhoneNo.Text + "','" + txtMobNo.Text + "','"+ dtpdob.Value.ToString()    +"','" + txtCollName.Text + "','" + txtCollPhoNo.Text + "','" + txtTutName.Text + "','" + txtTutPhoNo.Text + "','" + txtUsername.Text + "','" + txtpassword.Text + "','" + txtmachine.Text + "','" + InRecharge + "')";

                Ocmd = new OleDbCommand (StrInsertToServer1, Connect.AccConn ());
                Ocmd.Connection.Open();
                Ocmd.ExecuteNonQuery();
                Ocmd.Connection.Close();

here is my connection string

public static OleDbConnection AccConn()
        {
            //Access 2002-03
            return new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\Data\\SonamDb.mdb");
            //Access 2007
            //   return new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Application.StartupPath + "\\Data\\SNDB.accdb;Persist Security Info=False;");

        }

Direct query will working..
can anybody tell me where i am going wrong....
Any help will be appreciated

Thanks in advance

Recommended Answers

All 6 Replies

1. Have you printed the string to see what it looks like filled with values?
2. Does any of the data contain apostrophes?
3. Have you considered SQL parameters?

CONNECTION STRING:

using System.Data.OleDb;
using System.Data;
public class AccessMySQLConnection
{
    OleDbConnection c = new OleDbConnection();
    public string Path = Application.StartupPath + "\\Data\\SonamDb.mdb";
    public string connection_string = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";";
    public void Open()
    {
        if (c.State == ConnectionState.Closed) {
            c.ConnectionString = connection_string;
            c.Open();
        }
    }

    public void Close()
    {
        c.Dispose();
        c.Close();
    }

}

INSERT TO ACCESS:
AccessMySQLConnection ConnDB= new AccessMySQLConnection();
OleDb.OleDbConnection Conn = default(OleDb.OleDbConnection);
Conn = new OleDb.OleDbConnection(ConnDB.connection_string);

Conn.Open();
string StrInsertToServer1 = "insert into Registration_master (stud_id, stud_name, add1, add2, add3, phone_no, mobile_no, dob, college_name, college_no, tuition_name, tuition_no, username, password, machinecode,recharge_code) values('" + txtstud_id.Text + "','" + txtstudName.Text + "','" + txtAddress1.Text + "','" + txtAddress2.Text + "','" + txtAddress3.Text + "','" + txtPhoneNo.Text + "','" + txtMobNo.Text + "','"+ dtpdob.Value.ToString() +"','" + txtCollName.Text + "','" + txtCollPhoNo.Text + "','" + txtTutName.Text + "','" + txtTutPhoNo.Text + "','" + txtUsername.Text + "','" + txtpassword.Text + "','" + txtmachine.Text + "','" + InRecharge + "')";

OleDbCommand cmd1 = default(OleDbCommand);
cmd1 = new OleDbCommand(StrInsertToServer1, Conn);
cmd1.ExecuteNonQuery();
cmd1.Dispose();

Conn.Close();

Thanks for reply s . Actually the problem is Password is reserved in access.

I stumbled upon this thread, I am having a issue running the command..
this is what I have, (very similar to the code posted.

    protected void ClockIn_Click(object sender, EventArgs e)
    {
     //RightNow is not proprietary, may be removed.
      DateTime RightNow = DateTime.Now;
        Clockin.Visible = false;
        Clockout.Visible = true;
        st = "select * from Time where Clock-out > Clock-in";
        con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\data\\TestWriteCompatible.mdb;");
        con.Open();
        string StrInsertToServer1 = "insert into Time (Clock-in) values('" + DateTime.Now + "')";
        OleDbCommand cmd1 = default(OleDbCommand);
        cmd1 = new OleDbCommand(StrInsertToServer1, con);
        cmd1.ExecuteNonQuery();
        cmd1.Dispose();
        con.Close();
    }

Sorry for resurrecting an old thread, just dont see the use in referencing it if I am using the code from this thread.

What issue are you having?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.