oledb database updates on joined queries

here's my code:
       Dim oledbDA As System.Data.OleDb.OleDbDataAdapter
        Dim oleFillComm As System.Data.OleDb.OleDbCommand
        Dim oleUpdComm As System.Data.OleDb.OleDbCommand

        Dim oleDS As DataSet

        oleFillComm = New System.Data.OleDb.OleDbCommand
        oleUpdComm = New System.Data.OleDb.OleDbCommand
        oleDS = New DataSet

        oleconn = New System.Data.OleDb.OleDbConnection
        oledbDA = New System.Data.OleDb.OleDbDataAdapter
        oleconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\ShopHand\ShopHand\ShopHand\slspro.mdb;" & _
            "Persist Security Info=True"
        oleconn.Open()

        oleFillComm.Connection = oleconn

        oleFillComm.CommandText = "SELECT        Stock.VIN, Customers.LastName, Customers.FirstName, Customers.MiddleInitial, Customers.Address1, Customers.Address2, Customers.City, " & _
        "Customers.State, Customers.Zip, Customers.EmailAddress, Customers.CellPhone, Customers.HomePhone, Customers.WorkPhone, Customers.Fax, Customers.County, " & _
        "Stock.StockNum, Stock.Make, Stock.Model, Stock.ModelCode, Stock.[Memo], Stock.Body, Stock.Color, Stock.Engine, Stock.MilesIn, Stock.ExistingPlate, Stock.DeliveryDate, " & _
        "Customers.Do_Not_Call, Customers.IsBusiness, Customers.IsCurrent, Customers.BaseAddressID, Customers.ActiveDate, Customers.SocialSecurity, Customers.CountyCode, Customers.DateOfBirth, Customers.DriversLicense, " & _
        "Customers.DriversLicenseState, Customers.LastWriteCount, Customers.DriversLicenseExpiration, Stock.InventoryID, Stock.AdjustedCost, Stock.AdjustedMsrp, Stock.Adv, Customers.IsEmployee, Customers.Notes, Customers.PayrollDeduction, " & _
        "Stock.AppendTimeStamp, Stock.BookValue, Stock.BrokerAmount, Stock.BrokerID, Stock.Buyer,Stock.ColorCode, Stock.Cost, Stock.DMVType, Stock.EquipmentOptions, " & _
        "Stock.Freight, Stock.HoldBack, Stock.Ignition, Stock.IgnitionLocation, Stock.InvoiceDate, Stock.InvoiceNumber, Stock.Location, Stock.MileageType,  Stock.MilesOut, " & _
        "Stock.MSRP, Stock.NewVehicle, Stock.Pack, Stock.PictureFilename, Stock.PurchasedFrom, Stock.SellingPrice, Stock.SoldToBrokerAmount, Stock.SoldToBrokerID, Stock.StockDate, " & _
        "Stock.TitleNumber, Stock.TitleType, Stock.TradeACV, Stock.TradeAddToStock, Stock.TradeAllowance, Stock.TradeBookValue, Stock.TradeLien, Stock.Transmission, Stock.Trunk, " & _
        "Stock.TrunkLocation, Stock.WebPrice, Stock.Weight, Stock.[Year], Stock.VehicleStatus, Stock.IsOnFloorPlan, Stock.WasDemo, Stock.ExistingPlateExpires, " & _
        "Stock.FloorPlanCompanyOID, Stock.FloorPlanCompanyName, Stock.FloorPlanRate, Stock.FloorPlanGrace, Stock.FlooredAmount, Stock.IncludeFloorPlanInAdjCost, Stock.FloorPlanEndDate, Stock.FloorPlanAccum, " & _
        "Stock.InspectionStationNumber, Stock.WebDescription1, Stock.WebDescription2, Stock.WebDescription3, Stock.WebDescription4, Stock.WebDescription5, Stock.WebDescription6, Stock.WasWholesale, " & _
        "Stock.FormattedStockNum, Stock.IsRepo, Stock.NumCylinders, Stock.NumDoors, Stock.FuelType, Stock.InteriorColor, Stock.InteriorType, Stock.IsCertified, Stock.AutoRevoEquipment, " & _
        "Stock.AutoTraderEquipment, Stock.IsSpecial, Stock.SpecialExpires, Stock.SpecialPrice, Stock.SeatingCapacity, Stock.LastWriteCount AS Expr1, Stock.VehicleNotes, Stock.DataFeeds_DoNotSend, Stock.VideoURL, " & _
        "Stock.SegmentationCode, Stock.QRCode, Stock.TitleOrigin, Stock.VIN + ' ' + Customers.LastName + ',  ' + Customers.FirstName + ' ' + Customers.MiddleInitial AS VINDisplay, " & _
        "Customers.LastName + ',  ' + Customers.FirstName + ' ' + Customers.MiddleInitial + ' ' + Stock.VIN AS LastNameDisplay, Deals.DealID, Deals.ApprovalNumber, Deals.Buyer AS Expr2, Deals.CoBuyer, " & _
        "Deals.CoSigner, Deals.CustomerSource, Deals.DealDate, Deals.DealNumber, Deals.DealStatus, Deals.DealType, Deals.DeliveredDate, Deals.FinanceInfo, Deals.FinancingType, Deals.InsuranceCompany, " & _
        "Deals.InsurancePolicyNum, Deals.IsDelivered, Deals.LenderApprovalNumber, Deals.LenderID, Deals.ManagerID, Deals.Misc, Deals.PayrollDeductID, Deals.PlateExpireDate, Deals.PlateLevelWt, " & _
        "Deals.PlateMonth, Deals.PlateNumber, Deals.PlateType, Deals.PolicyNumber, Deals.SalesmanID, Deals.TaxExemptID, Deals.Trade1, Deals.Trade2, Deals.VehicleSold, Deals.TempReg, Deals.Trade1Leinholder, " & _
        "Deals.Trade2Leinholder, Deals.BuyerCurrentAddress, Deals.InsuranceEffectiveFrom, Deals.InsuranceEffectiveTo, Deals.CreditApp, Deals.CreditAppGUID, Deals.DrivewayAppID, Deals.DrivewaySubmissionDate, " & _
        "Deals.LocalLenderAppID, Deals.FundingStatus, Deals.FundingDate, Deals.LastWriteCount AS Expr3, Deals.LicensePlateCode, Customers.CustomerID " & _
