Solved

Creating DataAdapter UpdateCommand for FoxPro table returns error.

Posted on 2004-09-21
15
302 Views
Last Modified: 2010-04-23
I'm trying to create an UpdateCommand to update my FoxPro table.  The table in FoxPro does not have an ID field but I thought I could build the updateCommand myself.  My code is as follows:

 Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status = @status, " & _
                "rollstatus = @rollstatus, cpono = @cpono, qty = @qty where " & _
                "caddrno = @caddrno and barcode = @barcode", cnVam2)
            Dim prmUpdateSO As New OleDbParameter
            prmUpdateSO = cmdSoMag.Parameters.Add("@status", OleDbType.Char, 2, "status")
            prmUpdateSO = cmdSoMag.Parameters.Add("@rollstatus", OleDbType.Char, 2, "rollstatus")
            prmUpdateSO = cmdSoMag.Parameters.Add("@cpono", OleDbType.Char, 20, "cpono")
            prmUpdateSO = cmdSoMag.Parameters.Add("@caddrno", OleDbType.Char, 10, "caddrno")
            prmUpdateSO = cmdSoMag.Parameters.Add("@barcode", OleDbType.Char, 20, "barcode")
            prmUpdateSO.SourceVersion = DataRowVersion.Original
            daUpdateSo.UpdateCommand = cmdUpdateSO

Is what I'm trying to do even possible?  If so, what am I doing wrong?
0
Comment
Question by:britttd
  • 8
  • 7
15 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12112155
shouldn't it be like this

Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status = @status, " & _
                "rollstatus = @rollstatus, cpono = @cpono, qty = @qty where " & _
                "caddrno = @caddrno and barcode = @barcode", cnVam2)
            cmdupdateSO.Parameters.Add("@status", OleDbType.Char, 2, "status")
            cmdupdateSO.Parameters.Add("@rollstatus", OleDbType.Char, 2, "rollstatus")
            cmdupdateSO.Parameters.Add("@cpono", OleDbType.Char, 20, "cpono")
            cmdupdateSO.Parameters.Add("@caddrno", OleDbType.Char, 10, "caddrno")
            cmdupdateSO.Parameters.Add("@barcode", OleDbType.Char, 20, "barcode")
            daUpdateSo.UpdateCommand = cmdUpdateSO
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12112191
sorry

Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status = @status, " & _
                "rollstatus = @rollstatus, cpono = @cpono, qty = @qty where " & _
                "caddrno = @caddrno and barcode = @barcode", cnVam2)
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@status", OleDbType.Char, 2, "status"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@rollstatus", OleDbType.Char, 2, "rollstatus"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@cpono", OleDbType.Char, 20, "cpono"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@caddrno", OleDbType.Char, 10, "caddrno"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@barcode", OleDbType.Char, 20, "barcode"))
            daUpdateSo.UpdateCommand = cmdUpdateSO
0
 

Author Comment

by:britttd
ID: 12112583
Still getting a 'System.Data.OleDb.OleDbException' error.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113054
If you put a try catch around your code , what is the exact message

try
'yourcode
catch ex as exception
msgbox(ex.message)
end try
0
 

Author Comment

by:britttd
ID: 12113128
I get "Syntax Error".  
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113241
another try

Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status =  ?, " & _
                "rollstatus = ?, cpono = ?, qty = ? where " & _
                "caddrno = ? and barcode = ?", cnVam2)
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"status", OleDbType.Char, 2, "status"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"rollstatus", OleDbType.Char, 2, "rollstatus"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"cpono", OleDbType.Char, 20, "cpono"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"caddrno", OleDbType.Char, 10, "caddrno"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"barcode", OleDbType.Char, 20, "barcode"))
            daUpdateSo.UpdateCommand = cmdUpdateSO
0
 

Author Comment

by:britttd
ID: 12113331
Think it's close now.  I get "SQL Column 'Q0P5' is not found" as an error.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113354
is column Q0P5 part of your table ?
0
 

Author Comment

by:britttd
ID: 12113379
No.  It is not part of the table.  There are other fields in the table, but they would never change so I didn't create parameters for them.  Do I need to create parameters for each field in the table?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113479
do you know what this Q0P5 is then ?
0
 

Author Comment

by:britttd
ID: 12113492
I found this part out.  I left out the parameter "qty".  Once I added it as a numeric I am getting a data type mismatch.  Can't figure this out yet though.  
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 500 total points
ID: 12113576
So what is the error saying now.
0
 

Author Comment

by:britttd
ID: 12113933
Now I am getting a "Concurrency violation.  updatecommand affected 0 records" even show the dataset has changed.  Any ideas?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12120638
Hmmm, concurrency violations, usually means that somebody else has changed the data after you loaded your dataset,
is that possible ? (although I can guess your answer ;-))
0
 

Author Comment

by:britttd
ID: 12122487
No it's not possible for this.  I figured it out though last night.  Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

16 Experts available now in Live!

Get 1:1 Help Now