Solved

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

Posted on 2007-11-28
11
10,711 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now