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
Solved

How to run Update query in Ms access from Vb.net application

Posted on 2007-11-28
11
10,718 Views
Last Modified: 2008-03-24
Hi There,
I have created an application using VB.net 2003 backend Ms access. On my Vb.net application I have a button and on the onclick event,  Im trying to run an Update query( UpdatePO)  (that updates data from one table to another table) that I have in my backend Ms access  database.

The problem is, it did run the update query the first time, but when I do it the next time, I get an ERROR
The changes you requested to the table were not successful becoz they would create duplicate values in the index, primary key or relationship. Change the data in the field that contain duplicate data,remove the index, or redefine to permit duplicate.

The same update query when I run from the ms access it has no problem any number of times I run it. It is not creating any duplicate as it's only an update, but for some reason my onclick event in Vb.net assumes it is going to create duplicate and not just update the existing data.
Im wondering if I some how have to make the code in my Vb.net understand to continueupdate on error.
Please find my onclick event below


Private Sub IMPBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles IMPBTN.Click
       
        Try

            Dim conn As System.Data.OleDb.OleDbConnection

            Dim strConn As String

            strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

            strConn &= "Data Source = F:\Rail.mdb;"

            conn = New System.Data.OleDb.OleDbConnection(strConn)



            Dim cmd As New System.Data.OleDb.OleDbCommand("UpdatePO", conn)

            cmd.CommandType = CommandType.StoredProcedure
                        conn.Open()

            cmd.ExecuteNonQuery()

            conn.Close()

        Catch ex As Exception
            MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub


How can I make it work .I will appreciate any help on this.
Thanks

Private Sub IMPBTN_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles IMPBTN.Click
        ' start1()
        Try
 
            Dim conn As System.Data.OleDb.OleDbConnection
 
            Dim strConn As String
 
            strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"
 
            strConn &= "Data Source = F:\Distribution & Logistics\Railcars\RailTruck Program\Rail.mdb;"
 
            conn = New System.Data.OleDb.OleDbConnection(strConn)
 
 
 
            Dim cmd As New System.Data.OleDb.OleDbCommand("UpdatePO", conn)
 
            cmd.CommandType = CommandType.StoredProcedure
           
            conn.Open()
 
            cmd.ExecuteNonQuery()
 
            conn.Close()
 
 
 
 
 
        Catch ex As Exception
                       MsgBox(ex.Message + vbCrLf + ex.Source & vbCrLf & vbCrLf & ex.StackTrace, MsgBoxStyle.Critical, "Error")
        End Try
    End Sub

Open in new window

0
Comment
Question by:Sivasan
  • 5
  • 5
11 Comments
 
LVL 84
ID: 20370486
Can you post the SQL of UpdatePO?
0
 
LVL 4

Assisted Solution

by:JunnickYso
JunnickYso earned 50 total points
ID: 20371536
yes your UpdatePO we should check that first and
maybe, just maybe your StoredProcedure affects a primary key column, row in the affected table(s).
0
 

Author Comment

by:Sivasan
ID: 20371566
please find the sql for UpdatePO

UPDATE Main RIGHT JOIN Info ON Main.Pon = Info.PO SET Main.Pon = Info.PO, Main.Rstatus = Info.STA, Main.Vno = Info.Vnos, Main.Vnam = Info.Vname, Main.Ter = Info.Term, Main.ItemN = Info.Item, Main.Des1 = Info.ItemDes, Main.Des2 = Info.Desc2, Main.ShipLoc = Info.ShipTo, Main.SVia = Info.ShipVia, Main.Uncost = Info.UCost, Main.Qord = Info.QtyOrd, Main.Ordat = Info.Odate, Main.Reqdat = Info.ReqDate, Main.Recdat = Info.RecpDate, Main.Qrec = Info.QtyRcv, Main.ID = Info.AID;
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 84
ID: 20372849
What are the Primary key fields in tables Main and Info?
0
 

Author Comment

by:Sivasan
ID: 20374879
Hi,

For the table Main the primary key is ID and Info is actually a query. Please find the sql for the query Info

