Experts HELP Needed Asap. ASPX webforms to Access to Database Error

Question

User-989953910 posted

Error getting author. Syntax error in INSERT INTO statement.

I am giving my code of aspx.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OleDb;

using System.Data;

namespace WebApplication5

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Button1_Click(object sender, EventArgs e)

        {

            String type1="",ptype="",check1="",check2="",check3="";

            if(DropDownList7.SelectedItem.Text=="Other")

                type1 = TextBox56.Text;

            else

                type1 = DropDownList7.SelectedItem.Text;

            

            if(DropDownList23.SelectedItem.Text=="Other")

                ptype = TextBox160.Text;

            else

                ptype = DropDownList23.SelectedItem.Text;

            

            if(CheckBoxList1.SelectedValue == "1")

                check1 = "yes";

            else

                check1 = "no";

            if(CheckBoxList1.SelectedValue == "2")

                check2 = "yes";

            else

                check2 = "no";

            if(CheckBoxList1.SelectedValue == "3")

                check3 = "yes";

            else

                check3 = "no";

         /*   String Country,Reason,Race;

            if(country.SelectedItem.Value.ToString()=="Other")

                Country=TextBox8.Text;

            else

                Country = country.SelectedItem.Text;

            if(reasoncheck.SelectedItem.Text=="Other")

                Reason=TextBox170.Text;

            else

                Reason = reasoncheck.SelectedItem.Text;

            if(race.SelectedItem.Value=="Other")

                Race=TextBox51.Text;

            else

                Race = race.SelectedItem.Text;

           */

            OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database1.accdb;Persist Security Info=False;");//Data provider for MS ACCESS 

            OleDbCommand cmd = new OleDbCommand();

            cmd.Connection = con;

            cmd.CommandType = CommandType.Text;

            cmd.CommandText = "insert into complaint(complaintno, dateofcomp, nameofintake, oneprot, twoprot, threeprot, fourprot, fiveprot, sixprot, typeoftrans, allegation, fhrc, gender, nameofcom, addressofcom, cityofcom, stateofcom, phone1, phone2, email, fax, age, race, disable, sex, no, martialstatus, familysize, children, employer, position, salary, verified, semployer, sposition, ssalary, sverified, otherincome1, verified1, otherincome2, verified2, incomerec, monthlyinc, spname, spage, spsex, sprace, spno, sprelation, d1name, d1age, d1sex, d1race, d1no, d1relation, d2name, d2age, d2sex, d2race, d2no, d2relation, d3name, d3age, d3sex, d3race, d3no, d3relation, d4name, d4age, d4sex, d4race, d4no, d4relation, pets, emername, emeradd, emerphone, preshoustype, noofbedroom, typeof, dateofmovin, leaseexp, dateofmovout, reason, rent, subsidy, subsidydeposit, changemanag, overduerent, restype1, resname1, rescomp1, respos1, resadd1, resph1, resrace1, resprior1, restype2, resname2, rescomp2, respos2, resadd2, resph2, resrace2, resprior2, dateofinc, howclient, addressofprop, clientwant, typeofprop, witness, witname1, witrel1, witadd1, witcitstatzip1, witph1, witname2, witrel2, witadd2, witcitstatzip2, witph2, consuapp, consufees, wastold1, wastold2, wastold3, filedelse, fhrcinform) values(" + TextBox52.Text + ", '" + dateofcom.Text + "', '" + TextBox67.Text + "', '" + TextBox68.Text + "', '" + TextBox69.Text + "', '" + TextBox70.Text + "', '" + TextBox71.Text + "', '" + TextBox54.Text + "', '" + TextBox55.Text + "', '" + type1 + "', '" + TextBox57.Text + "', '" + TextBox62.Text + "', '" + RadioButtonList8.SelectedItem.Text + "', '" + TextBox64.Text + "', '" + TextBox65.Text + "', '" + TextBox72.Text + "', '" + TextBox73.Text + "', '" + TextBox74.Text + "', '" + TextBox75.Text + "', '" + TextBox76.Text + "', '" + TextBox77.Text + "', '" + TextBox78.Text + "', '" + TextBox79.Text + "', '" + TextBox80.Text + "', '" + RadioButtonList13.SelectedItem.Text + "', '" + TextBox81.Text + "', '" + TextBox82.Text + "', '" + TextBox83.Text + "', '" + TextBox84.Text + "', '" + TextBox85.Text + "', '" + TextBox86.Text + "', '" + TextBox87.Text + "', '" + TextBox88.Text + "', '" + TextBox89.Text + "', '" + TextBox90.Text + "', '" + TextBox91.Text + "', '" + TextBox92.Text + "', '" + TextBox93.Text + "', '" + TextBox94.Text + "', '" + TextBox95.Text + "', '" + TextBox96.Text + "', '" + TextBox97.Text + "', '" + TextBox98.Text + "', '" + TextBox107.Text + "', '" + TextBox108.Text + "', '" + DropDownList12.SelectedItem.Text + "', '" + DropDownList13.SelectedItem.Text + "', '" + TextBox109.Text + "', '" + TextBox110.Text + "', '" + TextBox115.Text + "', '" + TextBox116.Text + "', '" + DropDownList16.SelectedItem.Text + "', '" + DropDownList17.SelectedItem.Text + "', '" + TextBox117.Text + "', '" + TextBox118.Text + "', '" + TextBox119.Text + "', '" + TextBox120.Text + "', '" + DropDownList18.SelectedItem.Text + "', '" + DropDownList19.SelectedItem.Text + "', '" + TextBox121.Text + "', '" + TextBox122.Text + "', '" + TextBox123.Text + "', '" + TextBox124.Text + "', '" + DropDownList20.SelectedItem.Text + "', '" + DropDownList21.SelectedItem.Text + "', '" + TextBox125.Text + "', '" + TextBox126.Text + "', '" + TextBox111.Text + "', '" + TextBox112.Text + "', '" + DropDownList14.SelectedItem.Text + "', '" + DropDownList15.SelectedItem.Text + "', '" + TextBox113.Text + "', '" + TextBox114.Text + "', '" + TextBox127.Text + "', '" + TextBox128.Text + "', '" + TextBox129.Text + "', '" + TextBox130.Text + "', '" + TextBox131.Text + "', '" + TextBox132.Text + "', '" + DropDownList22.SelectedItem.Text + "', '" + TextBox133.Text + "', '" + TextBox134.Text + "', '" + TextBox135.Text + "', '" + TextBox136.Text + "', '" + TextBox137.Text + "', '" + TextBox138.Text + "', '" + TextBox139.Text + "', '" + TextBox140.Text + "', '" + TextBox141.Text + "', '" + TextBox142.Text + "', '" + TextBox144.Text + "', '" + TextBox170.Text + "', '" + TextBox145.Text + "', '" + TextBox146.Text + "', '" + TextBox148.Text + "', '" + TextBox147.Text + "', '" + TextBox149.Text + "', '" + TextBox143.Text + "', '" + TextBox150.Text + "', '" + TextBox171.Text + "', '" + TextBox151.Text + "', '" + TextBox152.Text + "', '" + TextBox153.Text + "', '" + TextBox154.Text + "', '" + TextBox155.Text + "', '" + TextBox156.Text + "', '" + TextBox157.Text + "', '" + TextBox158.Text + "', '" + TextBox159.Text + "', '" + ptype.ToString() + "', '" + DropDownList24.SelectedItem.Text + "', '" + TextBox161.Text + "', '" + TextBox162.Text + "', '" + TextBox172.Text + "', '" + TextBox163.Text + "', '" + TextBox164.Text + "', '" + TextBox165.Text + "', '" + TextBox166.Text + "', '" + TextBox173.Text + "', '" + TextBox167.Text + "', '" + TextBox168.Text + "', '" + DropDownList25.SelectedItem.Text + "', '" + TextBox169.Text + "', '" + "Check1" + "', '" + "check2" + "', '" + "check3" + "', '" + DropDownList26.SelectedItem.Text + "', '" + DropDownList27.SelectedItem.Text + "');";

         //   cmd.CommandText = "insert into client(dateofintake, intakeconducted, typeofcontact, clientname, clientaddress, country, homephone, workphone, cellphone, fax, reasonofcontact, problemsumm, outcomeofcontact, howhear, gender, noofpeople, proofofincome, incomelevel, typeofincome, income, ethn, race, disability) values ('20/12/2010', 'Vivek', 'Walk-in', 'Clientname', 'address', 'Norristown', 1234, 1234, 1234, 1234, 'Home Seeker', 'Summary', 'outcome', 'heard', 'male', 2, 'yes', 'Moderate', 'incometype', 43, 'Hispanic', 'American Indian', 'Yes')";

        //    cmd.CommandText = "insert into testing(textbox) values('" + type1 + "');";

             

            try

            {

                con.Open();

                cmd.ExecuteNonQuery();

            

                

            }

            catch (Exception err)

            {

                error.Text = "Error getting author. ";

                error.Text += err.Message;

            }

            finally

            {

                con.Close();

            }

        }

        protected void Page_Load(object sender, EventArgs e)

        {

            dateofcom.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

            TextBox133.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

            TextBox134.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

            TextBox135.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

            TextBox156.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

        }

    }

}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OleDb;

