CODEWITHSUNDEEP
c#ms-access
Kenneth Principe
Kenneth Principe

Reputation: 43

Insert Into error c#, access

I am trying to insert a record to access database but keeps getting syntax error in insert into statement.

Please help me.

try
{
    cnn.Open();
    OleDbCommand savea = new OleDbCommand();
    savea.Connection = cnn;
    savea.CommandText = "INSERT INTO FirstYear(LastName, FirstName,       MiddleName, ContactNumber, Age, BirthDateYear, HomeAddress, Gender, Religion,     Citizenship, GuardianName, GuardianContactNum, RelationtoStudent, Work,     GuardianHomeAddress) values ('" + lastname.Text + "' , '" + firstname.Text + "'     , '" + middlename.Text + "' , " + contactnum.Text + " , " + age.Text + " , '" +     dateTimePicker1.Text + "' , '" + homeadd.Text + "' , '" + gender.Text + "' , '"     + religion.Text + "', '" + citizenship.Text + "' , '" + fullname.Text + "' , " +     gcontactnum.Text + " , '" + rrelation.Text + "' , '" + work.Text + "' , '" +     ghomeadd.Text + "')";
    int temp = savea.ExecuteNonQuery();

    if (temp > 0)
    {
        MessageBox.Show("Successfully Submitted");
    }
}

catch (Exception ex)
{
    MessageBox.Show("Error" + ex);
}

cnn.Close();

Upvotes: 0

Views: 71

Answers (5)

Rajshekar Reddy
Rajshekar Reddy

Reputation: 18987

use this, I think the issue was string formatting. Also user parameters to pass values, this way by concatenating string you will be vulnerable to hacks (Sql Injection)

savea.CommandText = "INSERT INTO FirstYear(LastName, FirstName, MiddleName, ContactNumber, Age, BirthDateYear, HomeAddress, Gender, Religion,     Citizenship, GuardianName, GuardianContactNum, RelationtoStudent, Work, GuardianHomeAddress) values ('" + lastname.Text + "' , '" + firstname.Text + "'     , '" + middlename.Text + "' , '" + contactnum.Text + "' , '" + age.Text + "' , '" +     dateTimePicker1.Text + "' , '" + homeadd.Text + "' , '" + gender.Text + "' , '"     + religion.Text + "', '" + citizenship.Text + "' , '" + fullname.Text + "' , '" +     gcontactnum.Text + "' , '" + rrelation.Text + "' , '" + work.Text + "' , '" +     ghomeadd.Text + "')";

But I suggest you to follow the below coding style,

string sqlQuery= "INSERT INTO FirstYear(LastName, FirstName, MiddleName, ContactNumber, Age, BirthDateYear, HomeAddress, Gender, Religion,     Citizenship, GuardianName, GuardianContactNum, RelationtoStudent, Work, GuardianHomeAddress) values (@Lastname, @Firstname, @MiddleName, @ContactNumber, @Age, @BirthDateYear, @HomeAddress, @Gender, @Religion, @Citizenship, @GuardianName, @GuardianContactNum, @RelationtoStudent, @Work, @GuardianHomeAddress  )";

using (var cmd = new OleDbCommand(sqlQuery, connectionString))
{
  cmd.Parameters.Add("@Lastname", OleDbType.VarChar).Value = lastname.Text;
  cmd.Parameters.Add("@Firstname", OleDbType.VarChar).Value = firstname.Text;
 // ... so on
 cmd.ExecuteNonQuery();
}

Upvotes: 1

Anwar Ul-haq
Anwar Ul-haq

Reputation: 1881

Correct the highlighted error below, if you want to use this syntax.

enter image description here

Upvotes: 0

Izzy
Izzy

Reputation: 6866

You've got a a fair few '' mismatches . Also currently your code is vulnerable to Sql Injection, always use parameters. Also make use of using block to ensure the object is closed and disposed correctly

string myQuery = "INSERT INTO FirstYear(LastName, FirstName, ...) values (@Lastname, @Firstname, ... )";

using (var cmd = new OleDbCommand(myQuery, connectionString))
{
  cmd.Parameters.Add("@Lastname", OleDbType.VarChar).Value = lastname.Text;
  cmd.Parameters.Add("@Firstname", OleDbType.VarChar).Value = firstname.Text;
  ...
}

Upvotes: 0

Byyo
Byyo

Reputation: 2243

there are missing some ' characters which could be the reason. anyway i would use parameters instead of string concartenation to avoid injection attacks

savea.CommandText = "INSERT INTO FirstYear(LastName, FirstName,MiddleName ...)  values (@LastName, @FirstName,MiddleName ...)";
savea.Parameters.Add("@LastName", OleDbType.VarChar).Value = lastname.Text ;
savea.Parameters.Add("@FirstName", OleDbType.VarChar).Value = firstname.Text;
savea.Parameters.Add("@MiddleName", OleDbType.VarChar).Value = middlename.Text;
...

Upvotes: 0

Alexey Obukhov
Alexey Obukhov

Reputation: 854

possible errors (if you give error text, i can tell more specifically)

  1. invalid number of arguments. to avoid it use string.Format

    savea.CommandText = string.Format("insert into FirstYear({0}, {1}, ...) values('{10}', '{11}'...)", "LastName", "FirstName", ..., lastname.text, firstname.text, ...)

  2. invalid connection string. see at connectionstrings.com

  3. escape your strings

    firstname.text.Replace("'", "''")

Upvotes: 0

Related Questions