locked
Experts HELP Needed Asap. ASPX webforms to Access to Database Error RRS feed

  • 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. 

    Tuesday, December 28, 2010 2:42 AM

Answers

  • 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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 28, 2010 4:31 AM

All replies