C# connecting to access


I have an Access 2007 DB and a infopath form that is connected to a webservice which i want to insert into DB

I an having issues in creating the connection and executing the insert into table..

Below is the Connection string used to connect to DB.. This looks correct to me..

But I have no Idea how to write Insert String and execute it..
public class Service1 : System.Web.Services.WebService //Square1InfoPath
    {
        [WebMethod]
 public void SubmitForm(myFields newForm)
        {

            OleDbConnection Conn = new OleDbConnection();

            try
            {
                string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\ltorres\\Documents\\Visual Studio 2010\\Projects\\Square1WebService\\AccessDatabase\\Square1_Inspection.accdb;Persist Security Info=False;";
                Conn.ConnectionString = conn;

                Conn.Open();




            }


}
//The Insert statement

INSERT INTO VisualCheck 
	(MasterInspection_ID, AirBag, AirbagComments, AntiLock, AntiLockComments, SeatBeltWarning, SeatBeltWarningComments, CheckEngine, CheckEngineComments, TurnSignalIndicator, TurnSignalIndicatorComments, HighBeamIndicator, HighBeamIndicatorComments, OverdriveIndicator, OverdriveIndicatorComments, TOWHaulIndicator, TOWHaulIndicatorComments, ParkingBrake, ParkingBrakeComments, ShiftIndicator, ShiftIndicatorComments, EmergencyFlasher, EmergencyFlasherComments, WindshieldWiperWasher, WindshieldWiperWasherComments, Speakers, SpeakersComments, ManualPowerAntenna, ManualPowerAntennaComments, SunvisorLights, SunvisorLightsComments, OverheadConsoleDisplay, OverheadConsoleDisplayComments, MapLamps, MapLampsComments, GloveCompartmentLamp, GloveCompartmentLampComments, DomeLightCargoLightFloorLight, DomeLightCargoLightFloorLightComments, PowerMirrors, PowerMirrorsComments, PowerSeatControls, PowerSeatControlsComments, FrontDoorPowerLocks, FrontDoorPowerLocksComments, FrontDoorPowerWindow, FrontDoorPowerWindowComments, RearDoorPowerLocks, RearDoorPowerLocksComments, RearDoorPowerWindows, RearDoorPowerWindowsComments, RearWindowWiperWasher, RearWindowWiperWasherComments, HeadLampsLowBeam, HeadLampsLowBeamComments, HeadLampsHighBeam, HeadLampsHighBeamComments, FogLights, FogLightsComments, TurnIndicatorsinMirror, TurnIndicatorsinMirrorComments, SideLamps, SideLampsComments, ParkingLampsTurnLamps, ParkingLampsTurnLampsComments, RearTurnLamps, RearTurnLampsComments, TailLampsStopLamps, TailLampsStopLampsComments, BackUpLamps, BackUpLampsComments, VehicleNumber3, Accessories, Damages, Comments, OverAllStatus) 
VALUES 
	( , '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

Open in new window

LVL 8
Leo TorresSQL DeveloperAsked:
Who is Participating?
 
Ramone_HamiltonConnect With a Mentor Commented:
Ok here is what I was able to come up with, the only thing that you will need to change with the below code is when I am adding the parameters, there were so many fields that I did not change the values so you would have to change all those newForm.MasterInspection_ID's into the correct value.
private string InsertQuery()
        {
            var sb = new StringBuilder();
            sb.Append("INSERT INTO VisualCheck");
            sb.Append("(MasterInspection_ID, AirBag, AirbagComments, AntiLock, AntiLockComments, SeatBeltWarning, SeatBeltWarningComments, CheckEngine, CheckEngineComments, TurnSignalIndicator, TurnSignalIndicatorComments, HighBeamIndicator, HighBeamIndicatorComments, OverdriveIndicator, OverdriveIndicatorComments, TOWHaulIndicator, TOWHaulIndicatorComments, ParkingBrake, ParkingBrakeComments, ShiftIndicator, ShiftIndicatorComments, EmergencyFlasher, EmergencyFlasherComments, WindshieldWiperWasher, WindshieldWiperWasherComments, Speakers, SpeakersComments, ManualPowerAntenna, ManualPowerAntennaComments, SunvisorLights, SunvisorLightsComments, OverheadConsoleDisplay, OverheadConsoleDisplayComments, MapLamps, MapLampsComments, GloveCompartmentLamp, GloveCompartmentLampComments, DomeLightCargoLightFloorLight, DomeLightCargoLightFloorLightComments, PowerMirrors, PowerMirrorsComments, PowerSeatControls, PowerSeatControlsComments, FrontDoorPowerLocks, FrontDoorPowerLocksComments, FrontDoorPowerWindow, FrontDoorPowerWindowComments, RearDoorPowerLocks, RearDoorPowerLocksComments, RearDoorPowerWindows, RearDoorPowerWindowsComments, RearWindowWiperWasher, RearWindowWiperWasherComments, HeadLampsLowBeam, HeadLampsLowBeamComments, HeadLampsHighBeam, HeadLampsHighBeamComments, FogLights, FogLightsComments, TurnIndicatorsinMirror, TurnIndicatorsinMirrorComments, SideLamps, SideLampsComments, ParkingLampsTurnLamps, ParkingLampsTurnLampsComments, RearTurnLamps, RearTurnLampsComments, TailLampsStopLamps, TailLampsStopLampsComments, BackUpLamps, BackUpLampsComments, VehicleNumber3, Accessories, Damages, Comments, OverAllStatus)");
            sb.Append("VALUES");
            sb.Append("(@MasterInspection_ID, @AirBag, @AirbagComments, @AntiLock, @AntiLockComments, @SeatBeltWarning, @SeatBeltWarningComments, @CheckEngine, @CheckEngineComments, @TurnSignalIndicator, @TurnSignalIndicatorComments, @HighBeamIndicator, @HighBeamIndicatorComments, @OverdriveIndicator, @OverdriveIndicatorComments, @TOWHaulIndicator, @TOWHaulIndicatorComments, @ParkingBrake, @ParkingBrakeComments, @ShiftIndicator, @ShiftIndicatorComments, @EmergencyFlasher, @EmergencyFlasherComments, @WindshieldWiperWasher, @WindshieldWiperWasherComments, @Speakers, @SpeakersComments, @ManualPowerAntenna, @ManualPowerAntennaComments, @SunvisorLights, @SunvisorLightsComments, @OverheadConsoleDisplay, @OverheadConsoleDisplayComments, @MapLamps, @MapLampsComments, @GloveCompartmentLamp, @GloveCompartmentLampComments, @DomeLightCargoLightFloorLight, @DomeLightCargoLightFloorLightComments, @PowerMirrors, @PowerMirrorsComments, @PowerSeatControls, @PowerSeatControlsComments, @FrontDoorPowerLocks, @FrontDoorPowerLocksComments, @FrontDoorPowerWindow, @FrontDoorPowerWindowComments, @RearDoorPowerLocks, @RearDoorPowerLocksComments, @RearDoorPowerWindows, @RearDoorPowerWindowsComments, @RearWindowWiperWasher, @RearWindowWiperWasherComments, @HeadLampsLowBeam, @HeadLampsLowBeamComments, @HeadLampsHighBeam, @HeadLampsHighBeamComments, @FogLights, @FogLightsComments, @TurnIndicatorsinMirror, @TurnIndicatorsinMirrorComments, @SideLamps, @SideLampsComments, @ParkingLampsTurnLamps, @ParkingLampsTurnLampsComments, @RearTurnLamps, @RearTurnLampsComments, @TailLampsStopLamps, @TailLampsStopLampsComments, @BackUpLamps, @BackUpLampsComments, @VehicleNumber3, @Accessories, @Damages, @Comments, @OverAllStatus)");

            return sb.ToString();
        }
        public void SubmitForm(MyFields newForm)
        {
            using (
                OleDbConnection conn =
                    new OleDbConnection(
                        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\ltorres\\Documents\\Visual Studio 2010\\Projects\\Square1WebService\\AccessDatabase\\Square1_Inspection.accdb;Persist Security Info=False;")
                )
            {
                try
                {
                    using (OleDbCommand command = new OleDbCommand(InsertQuery(), conn))
                    {
                        command.Parameters.Add(new OleDbParameter("001", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("002", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("003", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("004", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("005", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("006", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("007", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("008", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("009", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("010", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("011", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("012", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("013", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("014", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("015", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("016", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("017", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("018", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("019", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("020", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("021", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("022", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("023", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("024", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("025", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("026", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("027", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("028", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("029", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("030", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("031", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("032", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("033", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("034", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("035", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("036", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("037", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("038", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("039", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("040", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("041", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("042", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("043", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("044", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("045", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("046", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("047", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("048", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("049", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("050", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("051", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("052", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("053", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("054", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("055", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("056", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("057", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("058", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("059", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("060", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("061", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("062", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("063", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("064", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("065", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("066", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("067", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("068", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("069", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("070", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("071", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("072", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("073", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("074", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("075", newForm.MasterInspection_ID));
                        command.Parameters.Add(new OleDbParameter("076", newForm.MasterInspection_ID));

                        conn.Open();

                        command.ExecuteNonQuery();

                        conn.Close();
                    }
                }
                catch(Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    if(conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }

Open in new window

0
 
HugoHiaslConnect With a Mentor Commented:
At least you have a missing value in the VALUES line.

The first character after the opening parenthesis should not be a comma.

0
 
Leo TorresSQL DeveloperAuthor Commented:
That much I know I dont know how to get them from the xml fle i think newForm.MYFIELD but thats not my concern yet
0
 
Leo TorresSQL DeveloperAuthor Commented:
Ramone: Thank you I will now try your code..
Question the string below wont it do the same thing??


public void SubmitForm(myFields newForm)
        {

            OleDbConnection Conn = new OleDbConnection();

             string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\ltorres\\Documents\\Visual Studio 2010\\Projects\\Square1WebService\\AccessDatabase\\Square1_Inspection.accdb;Persist Security Info=False;";
                Conn.ConnectionString = connstr;

                Conn.Open();
//                              
               string insertsql = "INSERT INTO VisualCheck "
	+"(AirBag, AirbagComments, AntiLock, AntiLockComments, SeatBeltWarning, SeatBeltWarningComments, CheckEngine, CheckEngineComments, TurnSignalIndicator, TurnSignalIndicatorComments, HighBeamIndicator, HighBeamIndicatorComments, OverdriveIndicator, OverdriveIndicatorComments, TOWHaulIndicator, TOWHaulIndicatorComments, ParkingBrake, ParkingBrakeComments, ShiftIndicator, ShiftIndicatorComments, EmergencyFlasher, EmergencyFlasherComments, WindshieldWiperWasher, WindshieldWiperWasherComments, Speakers, SpeakersComments, ManualPowerAntenna, ManualPowerAntennaComments, SunvisorLights, SunvisorLightsComments, OverheadConsoleDisplay, OverheadConsoleDisplayComments, MapLamps, MapLampsComments, GloveCompartmentLamp, GloveCompartmentLampComments, DomeLightCargoLightFloorLight, DomeLightCargoLightFloorLightComments, PowerMirrors, PowerMirrorsComments, PowerSeatControls, PowerSeatControlsComments, FrontDoorPowerLocks, FrontDoorPowerLocksComments, FrontDoorPowerWindow, FrontDoorPowerWindowComments, RearDoorPowerLocks, RearDoorPowerLocksComments, RearDoorPowerWindows, RearDoorPowerWindowsComments, RearWindowWiperWasher, RearWindowWiperWasherComments, HeadLampsLowBeam, HeadLampsLowBeamComments, HeadLampsHighBeam, HeadLampsHighBeamComments, FogLights, FogLightsComments, TurnIndicatorsinMirror, TurnIndicatorsinMirrorComments, SideLamps, SideLampsComments, ParkingLampsTurnLamps, ParkingLampsTurnLampsComments, RearTurnLamps, RearTurnLampsComments, TailLampsStopLamps, TailLampsStopLampsComments, BackUpLamps, BackUpLampsComments, VehicleNumber3, Accessories, Damages, Comments, OverAllStatus)" 
    +"VALUES "
    +" ('"newForm.AirBag"', '"newForm.AirbagComments"','"newForm.AntiLock"', '"newForm.AntiLockComments"', '"newForm.SeatBeltWarning"', '"newForm.SeatBeltWarningComments"', '"newForm.CheckEngine"', '"newForm.CheckEngineComments"', '"newForm.TurnSignalIndicator"', '"newForm.TurnSignalIndicatorComments"', '"newForm.HighBeamIndicator"', '"newForm.HighBeamIndicatorComments"' , '"newForm.OverdriveIndicator"' , '"newForm.OverdriveIndicatorComments"', '"newForm.TOWHaulIndicator"', '"newForm.TOWHaulIndicatorComments"', '"newForm.ParkingBrake"','"newForm.ParkingBrakeComments"' , '"newForm.ShiftIndicator"' ,'"newForm.EmergencyFlasher"'  ,'"newForm.EmergencyFlasherComments"' , '"newForm.WindshieldWiperWasher"' ,'"newForm.WindshieldWiperWasherComments"'  , '"newForm.Speakers"', '"newForm.SpeakersComments"' , '"newForm.ManualPowerAntenna"', '"newForm.ManualPowerAntennaComments"', '"newForm.SunvisorLights"', '"newForm.SunvisorLightsComments"', '"newForm.OverheadConsoleDisplay"', '"newForm.OverheadConsoleDisplayComments"', '"newForm.MapLamps"', '"newForm.MapLampsComments"','"newForm.GloveCompartmentLamp"','"newForm.GloveCompartmentLampComments"','"newForm.DomeLightCargoLightFloorLight"','"newForm.DomeLightCargoLightFloorLightComments"','"newForm.PowerMirrors"','"newForm.PowerMirrorsComments"','"newForm.PowerSeatControls"','"newForm.PowerSeatControlsComments"','"newForm.FrontDoorPowerLocks"','"newForm.FrontDoorPowerLocksComments"','"newForm.FrontDoorPowerWindow"' , '"newForm.FrontDoorPowerWindowComments"' , '"newForm.RearDoorPowerLocks"' , '"newForm.RearDoorPowerLocksComments"' , '"newForm.RearDoorPowerWindows"' , '"newForm.RearDoorPowerWindowsComments"' , '"newForm.RearWindowWiperWasher"' , '"newForm.RearWindowWiperWasherComments"' , '"newForm.HeadLampsLowBeam"' , '"newForm.HeadLampsLowBeamComments"' , '"newForm.HeadLampsHighBeam"' , '"newForm.HeadLampsHighBeamComments"' , '"newForm.FogLights"' , '"newForm.FogLightsComments"' , '"newForm.TurnIndicatorsinMirror"' , '"newForm.TurnIndicatorsinMirrorComments"' , '"newForm.SideLamps"' , '"newForm.SideLampsComments"' , '"newForm.ParkingLampsTurnLamps"' , '"newForm.ParkingLampsTurnLampsComments"' , '"newForm.RearTurnLamps"' , '"newForm.RearTurnLampsComments"' , '"newForm.TailLampsStopLamps"' , '"newForm.TailLampsStopLampsComments"' , '"newForm.BackUpLamps"' , '"newForm.BackUpLampsComments"' , '"newForm.VehicleNumber3"' , '"newForm.Accessories"' , '"newForm.Damages"' , '"newForm.Comments"' , '"newForm.OverAllStatus"')" ;

            
        
        }

Open in new window

0
 
Leo TorresSQL DeveloperAuthor Commented:
OK so I have inserted the code your code and it produces no errors. But I need to make all these inserts as well..

I can follow the same logic form the last insert but how to I do mulitiple inserts ..

Questions is how would I organize them??

All the inserts have to be done when the user hits the submit button


INSERT INTO MasterInspection
      (VEHICLENumber, ModelYear, Make, Model, Dive, VIN, ToyotaModelCode, VehicleSuppliedBy, DateReceived, ExteriorColor, InteriorColor, TailGate, RoofConsole, SunMoonRoof, FrontGlass, RearGlass, QuarterGlass, Backlite, BullBar, RoofRack, Spoiler, Winch, TowPackage, RearParkingSensor, VehicleNumber2, Odometer, DistanceMeasure, VehicleWeightFront, VehicleWeightRear, VehicleHeightLeftFront, VehicleHeightRightFront, VehicleHeightLeftRear, VehicleHeightRightRear, FuelLevel)
VALUES
      ('', '',  , '', '', '', '', '', #02/18/2011#, '', '', '',  ,  , '', '', '', '',  ,  ,  ,  ,  ,  , '',  , '', '', '', '', '', '', '', '')

INSERT INTO Engine
      (MasterInspection_ID, EngineSize, FuelType, DualBattery, FuelTank, Transmission, TransmissionType)
VALUES
      ( , '', '',  ,  , '', '')
      
INSERT INTO Exterior
      (MasterInspection_ID, OdometerReading, OdometerReadingUnits, VehicleWeightFront, VehicleWeightRear, VehicleHeightLeftFront, `Vehicle HeightRightFront`, `Vehicle HeightLeftRear`, VehicleHeightRightRear, DoorPanels, `Door PanelsComments`, Over-AllPaint, DoorPanelsComments, Windshield, WindshieldComments, Wipers, WipersComments)
VALUES
      ( ,  , '',  ,  ,  ,  ,  ,  , '', '', '', '', '', '', '', '')

INSERT INTO Interior
      (MasterInspection_ID, RearAC, UnderSeatHeater, CoolerBetweenFrontSeats, KneePads, MirrorCompass, OnStarEquipped, SeatConfigurationID, Headliner, HeadlinerComments, InsidePanels, InsidePanelsComments, Rugs, RugsComments, Upholstery, UpholsteryCondition, UpholsteryComments)
VALUES
      ( ,  ,  ,  ,  ,  ,  ,  , '', '', '', '', '', '', '', '', '')

INSERT INTO Suspension
      (MasterInspection_ID, FrontSuspension, FrontWheelLock, FrontShockType, FrontBraking, RearSuspension, RearShockType, RearBraking)
VALUES
      ( , '', '', '', '', '', '', '')
      
INSERT INTO TireRims
      (MasterInspection_ID, MainTiresBrand, MainTiresRimSize, MainTiresRimType, SpareTireBrand, SpareTireRimSize, SpareTireRimType, LugNuts, SpareTireBracket, ExtraSpareTire, SpareTireQty, ExtraSpareBrand, ExtraSpareTireRimSize, ExtraSpareTireRimType, TirePressureSensor, RunningBoards, SparePartsInside, WheelCode)
VALUES
      ( , '', '', '',  , '', '',  ,  ,  ,  , '', '', '',  ,  ,  , '')

INSERT INTO VehicleItems
      (MasterInspection_ID, AirConditioner, `Air ConditionerComments`, BookletsManuals, BookletsManualsComments, RadioCDCassettePlayer, RadioCDCassettePlayerComments, CDChangerinTrunk, CDChangerinTrunkComments, CDMagazine, CDMagazineComments, PowerSeats, PowerSeatsComments, FireExtinguisher, FireExtinguisherComments, FloorMats, FloorMatsComments, FuseBoxCover, Horn, HornComments, JackLugWrench, JackLugWrenchComments, Keys, KeysComments, RemoteEntry, RemoteEntryComments, SpareTireKey, SpareTireKeyComments, Lighter, LighterComments, Lights, LightsComments, Mirrors, MirrorsComments, ToolKit, ToolKitComments)
VALUES
      ( , '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '')

      
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.OleDb;
using System.Data;
using InfoPathFormService;
using System.Text;


namespace Square1WebService
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://localhost/Square1InfoPath.asmx")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService //Square1InfoPath
    {


        private string InsertQuery()
        {
            var sb = new StringBuilder();
            sb.Append("INSERT INTO VisualCheck");
            sb.Append("(MasterInspection_ID, AirBag, AirbagComments, AntiLock, AntiLockComments, SeatBeltWarning, SeatBeltWarningComments, CheckEngine, CheckEngineComments, TurnSignalIndicator, TurnSignalIndicatorComments, HighBeamIndicator, HighBeamIndicatorComments, OverdriveIndicator, OverdriveIndicatorComments, TOWHaulIndicator, TOWHaulIndicatorComments, ParkingBrake, ParkingBrakeComments, ShiftIndicator, ShiftIndicatorComments, EmergencyFlasher, EmergencyFlasherComments, WindshieldWiperWasher, WindshieldWiperWasherComments, Speakers, SpeakersComments, ManualPowerAntenna, ManualPowerAntennaComments, SunvisorLights, SunvisorLightsComments, OverheadConsoleDisplay, OverheadConsoleDisplayComments, MapLamps, MapLampsComments, GloveCompartmentLamp, GloveCompartmentLampComments, DomeLightCargoLightFloorLight, DomeLightCargoLightFloorLightComments, PowerMirrors, PowerMirrorsComments, PowerSeatControls, PowerSeatControlsComments, FrontDoorPowerLocks, FrontDoorPowerLocksComments, FrontDoorPowerWindow, FrontDoorPowerWindowComments, RearDoorPowerLocks, RearDoorPowerLocksComments, RearDoorPowerWindows, RearDoorPowerWindowsComments, RearWindowWiperWasher, RearWindowWiperWasherComments, HeadLampsLowBeam, HeadLampsLowBeamComments, HeadLampsHighBeam, HeadLampsHighBeamComments, FogLights, FogLightsComments, TurnIndicatorsinMirror, TurnIndicatorsinMirrorComments, SideLamps, SideLampsComments, ParkingLampsTurnLamps, ParkingLampsTurnLampsComments, RearTurnLamps, RearTurnLampsComments, TailLampsStopLamps, TailLampsStopLampsComments, BackUpLamps, BackUpLampsComments, VehicleNumber3, Accessories, Damages, Comments, OverAllStatus)");
            sb.Append("VALUES");
            sb.Append("(@AirBag, @AirbagComments, @AntiLock, @AntiLockComments, @SeatBeltWarning, @SeatBeltWarningComments, @CheckEngine, @CheckEngineComments, @TurnSignalIndicator, @TurnSignalIndicatorComments, @HighBeamIndicator, @HighBeamIndicatorComments, @OverdriveIndicator, @OverdriveIndicatorComments, @TOWHaulIndicator, @TOWHaulIndicatorComments, @ParkingBrake, @ParkingBrakeComments, @ShiftIndicator, @ShiftIndicatorComments, @EmergencyFlasher, @EmergencyFlasherComments, @WindshieldWiperWasher, @WindshieldWiperWasherComments, @Speakers, @SpeakersComments, @ManualPowerAntenna, @ManualPowerAntennaComments, @SunvisorLights, @SunvisorLightsComments, @OverheadConsoleDisplay, @OverheadConsoleDisplayComments, @MapLamps, @MapLampsComments, @GloveCompartmentLamp, @GloveCompartmentLampComments, @DomeLightCargoLightFloorLight, @DomeLightCargoLightFloorLightComments, @PowerMirrors, @PowerMirrorsComments, @PowerSeatControls, @PowerSeatControlsComments, @FrontDoorPowerLocks, @FrontDoorPowerLocksComments, @FrontDoorPowerWindow, @FrontDoorPowerWindowComments, @RearDoorPowerLocks, @RearDoorPowerLocksComments, @RearDoorPowerWindows, @RearDoorPowerWindowsComments, @RearWindowWiperWasher, @RearWindowWiperWasherComments, @HeadLampsLowBeam, @HeadLampsLowBeamComments, @HeadLampsHighBeam, @HeadLampsHighBeamComments, @FogLights, @FogLightsComments, @TurnIndicatorsinMirror, @TurnIndicatorsinMirrorComments, @SideLamps, @SideLampsComments, @ParkingLampsTurnLamps, @ParkingLampsTurnLampsComments, @RearTurnLamps, @RearTurnLampsComments, @TailLampsStopLamps, @TailLampsStopLampsComments, @BackUpLamps, @BackUpLampsComments, @VehicleNumber3, @Accessories, @Damages, @Comments, @OverAllStatus)");

            return sb.ToString();
        }

        [WebMethod]
        public void SubmitForm(myFields newForm)
        {
            using (
                OleDbConnection conn =
                    new OleDbConnection(
                        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\ltorres\\Documents\\Visual Studio 2010\\Projects\\Square1WebService\\AccessDatabase\\Square1_Inspection.accdb;Persist Security Info=False;")
                )

                try
                {
                    using (OleDbCommand command = new OleDbCommand(InsertQuery(), conn))
                    {
                        command.Parameters.Add(new OleDbParameter("001", newForm.AirBag));
                        command.Parameters.Add(new OleDbParameter("002", newForm.AirbagComments));
                        command.Parameters.Add(new OleDbParameter("003", newForm.AntiLock));
                        command.Parameters.Add(new OleDbParameter("004", newForm.AntiLockComments));
                        command.Parameters.Add(new OleDbParameter("005", newForm.SeatBeltWarning));
                        command.Parameters.Add(new OleDbParameter("006", newForm.SeatBeltWarningComments));
                        command.Parameters.Add(new OleDbParameter("007", newForm.CheckEngine));
                        command.Parameters.Add(new OleDbParameter("008", newForm.CheckEngineComments));
                        command.Parameters.Add(new OleDbParameter("009", newForm.TurnSignalIndicator));
                        command.Parameters.Add(new OleDbParameter("010", newForm.TurnSignalIndicatorComments));
                        command.Parameters.Add(new OleDbParameter("011", newForm.HighBeamIndicator));
                        command.Parameters.Add(new OleDbParameter("012", newForm.HighBeamIndicatorComments));
                        command.Parameters.Add(new OleDbParameter("013", newForm.OverdriveIndicator));
                        command.Parameters.Add(new OleDbParameter("014", newForm.OverdriveIndicatorComments));
                        command.Parameters.Add(new OleDbParameter("015", newForm.TOWHaulIndicator));
                        command.Parameters.Add(new OleDbParameter("016", newForm.TOWHaulIndicatorComments));
                        command.Parameters.Add(new OleDbParameter("017", newForm.ParkingBrake));
                        command.Parameters.Add(new OleDbParameter("018", newForm.ParkingBrakeComments));
                        command.Parameters.Add(new OleDbParameter("019", newForm.ShiftIndicator));
                        command.Parameters.Add(new OleDbParameter("020", newForm.EmergencyFlasher));
                        command.Parameters.Add(new OleDbParameter("021", newForm.EmergencyFlasherComments));
                        command.Parameters.Add(new OleDbParameter("022", newForm.WindshieldWiperWasher));
                        command.Parameters.Add(new OleDbParameter("023", newForm.WindshieldWiperWasherComments));
                        command.Parameters.Add(new OleDbParameter("024", newForm.Speakers));
                        command.Parameters.Add(new OleDbParameter("025", newForm.SpeakersComments));
                        command.Parameters.Add(new OleDbParameter("026", newForm.ManualPowerAntenna));
                        command.Parameters.Add(new OleDbParameter("027", newForm.ManualPowerAntennaComments));
                        command.Parameters.Add(new OleDbParameter("028", newForm.SunvisorLights));
                        command.Parameters.Add(new OleDbParameter("029", newForm.SunvisorLightsComments));
                        command.Parameters.Add(new OleDbParameter("030", newForm.OverheadConsoleDisplay));
                        command.Parameters.Add(new OleDbParameter("031", newForm.OverheadConsoleDisplayComments));
                        command.Parameters.Add(new OleDbParameter("032", newForm.MapLamps));
                        command.Parameters.Add(new OleDbParameter("033", newForm.MapLampsComments));
                        command.Parameters.Add(new OleDbParameter("034", newForm.GloveCompartmentLamp));
                        command.Parameters.Add(new OleDbParameter("035", newForm.GloveCompartmentLampComments));
                        command.Parameters.Add(new OleDbParameter("036", newForm.DomeLightCargoLightFloorLight));
                        command.Parameters.Add(new OleDbParameter("037", newForm.DomeLightCargoLightFloorLightComments));
                        command.Parameters.Add(new OleDbParameter("038", newForm.PowerMirrors));
                        command.Parameters.Add(new OleDbParameter("039", newForm.PowerMirrorsComments));
                        command.Parameters.Add(new OleDbParameter("040", newForm.PowerSeatControls));
                        command.Parameters.Add(new OleDbParameter("041", newForm.PowerSeatControlsComments));
                        command.Parameters.Add(new OleDbParameter("042", newForm.FrontDoorPowerLocks));
                        command.Parameters.Add(new OleDbParameter("043", newForm.FrontDoorPowerLocksComments));
                        command.Parameters.Add(new OleDbParameter("044", newForm.FrontDoorPowerWindow));
                        command.Parameters.Add(new OleDbParameter("045", newForm.FrontDoorPowerWindowsComments));
                        command.Parameters.Add(new OleDbParameter("046", newForm.RearDoorPowerLocks));
                        command.Parameters.Add(new OleDbParameter("047", newForm.RearDoorPowerLocksComments));
                        command.Parameters.Add(new OleDbParameter("048", newForm.RearDoorPowerWindows));
                        command.Parameters.Add(new OleDbParameter("049", newForm.RearDoorPowerWindowsComments));
                        command.Parameters.Add(new OleDbParameter("050", newForm.RearWindowWiperWasher));
                        command.Parameters.Add(new OleDbParameter("051", newForm.RearWindowWiperWasherComments));
                        command.Parameters.Add(new OleDbParameter("052", newForm.HeadLampsLowBeam));
                        command.Parameters.Add(new OleDbParameter("053", newForm.HeadLampsLowBeamComments));
                        command.Parameters.Add(new OleDbParameter("054", newForm.HeadLampsHighBeam));
                        command.Parameters.Add(new OleDbParameter("055", newForm.HeadLampsHighBeamComments));
                        command.Parameters.Add(new OleDbParameter("056", newForm.FogLights));
                        command.Parameters.Add(new OleDbParameter("057", newForm.FogLightsComments));
                        command.Parameters.Add(new OleDbParameter("058", newForm.TurnIndicatorsinMirror));
                        command.Parameters.Add(new OleDbParameter("059", newForm.TurnIndicatorsinMirrorComments));
                        command.Parameters.Add(new OleDbParameter("060", newForm.SideLamps));
                        command.Parameters.Add(new OleDbParameter("061", newForm.SideLampsComments));
                        command.Parameters.Add(new OleDbParameter("062", newForm.ParkingLampsTurnLamps));
                        command.Parameters.Add(new OleDbParameter("063", newForm.ParkingLampsTurnLampsComments));
                        command.Parameters.Add(new OleDbParameter("064", newForm.RearTurnLamps));
                        command.Parameters.Add(new OleDbParameter("065", newForm.RearTurnLampsComments));
                        command.Parameters.Add(new OleDbParameter("066", newForm.TailLampsStopLamps));
                        command.Parameters.Add(new OleDbParameter("067", newForm.TailLampsStopLampsComments));
                        command.Parameters.Add(new OleDbParameter("068", newForm.BackUpLamps));
                        command.Parameters.Add(new OleDbParameter("069", newForm.BackUpLampsComments));
                        command.Parameters.Add(new OleDbParameter("070", newForm.VehicleNumber3));
                        command.Parameters.Add(new OleDbParameter("071", newForm.Accessories));
                        command.Parameters.Add(new OleDbParameter("072", newForm.Damages));
                        command.Parameters.Add(new OleDbParameter("073", newForm.Comments));
                        command.Parameters.Add(new OleDbParameter("074", newForm.OverAllStatus));


                        conn.Open();

                        command.ExecuteNonQuery();

                        conn.Close();
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.ToString());
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }

        }


    }
}

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.