using System.Data;

namespace WebApplication5

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Button1_Click(object sender, EventArgs e)

        {

            String type1="",ptype="",check1="",check2="",check3="";

            if(DropDownList7.SelectedItem.Text=="Other")

                type1 = TextBox56.Text;

            else

                type1 = DropDownList7.SelectedItem.Text;

            

            if(DropDownList23.SelectedItem.Text=="Other")

                ptype = TextBox160.Text;

            else

                ptype = DropDownList23.SelectedItem.Text;

            

            if(CheckBoxList1.SelectedValue == "1")

                check1 = "yes";

            else

                check1 = "no";

            if(CheckBoxList1.SelectedValue == "2")

                check2 = "yes";

            else

                check2 = "no";

            if(CheckBoxList1.SelectedValue == "3")

                check3 = "yes";

            else

                check3 = "no";

         /*   String Country,Reason,Race;

            if(country.SelectedItem.Value.ToString()=="Other")

                Country=TextBox8.Text;

            else

                Country = country.SelectedItem.Text;

            if(reasoncheck.SelectedItem.Text=="Other")

                Reason=TextBox170.Text;

            else

                Reason = reasoncheck.SelectedItem.Text;

            if(race.SelectedItem.Value=="Other")

                Race=TextBox51.Text;

            else

                Race = race.SelectedItem.Text;

           */

            OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Database1.accdb;Persist Security Info=False;");//Data provider for MS ACCESS 

            OleDbCommand cmd = new OleDbCommand();

            cmd.Connection = con;

            cmd.CommandType = CommandType.Text;

            cmd.CommandText = "insert into complaint(complaintno, dateofcomp, nameofintake, oneprot, twoprot, threeprot, fourprot, fiveprot, sixprot, typeoftrans, allegation, fhrc, gender, nameofcom, addressofcom, cityofcom, stateofcom, phone1, phone2, email, fax, age, race, disable, sex, no, martialstatus, familysize, children, employer, position, salary, verified, semployer, sposition, ssalary, sverified, otherincome1, verified1, otherincome2, verified2, incomerec, monthlyinc, spname, spage, spsex, sprace, spno, sprelation, d1name, d1age, d1sex, d1race, d1no, d1relation, d2name, d2age, d2sex, d2race, d2no, d2relation, d3name, d3age, d3sex, d3race, d3no, d3relation, d4name, d4age, d4sex, d4race, d4no, d4relation, pets, emername, emeradd, emerphone, preshoustype, noofbedroom, typeof, dateofmovin, leaseexp, dateofmovout, reason, rent, subsidy, subsidydeposit, changemanag, overduerent, restype1, resname1, rescomp1, respos1, resadd1, resph1, resrace1, resprior1, restype2, resname2, rescomp2, respos2, resadd2, resph2, resrace2, resprior2, dateofinc, howclient, addressofprop, clientwant, typeofprop, witness, witname1, witrel1, witadd1, witcitstatzip1, witph1, witname2, witrel2, witadd2, witcitstatzip2, witph2, consuapp, consufees, wastold1, wastold2, wastold3, filedelse, fhrcinform) values(" + TextBox52.Text + ", '" + dateofcom.Text + "', '" + TextBox67.Text + "', '" + TextBox68.Text + "', '" + TextBox69.Text + "', '" + TextBox70.Text + "', '" + TextBox71.Text + "', '" + TextBox54.Text + "', '" + TextBox55.Text + "', '" + type1 + "', '" + TextBox57.Text + "', '" + TextBox62.Text + "', '" + RadioButtonList8.SelectedItem.Text + "', '" + TextBox64.Text + "', '" + TextBox65.Text + "', '" + TextBox72.Text + "', '" + TextBox73.Text + "', '" + TextBox74.Text + "', '" + TextBox75.Text + "', '" + TextBox76.Text + "', '" + TextBox77.Text + "', '" + TextBox78.Text + "', '" + TextBox79.Text + "', '" + TextBox80.Text + "', '" + RadioButtonList13.SelectedItem.Text + "', '" + TextBox81.Text + "', '" + TextBox82.Text + "', '" + TextBox83.Text + "', '" + TextBox84.Text + "', '" + TextBox85.Text + "', '" + TextBox86.Text + "', '" + TextBox87.Text + "', '" + TextBox88.Text + "', '" + TextBox89.Text + "', '" + TextBox90.Text + "', '" + TextBox91.Text + "', '" + TextBox92.Text + "', '" + TextBox93.Text + "', '" + TextBox94.Text + "', '" + TextBox95.Text + "', '" + TextBox96.Text + "', '" + TextBox97.Text + "', '" + TextBox98.Text + "', '" + TextBox107.Text + "', '" + TextBox108.Text + "', '" + DropDownList12.SelectedItem.Text + "', '" + DropDownList13.SelectedItem.Text + "', '" + TextBox109.Text + "', '" + TextBox110.Text + "', '" + TextBox115.Text + "', '" + TextBox116.Text + "', '" + DropDownList16.SelectedItem.Text + "', '" + DropDownList17.SelectedItem.Text + "', '" + TextBox117.Text + "', '" + TextBox118.Text + "', '" + TextBox119.Text + "', '" + TextBox120.Text + "', '" + DropDownList18.SelectedItem.Text + "', '" + DropDownList19.SelectedItem.Text + "', '" + TextBox121.Text + "', '" + TextBox122.Text + "', '" + TextBox123.Text + "', '" + TextBox124.Text + "', '" + DropDownList20.SelectedItem.Text + "', '" + DropDownList21.SelectedItem.Text + "', '" + TextBox125.Text + "', '" + TextBox126.Text + "', '" + TextBox111.Text + "', '" + TextBox112.Text + "', '" + DropDownList14.SelectedItem.Text + "', '" + DropDownList15.SelectedItem.Text + "', '" + TextBox113.Text + "', '" + TextBox114.Text + "', '" + TextBox127.Text + "', '" + TextBox128.Text + "', '" + TextBox129.Text + "', '" + TextBox130.Text + "', '" + TextBox131.Text + "', '" + TextBox132.Text + "', '" + DropDownList22.SelectedItem.Text + "', '" + TextBox133.Text + "', '" + TextBox134.Text + "', '" + TextBox135.Text + "', '" + TextBox136.Text + "', '" + TextBox137.Text + "', '" + TextBox138.Text + "', '" + TextBox139.Text + "', '" + TextBox140.Text + "', '" + TextBox141.Text + "', '" + TextBox142.Text + "', '" + TextBox144.Text + "', '" + TextBox170.Text + "', '" + TextBox145.Text + "', '" + TextBox146.Text + "', '" + TextBox148.Text + "', '" + TextBox147.Text + "', '" + TextBox149.Text + "', '" + TextBox143.Text + "', '" + TextBox150.Text + "', '" + TextBox171.Text + "', '" + TextBox151.Text + "', '" + TextBox152.Text + "', '" + TextBox153.Text + "', '" + TextBox154.Text + "', '" + TextBox155.Text + "', '" + TextBox156.Text + "', '" + TextBox157.Text + "', '" + TextBox158.Text + "', '" + TextBox159.Text + "', '" + ptype.ToString() + "', '" + DropDownList24.SelectedItem.Text + "', '" + TextBox161.Text + "', '" + TextBox162.Text + "', '" + TextBox172.Text + "', '" + TextBox163.Text + "', '" + TextBox164.Text + "', '" + TextBox165.Text + "', '" + TextBox166.Text + "', '" + TextBox173.Text + "', '" + TextBox167.Text + "', '" + TextBox168.Text + "', '" + DropDownList25.SelectedItem.Text + "', '" + TextBox169.Text + "', '" + "Check1" + "', '" + "check2" + "', '" + "check3" + "', '" + DropDownList26.SelectedItem.Text + "', '" + DropDownList27.SelectedItem.Text + "');";

         //   cmd.CommandText = "insert into client(dateofintake, intakeconducted, typeofcontact, clientname, clientaddress, country, homephone, workphone, cellphone, fax, reasonofcontact, problemsumm, outcomeofcontact, howhear, gender, noofpeople, proofofincome, incomelevel, typeofincome, income, ethn, race, disability) values ('20/12/2010', 'Vivek', 'Walk-in', 'Clientname', 'address', 'Norristown', 1234, 1234, 1234, 1234, 'Home Seeker', 'Summary', 'outcome', 'heard', 'male', 2, 'yes', 'Moderate', 'incometype', 43, 'Hispanic', 'American Indian', 'Yes')";

        //    cmd.CommandText = "insert into testing(textbox) values('" + type1 + "');";

             

            try

            {

                con.Open();

                cmd.ExecuteNonQuery();

            

                

            }

            catch (Exception err)

            {

                error.Text = "Error getting author. ";

                error.Text += err.Message;

            }

            finally

            {

                con.Close();

            }

        }

        protected void Page_Load(object sender, EventArgs e)

        {

            dateofcom.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

            TextBox133.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

            TextBox134.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

            TextBox135.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

            TextBox156.Text = DateTime.Now.Day.ToString() + "/" + DateTime.Now.Month.ToString() + "/" + DateTime.Now.Year.ToString();

        }

    }

}

