Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

C# connecting to access

Posted on 2011-02-18
5
Medium Priority
?
251 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 400 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 1600 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

These instructions are based on installing Owncloud on your new raspberry pi connected with a usb HDD. What do you need Part A? A Raspberry Pi, model B. A boot SD card for the Raspberry Pi. A usb HDD An Ethernet cable to connect to the lo…
The task of choosing a web design company to build a website for your business should never be taken in a light manner. Provided the fact that your website will act as a representative to your business and will be responsible for imposing an online …
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…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month13 days, 1 hour left to enroll

578 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