Solved

C# connecting to access

Posted on 2011-02-18
5
235 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
New Relic recently released its Synthetics product that allows for the creation of performance monitors that periodically test a site's performance. If you wish to test an interactive workflow New Relic employs Selenium WebDriverJS to run those test…
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

920 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

16 Experts available now in Live!

Get 1:1 Help Now