============++++++++++++++++++++++--======================================

It has 128 columns . The database has only date and text except for the first one as Number. So  ' '   will not be a problem . Kindly check and let me know where i am going wrong. I am stuck with this from 1 week. Tried everything. 

Answer 1

User-821857111 posted

Read this,

http://www.mikesdotnetting.com/Article/76/80040E14-MS-Access-Syntax-Error-messages

and then use parameters:

http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

Answer 2

User-1199946673 posted

My goodness, how can you make sence out of this!!!! Take Mikes advice to use parameters, but how about giving you textboxes and other controls meaningfull names (for eaxample txtCompaintNo instead of TextBox52)? Also instead of using 1 big table, you better seperate it into smaller tables. For example these fields:

d1name, d1age, d1sex, d1race, d1no, d1relation, d2name, d2age, d2sex, d2race, d2no, d2relation, d3name, d3age, d3sex, d3race, d3no, d3relation, d4name, d4age, d4sex, d4race, d4no, d4relation

Put them in a seperate table with fields:

name, age, sex, race, no, relation, complaintID

and add 4 records in this table after you inserted the table complaint and retrieved the Identity column:

http://www.mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

Also, read this article on date and access

http://www.mikesdotnetting.com/Article/92/MS-Access-Date-and-Time-with-ASP.NET

And last, in general, you better store a date of birth rather than an age, because an age will change over time, and you can calculate the age from the date of birth