SELECT dbo_POORDHDR_SQL.ord_no AS PO, dbo_POORDHDR_SQL.ord_status AS STA, dbo_POORDLIN_SQL.vend_no AS Vnos, dbo_APVENFIL_SQL.vend_name AS Vname, dbo_APVENFIL_SQL.ap_terms_cd AS Term, dbo_POORDLIN_SQL.item_no AS Item, dbo_POORDLIN_SQL.item_desc_1 AS ItemDes, dbo_POORDLIN_SQL.item_desc_2 AS Desc2, dbo_POORDHDR_SQL.ship_to_cd AS ShipTo, dbo_POORDHDR_SQL.ship_via_cd AS ShipVia, dbo_POORDLIN_SQL.exp_unit_cost AS UCost, dbo_POORDLIN_SQL.qty_ordered AS QtyOrd, dbo_POORDHDR_SQL.ord_dt AS Odate, dbo_POORDLIN_SQL.request_dt AS ReqDate, dbo_POORDLIN_SQL.receipt_dt AS RecpDate, dbo_POORDLIN_SQL.qty_received AS QtyRcv, dbo_POORDLIN_SQL.A4GLIdentity AS AID, dbo_POORDLIN_SQL.ord_status
FROM (dbo_APVENFIL_SQL INNER JOIN dbo_POORDHDR_SQL ON dbo_APVENFIL_SQL.vend_no = dbo_POORDHDR_SQL.vend_no) INNER JOIN dbo_POORDLIN_SQL ON (dbo_APVENFIL_SQL.vend_no = dbo_POORDLIN_SQL.vend_no) AND (dbo_POORDHDR_SQL.vend_no = dbo_POORDLIN_SQL.vend_no) AND (dbo_POORDHDR_SQL.ord_no = dbo_POORDLIN_SQL.ord_no)
WHERE (((dbo_POORDHDR_SQL.ord_status)="P" Or (dbo_POORDHDR_SQL.ord_status)="U") AND ((dbo_POORDHDR_SQL.ship_via_cd)="rr" Or (dbo_POORDHDR_SQL.ship_via_cd)="cc") AND ((dbo_POORDHDR_SQL.ord_dt)>20071001));
0
 
LVL 84
ID: 20375272
When you say you can run the query in Access with no error, do you mean the update runs EVERY time you run the query, or that you can run the query without error BUT the record isn't updated?

Often running queries on a remote connection will produce errors that don't show up in Access, since you can turn off warnings and such in Access (and you cannot do that on a remote connection, at least not easily). The first thing you must do is make SURE that your queries (and all the subqueries) aren't making changes to the data that are unexpected or unwanted; there's a reason the connection is throwing this erorr, and it's up to you to determine where the error is derived from. Check all the queries and subqueries, running each of them individually until you find the one that starts throwing the error, then go back and examine exactly what changed, how the queries/tables are joined, etc etc ...
0
 

Author Comment

by:Sivasan
ID: 20377737
Hi LSM,
When i run the UpdatePO query in the access any number of times it does update the table without any problem. I checked the data, all the rows and columns gets moved from info query to the table main., It does throw a erro window saying
Ms Access didn't update 0 fields due to a type conversion failure, 880 records due to key violations, 0 due to lock violations and 0 records due to validation rule violations.
Do you want to continue running this type of action query anyways- To ignore error click yes.
So when I do Yes, it does infact update the table and no data is lost. I have checked it.
But when I do it from vb.net application, doesn't do it.
I will keep checking on everything you have suggested, but I thought will let you know about this so you can have a clear picture and if you have any suggestion please let me know.
Thanks for all your help.
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 450 total points
ID: 20377971
Okay ... so the query IS, in fact, throwing errors in the Access environment, but you're ignoring them ... this would be why your queries aren't functioning properly in the .NET environment, since you can't really tell the connection to ignore those errors. You can build error handlers that may allow you to do this, but in general I'd suggest you work with the queries until you can make SURE that doing so won't undermine your data integrity.
0
 

Author Comment

by:Sivasan
ID: 20378439
Yes, I'm checking the data. Once I'm satisfied it doesn't affect the dat integrity when I ignorne the error, then I will have to find ways to ignorne that on the Vb.net app.
So how can i write error handlers that may allow me to do this.
Thanks a million for all your help
0
 

Author Comment

by:Sivasan
ID: 20385393
I fixed the issue with the primary key, everything work now.
Thanks a million for all your help
0
 
LVL 84
ID: 20386104
Glad you got it working ... sometimes it's all about the "grunt work" <g>!
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

791 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