Solved

C# connecting to access

Posted on 2011-02-18
5
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Header Font Size in Grid View 6 44
Set arraylist as datatable 2 26
VB.Net Developer - Suggestions for Web Programming OnLine Learning 4 57
Call to SQL server times out 5 50
Online collaboration is quickly becoming embedded in the workplace, and its benefits are tangible. See what the current landscape looks like and what the future holds for collaboration tools and the future of work.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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…

739 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