?
Solved

Creating DataAdapter UpdateCommand for FoxPro table returns error.

Posted on 2004-09-21
15
Medium Priority
?
308 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
[X]
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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

770 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