"FROM            ((Customers INNER JOIN " & _
                         "Stock ON Customers.CustomerID = Stock.PurchasedFrom) INNER JOIN " & _
                         "Deals ON Stock.InventoryID = Deals.VehicleSold) " & _
"ORDER BY Customers.LastName"

        oledbDA.SelectCommand = oleFillComm
        oledbDA.Fill(oleDS)

        Me.DataGridView1.DataSource = oleDS.Tables(0)


        oleconn.Close()

Open in new window


I'm loading the DATAGRIDVIEW and everything is fine with sorts, etc
my problem is when I want to update the TABLES in the database

Can anyone HELP with the update command?
MBHEY131Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JHMH IT StaffCommented:
What are you updating; are you adding new records or updating existing records?
0
MBHEY131Author Commented:
Expert Comment

JHMH IT Staff2015-06-04 at 04:06:54ID: 40812261




What are you updating; are you adding new records or updating existing records?
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
BOTH
===================================================================
0
MBHEY131Author Commented:
HERE'S my connection string:

       oleconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\ShopHand\ShopHand\ShopHand\slspro.mdb;" & _
            "Persist Security Info=True"
        oleconn.Open()
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MBHEY131Author Commented:
it appears I'm going to have to write the update text commands manually but how?
0
JHMH IT StaffCommented:
You're creating your view through a join, so the best thing I can recommend is to also pull the primary key of each record from each table, then programmatically hide those columns; they'll be used later to assist in your update queries. Then you'll need to loop through all the rows in your datagridview, updating each table's associated entries from the row.

For instance:

Dim cmdCommand as New SqlCommand
Dim sql As String

For i As Integer = 0 to DataGridView1.Rows.Count - 1

     cmdCommand = New SqlCommand
     With cmdCommand
          .Connection = con
          sql = "UPDATE Table1 SET Value1 = " & col1.Value & ", Value2 = " & col2.Value & " WHERE           (key = " & hidCol1.Value & ") "
          .CommandText = sql
          .CommandType = CommandType.Text
     End With
     con.Open()
     cmdCommand.ExecuteNonQuery()
     con.Close()

     cmdCommand = New SqlCommand
     With cmdCommand
          .Connection = con
          sql = "UPDATE Table2 SET Value1 = " & col3.Value & ", Value2 = " & col4.Value & " WHERE (key = " & hidCol2.Value & ") "
          .CommandText = sql
          .CommandType = CommandType.Text
     End With
     con.Open()
     cmdCommand.ExecuteNonQuery()
     con.Close()

Next

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MBHEY131Author Commented:
that's what I was afraid you were going to tell me:

Here's my plan - I am not going to change any code to this point - it just works too well and is exactly what everyone want's - My problem is on the update to the databases and I will make 3 defferent data adapters and datasets and copy each row (and column) to the appropriate dataset - which is what I think you were getting at in your post above
and then update with the update command!
thanx
0
MBHEY131Author Commented:
loop through the datagrid1 I mean
0
JHMH IT StaffCommented:
Yep, once you move away from the "built in" datagridview components you have to do a lot of manual labor to program in the other queries such as inserts and updates. Good luck!
0
MBHEY131Author Commented:
Expert Comment
JHMH IT Staff2015-06-04 at 06:18:57ID: 40812551
Yep, once you move away from the "built in" datagridview components you have to do a lot of manual labor to program in the other queries such as inserts and updates. Good luck!
+++++++++++++++++++++++++
this database is not mine as I am tying into another's data for integration purposes and I have very little room to maneuver here - so it's not really by choice but by customer and economic necessity!!
======================================
0
JHMH IT StaffCommented:
No, I meant the built in database functions in Visual Studio. When you add a DataGridView you can manually program it or attach a dataset through Visual Studio's database wizard.
0
MBHEY131Author Commented:
yes I know what you meant and although a novice programmer and just getting up to date on databases but I do have many datagrids that attach to "MY" database and yes the updates are defiantly easier but this data is oledb as opposed to sql and I really need the query to get more than 1 table in that dataset.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

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

Have a better answer? Share it in a comment.