Solved

C# connecting to access

Posted on 2011-02-18
5
234 Views
Last Modified: 2013-12-17

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

0
Comment
Question by:Leo Torres
  • 3
5 Comments
 
LVL 12

Assisted Solution

by:HugoHiasl
HugoHiasl earned 100 total points
ID: 34932275
At least you have a missing value in the VALUES line.

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

0
 
LVL 8

Author Comment

by:Leo Torres
ID: 34932338
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
 
LVL 6

Accepted Solution

by:
Ramone_Hamilton earned 400 total points
ID: 34932380
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
 
LVL 8

Author Comment

by:Leo Torres
ID: 34933697
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
 
LVL 8

Author Comment

by:Leo Torres
ID: 34934149
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
To properly understand GitHub, let’s divide it into two words ‘Git’ and ‘Hub’. Git is basically a ‘Distribution Version Control’ (DVC) and ‘Source Code Management’ (SCM) system widely used by software programmers while Hub means the efficient centre…
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now