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

Creating DataAdapter UpdateCommand for FoxPro table returns error.

Posted on 2004-09-21
15
304 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

838 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