Solved

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

Posted on 2007-11-28
11
10,713 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

930 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

10 Experts available now in Live!

Get 1:1 Help Now